[Driekus]

Hi all,

I’m struggling with the following. I want to query items from countries that are selected from a treeview (with checkboxes). However the number of countries that is selected by the user is not known beforehand. So how can I make a proper query when the number of OR operators is unknown? For example:

SELECT DISTINCT Item FROM Countries WHERE Country LIKE @c1 OR @c2 OR @c3 etc…

and then add the parameter values based on the selected countries in the treeview. I looked into parameter arrays but can’t make sense out of it really…
I hope anyone can help me here. I’m using VB.net in a winform application.

Many thanks,

[trevster344]

Why aren't they known before hand?

[Driekus]

because the user might select one country, or two, or...

[trevster344]

So they are selecting the countries in their heads? Before you fire a query you should gather all data needed from lists and such. That includes the number of selected indices, the values of the selected indices etc if you're using any sort of list control.s You can't do this? If so how come?

You're making it sound like the user is selecting countries from some unaffiliated list or program before, or as if you're trying to query without the necessary information to query.

Can I ask why you're using treeviews?

This post has been edited by trevster344: 11 June 2012 - 07:08 AM

[kasbaba]

It's a treeview with checkboxes. Once all selections have been done (and I assume it is not a multi-level) treeview, you want to get all items from your db.

So let's say the treeview is treeview1

'Loop through all nodes of treeview and form a SQL query. Here's how.
Dim SQL_Statement as string = "Select DISTINCT Items from Countries where Country In (#CountryList#)"
Dim CountryStr as string = string.empty
For each MyNode as TreeNode in Treeview1.Nodes
   CountryStr &= MyNode.Text & ","
Next

'Finally, we construct the SQL statement
SQL_Statement = SQL_Statement.Replace("#CountryList#",CountryStr)

'Now you can use the variable SQL_Statement wherever you wish.

There may be other solutions but this one should do the job. Let me know if this works.

[BobRodes]

Using IN is the best way to do it if the list of countries isn't too big.

You don't want to use LIKE and allow the user to put just any string in there. For example,
SELECT * FROM Countries where Name LIKE '%United%'
will return both United States and United Arab Emirates. If you don't put the percent signs, it will treat LIKE in the same way as =, so there's no reason to use LIKE anyway unless you are allowing users to pass a % sign into your string.

In any case, you are also allowing the string
'; DROP TABLE Countries --
That string would make your SQL statement look like this:
SELECT * FROM Countries where Name LIKE ''; DROP TABLE Countries --'"
This will first do a select, then delete your entire Countries table (SQL commands can be put on the same line if they are separated with a semicolon), and then comment out the apostrophe that you supply at the end of your string, as well as any remaining code on the line! I assume you'd like to avoid this. It's called an "injection attack", by the way, and you have to keep them in mind when you're figuring out your SQL commands.

This post has been edited by BobRodes: 15 June 2012 - 06:25 AM

[Driekus]

Thank you Kasbaba,
this is exactly what I was looking for.
Also BobRodes thanks for the tip/warning on (how to avoid) SQL injection!

[Driekus]

Hi Kasbaba,

I'm afraid I cheered too soon. I tried out your code using a listbox (not treeview) using the code below, but can't manage to get it working. What am I doing wrong?
Many thanks for any help!

Dim de As New DataTable
            Dim con As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\CountryDB.mdb")
            Dim MySQL As String = "SELECT DISTINCT Continent FROM Countries WHERE Country In (#CountryList#)"
            Dim db As New OleDb.OleDbDataAdapter(MySQL, con)

            Dim CountryStr As String = String.Empty
            For i = 0 To frmTry.ListBox2.Items.Count - 1
                CountryStr &= frmTry.ListBox2.Items(i).Text & ","
            Next

            MySQL = MySQL.Replace("#CountryList#", CountryStr)
            db.Fill(de)

            Me.DataGridView1.DataSource = de

[trevster344]

What's the error exactly?

[Driekus]

the datagridview stays completely empty. The strange thing is, when I have a textbox displaying the value of CountryStr I do get a value (see code snippet) and when I subsequently copy these textbox contents into the SQL query this works fine! So the SQL query "SELECT DISTINCT Continent FROM Countries WHERE Country IN ('Brazil','Canada',)" works fine but when I have "SELECT DISTINCT Continent FROM Countries WHERE Country IN (#CountryList#)" with Brazil and Canada in the Listbox nothing happens... Sorry if this sounds a bit vague, I hope I've explained it properly.
Many thanks,

 Dim CountryStr As String = String.Empty
        For i = 0 To ListBox2.Items.Count - 1
            CountryStr &= "'" & ListBox2.Items(i).ToString & "'" & ","
        Next

        TextBox2.Text = CountryStr

[BobRodes]

Sounds like you might have a glitch in the way the SQL statement is formed. The syntax should be
SELECT DISTINCT Continent FROM Countries WHERE Country In ('country1', 'country2', 'country3', '...')
Can you verify that your MySQL....Aha! The light dawns! Your for next loop will append a comma to the last value, resulting in a syntax error. You'll need to check to see if you're on the last loop each time and only put a comma if you aren't, or (probably more efficient, if uglier) whack the last comma off of your string when you're done looping.

Edit: although that looks like the problem, I still don't understand why your Fill method isn't throwing an exception if there's a syntax error in your SQL statement.

This post has been edited by BobRodes: 15 June 2012 - 08:34 AM

[Driekus]

Found it! The problem was the location of the data adapter in the code. It needs to be placed AFTER the for-next loop so that the parameter values are correctly passed to the query. Thank you all for your help, I very much appreciate it.
Here's the correct code:

Dim de As New DataTable
            Dim con As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\CountryDB.mdb")
            Dim MySQL As String = "SELECT DISTINCT Continent FROM Countries WHERE Country IN (#CountryList#)"
            Dim CountryStr As String = String.Empty

            For i = 0 To frmTry.ListBox2.Items.Count - 1
                CountryStr &= "'" & frmTry.ListBox2.Items(i).ToString() & "'" & ","
            Next

            MySQL = MySQL.Replace("#CountryList#", CountryStr)
            Dim db As New OleDb.OleDbDataAdapter(MySQL, con)
            db.Fill(de)

            Me.DataGridView1.DataSource = de

[BobRodes]

That was bothering me a bit too. Did you NOT get a syntax error in your for next loop? Sure looked like it would put an extra comma on the end.

[Driekus]

Hi Bob,

I did not get a syntax error, apparently the extra comma does not give any problems. The query "SELECT DISTINCT Continent FROM Countries WHERE Country IN ('Brazil','Canada',)" works just fine - note the comma at the end.