By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,203 Members | 1,549 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,203 IT Pros & Developers. It's quick & easy.

Returning autogenerated ID

P: n/a
I've got a table with an autogenerated number as it's primary key,
which is being accessed by an external .NET app. When the app inserts
a new record into the table, is there any way to return the ID? Using
a "SELECT [id] FROM table WHERE (repeat of inserted data) ORDER BY
[id]" and selecting the largest ID would probably work, but that's
extremely clunky.

I'm new to databases in general, but this should be a common enough
problem that somebody has a more elegant way around it.

Any words of wisdom from folks more experienced at this than I?
Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Hi Ted
I haven't got any .net experience, so there may be other 'tricks' here
available through the .net driver, but just in terms of generic sql &
client server type thinking (regardless of client or front end platforms),
variations on your approach are common.

Probably the most common idea is to use a (unique) timestamp field - you
can either populate it with the client, keep a copy and callback the
record by the timestamp, or have the server populate it and call back the
youngest record. Access times only go to a second, so you might want to
combine it with something else, say a workstation key, or like you're
doing now with some keys from the inserted data.

Someone from .NET land can probably give you something more specific, but
hey its better than no reply :-)
Glenn

On Mon, 25 Oct 2004 14:24:30 -0700, Ted Stewart wrote:
I've got a table with an autogenerated number as it's primary key,
which is being accessed by an external .NET app. When the app inserts
a new record into the table, is there any way to return the ID? Using
a "SELECT [id] FROM table WHERE (repeat of inserted data) ORDER BY
[id]" and selecting the largest ID would probably work, but that's
extremely clunky.

I'm new to databases in general, but this should be a common enough
problem that somebody has a more elegant way around it.

Any words of wisdom from folks more experienced at this than I?


Nov 13 '05 #2

P: n/a
The only solution I've been able to come up with, slightly less clunky, is
to always insert records with an updated (date-time stamp) field and an
updated_by (user login) field, then selecting for those two fields
(something like):

Dim uDate as Date
uDate = Now()

' INSERT date with uDate and userLogin

newId = "SELECT address_id FROM address WHERE updated = #" & uDate & "# AND
update_by = '" & userLogin & "'"
Darryl Kerkeslager
"Ted Stewart" <re*****@hotmail.com> wrote:
I've got a table with an autogenerated number as it's primary key,
which is being accessed by an external .NET app. When the app inserts
a new record into the table, is there any way to return the ID? Using
a "SELECT [id] FROM table WHERE (repeat of inserted data) ORDER BY
[id]" and selecting the largest ID would probably work, but that's
extremely clunky.

I'm new to databases in general, but this should be a common enough
problem that somebody has a more elegant way around it.

Any words of wisdom from folks more experienced at this than I?

Nov 13 '05 #3

P: n/a
The problem with your approach is that in a networked environment, another
user could possibly insert another record between your insert and your
retrieval, so the largest ID would be his, not yours.

HTH
- Turtle

"Ted Stewart" <re*****@hotmail.com> wrote in message
news:44**************************@posting.google.c om...
I've got a table with an autogenerated number as it's primary key,
which is being accessed by an external .NET app. When the app inserts
a new record into the table, is there any way to return the ID? Using
a "SELECT [id] FROM table WHERE (repeat of inserted data) ORDER BY
[id]" and selecting the largest ID would probably work, but that's
extremely clunky.

I'm new to databases in general, but this should be a common enough
problem that somebody has a more elegant way around it.

Any words of wisdom from folks more experienced at this than I?

Nov 13 '05 #4

P: n/a
Ted,
Unless you have a burning need for a custom autonumber scheme different from
the sequence and random autonumbers that Access provides I'd go with those.
But if you do need a custom autonumber the best solution I've seen involves
using a single row table to store the last sequence number generated. Then
your code increments that number and returns the result to whatever wanted
it. You should include exception handling that dumps the transaction if a
new sequence number cannot be generated.

"Ted Stewart" <re*****@hotmail.com> wrote in message
news:44**************************@posting.google.c om...
I've got a table with an autogenerated number as it's primary key,
which is being accessed by an external .NET app. When the app inserts
a new record into the table, is there any way to return the ID? Using
a "SELECT [id] FROM table WHERE (repeat of inserted data) ORDER BY
[id]" and selecting the largest ID would probably work, but that's
extremely clunky.

I'm new to databases in general, but this should be a common enough
problem that somebody has a more elegant way around it.

Any words of wisdom from folks more experienced at this than I?

Nov 13 '05 #5

P: n/a

