no result from output parameter

• swarm

  SSC Eights!

  Points: 976

  More actions

  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

• mccork

  SSC-Insane

  Points: 22288

  More actions

  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

• G.R.Prithiviraj Kulasingham

  SSCarpal Tunnel

  Points: 4105

  More actions

  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/

• nparray

  SSC Enthusiast

  Points: 146

  More actions

  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
  

• swarm

  SSC Eights!

  Points: 976

  More actions

  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