Wrox Programmer Forums
|
ASP.NET 3.5 Basics If you are new to ASP or ASP.NET programming with version 3.5, this is the forum to begin asking questions. Please also see the Visual Web Developer 2008 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 3.5 Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 4th, 2008, 05:37 AM
Authorized User
 
Join Date: Nov 2008
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default last record with ado.net

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 November 4th, 2008, 06:11 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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???
 
Old November 5th, 2008, 09:03 AM
Authorized User
 
Join Date: Nov 2008
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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 November 5th, 2008, 04:26 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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!
    ...
 
Old November 6th, 2008, 10:08 AM
Authorized User
 
Join Date: Nov 2008
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default

thank you !

But does ms-access supports IDENTITY?

also what is the CINT function?

thank u

 
Old November 6th, 2008, 04:39 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.





Similar Threads
Thread Thread Starter Forum Replies Last Post
ADO Record Locking pjohanne VB Databases Basics 3 March 9th, 2006 05:42 AM
ADO record copy and add to table Freddyfred Access 2 February 16th, 2005 10:36 PM
How to detect the record modified (ado) jolzy Pro VB Databases 3 October 28th, 2004 02:18 PM
Returning record in stored function with ADO nmbarbillo Oracle 0 June 15th, 2004 12:36 PM
ADO Record Set Question virtualdave Pro VB Databases 4 February 20th, 2004 12:21 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.