How to get the value of counter field

[fniles]

I am using VB.NET 2005 and Access database.
In the database I have table (myTbl) with a column called ID as a primary
key, and it is a counter field.
When I insert into myTbl, how can I get the value of ID ?

Private m_cmd As OleDb.OleDbCommand

m_cmd = New OleDb.OleDbCommand
sSQL = "insert into myTbl (col1,col2) VALUES (?,?)"
m_cmd.Parameters.Add("@col1", OleDb.OleDbType.VarChar, 50, "col1")
m_cmd.Parameters.Add("@col2", OleDb.OleDbType.VarChar, 50, "col2")
m_cmd.Parameters("@col1").Value = sParameterValue1
m_cmd.Parameters("@col2").Value = sParameterValue2
With m_cmd
.Connection = adoConOLE
.CommandText = sSQL
End With
m_cmd.ExecuteNonQuery()

How can I get the value of the column ID ?
m_cmd.Parameters.Add("@ID" & sParameterName, OleDb.OleDbType.BigInt, 4,
"ID").Direction = ParameterDirection.Output '--> Can I do this, and if I
can, is the following sql statement correct ?
insert into myTbl (col1,col2) VALUES (?,?)


Thank you.

 

[Bob Barrows [MVP]]

I am using VB.NET 2005 and Access database.

There was no way for you to know it (except maybe by browsing through some
of the previous questions in this newsgroup before posting yours - always a
recommended practice) , but this is a classic ADO newsgroup. ADO.Net bears
very little resemblance to classic ADO so, while you may be lucky enough to
find a dotnet-knowledgeable person here who can answer your question, you
can eliminate the luck factor by posting your question to a group where
those dotnet-knowledgeable people hang out. I suggest
microsoft.public.dotnet.framework.adonet.

In the database I have table (myTbl) with a column called ID as a
primary key, and it is a counter field.
When I insert into myTbl, how can I get the value of ID ?

Private m_cmd As OleDb.OleDbCommand

m_cmd = New OleDb.OleDbCommand
sSQL = "insert into myTbl (col1,col2) VALUES (?,?)"
m_cmd.Parameters.Add("@col1", OleDb.OleDbType.VarChar, 50, "col1")
m_cmd.Parameters.Add("@col2", OleDb.OleDbType.VarChar, 50, "col2")
m_cmd.Parameters("@col1").Value = sParameterValue1
m_cmd.Parameters("@col2").Value = sParameterValue2
With m_cmd
.Connection = adoConOLE
.CommandText = sSQL
End With
m_cmd.ExecuteNonQuery()

Open a datareader with this statement: "Select @@IDENTITY"

 

[Stephany Young]

You really mean an AutoIncrement column don't you.

The short answer is that you can't do this. Jet does not have the equivalent
of SQL Server's @@IDENTITY variable or SCOPE_IDENTITY() function.

Before I demonstrate a way of getting the information you want, I need to
add a big caveat.

If other applications are also inserting rows into the table then you will
run the risk of getting erroneous information if multiple applications
insert rows within in a short time of one another. By a short time I mean a
small number of milliseconds. The risk is low but is there all the same.

What you need to do is execute the equivalent of:

select max(id) from mytable

directly after the insert. Do not pass go, do not collect $200 and DO NOT
execute any other code in between.

Your code is overly complicated but the whole thing would become:

