Avatar of prunesquallor
 asked on

Classic ASP/VBScript/ADO MSSQL Insert failure with parameters

Using ASP/VBScript/ADO on an SQL Server 2012 database via IIS 7.5.

I'm trying to rework someone else's code to convert an insert to a parametized operation. After much fooling about, I've managed to get all the parameter definitions not to throw an error, and now get this error on the execute:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ')'.
/HDWASPForm2Database/Form2Database.asp, line 1123

So my syntax is bad. But I can't find the error.

The original non-parametized call looks like this:
DataC.Execute("INSERT INTO dbo.[" & HDW_TABLENAME & "] (" & qFields & ") VALUES(" & qValues & ")")

This works, so there's nothing wrong with the connection. When expanded, it looks like this:
INSERT INTO dbo.[Change Management Request] ([DateofRequest],[Requester],[RequesterExtl],[RequesterMobile],[RequesterEmail],[RequesterLineMgr],[LineManagerExt],[LineMgrMobile],[LineMgrEmail],[Request],[Purpose],[BudgetImplication],[GiveDetails],[NeedCBTApproval],[WhyNot],[IsItUrgent],[DoOutOfHours],[CustomerGroupImpact],[CommunicationPlan],[WhoOwnsPlan],[WhoConductingWork],[Position],[SuitableQual],[Supervisor],[SupPos],[PermitToWork],[WhoProvides],[StartDate],[StartTime],[EndDate],[EndTime],[DownTime],[RegressionPlan],[ProblemIdent],[TestValidation],[HandoverPlan],[DocChange],[DocChangeDate],[PreStartMeeting],[PreStartMeetingDate],[RequestAcceptedMgr],[AcceptanceDate],[Classification],[ApprovalIT],[ApprovalDate],[MeetingDate],[ApprovedITSG],[ApprovalDateITSG],[Notes],[CompletedorNot],[DecisionlDate],[ReviewNotes],[Status]) VALUES('12/03/2015','Pat Murphy','4444','07884 188084','pm@zed.co.uk','Fred Smith','5555','08877 12345','fs@zed.co.uk','aaaaa','bbbbbb','yes','','yes','','no','no','','no','IT','','','no','','','no','','','','','','','','','','','no','','no','','no','','Normal','no','','','no','','','no','','','')

All the values are directly brought in from the form fields. So we're parametizing them.

So my code looks as so:
set InsertCmd = Server.CreateObject ("ADODB.Command")
InsertCmd.ActiveConnection = "dsn=CMRsys"
InsertCmd.CommandText = "INSERT INTO dbo.[Change Management Request] (" & FieldListWrite & ") VALUES (" & vWriteFieldParams & ")"
InsertCmd.Prepared = true
InsertCmd.Parameters.Append InsertCmd.CreateParameter("@Status", adChar, adParamInput, 50, request("Status"))
InsertCmd.Parameters.Append InsertCmd.CreateParameter("@Requester", adVarChar, adParamInput, 50, request("Requester"))
InsertCmd.Parameters.Append InsertCmd.CreateParameter("@DateofRequest", adChar, adParamInput, 50, request("DateofRequest"))
InsertCmd.Parameters.Append InsertCmd.CreateParameter("@RequesterExtl", adVarChar, adParamInput, 50, request("RequesterExtl"))
.  Lots of more parameters, all in identical format
InsertCmd.Parameters.Append InsertCmd.CreateParameter("@DecisionlDate", adChar, adParamInput, 50, request("DecisionlDate"))
InsertCmd.Parameters.Append InsertCmd.CreateParameter("@ReviewNotes", adVarChar, adParamInput, 4000, request("ReviewNotes"))
set vRecSet = InsertCmd.Execute  <------Error here
set vRecSet = nothing
set InsertCmd = nothing

Now, when my command string is expanded, it looks as so:
INSERT INTO dbo.[Change Management Request] ([Status],[Requester],[DateofRequest],[RequesterExtl],[RequesterMobile],[RequesterEmail],[RequesterLineMgr],[LineManagerExt],[LineMgrMobile],[LineMgrEmail],[Request],[Purpose],[BudgetImplication],[GiveDetails],[NeedCBTApproval],[WhyNot],[IsItUrgent],[DoOutOfHours],[CustomerGroupImpact],[CommunicationPlan],[WhoOwnsPlan],[WhoConductingWork],[Position],[SuitableQual],[Supervisor],[SupPos],[PermitToWork],[WhoProvides],[StartDate],[StartTime],[EndDate],[EndTime],[DownTime],[RegressionPlan],[ProblemIdent],[TestValidation],[HandoverPlan],[DocChange],[DocChangeDate],[PreStartMeeting],[PreStartMeetingDate],[RequestAcceptedMgr],[AcceptanceDate],[Classification],[ApprovalIT],[ApprovalDate],[ApprovedITSG],[MeetingDate],[ApprovalDateITSG],[Notes],[CompletedorNot],[DecisionlDate],[ReviewNotes]) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

I've been through that string, and can't find anything wrong with it, although I've looked at it for so long I could be blind. There are the correct number of question marks, the parentheses match, and so forth (or at least I think they do).

If it's something wrong with the parameters, I would have expected an Insert error rather than a syntax error - or am I wrong on this? I have had to patch a couple of parameters, such as turning them into strings (which they are, in a sense) - one example is the dates. Even a smell of adDate (or 129) as the type specification throws errors.

So, if anyone has seen this before, I'd be grateful for any and all help!


John B
VB ScriptASP

Avatar of undefined
Last Comment

8/22/2022 - Mon
Big Monty

two things I would do to troubleshoot:

1) do a Response.Write of the command text, see if anything is up there:
       Response.Write InsertCmd.CommandText

2) start commenting out each parameter you add, starting with the bottom one. If you get a different message after the first one (saying the parameters don't match up numbers-wise), then eliminate the placeholder ? as you.


View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes