Getting Primary key values (auto number ) VB

Question

I have a database on Access and I want to insert into 2 tables

ReportReq
req_sysino

I want to get the last value of primary key (auto numbered) and insert it into req_sysino , I am stuck with this code and I dont know how to proccess

Private Function InsertSysInvToDB(intSysInv As Integer) As Integer

    Dim strSQLStatement As String = String.Empty
    Dim intNoAffectedRows As Integer = 0
    Dim con As New OleDb.OleDbConnection("PROVIDER = Microsoft.ace.OLEDB.12.0; Data Source = C:\Users\felmbanF\Documents\Visual Studio 2012\Projects\WebApplication3\WebApplication3\App_Data\ReportReq.accdb")
    Dim cmd As OleDb.OleDbCommand
    Dim reqnum As String = "Select  @@REQ_NUM from ReportReq"
    strSQLStatement = "INSERT INTO req_sysino (Req_num, sysinvo_ID)" +
        " VALUES (" & reqnum & "','" & intSysInv & ")"

    cmd = New OleDb.OleDbCommand(strSQLStatement, con)

    cmd.Connection.Open()

    intNoAffectedRows = cmd.ExecuteNonQuery()
    cmd.Connection.Close()

    Return intNoAffectedRows
End Function

this is my insert code that should generate autonumber

   Dim dbProvider = "PROVIDER = Microsoft.ace.OLEDB.12.0;"
    Dim dbSource = " Data Source = C:\Users\felmbanF\Documents\Visual Studio 2012\Projects\WebApplication3\WebApplication3\App_Data\ReportReq.accdb"

    Dim sql = "INSERT INTO ReportReq (Emp_EmpID, Req_Date,Req_expecDate,Req_repnum, Req_name, Req_Descrip, Req_columns, Req_Filtes, Req_Prompts)" +
        "VALUES (@reqNUM,@reqName,@reqDescrip,@reqcolumns,@reqfilters,@reqprompts)"

    Using con = New OleDb.OleDbConnection(dbProvider & dbSource)
        Using cmd = New OleDb.OleDbCommand(sql, con)
            con.Open()
            cmd.Parameters.AddWithValue("@EmpID", txtEmpID.Text)
            cmd.Parameters.AddWithValue("@reqDate", DateTime.Today)
            cmd.Parameters.AddWithValue("@reqExpecDate", DateTime.Parse(txtbxExpecDate.Text).ToShortDateString())
            cmd.Parameters.AddWithValue("@reqNUM", txtRep_NUM.Text)
            cmd.Parameters.AddWithValue("@reqName", txtRep_Name.Text)
            cmd.Parameters.AddWithValue("@reqDescrip", txtbxRep_Desc.Text)
            cmd.Parameters.AddWithValue("@reqcolumns", txtbxColReq.Text)
            cmd.Parameters.AddWithValue("@reqfilters", txtbxFilReq.Text)
            cmd.Parameters.AddWithValue("@reqprompts", txtbxPromReq.Text)
            cmd.ExecuteNonQuery()
        End Using
    End Using

Please post the INSERT statement that creates the last generated primary key value. — ron tornambe, Jun 29 '14 at 10:53
You need to execute the command to create `reqnum` before you try and insert it into the other table. — Grim, Jun 29 '14 at 10:56
possible duplicate of [Return Last ID (IDENTITY) On Insert row VB.NET MySQL](http://stackoverflow.com/questions/9791156/return-last-id-identity-on-insert-row-vb-net-mysql) — The Blue Dog, Jun 29 '14 at 11:21

score 0 · Answer 1 · answered Jun 29 '14 at 13:47

0

Immediately after you ExecuteNonQuery() your INSERT INTO ReportReq ... statement you need to run a

SELECT @@IDENTITY

query and retrieve its result, like this

cmd.ExecuteNonQuery()  ' your existing statement to run INSERT INTO ReportReq
cmd.CommandText = "SELECT @@IDENTITY"
Dim newAutoNumberValue As Integer = cmd.ExecuteScalar()

answered Jun 29 '14 at 13:47

Gord Thompson

116,920
32
215
418

it did work but how can i use it in my functin. if you notice the first code is a dieferent function from the insert into statment – Flimboo Jun 30 '14 at 11:18
@Flimboo If both Functions are in the same Module then you can just `Dim newAutoNumberValue` at the module level and then it will be available to both functions. – Gord Thompson Jun 30 '14 at 12:03

Getting Primary key values (auto number ) VB

1 Answers1