Hi All, I have 1 table in a MySQL Database "MyTest" called "order". It has the following fields: OrderID INT PrimaryKey Desc Varchar(255) Can be NULL Price INT Cannot be NULL Currently I am using OdbcConnection object to connect to database, and use OdbcDataAdapter
to do a "select * from `order`" query. Now I would like to perform a Insert when my ASP.NET page called. While the OrderID is PrimaryKey, I know I only need to input some text into Desc field, and some number into Price field....... My problem is, once I insert
the record, I want to know what is the OrderID for the newly insert record. I was try to get the Max value in OrderID before I do insert. Buf if there are more then 1 person doing the same action, the OrderID I get will be incorrect. I am a newbie on ASP.NET
and MySQL (and everything else)... can someone give me some advice or code fragment sample? Many many thanks in advance.
Directly after your insert, with the same connection object, you have do run this select:
Dim _cnn As New OdbcConnection("YOUR_CONNECTION_STRING")
Dim _cmd As New OdbcCommand("",_cnn)
'## -- really should name the field-name Desc to another name. Desc is a reserved word in SQL for sorting Descending (Desc = Descending) --
Dim _query As String = "INSERT INTO Order(Desc, Price) VALUES('Some value for Desc', 123.45)"
_cmd.CommandText = _query
_cmd.Connection.Open()
_cmd.ExecuteNonQuery()
_cmd.CommandText = "SELECT last_insert_id() FROM Order"
Dim _lastUniqueID As Integer = _cmd.ExecuteScalar()
_cmd.Connection.Close()
Thanks Patrik :) Many many thanks. By the way, I had read through the forum yesterday... seems that most people perfer to use the External/Thrid Party Class "MyOleDBConnection", "MyOleDataAdapter" rather than OdbcConnection and OdbcDataAdapter. Is there's a
performance issue, or any other reason that make people love to use these External/Thrid Party Class ?
Yes, there is a performence issue. ODBC have more overhead than a "native provider" BUT!!!! they are more "buggy" than the ODBC from MS. Until they have been "mature" I will stay on ODBC, but thats Just me ;o) cya, /PatrikB
andylo
Member
100 Points
20 Posts
Connecting MySQL with OdbcConnection ok, but how to get Last Inserted ID?
Oct 27, 2003 01:36 PM|LINK
patber
Member
380 Points
76 Posts
Re: Connecting MySQL with OdbcConnection ok, but how to get Last Inserted ID?
Oct 27, 2003 02:21 PM|LINK
Dim _cnn As New OdbcConnection("YOUR_CONNECTION_STRING") Dim _cmd As New OdbcCommand("",_cnn) '## -- really should name the field-name Desc to another name. Desc is a reserved word in SQL for sorting Descending (Desc = Descending) -- Dim _query As String = "INSERT INTO Order(Desc, Price) VALUES('Some value for Desc', 123.45)" _cmd.CommandText = _query _cmd.Connection.Open() _cmd.ExecuteNonQuery() _cmd.CommandText = "SELECT last_insert_id() FROM Order" Dim _lastUniqueID As Integer = _cmd.ExecuteScalar() _cmd.Connection.Close()cya, /PatrikBandylo
Member
100 Points
20 Posts
Re: Connecting MySQL with OdbcConnection ok, but how to get Last Inserted ID?
Oct 27, 2003 09:23 PM|LINK
patber
Member
380 Points
76 Posts
Re: Connecting MySQL with OdbcConnection ok, but how to get Last Inserted ID?
Oct 28, 2003 09:05 AM|LINK
andylo
Member
100 Points
20 Posts
Re: Connecting MySQL with OdbcConnection ok, but how to get Last Inserted ID?
Oct 29, 2003 09:23 PM|LINK