通过代码向SQL命令添加多个参数 [英] Add multiple parameters to SQL command through code

问题描述

您好，

我想在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。< span class =code-keyword> 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

这篇关于通过代码向SQL命令添加多个参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！