sql - 参数化查询需要未提供的参数

Question

我的代码有问题:

Private Sub TextBox2_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox2.TextChanged
    list.Items.Clear()

    cmd.CommandText = "SELECT * FROM borrow where (Department LIKE '%" & TextBox2.Text & "%')"
    cmd.Connection = con
    cmd.CommandType = CommandType.Text
    con.Open()


    rd = cmd.ExecuteReader()
    If rd.HasRows = True Then
        While rd.Read()

            Dim listview As New ListViewItem

            listview.Text = rd("ID").ToString
            listview.SubItems.Add(rd("Department").ToString)
            listview.SubItems.Add(rd("Purpose").ToString)
            listview.SubItems.Add(rd("Items_Details").ToString)
            listview.SubItems.Add(rd("Requested_by").ToString)
            listview.SubItems.Add(rd("Approved_by").ToString)
            listview.SubItems.Add(rd("Date").ToString)
            listview.SubItems.Add(rd("Status").ToString)
            listview.SubItems.Add(rd("Date_Returned").ToString)

            list.Items.Add(listview)

        End While
    End If
    con.Close()

当我在文本框中输入字符串来搜索项目时，我收到此错误:

The parameterized query '(@Parameter1 nvarchar(4000))SELECT * FROM borrow where (Departme' expects the parameter '@Parameter1', which was not supplied.

有人可以帮助我吗？

Answer 1

如果将空值传递给参数，即使添加参数后也会出现此错误所以尝试检查该值，如果它为空，则使用 DBNull.Value

这会起作用

cmd.Parameters.Add("@Department", SqlDbType.VarChar)

If (TextBox2.Text = Nothing) Then
    cmd.Parameters("@Department").Value = DBNull.Value
Else
    cmd.Parameters("@Department").Value = TextBox2.Text
End If

这会将对象层的 null 值转换为数据库可接受的 DBNull 值。