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/Form2
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],[Requeste
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
InsertCmd.CommandText = "INSERT INTO dbo.[Change Management Request] (" & FieldListWrite & ") VALUES (" & vWriteFieldParams & ")"
InsertCmd.Prepared = true
InsertCmd.Parameters.Appen
InsertCmd.Parameters.Appen
InsertCmd.Parameters.Appen
InsertCmd.Parameters.Appen
.
.
. Lots of more parameters, all in identical format
.
.
.
InsertCmd.Parameters.Appen
InsertCmd.Parameters.Appen
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],[Dat
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
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/Form2
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],[Requeste
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
InsertCmd.CommandText = "INSERT INTO dbo.[Change Management Request] (" & FieldListWrite & ") VALUES (" & vWriteFieldParams & ")"
InsertCmd.Prepared = true
InsertCmd.Parameters.Appen
InsertCmd.Parameters.Appen
InsertCmd.Parameters.Appen
InsertCmd.Parameters.Appen
.
.
. Lots of more parameters, all in identical format
.
.
.
InsertCmd.Parameters.Appen
InsertCmd.Parameters.Appen
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],[Dat
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
VB ScriptASP
Last Comment
prunesquallor
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
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.
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