Post reply

no result from output parameter

  • August 16, 2003 at 9:01 am

    #83046

    I don't know if it's my sproc or my vb code so i'll just post both of them here..

    this is my insert sproc that is supposed to return an output parameter (but none, hence my problem) 

    

    create procedure up_InsertProduct (@description varchar(50),

    	@cost smallmoney, @SRP smallmoney, @productID int out) as

    	

    	insert into Product (productCode, description, cost, SRP)

    	values (@productCode, @description, @cost, @SRP)

    

    	select @productID = @@identity

    go

    and the vb code that executes this sproc

    

    Dim cmd As New ADODB.Command

    With cmd

        .ActiveConnection = cn

        .CommandText = "dbo.up_InsertProduct"

        .CommandType = adCmdStoredProc      

        .Parameters.Append .CreateParameter("RV", adInteger, adParamReturnValue)

        .Parameters.Append .CreateParameter("Description", adVarChar, adParamInput, 50, strDescription)

        .Parameters.Append .CreateParameter("Cost", adCurrency, adParamInput, , curCost)

        .Parameters.Append .CreateParameter("SRP", adCurrency, adParamInput, , curSRP)

        .Parameters.Append .CreateParameter("ProductID", adInteger, adParamOutput, , lngProductID)    

        .Execute

    End With

    I'm expecting lngProductID to contain the identity of the newly inserted record but it's always zero - vb's default for long variable. As a workaround, I did the dirty trick of returning @@identity instead but I know that this is not the intended purpose of sproc return value so I still want my output parameter. I'm using the newest MSDE.

    Please help.

    Edited by - bani on 08/16/2003 09:03:02 AM

    Edited by - bani on 08/16/2003 09:04:44 AM


    "The way to get things done is not to mind who gets the credit for doing them." - Benjamin Howett

  • August 16, 2003 at 3:48 pm

    #470194

    To isolate whther it's the sproc, try calling it from a query Analyzer script and then print/select the output parameter.

    Cheers,

    - Mark


    Cheers,
    - Mark

  • August 16, 2003 at 11:01 pm

    #470203

    Please try by adding SET NOCOUNT ON as the first statement of the sp.

    G.R. Preethiviraj Kulasingham

    Chief Technology Officer.

    Softlogic Information Systems Limited,

    14 De Fonseka Place,

    Colombo 05.

    Sri Lanka.

    Cheers,
    Prithiviraj Kulasingham

    http://preethiviraj.blogspot.com/

  • August 17, 2003 at 10:58 am

    #470212

    try adding to the vb code this line after the cmd is executed

    

    lngProductID = cmd.Parameters.Item(4).value

    Navin Parray


    Navin Parray

  • August 18, 2003 at 9:11 am

    #470282

    I did test the sproc both from the analyzer and vb. And I also found out in some articles that SET NOCOUNT OFF has adverse effects on performance. But it was the new knowledge in VB that made my day.

    Thanks so much guys!


    "The way to get things done is not to mind who gets the credit for doing them." - Benjamin Howett

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply