[2005] Let user choose DB columns

[cptHotkeys]

Hi all.

I am building a datadriven application (without using the Visual Studios built in support for datasources ect). I add the constring ect manually. what I want to do is let the user choose which columns from a given table are displayed and in what order. I can currently think of a number of ways, however I was wondering if there are any more efficient solutions, heres the most usefull way i can think of.
Query the DB to get a list of column names for a table.
Let the user select the the column names from a list in a dialog type form which will save the results(drag from one list to another type deal, move up/down).
reconstruct the default select statement for this sql string.
eg:

Sub constructSQL(byval strTableName as string,byval fieldsToDisplay as list(of string))
    dim strSQL as string = "SELECT "
    for each s as string in feildsToDisplay
         strSQL &= s & ", "
    next
    'remove the last commer
    strSQL &= "FROM " & strTableName
end sub

This pseudo code also allows the user to reorder the columns, as the data is to be displayed in a list view and the listveiw gets its column headers via the column names from the datareader object. I also havent got to sorting yet, and was thinking that there might be a way to kill two birds with one stone.

Thanks in advance.

[jmcilhinney]

That pretty much the way you'd do it. You'll have to build each clause separately, e.g. WHERE and ORDER BY, and then put them all together.

[cptHotkeys]

Yeah, I get the where statement from a treeview nodes tag property or from a search, and I guess I could get the "order by" part of the clause from the listveiws column click event (the column headers text properties are column names).

Thanks...

[jmcilhinney]

You might wat to take a look at the Query Builder in VS for some ideas.