You want SCOPE_IDENTITY() not @@IDENTITY. Chris
-----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Kumar, Karthick Sent: Tuesday, April 19, 2005 8:29 AM To: [email protected] Subject: RE: [AspNetAnyQuestionIsOk] How to get the value of auto field of current inserted row I agree, it is not stable and lead to runtime errors. So the ideal way to go is to use stored procedure. Use EXEC @var = <stored procedure> variables, variables, variables etc. and get the @@IDENTITY in @var and use appropriately. I think somebody already suggested this way....and I am not sure how will this work in terms of the application being a multiuser env. Regards, Karthick -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] Behalf Of Charles Carroll Sent: Tuesday, April 19, 2005 1:25 PM To: [email protected] Subject: Re: [AspNetAnyQuestionIsOk] How to get the value of auto field of current inserted row That does not work in a multiuser situation. If 2 or more users insert records at same time this will produce incorrecut ids. It also does not work in situation where the database does not produce a next id that is +1 which some databases are capable of since they will not reuse ids if records are deleted. On 4/19/05, Kumar, Karthick <[EMAIL PROTECTED]> wrote: > Or simple as this: Get the last ID before inserting the row from the > table and then add +1 to it, which will give you the ID of the > recently inserted row's ID, given the situation that the row insert in > the table was successful. > > Hth, > Karthick > > > -----Original Message----- > From: [email protected] > [mailto:[EMAIL PROTECTED] Behalf Of Mat�as > Ni�o ListMail > Sent: Tuesday, April 19, 2005 3:26 AM > To: [email protected] > Subject: RE: [AspNetAnyQuestionIsOk] How to get the value of auto > field of current inserted row > > > > I'm no expert at ASP.NET yet, but since nobody responded to this I > figured I'd give it a shot. > > As far as I understand it, the only way to obtain the autoincremented > value of a table with ADO.NET talking to a modern SQL or Access > database is to fire a "SELECT @@IDENTITY" executescalar command > immediately after the insert command which will return the last > auto-value created by the database. > > If you're working with datasets/data adapters, you can also access > the data adapter's OnRowUpdated event and add code there that fires > the @@IDENTITY command and inserts the value into the new row's ID column. > > I have also read that DataTables with ID datacolumns that have all > the AutoIncrement settings set on them should also be able to produce > new values, but I imagine that won't be very favorable in a > disconnected state from the database. > > I know there is always the concern that in a situation with many new > rows being created at once, there is always the risk of the @@IDENTITY > command returning the wrong new value for the row. I wonder if it > would be possible to minimize this potential hazard by sending the > Insert command in batch format with the SELECT @@IDENTITY tagged on > the end of it and then grabbing the second resultset. > > Anyway, just some things to ponder. I'm sure Mr. Carroll knows of > some nifty component/assembly for you that releases you from having to > do any of this... ;-) > > ...Matias > > P.S. Speaking of nifty assemblies, I do know the .NET Data > Application Block (downloadable at MSDN) contains a bunch of methods > with very clean encapsulations of much of the wirey aspects of ADO.NET. > > ________________________________ > > From: [email protected] > [mailto:[EMAIL PROTECTED] On Behalf Of Arindam > Sent: Sunday, April 17, 2005 9:57 AM > To: [email protected] > Subject: [AspNetAnyQuestionIsOk] How to get the value of auto field > of current inserted row > > > I have a table with 4 columns, > > First Column is auto-incremented numeric field, > > I want to get > the value of that auto incremented field, when i insert a new > > record. > > because i need to send that number back to my funcation. > > > > > > Pls look at the function bellow. > > > > > > > > public bool AddHotelBookingInfo(Hashtable BookingInfo,ref string > > BookingConfrmNo) > > { > > ObjConn.OpenConnection (); > > try > > { > > string InsertQry="Insert into Tab_BookingDtls > (H_Name,H_Id,Guest_FName) > > values"; > > InsertQry += " '"+ > > > BookingInfo["h_Name"].ToString().Replace("'","''")+"','"+BookingInfo["h_Id"].ToString().Replace("'","''")+"','"+BookingInfo["g_FName"].ToString().Replace("'","''")+"'"; > > SqlCommand objCmd=new SqlCommand(InsertQry,ObjConn.mysqlconn); > > objCmd.ExecuteNonQuery(); > > BookingConfrmNo="100000"; //autoincremented value to be replaced > with. > > ObjConn.CloseConnection(); > > return true; > > } > > catch > > { > > BookingConfrmNo="0"; > > ObjConn.CloseConnection(); > > return false; > > } > > } > > > > Thanks & Regards > > Arindam > Web Designer & Developer > > > > > Yahoo! India Matrimony: Find your life partneronline. > > [Non-text portions of this message have been removed] > > > > ________________________________ > > Yahoo! Groups Links > > > * To visit your group on the web, go to: > http://groups.yahoo.com/group/AspNetAnyQuestionIsOk/ > > * To unsubscribe from this group, send an email to: > [EMAIL PROTECTED] > <mailto:[EMAIL PROTECTED] > bscribe> > > * Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service > <http://docs.yahoo.com/info/terms/> . > > > > > [Non-text portions of this message have been removed] > > > > > Yahoo! Groups Links > > > > > > > > > > ________________________________ > Yahoo! Groups Links > > > To visit your group on the web, go to: > http://groups.yahoo.com/group/AspNetAnyQuestionIsOk/ > > To unsubscribe from this group, send an email to: > [EMAIL PROTECTED] > > Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service. Yahoo! Groups Links Yahoo! Groups Links Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/AspNetAnyQuestionIsOk/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
