dynamic SQL query in a VB.net app

Homerboy

Lifer
Mar 1, 2000
30,890
5,001
126
I'm trying to write a small VB.net application.
In the application the user will have a series of check boxes to choose from
(Checkbox1, checkbox2...checkbox10)

What I'd like to have is the SQL query sting be dynamically adjusted depending on the checkboxes they choose. The checkboxes are the fields being returned, not the parameters of the query (that portion comes later)

can anyone help me get started here?
I'm failry new to the world of VB.net. I've created a few simpler apps, but this one is stumping me.

Thanks in advance.
 

JustAnAverageGuy

Diamond Member
Aug 1, 2003
9,057
0
76
Perhaps not the most efficient way, but the way I typically handle this is to have the SQL server return all the values regardless and only display the ones the user chooses to see.

Much easier to maintain as well.
 

ForumMaster

Diamond Member
Feb 24, 2005
7,792
1
0
Almost all programming languages have some kind of database api.
almost all api are similar and share the following basic principles:

  1. You must first open a connection (usually the parameters would be the connection string, username and password)
  2. Then, you must open a cursor (You will then initialize a cursor with a "query string".
  3. then you execute the cursor

Now, whenever working with database api, remember to make the connection object a singleton (a global, non private object) and the query string a global variable as well.

For your application, your code would construct the query string, and when the query string is complete, you open the cursor, execute it, return the results and close it.

Remember to always use bind variables when constructing your query string if you have any input parameters beyond check boxes, so that there's no risk of sql injection.

best of luck.
 

JamesV

Platinum Member
Jul 9, 2011
2,002
2
76
It's been ages since I've programmed, but I used to work alot with VB, including creating full-fledged in house applications in Access.

In effect you want the user to be able to query a database through checkboxes (ie. they are selecting the fields they want to see, and your SQL query simply returns the correct fields)?

If so, the option above to return all, yet just display selected works fine. Unless you have hundreds of checkboxes and huge databases, returning entire records instead of selected fields isn't a big issue.

Another option is to dynamically create the search string. As I said, it's been a while, so some pseudocode :

Code:
stringVariable = "Select FROM DBName "
if (checkbox1) stringVariable = stringvariable & "checkbox1FieldName "
if (checkbox2) stringVariable = stringvariable & "checkbox2FieldName "
...
stringvariable = stringVariable & "endOfSQLQueryString"

In the above, you'd also have to format, like putting commas between field names; the idea is to create the entire SQL query string on the fly each time via manipulating the text in the query. You could also get fancy and do this in a loop (my preferred method to keep code small).

Can't help with the exact code, like I said, it's been a while, but I've used dynamic string contruction alot. One hint - put some debug code into your program, so a window might pop up showing the created string (to find problems if it isn't working right).
 

ethebubbeth

Golden Member
May 2, 2003
1,740
5
91
How are you going to be presenting the data to the end user? If you are going to load the data table or data set into a grid, you could just hide specific columns as recommended by JustAnAverageGuy.

You could dynamically build the query string, as suggested by JamesV, but that leaves you open to SQL injection vulnerability if the user is able to do any sort of data entry to manipulate the query (such as specifying values to use in your parameters or to filter in the WHERE clause).

Another advantage is that since the query itself will be returning the same fields each time, your DBMS back end can optimize the query (especially if you call the query via a stored procedure).

Finally, it means that the user could check/uncheck boxes after the query is run and you could dynamically show/hide the columns without having to query the database each time.