Getting Primary key values (auto number ) VB

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

Answers


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()

Need Your Help

How long would it take for a For loop to run through 1000 loops?

php arrays

I have a SELECT query that could return a 1000 names max - more likely to return 300-400 names.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.