Re: Syntax error on insert from ASP to Access. Help please !

[Bob Barrows [MVP]]

Lee Holsenbeck wrote:
> hi,
>
> i am trying to write records from an ASP page to a access database,
> but get an error on my insert statement, but not on a select
> statement. please let me know if you can see what i'm doing wrong,
> code is below:
>
> strFirstName = Request.Form("FirstName")
> strLastName = Request.Form("LastName")
> strCompanyName = Request.Form("CompanyName")
> strTelephone = Request.Form("Telephone")
> strFAX = Request.Form("FAX")
> strStreetAddress = Request.Form("StreetAddress")
> strCity = Request.Form("City")
> strStateProvince = Request.Form("StateProvince")
> strZipPostalCode = Request.Form("ZipPostalCode")
> strUsername = Request.Form("Username")
> strPassword = Request.Form("Password")
>
> curDir = Server.MapPath("\registrationdb.mdb")
>
> set oConn = Server.CreateObject("ADODB.Connection")
> oConn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" &
> "C:\RegistrationDB.mdb" & ";"
> Set oRs = Server.CreateObject("ADODB.Recordset")

Do NOT use an expensive recordset object to run a query that will not return
records! Use the Connection's Execute method, and use the Execute options
argument to let ADO know that you are not expecting any records back. See
below

> Set oRs.ActiveConnection = oConn
> oRs.Source = "INSERT INTO tblCustomerInfo (FirstName, LastName,
> CompanyName, Telephone, FAX, StreetAddress, City, StateProvince,
> ZipPostalCode, Username, Password) VALUES ('" & strFirstName & "', '"
> & strLastName & "', '" & strCompanyName & "', '" & strTelephone & "',
> '" & strFAX & "', '" & strStreetAddress & "', '" & strCity & "', '" &
> strStateProvince & "', '" & strZipPostalCode & "', '" & strUsername &
> "', '" & strPassword & "')"
> oRs.Open

If you wish to keep using dynamic sql, then you are going to need to learn
to debug it. You need to use Response.Write to see the result of your
concatenation. It is always best to assign the concat result to a variable
to make it easier to handle. Like this:

sSQL = "INSERT ... "', '" & strPassword & "')"
'comment out the next line when finished debugging
Response.Write sSQL

oConn.Execute sSQL..129

The 129 is a combination of two constants: 1 (adCmdText - it tells ADO you
are executing a string containing a sql statement) and 128
(adExecuteNoRecords - it tells ADO not to construct a recordset object to
receive results)

However, you should make it easy on yourself and learn to use parameters.
Try this:

dim cmd,sSQL, arParms(10), oConn

arParms(0) = Request.Form("FirstName")
arParms(1)= Request.Form("LastName")
arParms(2)= Request.Form("CompanyName")
arParms(3)= Request.Form("Telephone")
arParms(4)= Request.Form("FAX")
arParms(5)= Request.Form("StreetAddress")
arParms(6)= Request.Form("City")
arParms(7)= Request.Form("StateProvince")
arParms(8)= Request.Form("ZipPostalCode")
arParms(9)= Request.Form("Username")
arParms(10)= Request.Form("Password")

'open connection as above, then:

sSQL = "INSERT INTO tblCustomerInfo (FirstName, LastName," & _
"CompanyName, Telephone, FAX, StreetAddress, City, " & _
"StateProvince,ZipPostalCode, Username, Password) VALUES " & _
"(?,?,?,?,?,?,?,?,?,?,?)"
'there's 11 ?'s there, right ;-)

set cmd=createobject("adodb.command")
cmd.commandtext = sSQL
set cmd.activeconnection=oConn
cmd.Execute ,arParms,129

See? No delimiters to worry about ... simple

HTH,
Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Lee Holsenbeck]

hi,

i am trying to write records from an ASP page to a access database, but get
an error on my insert statement, but not on a select statement. please let me
know if you can see what i'm doing wrong, code is below:

strFirstName = Request.Form("FirstName")
strLastName = Request.Form("LastName")
strCompanyName = Request.Form("CompanyName")
strTelephone = Request.Form("Telephone")
strFAX = Request.Form("FAX")
strStreetAddress = Request.Form("StreetAddress")
strCity = Request.Form("City")
strStateProvince = Request.Form("StateProvince")
strZipPostalCode = Request.Form("ZipPostalCode")
strUsername = Request.Form("Username")
strPassword = Request.Form("Password")

curDir = Server.MapPath("\registrationdb.mdb")

set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" &
"C:\RegistrationDB.mdb" & ";"
Set oRs = Server.CreateObject("ADODB.Recordset")