last record with ado.net

CyberW · November 4th, 2008, 05:37 AM

Hello,
I have ms-access DB.

I'm using .net 3.5

I would like to know how can i select the last record that was entered to DB,
using oleDBReader?

Also i would like to save a value of a field in it.

thank you!

Old Pedant · November 4th, 2008, 06:11 PM

No way to know what the last record entered was in *ANY* database unless *YOU* have a field in the table that will help tell you.

For example, if you have an AUTONUMBER field, in Access. Or maybe you have a WHENADDED field that captures the current date and time.

But databases do *NOT* have any "natural order" that depends on when records were entered. It's perfectly legal (and done all the time!) for a DB to re-use the "slot" that a deleted record was into hold a newly entered record.

Also, what happens if another user adds another record to the table while you are working on the table???

CyberW · November 5th, 2008, 09:03 AM

HI, thanks
But after i make an insert command with OledbCommand,

I need to know the userID that was created Automaticlly with AutoNumbering id field.

How do i do that, with OledbReader?

Thank you !

Old Pedant · November 5th, 2008, 04:26 PM

AHHH! Different question and MUCH BETTER question!

GOOD!

It's simple: You just do another query:

Code:

    SELECT @@IDENTITY

using the *SAME OPEN CONNECTION*!!!

This is important! Many of the VS auto-generated controls will open the connection, make a query, and close the connection. You MUST prevent this. Make sure you are using the same open connection.

So if you create the code yourself, it's very easy. And you don't even need an OleDbReader; you can just use ExecuteScalar on the OleDbCommand object.

So your code sequence might be something like:

Code:

...
    Dim SQL As String = "INSERT INTO table ..." ' wherever you get your SQL from

    Dim conn As New OleDbConnection(...your connection string...)
    conn.Open()
    Dim cmd As New OleDbCommand( SQL, conn)
    cmd.ExecuteNonQuery()
    cmd.Dispose()
    ' you can reuse the cmd variable this way or use a different one:
    cmd = New OleDbCommand( "SELECT @@IDENTITY", conn )
    Dim newID As Integer = CINT( cmd.ExecuteScalar() )
    cmd.Dispose()
    conn.Close() ' NOW it is okay to close connection!
    ...

CyberW · November 6th, 2008, 10:08 AM

thank you !

But does ms-access supports IDENTITY?

also what is the CINT function?

thank u

Old Pedant · November 6th, 2008, 04:39 PM

The MSAccess driver does not support the @@IDENTITY, but the JET OLEDB driver does (it was added I think in verions 2.0 of MDAC, just for compatibility w/ SQL Server, I assume). So just make sure you use the JET driver for your Connection string.

CINT( ) is a VB function that converts any value that *CAN* be converted to INTEGER to that type. You could use
CTYPE( cmd.ExecuteScalar(), INTEGER )
instead. They do the same thing. CINT is left over from older versions of VB (and VBScript) and is--I think--just handier and simpler to use.

If you are using C#, just use a cast, instead.