The parameterized query expects the parameter which was not supplied

user_455498 0
Viewed 81112

I'm having a problem with my code:

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()

Once I typed in the string in the textbox to search for an item I get this error:

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

Can anyone help me?

vb.net sql-server sql
6 Answers

Building on and simplifying ravidev's answer:

The VB.NET shorthand is:

cmd.Parameters.AddWithValue("@Department", IF(TextBox2.Text, DBNull.Value))

The C# shorthand is:

cmd.Parameters.AddWithValue("@Department", (object)TextBox2.Text ?? DBNull.Value)
user_11520540

If you are writing from a DataGridView control to your database, make sure there is no empty row. Set 'Allow User to add Rows' to false; it truncates the unnecessary last empty row.

user_107831840
Related