I have to use SQL Server 200 as that is what my customer has and won't move.
I need to increment and Invoice number and unfotunately the existing software is written in VB6 so I have to stay with that.
I am usin the following code to update the Invoice Number but want to use the new updated number to print on the invoice and store in the Invoice Header / Details table.
Issue I have is that I cannot be sure that if I perform the update followed by the select that I get my updated value back as another program on another PC may have gotten another update in after mine.
Update MasterData set NextInvoiceNumber = NextInvoiceNumber + 1
I am using ADO and Recordsets and problem I have is that the recordset is empty after the update.
So how can I get the updated InvoiceNumber back into my VB program.
I don't think OUTPUT was available in SQL Server 2000.
What I would do is put all of this into a stored procedure. Ideally, you would have the Invoice Number column be an identity column, and return the value of @@identity after the insert. If you can not do that, then you can at least put the insert, and select of the new invoice number in a single transaction, which should guarantee you the right result, but I would not recommend this, except for the smallest of databases.
Thanks for what you have said already but I am having problems.
I have created a Stored Procedure that has both the Update and then the Select Statement in it.
I execute the Stored procedure and can see from the table that the Update has worked, but when I try to access the recordset (ADO) within the VB6 program it says "Item cannot be found in the collection corresponding to the requested name or ordinal" so as I understand it the recordset has been closed, so nothing availabel for me to use.
How do I get the result of the select into the progarm?