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?

score 174 · Accepted Answer · edited Feb 29 '16 at 12:22

174

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.

edited Feb 29 '16 at 12:22

Filip Cornelissen

3,682
3
31
41

answered Dec 16 '11 at 07:09

ravidev

2,708
6
26
42

26

Shorthand: cmd.Parameters("@Department").Value = (object)TextBox2.Text ?? DBNull.Value; – Niels Brinch May 18 '12 at 09:02
This works for me. The .Add has been depreciated. cmd.Parameters.AddWithValue("@Department", (object)TextBox2.Text ?? DBNull.Value); – Joshua Sep 14 '17 at 20:52
`.Add(String, Object)` is [deprecated](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlparametercollection.add?view=netframework-4.7.1#System_Data_SqlClient_SqlParameterCollection_Add_System_String_System_Object_), `Add(String, SqlDbType)` is not. This answer (or the subsequent comments) do not use a deprecated method. – Dan Friedman Apr 30 '18 at 21:10
From [the docs](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlparametercollection.addwithvalue?view=netframework-4.8): "Use DBNull.Value instead of null, to indicate a null value." – epalm Jul 18 '19 at 16:49

score 16 · Answer 2 · answered Oct 05 '10 at 17:55

16

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.

answered Oct 05 '10 at 17:55

Abe Miessler

82,532
99
305
486

hi i've tnx.. ive changed my code but still im getting the 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." – demic0de Oct 05 '10 at 18:18
I suspect your error is being thrown from somewhere else. Can you post your stack trace? – Abe Miessler Oct 05 '10 at 18:53
1

Your answer misses the intent of `'%' + @DepartmentText + '%'`. – binki Dec 19 '18 at 21:49

score 2 · Answer 3 · edited Oct 30 '20 at 11:07

2

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)

edited Oct 30 '20 at 11:07

Adrian Mole

49,934
160
51
83

answered Apr 30 '18 at 21:08

Dan Friedman

4,941
2
41
65

Vishal · Answer 4 · 2010-10-05T18:00:17.493

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.

edited Oct 05 '10 at 18:00

answered Oct 05 '10 at 17:17

Vishal

12,133
17
82
128

I dont know why....but i get the feeling that you have a datatype issue here...or something...seeing the `Departmen` i feel you dont have enough characters alloted or something... – Vishal Oct 05 '10 at 18:41
hi i've tnx.. ive changed my code but still im getting the error.. cmd.CommandText = "SELECT * FROM borrow where (Department LIKE '%@DepartmentText%')" cmd.Parameters.Add("@DepartmentText", 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. – demic0de Oct 05 '10 at 18:52
The problem in this case is most likely that the parameter value should include the '% %' and not wrapping the parameter in quotes. – Thomas Kjørnes Dec 06 '11 at 10:10
it is ineresting that such form doesn't help : cmd.Add(new SqlParameter("@Department", SqlDbType.VarChar){Value=textBox.Department, IsNullable=true}); – Roman Pokrovskij Sep 08 '15 at 17:12

score 1 · Answer 5 · answered Dec 12 '18 at 22:06

1

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.

answered Dec 12 '18 at 22:06

Ismaila Jonathan

11
1

score -3 · Answer 6 · edited Jul 17 '12 at 14:34

SqlConnection conn = new SqlConnection(connectionString);

conn.Open();
//SelectCustomerById(int x);
comboBoxEx1.Items.Clear();

SqlCommand comm = new SqlCommand("spSelectCustomerByID", conn);
//comm.Parameters.Add(new SqlParameter("cust_name", cust_name));
//comm.CommandText = "spSelectCustomerByID";
comm.Parameters.Add(new SqlParameter("cust_id", SqlDbType.Int));
comm.CommandType = CommandType.StoredProcedure;
comm.ExecuteNonQuery();

SqlDataAdapter sdap = new SqlDataAdapter(comm);
DataSet dset = new DataSet();
sdap.Fill(dset, "cust_registrations");

if (dset.Tables["cust_registrations"].Rows.Count > 0)
{
    comboBoxEx1.Items.Add("cust_registrations").ToString();
}
comboBoxEx1.DataSource = dset;
comboBoxEx1.DisplayMember = "cust_name";

The parameterized query expects the parameter which was not supplied

6 Answers6

Linked

Related