Dim _cmd1 As New OleDb.OleDbCommand("insert into myTbl (col1,col2) VALUES
(?,?)", adoConOLE)

Dim _cmd2 As New OleDb.OleDbCommand("select max(id) from myTbl",
adoConOLE)

_cmd1.Parameters.Add("col1", OleDb.OleDbType.VarChar).Value =
sParameterValue1

_cmd1.Parameters.Add("col2", OleDb.OleDbType.VarChar).Value =
sParameterValue2

_cmd1.ExecuteNonQuery()

Dim _id As Integer = CType(_cmd2.ExecuteScalar(), Integer)

To make it a little more robust, (but still not foolproof), you could wrap
the operation in a transcation to give the operation a degree of 'locking':

Dim _cmd1 As New OleDb.OleDbCommand("insert into myTbl (col1,col2) VALUES
(?,?)", adoConOLE)


Dim _cmd2 As New OleDb.OleDbCommand("select max(id) from myTbl",
adoConOLE)

_cmd1.Parameters.Add("col1", OleDb.OleDbType.VarChar).Value =
sParameterValue1

_cmd1.Parameters.Add("col2", OleDb.OleDbType.VarChar).Value =
sParameterValue2


Dim _trn as OleDbTransaction = adoConOLE.BeginTransaction()

_cmd1.Transaction = _trn

_cmd2.Transaction = _trn

_cmd1.ExecuteNonQuery()

Dim _id As Integer = CType(_cmd2.ExecuteScalar(), Integer)

_trn.Commit()

Note that the transaction is as short-lived as possible.

Get the idea?

Your problem also demonstrates the pitfalls in using an AutoIncrement column
as a primary key, but that is a discussion for another time and place.

 

[Kerry Moorman]

Stephany,

Some versions of the Jet OleDB provider support Select @@Identity:

http://support.microsoft.com/default.aspx/kb/232144

I have not used Access in a while, but I have used Select @@Identity to
retrieve the newly generated autonumber value with Access in the past.

Kerry Moorman

 

[Stephany Young]

They snuck that one in quietly

I stand educated.

 

[David Carr]

fniles,

This is most certainly possible in Access, and from a bit of googling you'll
come up with hits that may include this one describing how to do it:
http://msdn2.microsoft.com/en-us/library/ks9f57t0(VS.80).aspx

I have used this approach against an Access, SQL Server, and Oracle DB -
works for all (changing the syntax/construct appropriately).

Taking this article one step further, I have used a standard approach when
using the DataSet designer in VS.NET, where:
1. Set up your xxxTableAdapter
2. Add a new query called 'GetNewID()'. The Access syntax for this simply
'SELECT @@IDENTITY'.
3. Add an event handler for OnRowUpdated() (see code segment below).
4. In your code using xxxTableAdapter, add the record, then call the
Update() method of your instantiation of xxxTableAdapter. Putting a
breakpoint within the OnRowUpdated() handler will see the value being set.

Voila...that's it.

Cheers,
David

namespace MyTableAdapters
Partial Public Class ProjectTableAdapter
Public Sub OnRowUpdated(ByVal sender As Object, ByVal args As
OleDb.OleDbRowUpdatedEventArgs) Handles _adapter.RowUpdated

Debug.Print("OnRowUpdated")

' If a row updated due to insertion, then set the Autonumber field.

If args.StatementType = StatementType.Insert Then

'Get the ID of the record just inserted

Dim lNewID As Long = 0

lNewID = CInt(Me.GetNewID())

args.Row("ID") = lNewID

End If

End Sub

End Class

End Namespace

 

[Stephen Howe]

This is most certainly possible in Access, and from a bit of googling

you'll come up with hits that may include this one describing how to do
it:
http://msdn2.microsoft.com/en-us/library/ks9f57t0(VS.80).aspx

Why are you posting to microsoft.public.data.ado ?
IT IS NOT A .NET NEWSGROUP, it is for ADO, not ADO.NET

I am sick and tired of clueless .NET programmers continually posting to
microsoft.public.data.ado.
Microsoft created hundreds of .NET newsgroups, all with "dotnet" somewhere
in the newsgroup name.
If a newsgroup does not have "dotnet" in the name it is not a .NET
newsgroup.

If you cant distinguish newsgroups you should abandon progarmming, you are
not up to it.

Stephen Howe

 

[David Carr]

My apologies to all but one for this oversight - I hit Reply Group simply to
offer assistance to fniles' original post. I would seem I overlooked that
he had originally cross-posted. In terms of my reply, I intended it only to
be sent to the dotnet.languages.vb.data group.

As for you, Stephen Howe, your bedside manner is something to be desired.
Shouting doesn't win support from anyone, let alone your holier than thou
and judgemental attitude. Clearly your illness and fatigue doesn't prevent
you from ranting.

I won't be engaging in a pissing contest - I most certainly am up to
programming and need not prove it to you nor anyone else - so I won't
respond further to this. I will, however, try to remember never to offer
advice to someone named 'Stephen Howe'.

Regards,
David