Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003

    Question Unanswered: Retrieve NewID after Insert (Using MS ACCESS)

    Hi. I'm trying to retrieve the unique id of an inserted row but I'm having difficulty doing so. This page wasn't written by me and I don't really whats going on (I'm re-writing this page in ASP.NET but its currently not compatible with the server so I'm forced to stick with the below for the moment). whoever wrote this seems to have written the entire page within VB scripts so I don't know if that makes it more of less difficult.

    Can someone help me figure out how I can retrieve the ID after the insert? There will only be one person using this at a time so even tips towards a work around would help... I tried getting a msgbox to display the Last record within the table but that didn't work too well either.

    This is what I have on the page;

    <script language="VB" runat="server">

    Dim gConnString as string = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath(".") & "\CourseEvaluations_Be.mdb;

    Sub Page_Load ()

    pnlForm.Visible = "False"
    pnlConfirm.Visible = "True"

    Dim conn as new OleDbconnection
    conn.connectionstring = gConnString

    Dim strSQL as string
    strSQL = "INSERT INTO Tbl_Events (CourseDate, CourseID, VenueID, CourseAttending, CourseEntryBy) Values (@Date, @CourseID, @VenueID, @CourseAttending, @EntryBy);"

    Dim dbComm as New OleDbCommand
    dbComm.CommandText = strSQL
    dbComm.Connection = Conn

    Dim paramDate as New OleDBParameter
    paramDate.ParameterName = "@Date"
    paramDate.Value = Val_Date.text
    paramDate.DbType =

    'There are more params but I won't copy them all down because its too much information - they are similar to the above


    dbcomm = nothing
    conn = Nothing
    strSQL = nothing

    End Sub

  2. #2
    Join Date
    Jan 2007
    Provided Answers: 10
    Well, Access is the worst offendor for this issue... In other DBMS' systems (E.g. MS SQL Server); you can do this far more efficiently (not to mention accurately) through the use of a stored procedure.

    Basically all I'm saying is that the following method should *only* be used with Access.
    Pseduo Code
    perform insert
    open recordset
        select Max(id) from table
        assign value to variable
    close recordset
    That should give you a theoretical way of doing it; give it a go and if you struggle with any of the detail, post back
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts