Unable to insert record to Access via SQL from an ASP page

M

mialjojo

I am using an ASP page to insert a record to Access and it is not working. It
appears that the SQL is trying to open the database (based on the file date
and time stamp) but it is not inserting the record. I am not getting any
error messages.

Below is the code from the form handler:

<%
If request.form("FirstName")="" then
Response.redirect("ul_reg.asp")
End If
%>
<html>

<head>
<meta http-equiv="pragma" content="no-cache>
<meta http-equiv=" content="text/html; charset=windows-1252" Content-Type">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>Send Registration to Database & Email</title>


</head>

<body>
Please be patient while your registration is being processed (Do not hit the
back key).......<P>
<%

'//////////////////////////////////////////////////////////
'// The ParseBody function is used to replace Chr(13)
'// with HTML so that it will display correctly on the
'// confirmation page.
'//////////////////////////////////////////////////////////
Function ParseBody(strText)

strText = Replace(strText, Chr(13), "<br>")

ParseBody = strText

End Function


'//////////////////////////////////////////////////////////
'// Send results to the database
'// This portion of the page sends the information
'// from the form to the Requirements database.
'//////////////////////////////////////////////////////////

'Set up the variables
Dim myConnString
Dim myConnection
Dim SQL
strFirstName = request.form("FirstName")
strLastName = request.form("LastName")




'Assign a value to the connection string variable.
myConnString = "Driver={Microsoft Access Driver (*.mdb)};DBQ=" &
Server.MapPath("../../mutable/2005_Conf.mdb") & ";"

'Create a new database connection
Set myConnection = Server.CreateObject("ADODB.Connection")

'Open the connection created above using the connection string
'stored in the conf_ConnectionString variable in the
'global.asa file.
myConnection.Open myConnString

'Strip Single Quotes
strLastName = Replace(strLastName, "'", "''")
strFirstName = Replace(strFirstName, "'", "''")

'Assign a SQL string based upon what was submitted from the form
SQL= "INSERT INTO tblReservations"
SQL= SQL &
"(RegType,confMem,confFee,MealFee,Social,SocialFee,Game,GameFee,Grade,FirstName,LastName,SSAN,Unit,Base,DutyStatus,DSNPhone,EmailNIPR,EmailSIPR,Arrival,Departure,Breakout_1,Breakout_2,Breakout_3,Breakout_4,Breakout_5,Breakout_6,Breakout_7,Breakout_8,AFPC,Banquet,Award,BanquetGuest,QuantityBeef,QuantityChicken,QuantityVeg)"
SQL= SQL & "VALUES ('" & Request.Form("RegType") & "','"
SQL= SQL & Request.Form("confMem") & "','"
SQL= SQL & Request.Form("confFee") & "','"
SQL= SQL & Request.Form("MealFee") & "','"
SQL= SQL & Request.Form("Social") & "','"
SQL= SQL & Request.Form("SocialFee") & "','"
SQL= SQL & Request.Form("Game") & "','"
SQL= SQL & Request.Form("GameFee") & "','"
SQL= SQL & Request.Form("Grade") & "','"
SQL= SQL & strFirstName & "'"
SQL= SQL & ",'" & strLastName & "'"
SQL= SQL & ",'" & Request.Form("SSAN") & "'"
SQL= SQL & ",'" & Request.Form("Unit") & "','"
SQL= SQL & Request.Form("Base") & "','"
SQL= SQL & Request.Form("DutyStatus") & "','"
SQL= SQL & Request.Form("DSNPhone") & "','"
SQL= SQL & Request.Form("EmailNIPR") & "','"
SQL= SQL & Request.Form("EmailSIPR") & "','"
SQL= SQL & Request.Form("Arrival") & "','"
SQL= SQL & Request.Form("Departure") & "','"
SQL= SQL & Request.Form("Breakout_1") & "','"
SQL= SQL & Request.Form("Breakout_2") & "','"
SQL= SQL & Request.Form("Breakout_3") & "','"
SQL= SQL & Request.Form("Breakout_4") & "','"
SQL= SQL & Request.Form("Breakout_5") & "','"
SQL= SQL & Request.Form("Breakout_6") & "','"
SQL= SQL & Request.Form("Breakout_7") & "','"
SQL= SQL & Request.Form("Breakout_8") & "','"
SQL= SQL & Request.Form("AFPC") & "','"
SQL= SQL & Request.Form("Banquet") & "','"
SQL= SQL & Request.Form("Award") & "','"
SQL= SQL & Request.Form("BanquetGuest1") & "','"
SQL= SQL & Request.Form("qbeef") & "','"
SQL= SQL & Request.Form("qchicken") & "','"
SQL= SQL & Request.Form("qveg") & "')"

myConnection.Execute SQL

I'm using Dreamweaver 2004 MX and ACCESS 2003 on a Windows XP machine.
 
N

Norman Yuan

What does it exactly mean, "Unable to insert"? Do you get any error message?
does the code go all the way through from myConnection.Open to
mConnection.Close()? Are you sure your LONG "INSERT INTO" statement is
correct? The code you posted has at lease 2 spaces missing after table name
and before "VALUES" in INSERT INTO tblxxxx <mising space here>(......)
<missing space here> VALUES (....). You should have gotten error message
when "myConnection.Exceute SQL" is called.
 
M

mialjojo

I don't get an error message. I'm not sure the statement is correct. I'll
correct the errors you pointed out. Thank you.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

rewriting with refresh 1
Send Form results to database error 0
Sending form results to database and email 0
ASP - Cannot insert form result to Access DB more than one records 1
Access Database record added via asp - but it is not inserted 3
using VBA to insert records from access database to sql server 8

Top