CODEWITHSUNDEEP
stored-proceduresparametersasp-classicvbscriptadodb
Stew
Stew

Reputation: 305

Arguments are of the wrong type with Stored Procedure Parameters

Im using a stored procedure to return a product price where i send two parameters to it, ones the stockcode and the other is like the customer number. When i run this is get the following error

ADODB.Command error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict 
with one another.

The two parameters i send a both strings and they can both vary in length.

The code is as follows.

bnno = request("bn")
stockcode = request("ht")

dim prices

   Set cn = Server.CreateObject("ADODB.Connection")
   cn.Open "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=stock;"&_
"User Id=username;Password=password;"
   Set cmd = Server.CreateObject("ADODB.Command")
   Set cmd.ActiveConnection = cn
   Set prices = Server.CreateObject("ADODB.RecordSet")
   cmd.CommandText = "sp_stockdata"
   cmd.CommandType = adCmdStoredProc
   cmd.Parameters.Append cmd.CreateParameter("htcode", adlongvarchar, _
      adParamReturnValue)
   cmd.Parameters.Append cmd.CreateParameter("bn", adlongvarchar, _
      adParamInput)

   cmd("htcode") = stockcode
   cmd("bn") = bnno

   set prices = cmd.Execute

Can someone please help me change this code so i can send the text to the parameters??

Upvotes: 0

Views: 3781

Answers (2)

Shadow Wizzard
Shadow Wizzard

Reputation: 66396

You are using the stored procedure in a wrong way. As mentioned in this other answer, adParamReturnValue means "use the stored procedure return value" which can only be a numeric code.

Looks like what you're really after is output parameter, which is 2 aka adParamOutput. You also can't assign output parameter to local variable like that.
After second reading I understand the above isn't correct, leaving it for others who might get confused in similar way.

What you really want as far as I can see is just send two input parameters, so proper code would be:

cmd.CommandText = "sp_stockdata"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("htcode", adLongVarChar, adParamInput, Len(stockcode), stockcode)
cmd.Parameters.Append cmd.CreateParameter("bn", adLongVarChar, adParamInput, Len(bnno), bnno)
set prices = cmd.Execute

Note that you must also specify the data length when using adLongVarChar type.

Upvotes: 0

MC ND
MC ND

Reputation: 70971

adParamReturnValue parameters can only be numeric. To return other kind of data, please read this (and select your sqlserver version)

Upvotes: 1

Related Questions