syntax error

I am getting the following error when trying to insert form data into an
Access db:

80040e14|[Microsoft][ODBC_Microsoft_Access_Driver]_Syntax_error_in_string_in_query_expression_''++++_)'. 500

I've traced it down to the following code:

'insert records into db
Set con = Server.CreateObject( "ADODB.Connection" )
con.Open "webLeads"

SQLstring = "INSERT INTO studentLeads ( la_fName, la_mName, la_lName,
la_street, " &_
" la_city, la_state, la_zip, la_country, la_email, la_phone, la_callTime,
la_dob, la_age, " &_
" la_education, la_intArea, la_message) VALUES ('" & fixQuotes( form_fName )
& "','" & fixQuotes( form_mName ) & "', " &_
" '" & fixQuotes( form_lName ) & "','" & fixQuotes( form_street ) & "','" &
fixQuotes( form_city ) & "', " &_
" '" & fixQuotes( form_stateID ) & "','" & fixQuotes( form_zip ) & "','" &
fixQuotes( form_country ) & "', " &_
" '" & fixQuotes( form_email ) & "','" & fixQuotes( form_phone ) & "','" &
fixQuotes( form_callTime ) & "', " &_
" '" & fixQuotes( form_dob ) & "','" & fixQuotes( form_age ) & "','" &
fixQuotes( form_education ) & "', " &_
" '" & fixQuotes( form_intArea ) & "','" & fixQuotes( form_message ) & " )"

con.Execute(SQLstring)
con.Close

I've also tried it w/ DSN-less connection, w/ same result. Variables are
assigned and validated as follows:

'get form data and remove any spaces
form_fName=trim(Request.Form("fName"))
form_mName=trim(Request.Form("mName"))
form_lName=trim(Request.Form("lName"))
form_street=trim(Request.Form("street"))
form_city=trim(Request.Form("city"))
form_stateID=trim(Request.Form("stateID"))
form_zip=trim(Request.Form("zip"))
form_country=trim(Request.Form("country"))
form_email=trim(Request.Form("email"))
form_phone=trim(Request.Form("phone"))
form_callTime=trim(Request.Form("callTime"))
form_dob=trim(Request.Form("dob"))
form_age=trim(Request.Form("age"))
form_education=trim(Request.Form("education"))
form_intArea=trim(Request.Form("intArea"))
form_message=trim(Request.Form("message"))

'validate form data
IF len(form_fname)< 1 THEN
validated_form = false
ELSE
validated_form = true
END IF

IF len(form_mname) < 1 THEN
validated_form = false
ELSE
validated_form = true
END IF

IF len(form_lname) < 2 THEN
validated_form = false
ELSE
validated_form = true
END IF

IF len(form_street) < 5 THEN
validated_form = false
ELSE
validated_form = true
END IF

IF len(form_city) < 2 THEN
validated_form = false
ELSE
validated_form = true
END IF

IF len(form_zip) < 5 THEN
validated_form = false
ELSE
validated_form = true
END IF

IF (form_country) = "" THEN
form_country = "U.S."
END IF

IF len(form_email) < 6 THEN
validated_form = false
ELSE
validated_form = true
END IF

IF len(form_phone) < 7 THEN
validated_form = false
ELSE
validated_form = true
END IF

IF (form_dob) = "" THEN
form_dob = "not given"
END IF

IF (form_age) = "" THEN
form_age = "not given"
END IF

IF (form_intArea)= "" THEN
validated_form = false
ELSE
validated_form = true
END IF

IF (form_message) = "" THEN
form_message = "no message indicated"
END IF

