A parameterized request expects a parameter that has not been set

Question

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

As soon as I typed a line in the text box to search for the item, I get this error:

Parameterized query '(@ Parameter1 nvarchar (4000)) SELECT * FROM to take where (Departme' expects parameter '@ Parameter1', which was not attached.

Can anybody help me?

Answer 1

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

It 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 converts null values ​​from the object level to DBNull values ​​acceptable for the database.

Answer 2

Your site is at serious risk of hacking.

Read on SQL Injection and how to prevent this in .NET.

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

But.....

Decision

@Misnomer is close, but not quite there:

Change your request to this:

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

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

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

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

Answer 3

Try adding parameters like this -

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

and change the command text to what @Abe Miessler does, he’s right, I just thought you would find out.

Answer 4

Based on the simplification of the answer and answering :

VB.NET Acronym

cmd.Parameters.AddWithValue("@Department", IF(TextBox2.Text, DBNull.Value))

C # abbreviation is

cmd.Parameters.AddWithValue("@Department", TextBox2.Text?? DBNull.Value)

Answer 5

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 blank line.

Answer 6

 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";