通过代码向SQL命令添加多个参数

本文介绍了通过代码向SQL命令添加多个参数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

您好， 我想在vb net中创建一个sql命令查询（使用ADO VS 2015 Community Edition ）我想在运行时添加多个参数，例如。我有一个字段，它有5个值，想要通过这个字段搜索记录

SELECT * FROM Table_1 WHERE carnum LIKE @ c1

现在我想在运行时添加多个carnum值，例如。使用5复选框选择最多5个carnum值并在选中2复选框时添加到查询中

SELECT * FROM Table_1 WHERE carnum LIKE @ c1 和 @ c2

有什么方法可以吗？ 我尝试了什么： 代码中没有任何内容只是为了知道是否可以完成，如果我使用if then else语句或选中复选大小写的情况我需要55'其他'语句是有一个简单的方法吗？

解决方案

如果我理解正确，你想要找到值列表中的行。因此，您必须使用 IN子句 [ ^ ] code> WHERE 语句（参见：'M'）。

SELECT * FROM 表1 WHERE carnum IN （ @ c1 ， @ c2 ）

但是如果你想找到满足任何N条件的行，你必须以这种方式构建查询：

SELECT * FROM Table_1 WHERE carnum 赞％@ c1 或 carnum 沥e @ c2％或 carnum 赞％@ c3％

我对你的问题的理解是你有一套 Checkbox es，可以任意组合检查0到5个。 理想情况下，你应该使用IN条款。问题是使用带有参数化查询的IN子句非常棘手。你应该总是在字符串连接上使用参数化查询。 我创建了一个带有5个复选框的项目，每个复选框的搜索值都是 Text 属性。以下适用于任意数量的复选框

使用 conn 正如 新 SqlConnection（constring） conn.Open（） 使用 cmd 作为 新 SqlCommand（） Dim qry As StringBuilder = 新 StringBuilder（ SELECT * FROM Table_1） Dim whereAdded As Boolean = False Dim commaRequired As Boolean = 错误 Dim i 作为 整数 = 1 对于 每个 c 作为控制在中控制 如果 c。 GetType （）= GetType （CheckBox）然后 Dim cb As CheckBox = c 如果 cb.Checked 那么 如果 不 whereAdded 然后 qry.Append（ WHERE carnum IN（） whereAdded = T rue 结束 如果 如果 commaRequired 那么 qry.Append（ ，） 结束 如果 qry.Append（ @ c + i.ToString（）） cmd .Parameters.AddWithValue（ @ c + i.ToString（），cb.Text）i + = 1 commaRequired = True 结束 如果 结束 如果 下一步 如果 whereAdded 然后 qry.Append（ ）） 结束 如果 cmd.CommandText = qry.ToString（ ） cmd.Connection = conn Dim dr As SqlDataReader = cmd.ExecuteReader （） while （dr.Read（）） Debug.Print（dr.GetString（ 0 ）） 结束 结束 使用 结束 使用

Hello, I want to create an sql command query in vb net (using ADO VS 2015 Community Edition) which I want to add multiple parameters in runtime eg. I have a field carnum which has 5 values and want to search through records by this field

SELECT * FROM Table_1 WHERE carnum LIKE @c1

now I want to add multiple carnum values in runtime eg. with 5 checkbox to select up to 5 values of carnum and to be added to the query if 2 check box selected

SELECT * FROM Table_1 WHERE carnum LIKE @c1 and @c2

Is there any way to do it? What I have tried: Nothing in code just to know if can be done, if I use an if then else statement or select case of checked checkbox I would need 55 'else' statements is there an easy way to do it?

解决方案

If i understand you correctly, you want to find rows that are in a list of values. So, you have to use IN clause[^] within WHERE statement (see: 'M').

SELECT * FROM Table_1 WHERE carnum IN (@c1, @c2)

But if you want to find rows that meet any of N conditions, you have to build query this way:

SELECT * FROM Table_1 WHERE carnum Like %@c1 OR carnum Like @c2% OR carnum Like %@c3%

My understanding of your question is that you have a set of Checkboxes and 0 to 5 of them can be checked in any combination. Ideally you should use an IN-clause. The problem is that it's quite tricky to use an IN clause with a Parameterized query. You should always use parameterized queries over string concatentation. I created a project with 5 checkboxes, each of which had my search value as the Text property. The following works with any number of checkboxes

Using conn As New SqlConnection(constring) conn.Open() Using cmd As New SqlCommand() Dim qry As StringBuilder = New StringBuilder("SELECT * FROM Table_1 ") Dim whereAdded As Boolean = False Dim commaRequired As Boolean = False Dim i As Integer = 1 For Each c As Control In Controls If c.GetType() = GetType(CheckBox) Then Dim cb As CheckBox = c If cb.Checked Then If Not whereAdded Then qry.Append("WHERE carnum IN(") whereAdded = True End If If commaRequired Then qry.Append(",") End If qry.Append("@c" + i.ToString()) cmd.Parameters.AddWithValue("@c" + i.ToString(), cb.Text) i += 1 commaRequired = True End If End If Next If whereAdded Then qry.Append(")") End If cmd.CommandText = qry.ToString() cmd.Connection = conn Dim dr As SqlDataReader = cmd.ExecuteReader() While (dr.Read()) Debug.Print(dr.GetString(0)) End While End Using End Using