locked
How to return latest ID from auto increment after adding new row to database

    Question

  • Hi,

    After I add a new customer to my database, I would like to return the ID which was automatically created via auto increment if this is possible?

    Current code to add the data to table:

    Me.CustomersTableAdapter1.InsertCustomer(CompName.Text, ContactName.Text, address1.Text, address2.Text, address3.Text, postcode.Text, ContactNo.Text, DateValue(Now), userid.Text)

     

    Wednesday, October 19, 2011 12:45 PM

Answers

  • As I stated above, Result isn't the ID - it's the number of rows affected by the command.

    If you want to display the new ID, presuming it's not displayed anywhere else, you will have to read it from the dataset.

    I made a simple test project with just one table with a single identity, primary key column and the following code works fine.  You should be able to adapt it to suit your project:

          Table1BindingSource.AddNew()
          Table1BindingSource.EndEdit()
          If Table1TableAdapter.Update(Test1DataSet.Table1) = 1 Then
             Dim ID As Integer = Test1DataSet.Table1(Test1DataSet.Table1.Count - 1).ID
             MsgBox("Customer added, ID = " & ID.ToString)
          End If
    
    

    • Marked as answer by 1deejuk Friday, October 21, 2011 2:16 PM
    Friday, October 21, 2011 11:06 AM

