Datatype problems with Stored Procedures

<i><b>Originally posted by : Allan S (aspielma@yahoo.com)</b></i><br />Datatype problems with Stored Procedures<br /><br />Every now and then the evil computer stumps me and I have to go for help.<br /><br />Julio, do you have any idea why SQL dows not like the parameters being passed by ASP? The stored proc works when I run it from I_SQL, or use it in other routines.<br /><br />(1) ASP Code: (I am using constants for testing, and yet this still fails!)<br /><br />vDBCommand.CommandText = "prcUpdatePbr"<br /><br />vDBCommand.CommandType = adCmdStoredProc<br /><br />vDBCommand.Parameters.Append vDBCommand.CreateParameter("@PbrID", adInteger, adParamInput, , 1)<br /> <br />vDBCommand.Parameters.Append vDBCommand.CreateParameter("@UsrId", adInteger, adParamInput, , 26)<br />vDBCommand.Parameters.Append vDBCommand.CreateParameter("@PubNa", adChar, adParamInput, 45, "CAMPSINI PUBLISHING")<br />vDBCommand.Parameters.Append vDBCommand.CreateParameter("@TaxId", adInteger, adParamInput, , "295504911")<br />vDBCommand.Parameters.Append vDBCommand.CreateParameter("@PubCae", adInteger, adParamInput, , 400055)<br />vDBCommand.Parameters.Append vDBCommand.CreateParameter("@PrAffSoc", adChar, adParamInput, 3, "003")<br />vDBCommand.Parameters.Append vDBCommand.CreateParameter("@MrSoc", adChar, adParamInput, 3, "xxx")<br />vDBCommand.Parameters.Append vDBCommand.CreateParameter("@SrSoc", adChar, adParamInput, 3, "xxx")<br />vDBCommand.Parameters.Append vDBCommand.CreateParameter("@UpdUid", adChar, adParamInput, 15, "500")<br /><br />vDBCommand.Execute (line 176)<br /><br />(2) And here's my error:<br /><br />Microsoft OLE DB Provider for ODBC Drivers error '80040e07' CREATE PROCEDURE prcUpdatePbr<br />[INTERSOLV][ODBC SQL Server driver][SQL Server]Implicit conversion from datatype 'VARCHAR' to 'INT' is not allowed. Use the CONVERT function to run this query. <br /><br />/cwrreg_0721/CommitLogin.asp, line 176 <br /><br /><br />(3) Here is the stored procedure:<br /><br />CREATE PROCEDURE prcUpdatePbr<br /> @PbrId int,<br /> @UsrId int,<br /> @PbrNa varchar(45),<br /> @TaxId int,<br /> @CaeNr int,<br /> @PrSoc char(3),<br /> @MrSoc char(3),<br /> @SrSoc char(3),<br /> @UpdUid char(15)<br /><br />AS<br /><br /> UPDATE tblPublisher<br /> SET PbrId = @PbrId,<br /> UsrId = @UsrId <br /> <br />,<br /> PbrNa = @PbrNa,<br /> TaxId = @TaxId,<br /> CaeNr = @CaeNr,<br /> PrSoc = @PrSoc,<br /> MrSoc = @MrSoc,<br /> SrSoc = @SrSoc,<br /> UpdTs = GETDATE(),<br /> UpdUid = @UpdUid<br /><br /> WHERE<br /> tblPublisher.PbrId = @PbrId<br />AND tblPublisher.UsrId = @UsrId <br />

<i><b>Originally posted by : TOUMI Fethi (toumi.f@decade.fr)</b></i><br /><br />hello,<br /><br />try to use :<br /><br />vDBCommand.Parameters.Append vDBCommand.CreateParameter("@TaxId", adInteger, adParamInput, , 295504911)<br /><br />and not <br /><br />vDBCommand.Parameters.Append vDBCommand.CreateParameter("@TaxId", adInteger, adParamInput, , "295504911")<br /><br /><br />bye<br /><br /><br /><br />------------<br />Allan S at 9/20/2000 3:31:30 PM<br /><br />Datatype problems with Stored Procedures<br /><br />Every now and then the evil computer stumps me and I have to go for help.<br /><br />Julio, do you have any idea why SQL dows not like the parameters being passed by ASP? The stored proc works when I run it from I_SQL, or use it in other routines.<br /><br />(1) ASP Code: (I am using constants for testing, and yet this still fails!)<br /><br />vDBCommand.CommandText = "prcUpdatePbr"<br /><br />vDBCommand.CommandType = adCmdStoredProc<br /><br />vDBCommand.Parameters.Append vDBCommand.CreateParameter("@PbrID", adInteger, adParamInput, , 1)<br /> <br />vDBCommand.Parameters.Append vDBCommand.CreateParameter("@UsrId", adInteger, adParamInput, , 26)<br />vDBCommand.Parameters.Append vDBCommand.CreateParameter("@PubNa", adChar, adParamInput, 45, "CAMPSINI PUBLISHING")<br />vDBCommand.Parameters.Append vDBCommand.CreateParameter("@TaxId", adInteger, adParamInput, , "295504911")<br />vDBCommand.Parameters.Append vDBCommand.CreateParameter("@PubCae", adInteger, adParamInput, , 400055)<br />vDBCommand.Parameters.Append vDBCommand.CreateParameter("@PrAffSoc", adChar, adParamInput, 3, "003")<br />vDBCommand.Parameters.Append vDBCommand.CreateParameter("@MrSoc", adChar, adParamInput, 3, "xxx")<br />vDBCommand.Parameters.Append vDBCommand.CreateParameter("@SrSoc", adChar, adParamInput, 3, "xxx")<br />vDBCommand.Parameters.Append vDBCommand.CreateParameter("@UpdUid", adChar, adParamInput, 15, "500")<br /><br />vDBCommand.Execute (line 176)<br /><br />(2) And here's my error:<br /><br />Microsoft OLE DB Provider for ODBC Drivers error '80040e07' CREATE PROCEDURE prcUpdatePbr<br />[INTERSOLV][ODBC SQL Server driver][SQL Server]Implicit conversion from datatype 'VARCHAR' to 'INT' is not allowed. Use the CONVERT function to run this query. <br /><br />/cwrreg_0721/CommitLogin.asp, line 176 <br /><br /><br />(3) Here is the stored procedure:<br /><br />CREATE PROCEDURE prcUpdatePbr<br /> @PbrId int,<br /> @UsrId int,<br /> @PbrNa varchar(45),<br /> @TaxId int,<br /> @CaeNr int,<br /> @PrSoc char(3),<br /> @MrSoc char(3),<br /> @SrSoc char(3),<br /> @UpdUid char(15)<br /><br />AS<br /><br /> UPDATE tblPublisher<br /> SET PbrId = @PbrId,<br /> UsrId = @UsrId <br /> <br />,<br /> PbrNa = @PbrNa,<br /> TaxId = @TaxId,<br /> CaeNr = @CaeNr,<br /> PrSoc = @PrSoc,<br /> MrSoc = @MrSoc,<br /> SrSoc = @SrSoc,<br /> UpdTs = GETDATE(),<br /> UpdUid = @UpdUid<br /><br /> WHERE<br /> tblPublisher.PbrId = @PbrId<br />AND tblPublisher.UsrId = @UsrId <br />

