Why Does My Parameterized Query Throw a 'Missing Parameter' Error?

Parameterized Query Error: Missing Parameter

Introduction:

Parameterized queries provide improved security and performance by allowing parameters to be specified separately from the query string. However, if a required parameter is not supplied, it can result in the error "The parameterized query expects the parameter which was not supplied."

Scenario:

Consider the following code snippet from a Visual Basic .NET application:

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

Issue:

Upon entering a search string in the textbox, the code raises the following error:

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

Resolution:

This error occurs because no parameter is specified in the query string. To resolve this, add a parameter such as @Department and specify its value based on the user input:

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 code checks if the textbox is empty and sets the parameter value to DBNull.Value if it is. Otherwise, it sets the parameter value to the text entered in the textbox.

By supplying the parameter, the query will execute successfully and return the filtered data.

The above is the detailed content of Why Does My Parameterized Query Throw a 'Missing Parameter' Error?. For more information, please follow other related articles on the PHP Chinese website!