Autonumber in Access / Dataset question..pls help!!!!!!

[Guest]

if I have a simple Access table like so:

Customer = (customerNo , firstName, lastName)

and the customerNo field has autonumbering set (i.e. is the primary key
value in the table and is a number that increments automatically when a new
record is added).

When I extract all records using a Dataset object. I want to add return the
value of the customerNo if I were to add a new record. So if I added a new
customer to the table I want to return a new customer number.

Do you know to do this >>!!? can the following code be adpated to do this
!?!??!


Public Function getData() As DataSet
Const INT_EMPTY As Integer = 0
Try
If mblnIsDatabaseOpen Then
Dim pstrSQL As String
If sqltext = "" Then Exit Function
Dim pobjAdtAdaptor As New OleDbDataAdapter
Dim pobjdstDataset As New DataSet
Dim sqlText = "SELECT * FOM CUSTOMER;"
pobjAdtAdaptor.SelectCommand = New OleDbCommand(sqltext,
mcnnObjConnection)
pobjdstDataset.Clear()
If pobjAdtAdaptor.Fill(pobjdstDataset, "Data") = INT_EMPTY
Then
getData = Nothing
Else
getData = pobjdstDataset.Tables("Data").DataSet
End If
pobjAdtAdaptor = Nothing
pobjdstDataset = Nothing
Else
MsgBox("Database not open")
End If
Catch ex As Exception
MsgBox("Could not query the database", MsgBoxStyle.Critical)
If mblnIsDatabaseOpen Then closeDatabase()
Exit Function
End Try
End Function

 

[Richard Myers]

Hi Karl

SELECT @@IDENTITY

Heres a C# article you should easily be able to pick thru.
http://www.c-sharpcorner.com/Code/2002/Nov/AutoNumber.asp

Richard

 

[Richard Myers]

Perhaps i should have added A2K and above.

 

[Guest]

I don't understand C##.

Whats @@IDENTITY mean!?!?!

cmd = new OleDbCommand("SELECT @@IDENTITY", conn);
int nId = (int)cmd.ExecuteScalar();

 

[Guest]

I don't understand C#.

Whats @@IDENTITY all about !??!

cmd = new OleDbCommand("SELECT @@IDENTITY", conn);
int nId = (int)cmd.ExecuteScalar();

please advise, Many thanks

 

[Richard Myers]

Hi Karl

You shouldn't have to understand C# for the purposes of reading that article as there is very little
difference in the code shown apart from {} etc when compared to VB.

@@IDENTITY

is an Access construct that allows you to query the Jet engine for the last AutoIncrement value.
Its Access specific not .net/vb/c# specific so you can use it from any platform/language.

So basically you run your insert code to insert the new record as per usual and then
you call the code you quoted below to get the the AutoInc supplied value of your PK column.

Because ExecuteScalar returns an object you then have to type it.
(int)cmd.ExecuteScalar() which is C# for CInt(cmd.ExecuteScalar)...... although
chances are your AutoInc column is of type Long in which case CLng(cmd.ExecuteScalar)
would be more appropriate.

hth
Richard

 

[Cor Ligthert]

Karl,

There is in my opinion not much to add to what Richard wrote, however Bill
made a complete article about this on MSDN.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/manidcrisis.asp

I hope this helps?

Cor