locked
Reading database ID (auto generated number) field ? RRS feed

  • Question

  • I use the code below to insert some data to my database :

     Dim cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\info.mdb;Persist Security Info=False;")
                If cn.State = ConnectionState.Open Then
                    cn.Close()
                End If
                cn.Open()
    
    
    
                Dim sSQL As String = "insert into _members(FirstName,LastName) values(@d1,@d2)"
                Dim cmd As OleDbCommand = New OleDbCommand(sSQL, cn)
    
    
    
                ' FirstName
                Dim FirstName As OleDbParameter = New OleDbParameter("@d1", OleDbType.VarWChar, 15)
                FirstName.Value = txtname.Text.ToString()
                cmd.Parameters.Add(FirstName)
    
                ' LastName
                Dim LastName As OleDbParameter = New OleDbParameter("@d2", OleDbType.VarWChar, 15)
                LastName.Value = txtlastname.Text.ToString()
                cmd.Parameters.Add(LastName)

    After adding a record , I need to read the first field called ID of the added record ,which is auto generated number, and i want to put it into a textbox How to do that ?

    Thanks


    • Edited by Kevin993 Wednesday, September 12, 2018 7:45 PM
    Wednesday, September 12, 2018 7:45 PM

Answers

  • I have a full example here.

    Here is an extract from the code in the link above. ExecuteNonQuery inserts the record while ExecuteScalar gets the new primary key (assumes it's auto-incrementing).

    cmd.ExecuteNonQuery() 
    cmd.CommandText = "Select @@Identity" 
    pIdentfier = CInt(cmd.ExecuteScalar) 


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Proposed as answer by Alex-KSGZ Thursday, September 13, 2018 5:42 AM
    • Marked as answer by Kevin993 Thursday, September 13, 2018 5:27 PM
    Wednesday, September 12, 2018 11:19 PM

All replies

  • Research "ExecuteScalar" to get your ID. To be certain that you are getting the ID from that particular row, I might recommend adding a GUID column, create a new param and insert the GUID, then your executescalar command should use this GUID in the where clause

    If applicable you can also add schema to a datatable VIA DataAdapter and this column value will then be available as you create the row as long as your adding the row with bindingsource/datatable/adapter.update



    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi



    • Edited by Gtripodi Wednesday, September 12, 2018 8:32 PM
    Wednesday, September 12, 2018 8:29 PM
  • I have a full example here.

    Here is an extract from the code in the link above. ExecuteNonQuery inserts the record while ExecuteScalar gets the new primary key (assumes it's auto-incrementing).

    cmd.ExecuteNonQuery() 
    cmd.CommandText = "Select @@Identity" 
    pIdentfier = CInt(cmd.ExecuteScalar) 


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Proposed as answer by Alex-KSGZ Thursday, September 13, 2018 5:42 AM
    • Marked as answer by Kevin993 Thursday, September 13, 2018 5:27 PM
    Wednesday, September 12, 2018 11:19 PM