CODEWITHSUNDEEP
sqlsql-servervb.net
demic0de
demic0de

Reputation: 1323

The parameterized query expects the parameter which was not supplied

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?

Upvotes: 67

Views: 86753

Answers (5)

Dan Friedman
Dan Friedman

Reputation: 5228

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)

Upvotes: 3

Ismaila Jonathan
Ismaila Jonathan

Reputation: 11

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.

Upvotes: 1

ravidev
ravidev

Reputation: 2738

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.

Upvotes: 174

Vishal
Vishal

Reputation: 12379

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.

Upvotes: 1

Abe Miessler
Abe Miessler

Reputation: 85126

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.

Upvotes: 17

Related Questions