MySQL LAST_INSERT_ID() Problems

[klong]

Hello,

I'm trying to make use of the MySQL Last_Insert_ID() function, and I'm not having any success.


dbConn.Open()
dbCmd = dbConn.CreateCommand
dbCmd2 = dbConn.CreateCommand
dbCmd.CommandText = "UPDATE schedulesequence set id = LAST_INSERT_ID(id + 1)"
dbCmd.ExecuteNonQuery()

dbCmd2.CommandText = "INSERT INTO download(id, server) " & _
"VALUES(LAST_INSERT_ID(), " & Trim(TextBox1.Text) & ")"
dbCmd2.ExecuteNonQuery()

The update to the sequence works, but the insert statement generates the following error.

Error:MySql.Data: Unknown table 'www.popnetworks' in field list

The value of TextBox1.Text is www.popnetworks.net.

Why did the value of the textbox get shorted?

When I expanded the query by inserting more values in more fields, the insert statement generated syntax errors. The other values were from three other textbox controls.

Thanks for your help.

Kerry

 

[Noinx]

Not sure if you found an answer yet.

To get the last inserted id for a given table, the id field must be set to auto increment. There is no need to do this: LAST_INSERT_ID(id + 1) Don't reference the id column at all while performing updates. You will continue to use the same id.

When inserting, don't use the id column or the value for id in the statement since this will be auto incremented. Just start with the next field which has data. After the insert, the auto increment id field will add 1 to the current value.

To retreive the last insert id, run this query:

SELECT LAST_INSERT_ID()

----------------------------------------------------------
So, your code could look like this:

Dim ID As Integer
dbConn.Open()
dbCmd.Connection = dbConn

-----update removed since it is not needed-----

dbCmd.CommandText = "INSERT INTO download(server) " & _
"VALUES(" & Trim(TextBox1.Text) & ")"
dbCmd.ExecuteNonQuery()

dbCmd.CommandText = "SELECT LAST_INSERT_ID()"
ID = dbCmd.ExecuteScalar()
------------------------------------------------------------


-Noinx