executing and returning value from stored procedure

[ikalair]

Hi, I'm having trouble running my stored procedure. I'm using the Connection and Command objects in my .asp. I keep getting the following error"

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

In my ASP script, I've tried running it with just the one parameter being used in the stored proc and I've also tried it with both the stored proc parameter and a return value parameter. Neither worked

Below is my Stored Procedure and .asp scripts.
-----------------------------------
Stored Proc
-------------------------------------
CREATE procedure dbo.sp_user_delete
-- Inbound Parameters:
@INID T_PKFK --this is simply an integer value
as
set nocount on
begin transaction
delete from dbo.tbl_user_info
where
id = @inid
if @@error <> 0 begin
rollback transaction
return 103
end
commit transaction
return(0)

GO


-------------------------------
ASP Script
------------------------------
set conn = server.CreateObject (&quot;ADODB.Connection&quot
Set cmdUpdate = Server.CreateObject(&quot;ADODB.Command&quot

conn.Open Application(&quot;Connection1_ConnectionString&quot

set cmdUpdate.ActiveConnection = conn
cmdUpdate.CommandText = &quot;sp_user_delete&quot;
cmdUpdate.CommandType = adCmdStoredProc

Set parValue = cmdUpdate.CreateParameter(&quot;ReturnValue&quot;, adInteger, adParamReturnValue)
cmdUpdate.Parameters.Append parValue

Set parValue = cmdUpdate.CreateParameter(&quot;INID&quot;, adInteger, adParamInput,4,2)
cmdUpdate.Parameters.Append parValue
cmdUpdate.Execute

 

[grtammi]

I've got a little solution for you ... but it's at work. I'll post it up tomorrow.

Greg Tammi, IT Design & Consultation
Work:

http://www.atsbell.com

Home:

http://members.shaw.ca/gregandliz

 

[jemminger]

i find this method to be easier:

set conn = server.CreateObject (&quot;ADODB.Connection&quot
Set cmdUpdate = Server.CreateObject(&quot;ADODB.Command&quot

conn.Open Application(&quot;Connection1_ConnectionString&quot

set cmdUpdate.ActiveConnection = conn
cmdUpdate.CommandText = &quot;sp_user_delete&quot;
cmdUpdate.CommandType = adCmdStoredProc
cmdUpdate.parameters(1) = 4 'or whatever int
cmdUpdate.Execute

returnVal = cmdUpdate.parameters(0)





=========================================================
-jeff

http://www.jeffemminger.com

try { succeed(); } catch(E) { tryAgain(); }

 

[ikalair]

thx jemminger. I'll try your method. It does seem to be cleaner.

I also figured out why my code wasn't working. I wasn't including adovbs.inc on my pages. In Visual Basic, this file isn't needed. But with ASP it needs to be since all the parameter types used need to be declared (adVarChar,adInteger,etc).

grtammi, would still like to see your solution.