mysql - 查询在 MySQL 中执行，但在调试时在 vb.net 中出错

Question

我有一个插入查询，我试图在其中将表 1 的数据复制到表 2。现在，当我直接在 MySQL 中执行但当我尝试通过 VB.Net 进行调试时，Query 工作正常”

INSERT INTO newMedicinesOrders (`OrderID`,`medicineName`, `power`, `form`, `fQuantity`, `iQuantity`, `type`, `cost`, `prescriptionLink`, `userID`) SELECT `orderID`, `name`, `power`, `form`, `fQuantity`, `iQuantity`, `type`, `mrp`, `prescriptionLink`, `userID` from myCart WHERE userID = '1'

我收到一条错误消息说

Unknown column 'orderID' in 'field list'

代码

Try
            Dim str1 As String = "INSERT INTO newMedicinesOrders (`OrderID`,`medicineName`, `power`, `form`, `fQuantity`, `iQuantity`, `type`, `cost`, `prescriptionLink`, `userID`) SELECT `orderID`, `name`, `power`, `form`, `fQuantity`, `iQuantity`, `type`, `mrp`, `prescriptionLink`, `userID` from myCart WHERE userID = '" + userid.Text + "'"

            Dim str2 As MySqlDataReader
            Dim adapter As New MySqlDataAdapter
            Dim command As New MySqlCommand
            command.CommandText = str1
            command.Connection = con
            adapter.SelectCommand = command
            con.Open()
            str2 = command.ExecuteReader
            con.Close()
            Response.Write("<script language='javascript'>alert('Success.');</script>")
        Catch ex As Exception
            Response.Write(ex)
        End Try

Answer 1

我相信你的错误来自于你正在使用 SQLCommand.ExecuteReader()

来自 this description :

ExecuteReader used for getting the query results as a DataReader object. It is readonly forward only retrieval of records and it uses select command to read through the table from the first to the last.

ExecuteNonQuery used for executing queries that does not return any data. It is used to execute the sql statements like update, insert, delete etc. ExecuteNonQuery executes the command and returns the number of rows affected.

根据 MSDN ，这就是您执行 INSERT、UPDATE 或 DELETE 语句的方式:

Public Sub CreateCommand(ByVal queryString As String, ByVal connectionString As String)
    Using connection As New SqlConnection(connectionString)
        Dim command As New SqlCommand(queryString, connection)
        command.Connection.Open()
        command.ExecuteNonQuery()
    End Using
End Sub

但是，我没有找到有关使用 ExecuteReader() 执行 INSERT 命令时发生的情况的信息，但我想这就是发生的情况...