Click here to Skip to main content
13,192,855 members (54,761 online)
Rate this:
 
Please Sign up or sign in to vote.
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?
Posted 19-May-17 5:16am
Updated 19-May-17 6:52am
v2
Comments
NotPolitcallyCorrect 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?
NotPolitcallyCorrect 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
Rate this: bad
 
good
Please Sign up or sign in to vote.

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').
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%
  Permalink  
Comments
CHill60 19-May-17 18:44pm
   
5'd
Maciej Los 20-May-17 2:49am
   
Thank you, Caroline.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

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
  Permalink  
Comments
Maciej Los 19-May-17 14:14pm
   
Caroline, seems, you understand OP issue the same way as me.
I'm bit disappointed that you didn't pointed out to my answer ;(
5 for the effort.
CHill60 19-May-17 18:42pm
   
Apologies. I didn't see your solution. My Internet connection is really bad at the moment plus the site has been having some performance problems.
You know me. ..I always give credit where I think it's due and you did indeed point the OP in the right direction.
Maciej Los 20-May-17 2:49am
   
Caroline, it was a "casual remark". Yeah, i know you. You're person who gives credits to other members, so i decided to post such of comment. It wasn't my intention make you guilty. Sorry...
CHill60 20-May-17 3:05am
   
:)

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web01 | 2.8.171017.2 | Last Updated 19 May 2017
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100