<i><b>Originally posted by : Allan (aspielma@yahoo.com)</b></i><br />Thanks, but acually my original attempt had no quotes on the number and it still failed.<br /><br />------------<br />TOUMI Fethi at 9/21/2000 4:34:13 AM<br /><br /><br />hello,<br /><br />try to use :<br /><br />vDBCommand.Parameters.Append vDBCommand.CreateParameter("@TaxId", adInteger, adParamInput, , 295504911)<br /><br />and not <br /><br />vDBCommand.Parameters.Append vDBCommand.CreateParameter("@TaxId", adInteger, adParamInput, , "295504911")<br /><br /><br />bye<br /><br /><br /><br />------------<br />Allan S at 9/20/2000 3:31:30 PM<br /><br />Datatype problems with Stored Procedures<br /><br />Every now and then the evil computer stumps me and I have to go for help.<br /><br />Julio, do you have any idea why SQL dows not like the parameters being passed by ASP? The stored proc works when I run it from I_SQL, or use it in other routines.<br /><br />(1) ASP Code: (I am using constants for testing, and yet this still fails!)<br /><br />vDBCommand.CommandText = "prcUpdatePbr"<br /><br />vDBCommand.CommandType = adCmdStoredProc<br /><br />vDBCommand.Parameters.Append vDBCommand.CreateParameter("@PbrID", adInteger, adParamInput, , 1)<br /> <br />vDBCommand.Parameters.Append vDBCommand.CreateParameter("@UsrId", adInteger, adParamInput, , 26)<br />vDBCommand.Parameters.Append vDBCommand.CreateParameter("@PubNa", adChar, adParamInput, 45, "CAMPSINI PUBLISHING")<br />vDBCommand.Parameters.Append vDBCommand.CreateParameter("@TaxId", adInteger, adParamInput, , "295504911")<br />vDBCommand.Parameters.Append vDBCommand.CreateParameter("@PubCae", adInteger, adParamInput, , 400055)<br />vDBCommand.Parameters.Append vDBCommand.CreateParameter("@PrAffSoc", adChar, adParamInput, 3, "003")<br />vDBCommand.Parameters.Append vDBCommand.CreateParameter("@MrSoc", adChar, adParamInput, 3, "xxx")<br />vDBCommand.Parameters.Append vDBCommand.CreateParameter("@SrSoc", adChar, adParamInput, 3, "xxx")<br />vDBCommand.Parameters.Append vDBCommand.CreateParameter("@UpdUid", adChar, adParamInput, 15, "500")<br /><br />vDBCommand.Execute (line 176)<br /><br />(2) And here's my error:<br /><br />Microsoft OLE DB Provider for ODBC Drivers error '80040e07' CREATE PROCEDURE prcUpdatePbr<br />[INTERSOLV][ODBC SQL Server driver][SQL Server]Implicit conversion from datatype 'VARCHAR' to 'INT' is not allowed. Use the CONVERT function to run this query. <br /><br />/cwrreg_0721/CommitLogin.asp, line 176 <br /><br /><br />(3) Here is the stored procedure:<br /><br />CREATE PROCEDURE prcUpdatePbr<br /> @PbrId int,<br /> @UsrId int,<br /> @PbrNa varchar(45),<br /> @TaxId int,<br /> @CaeNr int,<br /> @PrSoc char(3),<br /> @MrSoc char(3),<br /> @SrSoc char(3),<br /> @UpdUid char(15)<br /><br />AS<br /><br /> UPDATE tblPublisher<br /> SET PbrId = @PbrId,<br /> UsrId = @UsrId <br /> <br />,<br /> PbrNa = @PbrNa,<br /> TaxId = @TaxId,<br /> CaeNr = @CaeNr,<br /> PrSoc = @PrSoc,<br /> MrSoc = @MrSoc,<br /> SrSoc = @SrSoc,<br /> UpdTs = GETDATE(),<br /> UpdUid = @UpdUid<br /><br /> WHERE<br /> tblPublisher.PbrId = @PbrId<br />AND tblPublisher.UsrId = @UsrId <br />