Output parameter returning NULL

[coco86]

I have a stored procedure to copy a purchase order and all the order items associated with it but I need to know the new number when it's done. I found a sample on the web and modified it so that it looks like this

CREATE Procedure TestCopy
  @ID int
, @autonum int OUTPUT
AS
 begin tran
 insert tblPO (OrderID, CompanyID, VendorID, 
               OrderedBy,InputAmount, Shipto, ShipVia, 
               Terms, FOB, TaxID, ContactName, Phone, 
               Fax, POComments)
 Select OrderID, CompanyID, VendorID, OrderedBy, 
        InputAmount, Shipto, ShipVia, Terms, 
        FOB, TaxID, ContactName, Phone, Fax, 
        POComments
 from tblPO where POID = @ID
 set @autonum = @@identity
 Insert tblPOItems (POID, ItemName, ItemQty, ItemDescrip, 
                    ItemPrice) 
 select @autonum, ItemName, ItemQty, ItemDescrip, 
        ItemPrice 
 from tblPOItems where POID = @ID
 commit tran

Then I call it from my application like this:

Public Function copyPO(ByVal OldID As Long) As Long
 Dim NewID As Long
 Dim conn As New ADODB.Connection

 conn.Open
 conn.testcopy OldID, NewID
 conn.Close
 copyPO = NewID
End Function

The PO gets copied, but NewID is 0. If I run the stored procedure from Query Analyzer, I get NULL.

I can't quite figure it out because @autonum is getting set correctly, otherwise my line items wouldn't have the correct number and they do. I've never worked with output parameters before so maybe I'm doing something wrong.

-Coco

 

[JayKusch]

Am I safe in assuming that the OrderID is set as an Identity Field?

Thanks

J. Kusch

 

[coco86]

Yes, I'm sorry, I should have said that.

-Coco

 

[coco86]

wait, I'm sorry, no it's not. The identity field in this case is POID

-Coco

 

[JayKusch]

Looks like you need to set your @autonum below the tblPOItems insert statment.

Thanks

J. Kusch

 

[coco86]

Wouldn't that give me the Identity field (POItemID) from the tblPOItems?

-Coco

 

[JayKusch]

YES ... If you are wanting the Identity of the tblPO, one of the fields has to be set as an Identity field or the @@Identity will always come back null.

Thanks

J. Kusch

 

[coco86]

I tried taking out the second half of the SP so I ended up with this:

CREATE Procedure TestCopy
  @ID int
, @autonum int OUTPUT
AS
 begin tran
 insert tblPO (OrderID, CompanyID, VendorID, 
               OrderedBy,InputAmount, Shipto, ShipVia, 
               Terms, FOB, TaxID, ContactName, Phone, 
               Fax, POComments)
 Select OrderID, CompanyID, VendorID, OrderedBy, 
        InputAmount, Shipto, ShipVia, Terms, 
        FOB, TaxID, ContactName, Phone, Fax, 
        POComments
 from tblPO where POID = @ID
 set @autonum = @@identity 
 commit tran

I was thinking that if I could get the ID number, I could simply use a second SP to copy the line items. However, the results were the same.

-Coco

 

[JayKusch]

I believe your issue stems from the fact that your tblPO, from what I am reading, DOES NOT have an Identity field set. If there is no Identity field set on the table you are inserting into ... @@Identity will always be Null.

Thanks

J. Kusch

 

[JayKusch]

Here is the verbiage straight from BOL (Books OnLine) ...

@@IDENTITY
Returns the last-inserted identity value.

Syntax
@@IDENTITY

Return Types
numeric

Remarks
After an INSERT, SELECT INTO, or bulk copy statement completes, @@IDENTITY contains the last identity value generated by the statement. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated. If the statement fires one or more triggers that perform inserts that generate identity values, calling @@IDENTITY immediately after the statement returns the last identity value generated by the triggers. If a trigger is fired after an insert action on a table that has an identity column, and the trigger inserts into another table that does not have an identity column, @@IDENTITY will return the identity value of the first insert. The @@IDENTITY value does not revert to a previous setting if the INSERT or SELECT INTO statement or bulk copy fails, or if the transaction is rolled back.




Thanks

J. Kusch

 

[coco86]

Thanks for the information but that doesn't really address my issue. What I really need to know is why if I specify this:

  @ID int
, @autonum int OUTPUT

and this:

set @autonum = @@identity

and then call this:

conn.testcopy OldID, NewID

do I get a value of 0 for NewID? Even though this:

Insert tblPOItems (POID, ItemName, ItemQty, ItemDescrip, 
                    ItemPrice) 
 select @autonum, ItemName, ItemQty, ItemDescrip, 
        ItemPrice 
 from tblPOItems where POID = @ID

works?


-Coco

 

[coco86]

I finally got this to work. After locating another similar post I tried changing my code to this:

Public Function copyPO(ByVal OldID As Long) As Long
    Dim NewID As Long
    Dim conn As New ADODB.Connection
    Dim cmdStoredProc As ADODB.Command
    
    conn.ConnectionString = MS_CONN_STRING
    conn.CursorLocation = adUseClient
    conn.Open
    Set cmdStoredProc = New ADODB.Command
    Set cmdStoredProc.ActiveConnection = conn
    
    With cmdStoredProc
        .CommandText = "testcopy"
        .CommandType = adCmdStoredProc
        .Parameters.Append .CreateParameter("@ID", 
                       adInteger, adParamInput, , OldID)
        .Parameters.Append .CreateParameter("@autonum", 
                       adInteger, adParamOutput, , NewID)
        .Execute
    End With
    
    copyPO = NewID
End Function

but it still didn't work. After more research and several unsuccessful modifications to the SP, I made this change:

    With cmdStoredProc
        .CommandText = "testcopy"
        .CommandType = adCmdStoredProc
        Set p1 = .CreateParameter("@ID", adInteger, 
                      adParamInput, , OldID)
        Set p2 = .CreateParameter("@autonum", adInteger, 
                      adParamOutput, , Null)
        .Parameters.Append p1
        .Parameters.Append p2
        .Execute
    End With
    
    copyPO = p2.Value

and I finally got the new PO number. I still don't understand exactly why the other methods didn't work for me as they seem to work for others but at least I now have a working function so I'm happy.

-Coco