help on insert statement

[Peanut]

I keep getting the following error on my insert statement:

Microsoft OLE DB Provider for SQL Server- Error '80040e14'

Incorrect syntax near ','.

line 207

On this line is the command1.execute

Code is as follows:

<%
varSchoolID = Request.QueryString("sid")
varAppliedForEasy = "1"
varHead = "1"
varRegistrar = "0"
varDietaryReq = Request.Form("DietaryReq")
varTitle = Request.Form("HeadTitle")
varFirstName = Request.Form("CHeadFirstName")
varLastName = Request.Form("CHeadLastName")
varJobTitle = Request.Form("CHeadJobTitle")
varEmail = Request.Form("CHeadEmail")
varTelephone = Request.Form("CHeadTelephone")
varFax = "NULL"
if(Request.Form("NewEvents") <> "") then
varEvents = Request.Form("NewEvents")
varEvents = "'" & Replace(varEvents, "'", "''") & "'"
Else
varEvents = "NULL"
End If
if(Request.Form("UpdateStep3") <> "") then
varUpdateStep3 = Request.Form("UpdateStep3")
varUpdateStep3 = "'" & Replace(varUpdateStep3, "'", "''") & "'"
Else
varUpdateStep3 = "NULL"
End If
%>

<%

set Command1 = Server.CreateObject("ADODB.Command")
Command1.ActiveConnection = MM_SecAct_STRING
Command1.CommandText = "INSERT INTO dbo.secSchoolContact (Head, Registrar,
SchoolID, AppliedForEasy, DietaryReq, Title, FirstName, LastName, JobTitle,
Email, Telephone, Fax, Events) VALUES (1, 0, " & varSchoolID & ", 1, " &
varDietaryReq & ", " & varTitle & ", " & varFirstName & ", " & varLastName &
", " & varJobTitle & ", " & varEmail & ", " & varTelephone & ", " & varFax &
", " & varEvents & ")"
Command1.CommandType = 1
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Execute()

%>

Please help it is driving me mad and I know it is probably a stupid
mistake!!

[Bob Barrows]

Peanut wrote:
> I keep getting the following error on my insert statement:
>
> Microsoft OLE DB Provider for SQL Server- Error '80040e14'
>
> Incorrect syntax near ','.
>
> line 207
>
> On this line is the command1.execute
>
> Code is as follows:
>
>

> set Command1 = Server.CreateObject("ADODB.Command")
> Command1.ActiveConnection = MM_SecAct_STRING

This is a bad practice promoted by the use of crutches like dreamweaver.
Always explicitly create a connection object and use the connection string
to open it rather than forcing ADO to create an implicit connection behind
the scenes. That way you are in control over when the connection is closed
and destroyed.

Also, there has been no need to use the Server's CreateObject method for
years. You can now simply use the CreateObject method that is supplied by
the vbscript runtime library:

Set cn=CreateObject("ADODB.Connection")
cn.open MM_SecAct_STRING
set Command1 = CreateObject("ADODB.Command")
set Command1.ActiveConnection=cn

> Command1.CommandText = "INSERT INTO dbo.secSchoolContact (Head,
> Registrar, SchoolID, AppliedForEasy, DietaryReq, Title, FirstName,
> LastName, JobTitle, Email, Telephone, Fax, Events) VALUES (1, 0, " &
> varSchoolID & ", 1, " & varDietaryReq & ", " & varTitle & ", " &
> varFirstName & ", " & varLastName & ", " & varJobTitle & ", " &
> varEmail & ", " & varTelephone & ", " & varFax & ", " & varEvents &
> ")" Command1.CommandType = 1
> Command1.CommandTimeout = 0
> Command1.Prepared = true
> Command1.Execute()
>
> %>
>

You cannot debug a syntax error in a sql statement without knowing what the
sql statement is. Best practice when forced to use dynamic sql is to always
assign the string containing the resulting sql statement to a variable, so
you can easily output the statement when you need to look at it. Like this:

sql= "INSERT INTO dbo.secSchoolContact (Head, Registrar, " & _
"SchoolID, AppliedForEasy, DietaryReq, Title, FirstName, " & _
"LastName, JobTitle, Email, Telephone, Fax, Events), " & _
" VALUES (1, 0, " & varSchoolID & ", 1, " & varDietaryReq & _
", " & varTitle & ", " & varFirstName & ", " & varLastName & _
", " & varJobTitle & ", " & varEmail & ", " & varTelephone & ", " & _

varFax & ", " & varEvents & ")"

'comment out these 2 lines when finished debugging
response.write sql
response.end

Command1.Execute sql,,129
cn.close: set cn=nothing

The 129 is the combination of 1 (adCmdText) and 128 (adExecuteNoRecords) -
always tell ADO not to create a recordset behind the scenes when you know
the sql statement will not return records.

Run the code and inspect the resulting sql statement in the browser window.
Does any problem stand out? If not, copy it to the clipboard and paste it
into Query Analyzer (pre-SQL2005) or SSMS and try to execute it. The error
message you get may be more helpful. Still can't see the problem? Post the
statement here along with the datatypes of the columns into which the data
is being inserted.

Further points to consider:
Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

Personally, I prefer using stored procedures:
http://groups.google.com/group/microsoft.public.inetserver.asp.general/msg/5d3c9d4409dc1701?hl=en&

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
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"