All form entries are defined as <input type="text"> and also as text fields
in the db. There is also a "fixquotes" function that takes care of the '
problem (have used this function over and over in many scripts.

I'm sure there's something simple I'm missing here, but maybe I've been
looking at it too long. The validation works for return values, and correctly
sends email to the user and to someone in the organization. Can anyone see
anything in my code that is causing the error and keeping the data out of the
db?

TIA...

ba

LakeGator
Mon Dec 26 09:38:22 CST 2005

Have you displayed the value of the SQLstring variable? It seems quite
possible that it contains the invalid syntax. You would probably need
to comment out the con.Execute(SQLstring) statement or exit the code
prior to reaching it.

BA
Mon Dec 26 10:02:02 CST 2005

I did a response.write(varName) for each variable and commented out the
entire sequence from SQLstring = INSERT to con.execute, and the values
display as expected. Errors are also reported correctly. Just can't seem to
get the values into the db.

ba

"LakeGator" wrote:

> Have you displayed the value of the SQLstring variable? It seems quite
> possible that it contains the invalid syntax. You would probably need
> to comment out the con.Execute(SQLstring) statement or exit the code
> prior to reaching it.
>
>

LakeGator
Mon Dec 26 10:37:01 CST 2005

The value of the SQLstring variable is the proof of the pudding while
the individual field variables are just ingredients. The syntax error
is likely being generated in the build of the SQLstring string. It is
not certain that doing a Response.Write(SQLstring) will reveal the
error but it will eliminate one possibility, at least.

Bob
Mon Dec 26 10:44:46 CST 2005

Show us the result of
response.write(SQLstring)

We can't debug a sql statement without seeing what it is.

If you've created it correctly, you should be able to copy it to the
clipboard from the browser window, open your database in Access, create a
new query in SQL View, paste it in and run it without modification. This
should always be your first step when experiencing problems running a query
from ASP. You may luck out and get a more helpful error message from Access
than the one reported by the ODBC driver(incidently, youshould switch to the
more currecnt OLE DB provider - http://www.aspfaq.com/show.asp?id=2126)

There is likely to be a problem with one of your delimiters, which is the
one thing that screws up most initial attmpts at using dynamic sql.
Unfortunately, dynamic sql, one of the worst techniques for executing sql
statements, is usually the first technique a beginner sees. Here are a few
posts I've made on the subject, in which I try to make clear why using
parameters is so superior to building dynamic sql statements:

http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl

http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/b3d322b882a604bd

Using Command object to parameterize CommandText:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

SQL Injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

Bob Barrows

BA wrote:
> I did a response.write(varName) for each variable and commented out
> the entire sequence from SQLstring = INSERT to con.execute, and the
> values display as expected. Errors are also reported correctly. Just
> can't seem to get the values into the db.
>
> ba
>
> "LakeGator" wrote:
>
>> Have you displayed the value of the SQLstring variable? It seems
>> quite possible that it contains the invalid syntax. You would
>> probably need
>> to comment out the con.Execute(SQLstring) statement or exit the code
>> prior to reaching it.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

BA
Mon Dec 26 11:07:02 CST 2005

Did a response.write(SQLstring) and it returned the following:

INSERT INTO studentLeads ( la_fName, la_mName, la_lName, la_street, la_city,
la_state, la_zip, la_country, la_email, la_phone, la_callTime, la_dob,
la_age, la_education, la_intArea, la_message) VALUES ('bob','g', 'smith','123
any st','tampa', 'FL','33333','U.S.',
'bappel@bayway.net','333-999-6541','Any', 'not given','not given','Still in
High School', 'Cosmetology, Cosmetology w/ Facials, Cosmetology w/ Nails,
Cosmetology w/ Bootcamp','no message included. )

which indicates a missing ' at the end of the string, just as you suspected.
And yes, those delimiters always kick my a**, specifically when the strings
are so long they need to be on subsequent lines. I'm not exactly a beginner,
but I don't do this stuff every day, and I'm a couple of years behind on some
of the stuff (I used to use Access97 on IIS4, and most of my code was written
for that).

The immediate problem is resolved, but I will go in and take a look at all
the articles you've noted. I've been reading a lot of stuff over the last
week or so trying to get current, so this will surely help.

Thanks guys...

ba

"Bob Barrows [MVP]" wrote:

> Show us the result of
> response.write(SQLstring)
>
> We can't debug a sql statement without seeing what it is.
>
> If you've created it correctly, you should be able to copy it to the
> clipboard from the browser window, open your database in Access, create a
> new query in SQL View, paste it in and run it without modification. This
> should always be your first step when experiencing problems running a query
> from ASP. You may luck out and get a more helpful error message from Access
> than the one reported by the ODBC driver(incidently, youshould switch to the
> more currecnt OLE DB provider - http://www.aspfaq.com/show.asp?id=2126)
>
> There is likely to be a problem with one of your delimiters, which is the
> one thing that screws up most initial attmpts at using dynamic sql.
> Unfortunately, dynamic sql, one of the worst techniques for executing sql
> statements, is usually the first technique a beginner sees. Here are a few
> posts I've made on the subject, in which I try to make clear why using
> parameters is so superior to building dynamic sql statements:
>
> http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl
>
> http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl
>
> http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/b3d322b882a604bd
>
> Using Command object to parameterize CommandText:
> http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e
>
> SQL Injection:
> http://mvp.unixwiz.net/techtips/sql-injection.html
> http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
>
> Bob Barrows
>
> BA wrote:
> > I did a response.write(varName) for each variable and commented out
> > the entire sequence from SQLstring = INSERT to con.execute, and the
> > values display as expected. Errors are also reported correctly. Just
> > can't seem to get the values into the db.
> >
> > ba
> >
> > "LakeGator" wrote:
> >
> >> Have you displayed the value of the SQLstring variable? It seems
> >> quite possible that it contains the invalid syntax. You would
> >> probably need
> >> to comment out the con.Execute(SQLstring) statement or exit the code
> >> prior to reaching it.
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>
>