mysql - 2nd query fails to run

Question

I'm new to programming and I have a problem with the following code. The 2nd query is not running. It should insert all the data in the first database to the other database.

MySQLConn = New MySqlConnection
MySQLConn.ConnectionString = Connection
Adapter = New MySqlDataAdapter
Dim QRY = "SELECT EquipmentID, Quantity FROM subdbborroweq"
Dim EQID As Integer
Dim QTY As Integer

Dim TimeAndDate As String = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")

Try
    MySQLConn.Open() 
    Command = New MySqlCommand(QRY, MySQLConn)
    Reader = Command.ExecuteReader
    While Reader.Read() 
        EQID = Reader(0)
        QTY = Reader(1)
        Dim QRY1 = "INSERT INTO borrowlogs( `BorrowerName`, `EquipmentID`, `Quantity`, `TimeDate`) VALUES (" &
                    AddBorrower.TextBox1.Text & "," & EQID & ", " & QTY & "," &
                    TimeAndDate & ")"

        Command = New MySqlCommand(QRY1, MySQLConn)
    End While

    MySQLConn.Close()

Answer 1

I have put together some code but please note this is untested as I don't use MySQL anymore.

Instead of a MySqlDataReader I've used a DataTable as I find them a little easier to work with but that is preference. I've also implement Using for the MySqlConnection and MySqlCommand objects. This is so the objects are disposed of properly and you don't have to worry about do that.

Please note that I don't know your data structure. I have taken a guess of what the MySqlDbTypes are. You may have to change. I would suggest however saving TimeDate as just that, a DateTime.

You may also want to implement a little further checking for DBNulls on row(0) and row(1). I've left this to you to look at, it may not be necessary but it's always worth looking into as they do cause problems when the crop up.

I'm unsure how you want to handle multiple rows in your DataTable brought back from the SELECT statement. So what I am doing is looping through the Rows collection. If you don't want to and you simply want the first row, you can change the SELECT statement to only bring back the first row which I believe is done using LIMIT. This would mean your statement would look something like SELECT EquipmentID, Quantity FROM subdbborroweq LIMIT 1. You may want to look at a filter using WHERE and you may want to consider ordering your data using ORDER BY. Alternatively remove the For Each row loop and use Integer.TryParse(dt.Rows(0).Item(0).ToString(), EQID)

This is the code I have put together. It may not be 100% but hopefully it will give you something to go on:

Dim dt As New DataTable

Using con As New MySqlConnection(Connection),
      cmd As New MySQLCommand("SELECT EquipmentID, Quantity FROM subdbborroweq", con)

    con.open()

    dt.Load(cmd.ExecuteReader)

    If dt.Rows.Count > 0 Then

        cmd.CommandText = "INSERT INTO borrowlogs(BorrowerName, EquipmentID, Quantity, TimeDate) VALUES (@Uname, @EQID, @QTY, @TAD)"

        cmd.Parameters.Add("@Uname", MySqlDbType.VarChar)
        cmd.Parameters.Add("@EQID", MySqlDbType.Int32)
        cmd.Parameters.Add("@QTY", MySqlDbType.Int32)
        cmd.Parameters.Add("@TAD", MySqlDbType.DateTime)

        For Each row As DataRow In dt.Rows

            cmd.Parameters("@Uname").Value = AddBorrower.TextBox1.Text
            cmd.Parameters("@EQID").Value = row.Field(Of Int32)(0)
            cmd.Parameters("@QTY").Value = row.Field(Of Int32)(1)
            cmd.Parameters("@TAD").Value = DateTime.Now

            cmd.ExecuteNonQuery()

        Next

    End If

 End Using