"Glenn Davy" <gS***********@tpg.com.au> wrote in message
news:pa***************************@tpg.com.au...
Hi Ted
I haven't got any .net experience, so there may be other 'tricks' here
available through the .net driver, but just in terms of generic sql &
client server type thinking (regardless of client or front end platforms),
variations on your approach are common.

Probably the most common idea is to use a (unique) timestamp field - you
can either populate it with the client, keep a copy and callback the
record by the timestamp, or have the server populate it and call back the
youngest record. Access times only go to a second, so you might want to
combine it with something else, say a workstation key, or like you're
doing now with some keys from the inserted data.

Someone from .NET land can probably give you something more specific, but
hey its better than no reply :-)
Glenn

On Mon, 25 Oct 2004 14:24:30 -0700, Ted Stewart wrote:
I've got a table with an autogenerated number as it's primary key,
which is being accessed by an external .NET app. When the app inserts
a new record into the table, is there any way to return the ID? Using
a "SELECT [id] FROM table WHERE (repeat of inserted data) ORDER BY
[id]" and selecting the largest ID would probably work, but that's
extremely clunky.

I'm new to databases in general, but this should be a common enough
problem that somebody has a more elegant way around it.

Any words of wisdom from folks more experienced at this than I?

The following page is written with ASP.NET and shows you an example of
getting the ID. It assumes you have a file C:\Contacts.mdb which has a
single table tblContacts with fields ConID (PK Autonumber) and ConName
(Text).

' ************************************************** *
<%@ Page Language="VB" Strict="true" Debug="true" %>
<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.OleDb" %>

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

Dim cnn As OleDbConnection

Sub AddContact(sender As Object, e As System.EventArgs)

Dim strCnn As String
Dim strConName As String
Dim daContacts As OleDbDataAdapter
Dim dsContacts As Data.DataSet
Dim newRow As Data.DataRow

strConName = Trim(CStr(txtConName.Value))

If Len(strConName) = 0
divMessage.InnerHtml = "You must enter a contact name."
Exit Sub
End If

strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Contacts.mdb;"

cnn = New OleDbConnection(strCnn)

cnn.Open()

daContacts = New OleDbDataAdapter("SELECT ConID, ConName FROM tblContacts
ORDER BY ConID", cnn)
daContacts.InsertCommand = New OleDbCommand("INSERT INTO tblContacts
(ConName) Values(?)", cnn)
daContacts.InsertCommand.CommandType = CommandType.Text
daContacts.InsertCommand.Parameters.Add("@ConName" , OleDbType.Char, 50,
"ConName")

dsContacts = New DataSet
daContacts.Fill(dsContacts, "tblContacts")
newRow = dsContacts.Tables("tblContacts").NewRow()
newRow("ConName") = strConName

dsContacts.Tables("tblContacts").Rows.Add(newRow)
AddHandler daContacts.RowUpdated, New OleDbRowUpdatedEventHandler(AddressOf
OnRowUpdated)
daContacts.Update(dsContacts, "tblContacts")
divMessage.InnerHtml = "A new contact has been added:<br>" & _
"Name = """ & strConName & """<br>" & _
"ID = " & CStr(newRow("ConID"))
txtConName.Value = ""

cnn.Close()
cnn.Dispose
End Sub

Sub OnRowUpdated(sender As Object, args As OleDbRowUpdatedEventArgs)

Dim cmd As OleDbCommand
Dim conID As Integer

cmd = New OleDbCommand("SELECT @@IDENTITY", cnn)

If args.StatementType = StatementType.Insert
conID = CInt(cmd.ExecuteScalar())
args.Row("ConID") = conID
End If

End Sub

Sub Cancel(sender As Object, e As System.EventArgs)
txtConName.Value=""
End Sub

Sub Page_Load(Source As Object, E As EventArgs)
divMessage.InnerHtml = ""
End Sub
</script>

<title>Jet Autonumber Test</title>
</head>
<body>

<form runat="server">
Add New Contact:<br>
<input id="txtConName" type="text" runat="server"/>
<input type="submit" value="Cancel" runat="server" onserverclick="Cancel"/>
<input type="submit" value="Add" runat="server" onserverclick="AddContact"/>
<br><br><div id="divMessage" runat="server" />
</form>

</body>
</html>
' ************************************************** *
Nov 13 '05 #6

P: n/a
Using the current timestamp hadn't even occurred to me. Access might
only store time to the second, but I can retrieve the current time
down to the nearest 100 nanosecond tick and store it in a number field
or string. That will give me my primary key.

So my client will insert the record using the current timestamp to the
tick as the primary key, which can then be used by the client to
handle any updates.

Thanks for the help, everyone.
Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.