Wrox Programmer Forums
Go Back   Wrox Programmer Forums > .NET > Other .NET > ADO.NET
| Search | Today's Posts | Mark Forums Read
ADO.NET For discussion about ADO.NET.  Topics such as question regarding the System.Data namespace are appropriate.  Questions specific to a particular application should be posted in a forum specific to the application .
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ADO.NET 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 February 13th, 2005, 11:21 PM
Registered User
 
Join Date: Feb 2005
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default Insert Record & retrieve ID

Hi,

I have a frustrating problem. My code inserts a new record into an Access DB via ADO.Net. I need access to the autoincremented ID number of the new record.

When the new record is inserted, I open the connection, execute the procedure and close the connection again. If I check the DB in Access the new record is there, with the correct ID number.

I then run the following code:
        dim lngID as Long
        lngID=da.ExecuteScalar("SELECT MAX(custID) FROM Customers")

where da is a DataAccess DLL with the following code for the method used:
        Function ExecuteScalar(byval SQL as string) as object
            Dim c as new oledbConnection(connString)
            Dim cmd as oledbCommand(SQL,c)
            Dim newValue as Object
            c.Open
            newValue=cmd.ExecuteScalar
            'tidy up
            c.close
            return newValue
        End Function

This all works fine, BUT the number returned is 1 smaller than the ID assigned to the newly inputted record! i.e. the last value added.
Anyone know what's going on??

Thanks

Dave

 
Old February 18th, 2005, 08:45 AM
Friend of Wrox
Points: 410, Level: 7
Points: 410, Level: 7 Points: 410, Level: 7 Points: 410, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2004
Location: New Delhi, India.
Posts: 109
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to anubhav.kumar
Default

hi Dave

have you tried ExecuteReader() instead of ExecuteScalar()?

Anubhav Kumar
 
Old February 18th, 2005, 09:26 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,110
Thanks: 0
Thanked 3 Times in 3 Posts
Default

 Try
'insert here
    Try
        lngID=da.ExecuteScalar("SELECT MAX(custID) FROM Customers")
    Catch
    End Try
Catch
End Try

The problem with this concept is what if 2 people insert one right after the other and you were the first one to insert you won't get the right id. SQL Server offers stored procedures where you can return the autonumber(Identity) to the calling function or procedure.


 
Old February 28th, 2005, 04:25 PM
Registered User
 
Join Date: Feb 2005
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the replies guys. I'll try the datareader approach, though I don't see what difference that will make. I am only after one value, and the ExecuteScalar method is designed to give exactly that!

I also appreciate the potential concurrency problems, however the behaviour I'm trying to understand occurs on my development machine and I am definitely the only user! As a by-the-by I thought that "SELECT @@Identity" when executed against an AccessXP db would return the last autoincremented value for the last table so updated. I can't get this to work either.

Dave



 
Old March 1st, 2005, 03:33 AM
Authorized User
 
Join Date: Nov 2004
Location: mumbai, maharashtra, India.
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default

hi fren..

use this piece of code..i am sure it will give u the desired result..

ur code..
.....
 lngID=da.ExecuteScalar("SELECT MAX(custID) FROM Customers select @@identity ")
.....

happy coding..
suneeta
 
Old July 12th, 2007, 10:03 PM
Kia Kia is offline
Authorized User
 
Join Date: Jun 2007
Location: Sydney, NSW, Australia.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I have the same problem, but my insert/update method is different.

  bld = New SqlClient.SqlCommandBuilder(daMyDataAdapter)
  Me.bsMyBindingSource.EndEdit()
  Me.daMyDataAdapter.Update(Me.dsMyDataset, "tblPAYMENT")

Then I'm using
  Me.bsMyBindingSource.Current("PaymentID").ToString

Which is obviously wrong because the BS position just moves to the last one I was on before adding the new record and the newly inserted record is not in the BS untill I call .Fill on the DA again.

I didn't really understand the solutions earlier in this thread. Can anyone dumb it down for me?

Ta,
Kia




Similar Threads
Thread Thread Starter Forum Replies Last Post
How to retrieve ID field of newly-created record Steve777 Classic ASP Professional 3 October 2nd, 2006 11:27 AM
Retrieve last record savoym SQL Server 2000 4 March 3rd, 2006 12:43 PM
ASP Insert - Get Record ID llowe Classic ASP Databases 2 March 24th, 2004 11:11 AM
Insert new record ID levinho Classic ASP Databases 5 January 14th, 2004 12:03 PM





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