Sql – ASP and ADO error: No value given for one or more required parameters

adoasp-classicsql servervbscript

Language is vbscript and classic ASP.

The following SQL works when values are hard coded in the sql statement:

sql = "UPDATE STORE2_ITEM SET sku = 'abcd' WHERE id = 224 and host_id = 1"

What I'm trying to do is add parameters so I replaced the field1 assignment with the following:

sql = "UPDATE STORE2_ITEM SET sku = ? WHERE id = 224 and host_id = 1"

(keeping it simple for now just to see if I can get this parameter to work).

I get the following error:

"No value given for one or more required parameters."

rest of the code is as follows:

Set DynaDb = Server.CreateObject("ADODB.Connection")
DynaDB.ConnectionString = STORE_CONNECTION_STRING
DynaDb.Open

sql = "UPDATE STORE2_ITEM SET sku = ? WHERE id = 224 and host_id = 1"

Set cmd = Server.CreateObject("ADODB.Command")
cmd.CommandText = sql

cmd.CommandType adCmdText
cmd.Prepared = true

cmd.Parameters.Append(cmd.CreateParameter("", 200, 1, "AbcD"))

cmd.ActiveConnection = DynaDB
cmd.Execute

Other info:
Connection String:
Provider=SQLOLEDB.1;Data Source=xxxxxxx;Initial Catalog=xxxxxx;Persist Security Info=True;User ID=xxxx;User Id=mkj;PASSWORD=xxxxxx;

EDIT: I removed the code that was giving me a Type Mismatch error as it really wasn't relevant.

EDIT: I removed my answer here and posted it as an "Answer" below.

Best Answer

The syntax for parameters in an ADODB SQL Server command string is:

@ParameterName

Here is some example code:

Dim rst As ADODB.Recordset

Set cmd = New ADODB.Command

cmd.ActiveConnection = "Provider=SQLOLEDB.1;Data Source=(local);" & 
"Integrated Security=SSPI;Initial Catalog=DatabaseName"

cmd.CommandText = "DECLARE @PARAMETER1 datetime, @PARAMETER2 datetime, 
@PARAMETER3 bit;" & _
"SELECT * FROM blah, blah.... " & _
"WHERE something>= @PARAMETER3 AND " & _
"something BETWEEN @PARAMETER1 AND @PARAMETER2"

cmd.CommandType = adCmdText

Set PARAMETER1 = cmd.CreateParameter("@PARAMETER1", adDate, adParamInput)
cmd.Parameters.Append PARAMETER1
PARAMETER1.Value = "01/01/2000"

Set PARAMETER2 = cmd.CreateParameter("@PARAMETER2", adDate, adParamInput)
cmd.Parameters.Append PARAMETER2
PARAMETER2.Value = "05/01/2007"

Set PARAMETER3 = cmd.CreateParameter("@PARAMETER3", adInteger, adParamInput)
cmd.Parameters.Append PARAMETER3
PARAMETER3.Value = 0

Set rst = New ADODB.Recordset
Set rst = cmd.Execute()
Related Topic