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]> > > * 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 <*> 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/
