I posted this question on the Microsoft VB.Net forum a while ago, but nobody's replied yet.
I am working with a program that collects a lot of data about applicants, and saves that data as an XML file, 1 file per applicant. The program's main screen is a grid that displays a few columns in order to help the user select the correct record for viewing/editing. That grid was not designed to allow the user to determinie which columns will be displayed, to export the contents of the grid, or to be queryable.
I am building a tool application (using VB2008) that finds all of the XML files, loads them into a dataset and displays the data in a datagridview, allows users to choose which columns will be displayed, allows users to use simple queries to narrow the results, and allows the data to be exported to an Excel spreadsheet.
Everything else is working fine, but I am having problems with the query portion. If the user wants to run a query, the application opens up a new form called frmSimpleQuery which contains a listbox (has all column names) and a textbox. The user selects a column name from the listbox and provides a value to check against in the textbox. When the user clicks btnRUN, the program is supposed to create a simple query and execute it displaying the results in the datagridview.
The following is the code I currently use for finding data based on the Employer column:
CODE
Dim strValue As String = tbxValue.Text
Dim PD = _
From c In doc...<MAPT1> _
Where c.<Employer>.Value = strValue _
Order By c.<LNAM>.Value _
Select New With {c.<FName>.Value, c.<MName>.Value, c.<LName>.Value}
datagridview1.DataSource = PD.ToList
Although this query does work, it is not efficient. There are over 100 columns in the XML files i am working with. In order to select all columns, I would have to include all of the column names in the SELECT statement. Also, in order to allow the user to search against other columns, I would have to rewrite this code (well, copy and paste) modifying the WHERE clause for each of the columns. Like I said... it is not efficient.
Two questions:
1. Does anybody know how to make the WHERE clause dynamic? Maybe something like:
CODE
Dim strValue As String = tbxValue.Text
Dim PD = _
From ... _
[color=#FF0000]Where c.<ListboxColumnName.text>.value = strValue _[/color]
Select ...
datagridview1.DataSource = PD.ToList
2. Does anybody know how to make the SELECT statement grab the values from all columns and still be usable with a DataGridView? Maybe something like:
CODE
Dim strValue As String = tbxValue.Text
Dim PD = _
From ... _
Where ... _
[color=#FF0000]Select New With {c.<*>.Value}[/color]
datagridview1.DataSource = PD.ToList
Thank you very much for any help you are able to provide.
-Rob
| 