Link to home
Start Free TrialLog in
Avatar of prunesquallor
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
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
Avatar of Big Monty
Big Monty
Flag of United States of America image

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
ASKER CERTIFIED SOLUTION
Avatar of prunesquallor
prunesquallor

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial