Can you modify a VB DataAdapter select statement at run time in VS 2010?

Question

I wish to populate a datagrid from a data source using parameters specified by the user.  I have successfully achieved this by using the TableAdapter.Fill method with VB in VS 2010 Express.  However, the potential permutation of parameters is becoming too large to have a separate Fill method for every possible combination.  Ideally I would like to construct the SQL SELECT statement at run time to reflect the user's options.  Can the Fill select command be set at run time, and if so, how?  I only need to build the WHERE clause, and the query is read only.

Suggestions would be much appreciated.

Answer 1

Hi Code-player,

I’m afraid that you need to give more explanations about” I only need to build the WHERE clause”

I’d like to give you a sample. In this code, add the items in combobox to select the column name and add a Textbox to let users input the selected column value. Here is a code:

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
                Using con As New SqlConnection( _
           "Data Source=.;Initial Catalog=testdb;Integrated Security=True")
            Dim constr As String = "select * from Table2 where " & ComboBox1.SelectedItem.ToString & "=" & TextBox1.Text
            Dim dataAdapter As New SqlClient.SqlDataAdapter(constr, con)
            Dim WTDataTable As New DataTable("CatalogWorkTable")
            dataAdapter.Fill(WTDataTable)
            DataGridView1.DataSource = WTDataTable
        End Using
    End Sub

Hope this helps.

---

Mark Liu-lxf [MSFT]
MSDN Community Support | Feedback to us

Answer 2

Thanks Mark.

My reference to the WHERE clause was simply to illustrate that this is the only part of the SELECT statement I wish to change at run time.

Because I'm using VS 2010 Express, the Data Source and DataAdapter connecting my application to my database are already defined.  At present I populate my DataGridView directly using Fill e.g.

Me.ItemTableAdapter.FillBy1(Me.Home_inventoryDataSet.Item, catComboBox.Text)

where the SQL SELECT statement is embedded in the properties of FillBy1, namely: SELECT * WHERE cat=?  It is this SELECT statement that I wish to construct at run time to reflect user input e.g.

constr = "SELECT * from item WHERE " & DBfield1 & "=" & opt1.Text & " AND " & DBfield2 & "=" & opt2.Text

Sadly, I'm still a newbie to DB manipulation in VS Express.

Answer 3

Hi Code-player,

>>“Because I'm using VS 2010 Express, the Data Source and DataAdapter connecting my application to my database are already defined.”

No, there is no relationship with editing the SQL Query with VS2010 express version. You can use Data Source Configuration Wizard or connection code to connect the database. And both them also can using the customer SQL query. 

And then, I also have no idea about your requirement. If you add your code in form_load event, you will fail to set the parameters to the SQL Query at run time, because you have no time to add them. In my sample, you need to add the parameters in textbox and combobox after the Form showed, and Click the button to reconnect the database, and using the new data to replace the old one.

By the way, According to your description, I’m afraid that you need to learn more about the SQL query and basic knowledge of VB.Net. The following information can help you:

Writing SQL Queries: Let's Start with the Basics: http://msdn.microsoft.com/en-us/library/bb264565(v=sql.90).aspx 

SqlConnection Class: http://msdn.microsoft.com/en-us/library/sd2728ad.aspx 

Hope this helps.

---

Mark Liu-lxf [MSFT]
MSDN Community Support | Feedback to us

Answer 4

Sorry Mark, I did not understand your answer; perhaps you did not understand my question.  I guess I will just have to worry the problem for myself, but thanks anyway for trying to help.

My SQL was only an example of what I want to achieve.  Although I accept that I may still have much to learn, after fifty years programming and almost forty years writing database query scripts, I doubt that 'Writing SQL Queries: Let's Start with the Basics' will have much to tell me!

Answer 5

Hi Code-player,

I’m sorry for my misunderstanding.

My understanding is that you wants to add the parameters to the SQL query to connect the database when the application running. So I made the first part of sample code for you. The code use SqlConnection Class and the SqlDataAdapter.fill method to add two parameters into SQL and show the result in datagridview. The two parameters are provided by a combobox control and a textbox control. And you can change the controls to fit your special situation.

But your second reply confused me. I have no idea about the way you connect to the database, using connection code or using Data Source Configuration Wizard. My second reply wants to let you know that whatever the way you connection to the database, you also need a control to store the data at firstly, and then add an event or method to invoke the Query. The SQL query has something different with it in database, Please check this link: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx

The Microsoft .NET Framework Data Provider for SQL Server does not support the question mark (?) placeholder for passing parameters to a SQL Statement or a stored procedure called by a command of CommandType.Text.

If you using Data Source Configuration Wizard to connection the database, the following information can help you change the SQL query in Fill method: How to: Edit TableAdapters: http://msdn.microsoft.com/en-US/library/ms171902(v=vs.100).aspx

Hope this will be more clearly for my suggestion. As there are many ways to reach your goals, if I misunderstood, you can post some of your code or sharing some details about your application and your requirements, such as the way connection to the database or the UI design. 

---

Mark Liu-lxf [MSFT]
MSDN Community Support | Feedback to us