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 = DbType.date
dbComm.Parameters.Add(paramDate)

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

Conn.open
dbComm.ExecuteNonQuery()

conn.Close

dbcomm = nothing
conn = Nothing
strSQL = nothing

End Sub

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