C# 如何使用 sql 参数进行选择查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10291417/
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
How to use sql parameters for a select query?
提问by NewBie
I need to fetch the records based on a 'like' match against a set of records,
我需要根据对一组记录的“喜欢”匹配来获取记录，
The below query im using is not working . Does anyone knows what's wrong with the query?
我使用的以下查询不起作用。有谁知道查询有什么问题？
sqlCommand.CommandText =String.Format("SELECT * FROM Customer" +
" WHERE (Name like @Name)","'%" +searchString.Trim()+"%'");
sqlCommand.Parameters.AddWithValue("Name", searchString);
This query isnt fetching the desired records.
此查询未获取所需的记录。
I'm getting the following error while running the above snippet:
运行上述代码段时出现以下错误：
Must declare the scalar variable "@Name".
采纳答案by Aaron Bertrand
What happens this way?
这样会发生什么？
sqlCommand.CommandText = "SELECT * FROM Customer WHERE Name LIKE @Name;";
sqlCommand.Parameters.AddWithValue("@Name", "%" + searchString + "%");
You could also code it as follows to avoid all the wildcard formatting in the first place:
您还可以按如下方式对其进行编码，以避免首先使用所有通配符格式：
sqlCommand.CommandText = "SELECT * FROM Customer WHERE CHARINDEX(@Name, Name) > 0;";
sqlCommand.Parameters.AddWithValue("@Name", searchString);
If you're going to insist on doing it the unsafe way, at the very least double-up any single quotes found in searchString, e.g.
如果您要坚持以不安全的方式执行此操作，则至少将 中找到的任何单引号加倍searchString，例如
searchString.Replace("'", "''")
回答by StuartLC
String.Format needs a placeholder, like {0} {1} etc.
String.Format 需要一个占位符，例如 {0} {1} 等。
sqlCommand.CommandText = "SELECT * FROM Customer WHERE Name LIKE @Name;";
sqlCommand.Parameters.AddWithValue("@Name", String.Format("%{0}%", searchString));
回答by cmkrushchev
If Not con.State = ConnectionState.Open Then con.Open() End If
如果不是 con.State = ConnectionState.Open 那么 con.Open() End If
Try
Dim cmd As New OleDbCommand("UPDATE med_records SET Medicine=@Medicine,Dosage=@Dosage,Format=@Format,Expiration_date=@Expiration_date,Quantity=@Quantity where M_id=@M_id", con)
cmd.Parameters.AddWithValue("@Medicine", txtMedicine.Text)
cmd.Parameters.AddWithValue("@Dosage", txt_Dosage.Text)
cmd.Parameters.AddWithValue("@Format", txt_Format.Text)
cmd.Parameters.AddWithValue("@Expiration_date", txt_Expirationdate.Text)
cmd.Parameters.AddWithValue("@Quantity", NumericUpDown1.Text)
cmd.Parameters.AddWithValue("@M_id", txt_M_id.Text)
cmd.ExecuteNonQuery()
MsgBox("Update data")
con.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
