The parameterized query expects the parameter which was not supplied.

Question

hi im 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."" hope anyone could help me..

Answer 1

Try adding parameters like this -

cmd.Parameters.Add("@Department", SqlDbType.VarChar)
cmd.Parameters("@Department").Value = TextBox2.Text

and change your command text to what @Abe Miessler does he is right i just thought you will figure it out.

Answer 2

Your website is in serious danger of being hacked.

Read up on SQL Injection and how to prevent it in .NET

Your query problem is the least of your concerns right now.

But.....

@Misnomer's solution is close but not quite there:

Change your query to this:

cmd.CommandText = "SELECT * FROM borrow where (Department LIKE '%@DepartmentText%')"

and add parameters this way (or the way that @Misnomer does):

cmd.Parameters.AddWithValue("@DepartmentText",TextBox2.Text)

The important difference is that you need to change your CommandText.

Answer 3

Hi here's my code

 Private Sub TextBox2_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox2.TextChanged
    list.Items.Clear()
    Dim mycomand = <xml>
    SELECT * FROM borrow where (Department LIKE '%@DepartmentText%');
    </xml>

    cmd.CommandText = mycomand.Value
    cmd.CommandType = CommandType.Text
    cmd.Parameters.AddWithValue("@DepartmentText", TextBox2.Text)
    cmd.Connection = con

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

End Sub

what im getting is DepartmentText is already declared.. and if not that im getting this error

cmd.CommandText = "SELECT * FROM borrow where (Department LIKE '%@DepartmentText%')" cmd.Parameters.Add("@Department", SqlDbType.VarChar) cmd.Parameters.AddWithValue("@DepartmentText", TextBox2.Text) error "The parameterized query '(@Parameter1 nvarchar(4000),@Department varchar(8000),@Departmen' expects the parameter '@Parameter1', which was not supplied.

how can i post my stack trace?.. sorry about this noobie question..