Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

PRB: Implicit Conversion Error Calling Second Stored Procedure


View products that this article applies to.

This article was previously published under Q178038

↑ Back to the top


Symptoms

Either of the following errors occur when you execute an Active Server Pages (ASP) page that is making subsequent stored procedure calls:

Implicit conversion from the datatype 'varchar' to 'int' is not allowed. Use the convert function to run this query.
Too many parameters were supplied for procedure <your stored procedure>.

↑ Back to the top


Cause

Two conditions that cause this error to occur are as follows:

  • More than one call to a stored procedure using the same ADODB.Command object.

    -or-
  • The parameters passed to the stored procedure are of a different data type from one stored procedure call to the other.
The root cause of this behavior is that the parameters in the command object's parameter collection are not overwritten with subsequent definitions; you must first delete the parameter(s) from the Parameters Collection before defining a new parameter(s) in the collection.

↑ Back to the top


Resolution

Delete the parameter(s) in question using the "Delete" method of the collection object.
   collection.Delete Index
				
The collection placeholder represents the collection from which you want to delete an object. The Index argument is a string representing the name of the object you want to delete.

The Index in the preceding syntax works only for named parameters; a numeric index does not work. In other words, you must name your parameter when you use CreateParameter to create the parameter.

If you use the DataCommand control in Visual InterDev to add the code necessary to accomplish the stored procedure call, Visual InterDev inserts code to re-create the Command object. This is a valid approach, but it does add overhead of re-creating the object.

↑ Back to the top


Status

This behavior is by design.

↑ Back to the top


More information

Steps to Reproduce Behavior

  1. Open or create a new project in Visual InterDev.
  2. Add a dataconnection named "pubs" to the project and point the data source name (DSN) to the "Pubs" database in SQL Server 6.5.
  3. Create two stored procedures that take input parameters of different data types.

       create procedure sp_varchar
       @cust varchar
       AS
       SELECT *
       FROM employee
    						


    -and-
       create procedure sp_int
       @cust int
       AS
       SELECT *
       FROM employee
    					
  4. Add a new ASP page to the project that calls the two stored procedures using the same command object. The code example is as follows:
    
       ==============sample ASP code===============
       <%
       Set pubs = Server.CreateObject("ADODB.Connection")
       pubs.ConnectionTimeout = Session("pubs_ConnectionTimeout")
       pubs.CommandTimeout = Session("pubs_CommandTimeout")
       pubs.Open Session("pubs_ConnectionString"),
       Session("pubs_RuntimeUserName"), Session("pubs_RuntimePassword")
       Set cmdTemp = Server.CreateObject("ADODB.Command")
       Set DataCommand1 = Server.CreateObject("ADODB.Recordset")
       cmdTemp.CommandText = "dbo.""sp_varchar"""
       cmdTemp.CommandType = 4
       Set cmdTemp.ActiveConnection = pubs
    
       'Syntax:
       'Set parameter = command.CreateParameter(Name, Type, Direction, Size,
       'Value)
       '200 is the value for Varchar datatype.
       '1 is the value for an input parameter.
    
       Set tmpParam = cmdTemp.CreateParameter("@cust", 200, 1, 6,"mytest")
       cmdTemp.Parameters.Append tmpParam
       DataCommand1.Open cmdTemp, , 0, 1
       %>
    
       <%
       '========================
       'The commented out line below is required to correctly clear the
       'varchar parameter before creating the int parameter. Uncomment the
       'line below and the code will work correctly.
       '========================
       'cmdTemp.Parameters.Delete "@cust"
    
       Set DataCommand2 = Server.CreateObject("ADODB.Recordset")
       cmdTemp.CommandText = "dbo.""sp_int"""
       cmdTemp.CommandType = 4
       Set cmdTemp.ActiveConnection = pubs
    
       'Integer data type with a value of 3
       Set tmpParam = cmdTemp.CreateParameter("@cust", 3, 1, 4,3)
       cmdTemp.Parameters.Append tmpParam
       DataCommand2.Open cmdTemp, , 0, 1
       %>
       ========end sample ASP code=================
    					

↑ Back to the top


Keywords: KB178038, kbprb

↑ Back to the top

Article Info
Article ID : 178038
Revision : 4
Created on : 3/14/2005
Published on : 3/14/2005
Exists online : False
Views : 413