none
Number Changes when saving Data to another Database to a minus Number RRS feed

  • Question

  • Hi Great People

    I have an issue when saving Data into another database.

    What is happening is, When I click a Button (Save) The integer number is (1) It is also auto incremented, so when the next lot of data is inserted the Integer Number is (2) When I click the Save Button.

    I also insert the data to another database at the same time.

    But when I check the data in the other Database the integer number is a minus Number (-1) when it should be the same as the first database (1)

    Have any of you Great People Go any Idea's of how to solve this problem.

    Pictures Below

    First Database

    Second Database As you can see The Second Column (Parts Purchased ID) is different from The First Picture (Parts Purchased ID) 

    Kind Regards

    Gary


    Gary Simpson

    Saturday, November 21, 2020 8:08 PM

Answers

  • Good to hear that you have figured it out. I was going to say that usually -1 means a new record not yet saved and once saved is populated with the real number (only for SQL-Server and Oracle)

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    My GitHub code samples
    GitHub page

    • Marked as answer by Gary Simpson Saturday, November 21, 2020 10:48 PM
    Saturday, November 21, 2020 10:46 PM
    Moderator

All replies

  • Hello,

    What does the INSERT statement look like? 

    Here is an example for how I do it which returns to new primary key.

    INSERT INTO dbo.employee (FirstName, LastName, HiredDate) VALUES (@FirstName, @LastName, @HiredDate); SELECT CAST(scope_identity() AS int);


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    My GitHub code samples
    GitHub page

    Saturday, November 21, 2020 8:42 PM
    Moderator
  • Hi Karen

    Thank you For getting back to me. My insert statement looks like this....

    Private Sub InsertIntoPartsAwaitingPayment()
    
            SQL.AddParam("@PartsPurchasedID", TxtPartsPurchasedID.Text)
            SQL.AddParam("@ReceiptNumber", TxtReceiptNumber.Text)
            SQL.AddParam("@CompanyNameBroughtFrom", TxtCompanyNameBroughtFrom.Text)
            SQL.AddParam("@TypeOfPart", TxtTypeOfPart.Text)
            SQL.AddParam("@DatePurchased", GetDateFromString(TxtDatePurchased.Text))
            SQL.AddParam("@RegistrationNo", TxtRegistrationNo.Text)
            SQL.AddParam("@PartPrice", GetDec(TxtPartPrice.Text))
            SQL.AddParam("@AmountPaid", GetDec(TxtAmountPaid.Text))
            SQL.AddParam("@VATPaid", GetDec(TxtVATPaid.Text))
            SQL.AddParam("@Notes", TxtNotes.Text)
            SQL.AddParam("@Username", LbUsername.Text)
            SQL.AddParam("@DateInserted", GetDateFromString(LbDateInserted.Text))
    
            SQL.ExecQuery("INSERT INTO PartsToPayFor(PartsPurchasedID,ReceiptNumber,CompanyNameBroughtFrom, " &
                          "TypeOfPart,DatePurchased,RegistrationNo,PartPrice,AmountPaid,VATPaid,Notes,Username,DateInserted) " &
                          "VALUES(@PartsPurchasedID,@ReceiptNumber,@CompanyNameBroughtFrom, " &
                          "@TypeOfPart,@DatePurchased,@RegistrationNo,@PartPrice,@AmountPaid,@VATPaid,@Notes,@Username,@DateInserted);")
    
            If SQL.HasException(True) Then Exit Sub
            MsgBox("Saved Successfully!", MsgBoxStyle.Information, LbUsername.Text)
    
            LoadGrid()
        End Sub


    Gary Simpson

    Saturday, November 21, 2020 9:16 PM
  • Hi Karen

    I think I have the answer.

    I have to save the data before inserting the data into another Database.

    Because If I use my insert Statement, The TxtPartsPurchasedID value is actually (-1) That Means I have to save the data for the TxtPartsPurchasedID Changes to the next incremented Number.

    It's only Then I can insert the data into another database.

    But thank you for replying to my question

    Best Regards

    Gary


    Gary Simpson

    Saturday, November 21, 2020 9:44 PM
  • Good to hear that you have figured it out. I was going to say that usually -1 means a new record not yet saved and once saved is populated with the real number (only for SQL-Server and Oracle)

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    My GitHub code samples
    GitHub page

    • Marked as answer by Gary Simpson Saturday, November 21, 2020 10:48 PM
    Saturday, November 21, 2020 10:46 PM
    Moderator