SqlClient命令错误：“列名‘A’无效”

Question

Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged
    Dim SQLData As New System.Data.SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True")
    Dim cmdSelect As New System.Data.SqlClient.SqlCommand("SELECT * FROM a1_bustype where bustype=" & DropDownList1.SelectedItem.Text.ToString, SQLData)
    SQLData.Open()
    Dim dtrReader As System.Data.SqlClient.SqlDataReader = cmdSelect.ExecuteReader()
    If dtrReader.HasRows Then
        While dtrReader.Read()
            TextBox1.Text = dtrReader("buscode")
        End While
    End If

    dtrReader.Close()
    SQLData.Close()
End Sub

我在数据库中有以下条目，我有..空调座椅沃尔沃和总线代码是S41

当我运行该网页时，它显示以下错误:无效列名'A‘

Answer 1

如果bustype是text或varchar，则必须在查询中添加单引号

UPD:我不太确定它在vb中会是什么样子，就像这样：

"SELECT * FROM a1_bustype where bustype='" & DropDownList1.SelectedItem.Text.ToString & "'"

Answer 2

正如X2在他的回答中所说的，您需要用单引号将字符串括起来，因此将以Dim cmdSelect...开头的行更改为：

Dim cmdSelect As New System.Data.SqlClient.SqlCommand(String.Format("SELECT * FROM a1_bustype where bustype='{0}'", DropDownList1.SelectedItem.Text.Replace("'", "''")), SQLData)

如果DropDownList中的值本身可以包含单引号，这也将使其支持。

但是，你没有在代码中正确地关闭代码，以防出现错误，我建议将其更改为如下所示：

Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged
    Using SQLData As New System.Data.SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True")
        Using cmdSelect As New System.Data.SqlClient.SqlCommand(String.Format("SELECT * FROM a1_bustype where bustype='{0}'", DropDownList1.SelectedItem.Text.Replace("'", "''")), SQLData)
            SQLData.Open()
            Using dtrReader As System.Data.SqlClient.SqlDataReader = cmdSelect.ExecuteReader()
                If dtrReader.HasRows Then
                    While dtrReader.Read()
                        TextBox1.Text = dtrReader("buscode")
                    End While
                End If
            End Using
        End Using
    End Using
End Sub

Using / End Using语句将确保即使在某个地方引发了异常，也能正确地处理资源。