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
vRecSet.Close
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!

ATB

John B
prunesquallorAsked:
Who is Participating?
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 MontySenior Web Developer / CEO of ExchangeTree.org Commented:
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.

HTH
0
 
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: http://support.microsoft.com/en-gb/kb/214459

ATB

John B
0

Experts Exchange Solution brought to you by ConnectWise

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.