[RESOLVED] [2005] Tableadapter SQL select command - Command text query

[Stuw]

Is it possible to change a Tableadapter's SQL Command text property using code?

Im filling my datagridview with data as below. But i want to put in a where clause in my sql statement using a variable

i.e - SELECT * FROM Faults WHERE Callnumber = ('" + callopen + "')"

Try
            Me.FaultsTableAdapter1.usercall(Me.Database1DataSet1.Faults)
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
    End Sub

Any ideas?

[jmcilhinney]

It's possible but there should never be a reason to do so. If you want to execute a different query then you should be adding that query to your TableAdapter in the DataSet designer.

[Stuw]

But i can't search using the variable in the designer, or can i?

[jmcilhinney]

Yes, you can. You add a query and include one or more parameters in it. The wizard then regenerates your TableAdapter and adds another method to execute the new query. It will include an argument for each parameter in your query. You supply the parameter values to the method and it will insert them into your query.

[Stuw]

I don't understand it enough to put what u said into practice im afraid

Im guessing its not just a case of putting the right string in the commandtext properties, ive looked at the parameters properties but can't see anything of use there.

How do you do it with code!?

[Stuw]

Anyone?

[jmcilhinney]

Forget doing it in code. If you want to create ad hoc SQL statements then you shouldn't be using a TableAdapter at all. Let's do it the proper way.

Double-click your DataSet in the Solution Explorer to open it in the designer. Right-click your TableAdapter and select Add Query. Now open the Query Builder to build your query visually.

You can use the top section to add tables to your query. Right-click it and add the table you want to get the data from if it's not already there.

You can use the second section to select which columns you want returned from the selected table(s). Select your desired columns if they are not already. Make sure you select the same columns as you did for the original query. If you want all columns then select the *.

The third section will show you the actual SQL code that will be executed.

Now, you want to filter your data on the CallNumber column. Go back to the second section and, if CallNumber is not already listed, add it and uncheck the Include box. Now, in the Filter column type @CallNumber if you're using SQL Server or ? if you're using Access. Tab away from that cell and observe how the SQL code has changed.

Now click OK and then click Next until you get asked to name your query. You will be asked for two names: one starting with "FillBy" and one starting with "GetDataBy". Set the names to "FillByCallNumber" and "GetDataByCallNumber". Now finish the wizard. That's it. You're done.

Your TableAdapter's code has now been regenerated and two new methods added: FillByCallNumber and GetDataByCallNumber. Now, instead of calling Fill you can call FillByCallNumber. Instead of passing just a DataTable to be filled you also pass a CallNumber value. That value is inserted into the query and will be used to filter the data returned by the method.

[Stuw]

Thanks for your help, ive done the above but im getting the following error with this bit -

Try
            Me.FaultsTableAdapter1.getdatabycallnumber(Me.Database1DataSet1.Faults)
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try

Error 1 Value of type 'IC_Fault_logging_system.Database1DataSet.FaultsDataTable' cannot be converted to 'String'.


If i change it to -

  Try
            Me.FaultsTableAdapter1.fillbycallnumber(Me.Database1DataSet1.Faults)        
            Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try

I get this error -

Error 1 Argument not specified for parameter 'callnumber' of 'Public Overridable Overloads Function fillbycallnumber(dataTable As Database1DataSet.FaultsDataTable, callnumber As String) As Integer'.

[jmcilhinney]

The whole point of this was so you could filter the data retrieved by CallNumber. Where are you specifying the CallNumber value to filter by?

The Fill method populates an existing DataTable, so you need to pass the DataTable to be populated AND any parameter values required by the SQL query, e.g.

Me.FaultsTableAdapter1.fillbycallnumber(Me.Database1DataSet1.Faults, callNumber)

Intellisense would have told you that as you were typing and if you read the error message for your second code snippet you'll see that it's telling you the same thing.

The GetData method creates a DataTable itself, populates it and then returns it. You do NOT pass a DataTable to GetData. You ONLY pass the parameter values and the function will return a new DataTable to you, e.g.

myDataTable = Me.FaultsTableAdapter1.getdatabycallnumber(callNumber)

If you already have a DataSet that contains a DataTable, which you do, then you would call Fill to populate that existing DataTable. If you don't already have a DataTable then GetData will return one to you, which you would do if you were not using a DataSet.

[Stuw]

Thanks alot thats working now