TableAdapter Question

cory thompson

15y3.6k.NET

I am a complete newbie and am stuck trying to return a search using a tableadapter. If I use the following code I can search my database from whatever I enter into my textbox, but I use a tableadapter with radio buttons in my code which filters down my datagrid.
If I use my current code to search then my radio buttons stop working.
da.SelectCommand = new SqlCommand(string.Format("SELECT * from LoggedCalls where [Fault] like '%{0}%' OR [Resolution] like '%{0}%'", tboxSearch.Text), cs);

how can I convert this to use a tableadapter?

Thanks

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sam Hobbs15y ago
Accepted answer
Okay, let's start by creating a separate program to work with. That will be easier for me to explain things.

Read my article Database Table Update in a DataGridView without Writing Code. Use your table as the table for the data source; in other words, the SQL can be "SELECT * from LoggedCalls" without any filter ("Like" clause). Then do something in the form, such as add a push button or add Radio Buttons or something. This is just for test purposes.

Then, in the designer for the form, along the bottom, it should show the DataSet (such as loggedCallsDataSet), TableAdapter (such as loggedCallsTableAdapter) and a BindingSource (such as loggedCallsBindingSource).

Let's say you just added a push button. In the handler for the clicked event, you can set a filter. I am not sure of the syntax of what goes into the filter, but probably it is the same as the where clause. So set the filter using the following or whatever is relevant close to the following:

loggedCallsBindingSource.Filter = "Fault = 'whatever'";

Note that I am not using a Like clause there. If everything works up to that point, then go ahead and try adding a more complex filter, such as one that uses a like clause. Note that a filter can be canceled by simply setting it to an empty string, as in "". Let me know how you do with that.
Sam Hobbs15y ago
As such a beginner, you are doing very well. Thank you for figuring it out without very detailed guidance.

When I was a beginner with only a couple of months of experience, I was going to the local community college to use a computer made in the late 1950's. I had to put my programs onto IBM 80 column punched cards (as described in punched computer cards) that were designed nearly a century ago. I got only a few runs (compiles or tests) per week. You however can get a few runs a minute.
cory thompson15y ago
Yes it was :)

Where I was getting stuck was how to use the bindingsouce.filter as in what came atfter .filer, now I have been shown what it should look like it was easy for me to transfare to my code. Just didn't know it was a standard sql command.

I was thinking all along that I would need to declare a textbox somehow in a tableadapter query but didn't know how to do it.

I have only been developing for 6 weeks and its all been from reading books after work so a couple of hours a night maximum. That said, my program is coming along....

Thanks again
Sam Hobbs15y ago
So was that enough for you to be able to figure out how to do what you were asking about?
cory thompson15y ago
Thanks for your help Sam
Its all working great. Here is the code I used

loggedCallsTableAdapter.FillBy_All(dataSet1.LoggedCalls);
loggedCallsBindingSource.Filter =
"Fault LIKE '%" + tboxSearch.Text + "%'";
cory thompson15y ago
Thanks Sam.
I am still having trouble with this.
Any chance you could create an example for me? I am a complete newbie and in need of lots of help.

Here is what I have so far:

This is a Query in my LoggedCalls TableAdapter
SELECT   * (or I include all my columns)
FROM         LoggedCalls
WHERE     (Fault LIKE '%@search%') OR (Resolution LIKE '%@search%')
(I'm not sure if @search is correct here ^^^^^ I would use @search in my form to point to my textBox, which is named tboxSearch. I think I would then need to put @search = tboxSearch.Text somewhere.)

If I dont use my TableAdapter I can return a search from either one of the following.

da.SelectCommand = new SqlCommand("SELECT * from LoggedCalls where [Fault] like '%" + tboxSearch.Text + "%' OR [Resolution] like '%" + tboxSearch.Text + "%'", cs);
da.SelectCommand = new SqlCommand(string.Format("SELECT * from LoggedCalls where [Fault] like '%{0}%' OR [Resolution] like '%{0}%'", tboxSearch.Text), cs);
But I need to use the TableAdapter, I have a datagrid that show data from the TableAdapter and is filtered using radio buttons so I dont want to execute another sql command to poulate the datagrid.
Thanks.
Sam Hobbs15y ago
Create a data source (TableAdapter) with parameters in the SQL. Or better yet, I think you can add a query to your existing TableAdapter except the new query will have parameters. I don't see any parameters being passed to the FillBySearch method. If you create a new query that has parameters then the TableAdapter will include a query that does take parameters.

Or better yet, I think you can use the TableAdapter the way it is and then when the program needs to, it can create a filter dynamically and set the BindingSource.Filter property to the filter.
cory thompson15y ago
If I do this
this.loggedCallsTableAdapter.FillBySearch(this.dataSet1.LoggedCalls);
Then my table adapter will return info with my radio buttons still woking, but I have defined in the dataset what to search for. How can I get the dataset to search from a textbox?