All replies

  • Hello 1deejuk,

    Hi,

    After I add a new customer to my database, I would like to return the ID which was automatically created via auto increment if this is possible?

    Current code to add the data to table:

    Me.CustomersTableAdapter1.InsertCustomer(CompName.Text, ContactName.Text, address1.Text, address2.Text, address3.Text, postcode.Text, ContactNo.Text, DateValue(Now), userid.Text)

     


    an easy way to get the last id and retrieve as many rows to the table, assuming that you have a dataset in your application, this example writes to a textbox control the number of rows in the table.

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Me.TextBox1.Text = Me.yourDataSetname.yourtablename.Rows.Count.ToString
        End Sub
    

    Where 'DataBase1DataSet' you enter the name of your dataset,
    Where 'USERS1' they will need to enter the name of your table.

    Regards.


    Carmelo La Monica

    Blog http://community.visual-basic.it/carmelolamonica/

    WordPress http://carmelolamonica.wordpress.com/

    Twitter  http://twitter.com/carmelolamonica

    Wednesday, October 19, 2011 1:06 PM
  • What if say rows 5&6 were deleted out of 10 rows, then if the latest row 11 would be counted as 9 when in actual fact the ID would be 11.

    Is there a more reliable way to do it?

    Thanks for your help

    Wednesday, October 19, 2011 1:09 PM
  • The method depends upon what sort of database you are using.  So .... what are you using?
    Wednesday, October 19, 2011 2:19 PM
  • sql express, sorry for not explaining better!

    Thanks

    Wednesday, October 19, 2011 3:28 PM
  • You can modify your insert statement to concatenate a select statement to retrieve the newly added record.

    You should be able to get VB to do that automatically if you right click on the tableadapter and select configure.

    Click on Advanced Options.  Check the box that says "Refresh the data table".

     

    Wednesday, October 19, 2011 4:24 PM
  • Hello,

    The only true way to get that value is in your T-SQL via "SELECT @@IDENTITY" as a return value from your insert.

    Adam


    Already reported as abusive
    • Proposed as answer by Armin Zingler Wednesday, October 19, 2011 5:14 PM
    Wednesday, October 19, 2011 4:32 PM
  • Hallo 1deejuk,

    sql express, sorry for not explaining better!

    Thanks

    I'm sorry, I got to return the number of rows and the last inserted id, I agree with what Adam said.
    For more information see the following examples.

    http://msdn.microsoft.com/en-us/library/ms187342.aspx

    http://msdn.microsoft.com/en-us/library/ms190315.aspx

     

    Regsrds.


    Carmelo La Monica

    Blog http://community.visual-basic.it/carmelolamonica/

    WordPress http://carmelolamonica.wordpress.com/

    Twitter  http://twitter.com/carmelolamonica

    Wednesday, October 19, 2011 7:10 PM
  • Hi,

    Thanks for your replies, that was very helpful I do have this on the end of my insert query "SELECT ID, companyname, contactname, address1, address2, address3, postcode, contactnumber, datereg, userid FROM Customers WHERE (ID = SCOPE_IDENTITY())" but it allways returns "1" even when it is say 6,7,8 ??

    Is the query wrong or am I returning it wrong? I just have: Result = Me.CustomersTableAdapter1.InsertCustomer(CompName.Text, etc..)

    My guess is that it is allways returning 1 as the result of success on adding a new row, so I guess the query is wrong?

    Thanks


    • Edited by 1deejuk Friday, October 21, 2011 8:49 AM
    Friday, October 21, 2011 8:41 AM
  • The InsertCustomer function simply returns the number of rows inserted which is why you are always seeing 1.

    The Select at the end of your insert command text reads the newly added row back in to the dataset and so any controls you have bound to the dataset should reflect the changes.

    How do you know it is not reading the allocated ID correctly?

    Friday, October 21, 2011 10:02 AM
  • There are 6 Rows in the database the last ID inserted is 6, after the row has been inserted I check the database again and the new row which has been inserted has been given ID 7, so therefore its not returning the ID.

    So is it to do with the insertcustomer command and this needs to be changed?

    Thanks

    Friday, October 21, 2011 10:32 AM
  • You still haven't told us where you are not seeing the ID correctly.

    Assuming you have a control which displays the ID when you call AddNew on the BindingSource you would normally see -1 displayed in the ID control as the dataset at that time doesn't know what ID is going to be allocated.  After calling insert (with the added select) that negative number would change to reflect the allocated ID.  Are you not seeing that?

    Friday, October 21, 2011 10:40 AM
  • Sorry the ID gets shown in a msg box after the insert shown below in the code, I changed -1 to +1 which ended up being just 1 as I thought -1 would be the direcrion of the increment, is this wrong should have I left it to -1? (just tried putting 1 back to -1 did not make a difference  )

     

    Result = Me.CustomersTableAdapter1.InsertCustomer(CompName.Text, ContactName.Text, address1.Text, address2.Text, address3.Text, postcode.Text, ContactNo.Text, DateValue(Now), userid.Text)
                 MsgBox("Customer added successfuly, you can now add quotes to this customer! New Customer ID " + Result, vbInformation) 

     

    Thanks



    • Edited by 1deejuk Friday, October 21, 2011 10:48 AM
    Friday, October 21, 2011 10:43 AM
  • As I stated above, Result isn't the ID - it's the number of rows affected by the command.

    If you want to display the new ID, presuming it's not displayed anywhere else, you will have to read it from the dataset.

    I made a simple test project with just one table with a single identity, primary key column and the following code works fine.  You should be able to adapt it to suit your project:

          Table1BindingSource.AddNew()
          Table1BindingSource.EndEdit()
          If Table1TableAdapter.Update(Test1DataSet.Table1) = 1 Then
             Dim ID As Integer = Test1DataSet.Table1(Test1DataSet.Table1.Count - 1).ID
             MsgBox("Customer added, ID = " & ID.ToString)
          End If
    
    

    • Marked as answer by 1deejuk Friday, October 21, 2011 2:16 PM
    Friday, October 21, 2011 11:06 AM
  • Counting rows or counting anything for that matter, has nothing to do with auto-increment ID's unless you never delete rows and set the seed at 1.
    Already reported as abusive
    Friday, October 21, 2011 11:44 AM
  • Hi Dave,

    Thanks for that I just tried your code suggestion and it throws out a strange error in the DataSet.designer "

     

    Return CType(Me.Rows(index),CustomersRow)"

    Error: There is no row at position -1.

    Thanks

     
    Friday, October 21, 2011 12:17 PM
  • Sorry it is because this was needed

    TableAdapter.Fill(DataSet.Customers) at the top!

    Many thanks it now works perfect, thanks Dave (once again)!!!!

    Friday, October 21, 2011 2:16 PM
  • Counting rows or counting anything for that matter, has nothing to do with auto-increment ID's unless you never delete rows and set the seed at 1.
    Already reported as abusive

    Does that have anything to do with the proposed solution?
    Friday, October 21, 2011 3:10 PM