Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Classic ASP Professional For advanced coder questions in ASP 3. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Professional section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old March 1st, 2006, 02:10 PM
Registered User
 
Join Date: Mar 2006
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Calling Stored Procedure with parameters

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

  #2 (permalink)  
Old March 2nd, 2006, 08:34 AM
Authorized User
 
Join Date: Feb 2006
Location: Verona, Italy, Italy.
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #3 (permalink)  
Old March 2nd, 2006, 09:03 AM
Registered User
 
Join Date: Mar 2006
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.



  #4 (permalink)  
Old March 2nd, 2006, 11:35 AM
Authorized User
 
Join Date: Feb 2006
Location: Verona, Italy, Italy.
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #5 (permalink)  
Old March 2nd, 2006, 11:57 AM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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.


Similar Threads
Thread Thread Starter Forum Replies Last Post
calling stored procedure jomet JSP Basics 0 November 23rd, 2007 08:06 AM
stored procedure with parameters Danmalam VB Databases Basics 0 February 27th, 2005 03:52 PM
Calling stored procedure with output parameters bansalh SQL Server 2000 2 November 10th, 2004 11:00 AM
Calling an Oracle Stored Procedure booksnore2 BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 2 October 1st, 2004 09:35 AM
Calling Stored procedures with out parameters bansalh Access 1 November 25th, 2003 04:30 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.