This code sample shows how to grab the last id# after an insert, using older vesions of MS Access (before Access 2000).
The database which is being used, has two tables, one called Items and one called Cateogories. the Items database has an autonumbering ID field, and two other text fields (ItemName and ItemText). The Categories table has two fields (CatID, also Autonumbering, and CatName).
To see a more preferred method, using Access 2000 and above, check out this code sample.
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDB" %>
<html>
<head>
<script language="VB" runat="server">
Dim intNewID
Sub Page_Load(Source as Object, E as EventArgs)
Dim strConn as string = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("/data/Newprocedures.mdb") & ";"
Dim MySQL as string = "Select CatID, catName from Categories Order by CatID"
Dim MyConn as New OleDBConnection(strConn)
Dim objDR as OleDBDataReader
Dim Cmd as New OLEDBCommand(MySQL, MyConn)
MyConn.Open()
objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
frmCatID.DataTextField="catName"
frmCatID.DataValueField="catID"
frmCatID.DataSource = objDR
frmCatID.DataBind()
MyConn.Close()
End Sub
Sub doInsert(Source as Object, E as EventArgs)
Dim strConn as string = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("/data/Newprocedures.mdb") & ";"
Dim MySQL as string = "Insert into Items (itemName, itemText, CatID) Values (@itemName, @itemText, @CatID);"
Dim MySQL2 as string = "select MAX(ID) from Items"
Dim MyConn as New OleDbConnection(strConn)
Dim Cmd as New OleDbCommand(MySQL, MyConn)
Dim Cmd2 as New OLEDBCommand(MySQL2, MyConn)
With Cmd.Parameters
.Add(New OleDbParameter("@itemName", frmitemName.text))
.Add(New OleDbParameter("@itemText", frmitemText.text))
.Add(New OleDbParameter("@CatID", frmCatID.selecteditem.value))
End With
MyConn.Open()
cmd.ExecuteNonQuery()
Label1.text = "It's Done - your information has been inserted!<p>"
label1.text +="Last ID Inserted = " & cmd2.executescalar()
MyConn.Close
End Sub
</script>
<meta name="GENERATOR" Content="ASP Express 2.1">
<title>Get Last Inserted ID with MS Access</title>
</head>
<body>
<form runat="server">
<table>
<tr>
<td align="right">Item Name</td>
<td> <asp:textbox id="frmitemName" runat="server" /></td>
</tr>
<tr>
<td align="right">Item Text</td>
<td> <asp:textbox id="frmitemText" runat="server" /></td>
</tr>
<tr>
<td align="right">Category</td>
<td> <asp:dropdownlist id="frmCatID" runat="server" /></td>
</tr>
<tr>
<td align="right"><asp:button id="button1" onclick="doInsert" Text="Submit" runat="server" /></td>
<td></td>
</tr>
</table>
<asp:Label ID="label1" runat="server" />
</form>
</body>
</html>