第二查询无法运行 [英] 2nd query fails to run
问题描述
我是编程的新手，以下代码有问题。第二查询未运行。它应该将第一个数据库中的所有数据插入另一个数据库中。
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()
推荐答案
我整理了一些代码，但是请注意未经测试，因为我不再使用MySQL。
I have put together some code but please note this is untested as I don't use MySQL anymore.
我使用的是实现了使用 MySqlConnection 和 MySqlCommand 对象。这样一来，物品就可以妥善处理，您不必担心这样做。
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.
请注意，我不知道您的数据结构。我已经猜到了 MySqlDbType 是什么。您可能需要更改。我建议不过将 TimeDate 保存为 DateTime 。
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.
您可能还想对 row（0）和<$ DBNulls 进行进一步的检查c $ c> row（1）。我把这个留给您看，也许没有必要，但是它总是值得研究的，因为它们在收割时确实会引起问题。
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.
不确定如何处理 SELECT 语句带来的 DataTable 中的多行。所以我正在做的是遍历 Rows 集合。如果您不想这样做，而只是想要第一行，则可以更改 SELECT 语句，以仅带回第一行，我相信使用 LIMIT 。这意味着您的对帐单看起来像 SELECT EquipmentID，从FROM dbdbrowroweq LIMIT 1 起的数量。您可能要使用 WHERE 查看过滤器，并且可能要考虑使用 ORDER BY 对数据进行排序。或者删除对于每一行循环，并使用 Integer.TryParse（dt.Rows（0）.Item（0）.ToString（），EQID）
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)
这是我编写的代码。可能不是100％，但希望它将给您带来帮助：
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
这篇关于第二查询无法运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
