Return Value Problem with sp 2

[gny]

Why won´t this work?
I´m trying to add a new record in an sp and return the new ID to an asp page. On the row with the Execute-command in the asp code I get the following error message:

Microsoft OLE DB Provider for SQL Server (0x80040E07)
Error converting data type varchar to int

Heres the sp code:

CREATE PROCEDURE dbo.sp_AddOrder

    @nNewID        int OUTPUT,
    @sCreatedBy    varchar(50)
AS
    INSERT 
        OD_Order
        (
            CREATED_BY
        )
    VALUES
        (
            @sCreatedBy
        )
    SET @nNewID = @@IDENTITY

And the asp code:

    dim comNew    
    set comNew=server.createObject("ADODB.Command")
    comNew.ActiveConnection = conn
    comNew.CommandType = adCmdStoredProc
    comNew.CommandText = "sp_AddOrder"
    '*** Sätt inparametrar
    comNew.Parameters.Append comNew.CreateParameter("nNewID", adInteger, adParamReturnValue, 0, 0)
    comNew.Parameters.Append comNew.CreateParameter("sCREATED_BY", adVarchar, adParamInput, 50, Request("REMOTE_ADDR"))

    comNew.Execute
        session("OD_ID") = comNew.Parameters("nNewID")

It seems as though the first parameter isn´t noticed... What am I doing wrong?

 

[Geee]

I have had this problem before which I solved by converting the varchar to an integer before passing it to the database. To do this put a:

cint(adVarChar)

in you code before passing the variable to SQL server.

G -GTM Solutions, Home of USITE-
-=

http://www.gtmsolutions.com=-

 

[gny]

Thanks, but the varchar variable (the second parameter) should be a varchar. I still need some help with this one...!
Does anyone see what´s wrong in the code?

 

[vasah20]

have you tried explicitly casting it as a str?

ex:
comNew.Parameters.Append comNew.CreateParameter("sCREATED_BY", adVarchar, adParamInput, 50, cstr(Request("REMOTE_ADDR&quot))

hope this helps
leo

 

[gny]

Thanks for the tip, but it didn´t work.
It seems like it ignores the first parameter (int) and tries to use the second parameter (remote_addr) as the first int parameter instead. weird... I´m stuck.

 

[vasah20]

try switching the order of the parameters.

it seems like it wouldn't matter, but I know that for some reason all the code I've ever seen shows the return param as being last in the list.

Remember -
in your ASP code, the return variable has to be the first one supplied, because it always gets returned first.

hope this helps
leo

 

[gny]

I switched the parameters in both the sp and the asp code, and i worked. What do you mean the return variable has to be the first supplied? That´s exectly what didn´t work for me, isn´t it?
Well, thanks a lot leo!

 

[vasah20]

If you switch this

comNew.Parameters.Append comNew.CreateParameter("nNewID", adInteger, adParamReturnValue, 0, 0)
comNew.Parameters.Append comNew.CreateParameter("sCREATED_BY", adVarchar, adParamInput, 50, Request("REMOTE_ADDR&quot)

to
comNew.Parameters.Append comNew.CreateParameter("sCREATED_BY", adVarchar, adParamInput, 50, Request("REMOTE_ADDR&quot)
comNew.Parameters.Append comNew.CreateParameter("nNewID", adInteger, adParamReturnValue, 0, 0)

the return value will not be returned, and your parameters will be all off.

anyways - glad you got it working.
leo

 

[jonscott8]

I think your confusion stems from not fully understanding parameters. An output parameter is NOT the same as a return value parameters.

In your SP, you have the parameter defined as an Ouput parameter, so you should change
comNew.Parameters.Append comNew.CreateParameter("nNewID", adInteger, adParamReturnValue, 0, 0)

to
comNew.Parameters.Append comNew.CreateParameter("nNewID", adInteger, adParamOutput, 0, 0)

Now, the reason you are recieving the datatype error is because your VBS declared the 1st param as a return value parameter ISO an output parameter; hence the 2nd parameter (which is string/char type) is being passed as the 1st parameter to your SP. Your 1st parameter in the SP is defined as INT; hence the error.

See the KB articles in the following thread for more info:
connection.execute
thread333-71670 Jon Hawkins

 

[gny]

You´re right...
I pretty much ripped the code from somewhere else.
Thanks for setting me straight.
AdParamOutput worked like a charm