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

Question

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.

Answer 1

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()

Answer 2

Other syntax:

with server.createobject("adodb.command")
  .activeConnection = application("connection_string")
  .commandText "update sometable set some_col=?"
  .execute , array(value)
end with

Answer 3

Remove this line from your code:-

cmd.Prepared = true

What happens when you use this line (or attempt to index into the Parameters collection before adding anything to it) is ADO round trips to the DB server requesting the set of parameters needed to execute the command. Hence this line creates the required parameter entry for you but without a value.

Now when you add a parameter its added in addition to the correct parameter already present in the parameters collection. When you execute your "extra" parameter is superflous to requirements and ignored but the correct parameter is found to not have its value set, hence the error.

By removing the line you've taken it upon yourself to correctly configure the parameter collection and hence no extra roundtrip is needed to create the collection. Assuming you add all the corrrect parameters it should work.

Answer 4

I figured it out. Anthony and Robert pointed me in the right direction.

I realized that while the error message told me what was wrong, it seemed misleading to me. It would give me the error above when my parameter wasn't declared correctly.

The main issue was the constants didn't exist somehow. By putting their integer values, I was able to get it to work. I think I had realized this at first, which is why my original post had "200" for the adVarChar. So, I just declared these variables at the top of my asp (incidentally, anyone know how to get those constants to be included?).

Also, it seemed to want the size of the parameters (i.e., the 50 for the adVarChar). So, my final code looked like this (:

' Data Types
adInteger = 3
adVarChar = 200
adDecimal = 14

' Direction Constants
adParamInput = 1
adParamOutput = 2

On Error Resume Next
Set conn = Server.CreateObject("ADODB.Connection")
Set cmd = Server.CreateObject("ADODB.Command")

conn.ConnectionString = "Provider=SQLOLEDB.1;Data Source=BRYCE-PC7\SQLEXPRESS;Initial Catalog=Funeral;Persist Security Info=True;User ID=mkj;PASSWORD=jibenear32;"
conn.Open

cmd.ActiveConnection = conn
cmd.CommandType = 1
cmd.CommandText = "UPDATE STORE2_ITEM SET sku = ? WHERE id = 224 and host_id = 1"

Set param = cmd.CreateParameter(, adVarChar, adParamInput, 50)
param.Value = "NEW SKU"

cmd.Parameters.Append param

cmd.Execute