SQL 参数化查询需要未提供的参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3865982/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
The parameterized query expects the parameter which was not supplied
提问by demic0de
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.
参数化查询 '(@Parameter1 nvarchar(4000))SELECT * FROM Borrow where (Departme' 需要参数 '@Parameter1'，但未提供该参数。
Can anyone help me?
谁能帮我？
回答by ravidev
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
如果将空值传递给参数，即使在添加参数后也会出现此错误，因此请尝试检查该值，如果为空，则使用 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.
这会将对象层的空值转换为数据库可接受的 DBNull 值。
回答by Abe Miessler
Your website is in serious danger of being hacked.
您的网站有被黑客入侵的严重危险。
Read up on SQL Injectionand 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:
@Misnomer 的解决方案很接近但不完全存在：
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):
并以这种方式（或@Misnomer 的方式）添加参数：
cmd.Parameters.AddWithValue("@DepartmentText",TextBox2.Text)
The important difference is that you need to change your CommandText.
重要的区别是您需要更改您的 CommandText。
回答by Dan Friedman
Building on and simplifying ravidev's answer:
建立并简化拉维德夫的回答：
The VB.NET shorthand is
VB.NET 的简写是
cmd.Parameters.AddWithValue("@Department", IF(TextBox2.Text, DBNull.Value))
cmd.Parameters.AddWithValue("@Department", IF(TextBox2.Text, DBNull.Value))
The C# shorthand is
C# 简写是
cmd.Parameters.AddWithValue("@Department", TextBox2.Text ?? DBNull.Value)
cmd.Parameters.AddWithValue("@Department", TextBox2.Text ?? DBNull.Value)
回答by Vishal
Try adding parameterslike 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.
并将您的命令文本更改为@Abe Miessler 所做的他是对的，我只是认为您会弄明白。
回答by Ismaila Jonathan
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.
如果您从 DataGridView 控件写入数据库，请确保没有空行。将“允许用户添加行”设置为 false；它截断了不必要的最后一个空行。
回答by yaki
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";
