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','','Fred Smith','5555','08877 12345','','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
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Big MontyWeb Ninja at largeCommented:
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.

prunesquallorAuthor Commented:
@BIG Monty
Thanks for the reply. Yeah, the strings you see above came from Response.Write. I ended up building a complete mini-app, with a table of each major data types.

I have found the solution, which may help those who also want to play this game. Basically:
1. The parameters must exactly match the widths of the relevant table columns.
2. Not all types are supported, and you may or may not get an appropriate error (adDate and adTime are out, for a start - use adDBTimeStamp: which then throws an error if empty [even if the target table will accept a NULL]).

Gory details may be found here:


John B

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.