Add multiple parameters to SQL command through code

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

SQL

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

SQL

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

SQL

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?

Posted 19-May-17 5:16am

Member 3892343

Updated 19-May-17 6:52am

v2

Add a Solution

Comments

[no name] 19-May-17 11:30am

"if can be done", okay then yes

Member 3892343 19-May-17 11:43am

sorry, 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?

[no name] 19-May-17 11:52am

Sorry, that is a new and completely different question. The comment answered your question for you.

Member 3892343 19-May-17 11:42am

Sorry how it 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?

F-ES Sitecore 19-May-17 11:48am

If you have a maximum number you can do it like

where (@c1 is null or carnum like @c1) and (@c2 is null or carnum like @c2) and (@c3 is null or carnum like @c3) ....

Then if you have two values pass them in as @c1 and @c2 and leave @c3, @c4 and @c5 as null.

If you have no maximum you could just construct the query as a text string and use standard string concatination to add as many "or" statements as you need.

Member 3892343 19-May-17 13:18pm

ty

2 solutions

Solution 1

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').

SQL

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:

SQL

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

Posted 19-May-17 6:04am

Maciej Los

Comments

CHill60 19-May-17 18:44pm

5'd

Maciej Los 20-May-17 2:49am

Thank you, Caroline.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2017-05-19T06:52:00

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

VB

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