Results 1 to 8 of 8

Thread: SQL Server 2000

  1. #1
    Join Date
    Dec 2009
    Posts
    3

    Unanswered: SQL Server 2000

    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.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    UPDATE MasterData
    SET    NextInvoiceNumber = NextInvoiceNumber + 1
    OUTPUT inserted.NextInvoiceNumber
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    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.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Apologies, I was skim reading in a rush earlier so missed that subtlty

    Is Scope_Identity() available in 2000?
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    As a matter of fact, it is.

  6. #6
    Join Date
    Dec 2009
    Posts
    3
    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?

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    use SET NOCOUNT ON in the stored procedure. ADO gets confused, when it sees multiple messages about rows affected.

  8. #8
    Join Date
    Dec 2009
    Posts
    3
    Thats it.

    Done it now.

    Thanks very much

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •