The parameterized query expects the parameter which was not supplied

Question

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?

Answer 1

If you pass null value to parameter,you will get this error even after you add the parameter so try to check the value and if it null then use DBNull.Value

This will work

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 

This will convert the null values from the object layer to DBNull values that are acceptable to the database.