Dynamically building a sql Where clause

Question

Dynamically building a sql Where clause

b.carlson

SSC Enthusiast

Points: 106
More actions
April 11, 2008 at 12:31 pm

#95850

I have an Application that I want to allow users to filter the DataGridView by selecting options from comboboxes that I have populated from different columns in the dataset.
Where I'm having a problem is in building the where clause from the comboboxes 4 in this case. What I'm trying is building a string with the selections from the comboboxes however I have to append the "AND" after each selection except for the last selection where no "AND" is needed.
I'm sure what I'm doing is nothing new so I'm wondering if anyone has some sample code that I could look at for ideas.
Thanks

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

This should really be done in a stored procedure instead of dynamic SQL in a GUI... dynamic SQL in a GUI is a primary source of SQL Injection attacks... and it's a very serious problem.

Please post an example of the dynamic SQL you're trying to create and let's see if we can get you on the right road.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jezemine SSCrazy Eights Points: 8059 More actions · Answer 2

you don't necessarily need dynamic sql for this.

see: http://sommarskog.se/dyn-search.html

---------------------------------------
elsasoft.org

b.carlson SSC Enthusiast Points: 106 More actions · Answer 3

Thank you for the replies, I did figure it out and it is quite simple. I remember doing this several years ago and the solution was not more that 6 or 7 lines of code.

Anyway what I have is 4 non-editable comboboxes, these comboboxes are populated by a simple SQL statement like "SELECT DISTINCT Code FROM CODES ORDER BY Code"

The user can select an item from any of the 4 comboboxes and the DataGridView will be filtered based on the selection. All of the 4 comboboxes can be used or only one in any order.

I do not build the complete SQL statement for the DataGridView I'm using the built in filter property of the DataGridView. I should say this is not ASP but VB.NET in a Win32 project.

The solution was very simple with an array and a couple of functions.

Thanks for taking the time to respond.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

b.carlson (4/11/2008)
The solution was very simple with an array and a couple of functions.
Thanks for taking the time to respond.

You could thank us by posting the code you used... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

The standard technique for this is to add a dummy clause to the WHERE:

strWhere = " WHERE (1=1) "

For i = 1 to 4

strWhere = strWhere + " And " + Col(i) + " = " Val(i)

Next i

As far as the SQL Injection concerns, as long as they can only pick values off a list, you should be OK. however, if they can type in the values themselves, you will need to protect against Injection. The SOP way to do this is, as Jeff says, by passing parameters to a stored procedure that then uses them as variables to match in the WHERE clause.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

b.carlson SSC Enthusiast Points: 106 More actions · Answer 6

The code I used is very similar to what TC posted. Every time the user selected an item from one of the 4 comboboxes I built a string with the selected value. I used a separate function to check if the next item in the list was actually selected in which case I would need to add the " AND " Else nothing was added which satisfied the case of only one item selected. When I get back to work on Monday I will post the code. If I remember correctly I have the display property of the comboboxes set to Listbox and readonly (locked) which satisfies the SQL injection threat.

BC

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

TC?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

b.carlson SSC Enthusiast Points: 106 More actions · Answer 8

b.carlson

SSC Enthusiast

Points: 106

April 12, 2008 at 7:34 pm

#801839

Sorry for the abbreviation TC = Ten Centuries

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

b.carlson (4/12/2008)
Sorry for the abbreviation TC = Ten Centuries

Harrumph. You know "Joel" at least had some letters in common with Jason S.'s real name...

🙁

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

b.carlson SSC Enthusiast Points: 106 More actions · Answer 10

Here is the code that I used.

Note: I included the code from one combobox.

Private Sub PeriodComboBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PeriodComboBox.SelectedIndexChanged

Try

strSQLFilters(3) = " Period = " & Me.PeriodComboBox.Text

BolSQLFilterChanged(3) = True

BuildSQL()

Catch ex As Exception

MsgBox("ERROR PeriodComboBox_SelectedIndexChanged")

End Try

End Sub

Private Sub BuildSQL()

Dim i As Integer

StrSQLBuild = ""

' We need to place our "AND" in the where clause

For i = 0 To 3

If CheckForNextInList(i) Then

StrSQLBuild = StrSQLBuild & strSQLFilters(i) & " AND "

Else

StrSQLBuild = StrSQLBuild & strSQLFilters(i)

End If

End Sub

Private Function CheckForNextInList(ByVal Posistion As Integer) As Boolean

If BolSQLFilterChanged(Posistion + 1) Then

Return True

Else

Return False

End If

End Function

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

Thanks!

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

b.carlson SSC Enthusiast Points: 106 More actions · Answer 12

After further testing my original code did not work in all cases. Specifically selecting the first and the last comboboxes in that order. I had to change the code slightly. Instead of just checking for the next combobox being selected I checked for any of the next comboboxes being selected.

Private Sub BuildSQL()

Dim i As Integer

StrSQLBuild = ""

' We need to place our "AND" in the where clause

For i = 0 To 3

If (CheckForNextInList(i) And BolSQLFilterChanged(i)) Then

StrSQLBuild = StrSQLBuild & strSQLFilters(i) & " AND "

Else

StrSQLBuild = StrSQLBuild & strSQLFilters(i)

End If

End Sub

Private Function CheckForNextInList(ByVal Posistion As Integer) As Boolean

Dim i As Integer

For i = Posistion To 3

If BolSQLFilterChanged(i + 1) Then

Return True

End If

End Function

b.carlson SSC Enthusiast Points: 106 More actions · Answer 13

I have a different issue, when I close the program I get an error which is being caused by the "SelectedIndexChanged" event of the comboboxes. It seems when the program is terminated the comboboxes are cleared which changes the selected index, which in turn fires the "SelectedIndexChanged" event. I'm just starting with VS 2005, I'm much more familiar with VB6, the error could in the way I populated the comboboxes. I used a dataset with TableAdaptors which I'm told is "Strong Typing" approach. Maybe I should have populated them differently?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 14

b.carlson (4/14/2008)
I have a different issue, when I close the program I get an error which is being caused by the "SelectedIndexChanged" event of the comboboxes. It seems when the program is terminated the comboboxes are cleared which changes the selected index, which in turn fires the "SelectedIndexChanged" event. I'm just starting with VS 2005, I'm much more familiar with VB6, the error could in the way I populated the comboboxes. I used a dataset with TableAdaptors which I'm told is "Strong Typing" approach. Maybe I should have populated them differently?

Catch the [font="System"]Form.FormClosing[/font] event and set a module level flag to indicate that your form is closing. Then check this flag in your event-handling procedures and just exit if it is set.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]