MySQL, Querying last auto-increment

[PeeWee]

Greetings and salutations!
I am still working on my application. Now... I have a form where you create a characters main values and then save
them to the MySQL-server. I need the ID for the newly created chummer, though. I tried:

        Dim QueryString As String = "INSERT INTO pc VALUES (NULL,'" + txtCharName.Text + "'," + Reaction.Value.ToString + "," + Initiative.Value.ToString + "," + Perception.Value.ToString + "," + Body.Value.ToString + "," + Willpower.Value.ToString + "," + Mental.Value.ToString + "," + Physical.Value.ToString + "," + Overflow.Value.ToString + ")"
        Dim QueryID As String = "SELECT * FROM 'pc' WHERE id=LAST_INSERT_ID();"

        Dim myReader As OdbcDataReader
        Dim myConnection As New OdbcConnection(MDIForm.MyConString)
        Dim myCommand As New OdbcCommand(QueryString, myConnection)
        Dim myQueryCommand As New OdbcCommand(QueryID, myConnection)
        Dim id As Integer

        If TestConnect(myConnection) Then
            myConnection.Open()
            myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
            myReader = myQueryCommand.ExecuteReader(CommandBehavior.CloseConnection)
            While myReader.Read
                id = myReader.GetInt16(0)
            End While
        End If
        myConnection.Close()

Naturally, it fails, as I try to send two Executereader() with the same connection. However, LAST_INSERT_ID()
only lasts during the connection, the next time I connect to the server, that data is lost.
I really need that ID. How should I do to query it, you think?


Note:
TestConnect() is a function I wrote to confirm that you can actually connect to the MySQL server, and have
access to the proper database. That connection closes down before the function is exited, however.

[Athley]

If the last entered ID is the greatest you might just use the MAX function in a select query to get it.

Ex (NorthWind):
"SELECT MAX(EmployeeID) FROM employees" gives 9.

The good thing about that is that it is really easy to use as a subquery if you want to use the result in another query.

[PeeWee]

It might work, since I use auto-increment, and is auto-generated. But will it always generate the highest value?

(UPDATED)
Ok, I tested back and forth. It does generate the highest number.
Thanks for the help!

[MarkusJ_NZ]

Does MySQL have a function similiar to the following in SQL server system function

SELECT @@IDENTITY FROM TableName

This returns the last autoincremented integer from the table.

Using the MAX function may cause problems if a few people are using the application at one time...

Regards
Mark

[Mr.No]

Hi

Why not try the following:

Dim QueryString As String = "INSERT INTO pc VALUES (NULL,'" + txtCharName.Text + "'," + Reaction.Value.ToString + "," + Initiative.Value.ToString + "," + Perception.Value.ToString + "," + Body.Value.ToString + "," + Willpower.Value.ToString + "," + Mental.Value.ToString + "," + Physical.Value.ToString + "," + Overflow.Value.ToString + "); " + "SELECT * FROM 'pc' WHERE id=LAST_INSERT_ID();"

I haven't tried action statements in ADO.NET yet but if I follow a sample from the old ADO the execute statement could return a recordset. So you could do the following when using SQL Server:

Dim conn as ADO.Connection
Dim rs as ADO.Recordset
strsql = "SET NOCOUNT ON; INSERT INTO TABLE1 (....); SELECT @@IDENTITY"
 
' open a new connection
 
set rs = conn.Execute(strsql)
 
newid = rs(0)

May be you could try to implement the above idea in ADO.NET