ASP and ADO error: value set for one or more required parameters
The language is vbscript and classic ASP.
The following SQL works when values are hard-coded into sql statements:
sql = "UPDATE STORE2_ITEM SET sku = 'abcd' WHERE id = 224 and host_id = 1"
What I am trying to do is add parameters, so I replaced the assignment of field1 with this:
sql = "UPDATE STORE2_ITEM SET sku = ? WHERE id = 224 and host_id = 1"
(keeping it simple, just to see if I can get this setting to work).
I am getting the following error:
"There is no value for one or more of the required parameters.
the rest of the code looks like this:
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
Additional information: Connection string: Provider = SQLOLEDB.1; Data source = xxxxxxx; Home directory = xxxxxx; Persist Security Info = True; User ID = xxxx; User ID = mkj; PASSWORD = xxxxxx;
EDIT: I removed the code that was giving me the type mismatch error as it really didn't matter.
EDIT: I deleted my answer and posted it as "Answer" below.
source to share
I understood that. Anthony and Robert pointed me in the right direction.
I realized that although the error message told me what happened, it seemed misleading to me. This will give me the error above when my parameter was not declared correctly.
The main problem was that the constants didn't exist anyway. By putting their integer values in, I was able to get it to work. I think I figured it out first, which is why my original post had "200" for adVarChar. So, I just declared these variables at the top of my asp (by the way, does anyone know how to include these constants?).
Also, it seemed to need the size of the parameters (i.e. 50 for adVarChar). So my last 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
source to share
The syntax for the options on the SQL Server ADODB command line is:
@ParameterName
Here's some sample 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()
source to share
Remove this line from your code: -
cmd.Prepared = true
What happens when you use this string (or try to index into the Parameters collection before adding anything to it) is an ADO round to the DB server asking for a set of parameters required to execute the command. Hence, this line creates the required parameter entry for you, but no value.
Now, when you add a parameter, it is added in addition to the correct parameter already present in the parameters collection. When you execute your "extra" parameter, it is oversensitive to requirements and is ignored, but a valid parameter is found that has no set value, hence an error.
By deleting the line, you have taken it upon yourself to set up the collection of parameters correctly, and therefore no additional feedback is needed to create the collection. Assuming you add all of the original parameters, they should work.
source to share