Calling Stored Procedure with parameters

zarina_24 · March 1st, 2006, 02:10 PM

hello everyone
i was using stored procedure(passing input parameters) for a simple updation in database( SQL server). I encountered following error in asp page which calls the stored procedure.
Error Type:
ADODB.Command (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. line 27

here is that part of the ASP code
set cmdCalculate = Server.CreateObject("ADODB.Command")

    with cmdCalculate
    .ActiveConnection = strconn
    .CommandText = "usp_CalculateCalories"
    .CommandType = adCmdStoredProc
    .Parameters.Append= cmdCalculate.CreateParameter("@RecipeNm" ,adVarChar , adParamInput,,strRecipeNm)//line 27
    .Parameters.Append =cmdCalculate.CreateParameter("@Serving" ,adInteger , adParamInput,,intServing)

    .Execute lngRecs, ,adExecuteNoRecords
    end with

I checked the syntax and order for passing the parameters.Also included the file adovbs.inc.

can someone help me how to remove this bug?

thanks a lot:)
zarina

Steweb · March 2nd, 2006, 08:34 AM

Hi zarina !

@RecipeNm = VarChar(size)
CreateParameter(name ,[Type] , [Direction],[size],[Value] )

.Parameters.Append= cmdCalculate.CreateParameter("@RecipeNm" ,adVarWChar , adParamInput, [size],[Value] )

Steweb

Visit my web site !
www.steweb.net

zarina_24 · March 2nd, 2006, 09:03 AM

hey steweb,

thanks for helping

I tried changing the size of variable in CreateParameter, same error is occuring

I guess the problem is in the
Parameters.Append Property of Parameters collection. because syntax of CreateParameter seems to be all correct.

thanks again.

Steweb · March 2nd, 2006, 11:35 AM

OK !!

try that code.!

Code:

<%
   Set strconn = Server.CreateObject("ADODB.Connection")

   ' The following line must be changed to reflect your data source info
   Conn.Open "Yoour connection", "user id", "password"   set cmdCalculate = Server.CreateObject("ADODB.Command")
   set cmdCalculate.ActiveConnection = strconn

   ' Specify the name of the stored procedure you wish to call
    cmdCalculate.CommandText = "usp_CalculateCalories"
    cmdCalculate.CommandType = adCmdStoredProc

    ' Query the server for what the parameters are
    cmdCalculate.Parameters.Refresh
   %>
   <Table Border=1>
   <TR>
      <TD><B>PARAMETER NAME</B></TD>
      <TD><B>DATA-TYPE</B></TD>
      <TD><B>DIRECTION</B></TD>
      <TD><B>DATA-SIZE</B></TD>
   </TR>
   <% For Each param In cmdCalculate.Parameters %>
   <TR>
      <TD><%= param.name %></TD>
      <TD><%= param.type %></TD>
      <TD><%= param.direction %></TD>
      <TD><%= param.size %></TD>
   </TR>
   <%
    Next
    Conn.Close
   %>
   </TABLE>
   </BODY>
   </HTML>

You see a table with parameters value

Steweb

Visit my web site !
www.steweb.net

Imar · March 2nd, 2006, 11:57 AM

Quote:

quote:Originally posted by zarina_24
I guess the problem is in the Parameters.Append Property

Append is not a Property, but a method. So, you shouldn't assign a new parameter, but pass it as a parameter (no pun intended) to the Append method:

.Parameters.Append .CreateParameter("@RecipeNm" ,adVarChar , adParamInput,,strRecipeNm)//line 27

I removed the = symbol. I also removed cmdCalculate because it's already inside a With block for cmdCalculate.

HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.