Retrieving auto-incremented column value in ASP.NET from mySQL table WITHOUT using stored proceedure

Question

I a function that used a mySQL Data Adapter and inserts a new row.

I want the function to return the value of the auto-incremented column of the updated row.

Please help.

Public Function addRow(ByVal colA as String, ByVal colB as String) As Integer
    Dim dbConnection As New MySqlConnection("server=xxx; user id=xxx; password=xxx; database=xxx; pooling=false;")
    Dim dbDataAdapter As New MySqlDataAdapter("SELECT * FROM table", dbConnection)
    Dim dbDataSet As DataSet = New DataSet
    dbDataAdapter.Fill(dbDataSet, "table")

    Dim dbNewRow As DataRow = dbDataSet.Tables("table").NewRow
    dbNewRow("colA") = colA
    dbNewRow("colB") = colB

    dbDataSet.Tables("table").Rows.Add(dbNewRow)

    dbDataAdapter.InsertCommand = New MySqlCommand("INSERT into table(colA, colB) VALUES (@colA, @colB)", dbConnection)
    dbDataAdapter.InsertCommand.Parameters.Add("colA", MySqlDbType.VarChar, 256, "colA")
    dbDataAdapter.InsertCommand.Parameters.Add("colB", MySqlDbType.VarChar, 256, "colB")

    Dim dbParm As MySqlParameter = dbDataAdapter.InsertCommand.Parameters.Add("@colID", MySqlDbType.Int16, 16, "colID")
    dbParm.Direction = ParameterDirection.ReturnValue

    dbDataAdapter.Update(dbDataSet, "table")

    Return dbDataAdapter.InsertCommand.Parameters("@colID").Value

End Function

The "Return..." line doesn't work and don't know what the correct code would be.

Answer 1

Try this:

Dim result As Integer = 0
Integer.TryParse(dbDataAdapter.InsertCommand.Parameters("@colID").Value, result)
Return result

Answer 2

Assuming the insert stored procedure is doing something like

RETURN @@IDENTITY

You will need the equivalent of this:

oDatabase.AddParameter(oDbCommand, "@RETURN_VALUE", System.Data.DbType.Int32, System.Data.ParameterDirection.ReturnValue, "", System.Data.DataRowVersion.Current, null);
...
oDbCommand.ExecuteNonQuery();
returnStatusCode = (int)oDbCommand.Parameters[0].Value;

Answer 3

I did it myself again.

Public Function addRow(ByVal colA as String, ByVal colB as String) As Integer 
    Dim dbConnection As New MySqlConnection("server=xxx; user id=xxx; password=xxx; database=xxx; pooling=false;") 
    Dim dbDataAdapter As New MySqlDataAdapter("SELECT * FROM table where colID = 0", dbConnection) 
    Dim dbDataSet As DataSet = New DataSet 
    dbDataAdapter.Fill(dbDataSet, "table") 

    Dim dbNewRow As DataRow = dbDataSet.Tables("table").NewRow 
    dbNewRow("colA") = colA 
    dbNewRow("colB") = colB 

    dbDataSet.Tables("table").Rows.Add(dbNewRow) 

    dbDataAdapter.InsertCommand = New MySqlCommand("INSERT into table(colA, colB) VALUES (@colA, @colB); Select * from table where colID = LAST_INSERT_ID()", dbConnection) 
    dbDataAdapter.InsertCommand.Parameters.Add("colA", MySqlDbType.VarChar, 256, "colA") 
    dbDataAdapter.InsertCommand.Parameters.Add("colB", MySqlDbType.VarChar, 256, "colB") 

    dbDataAdapter.Update(dbDataSet, "table") 

    Return dbDataSet.Tables("table").Rows(0).Item(0)

End Function

Seems all I needed to do was add a Select statement on the end of the Insert statement and use the LAST_INSERT_ID function to just get the last Inserted row. Then just read from that one rows ID and return it.

I am quite disappointed that DataAdapters work this way. I'm sure classic ASP was easier than this! Thanks to all who responded.