Question My filter control is not working properly on my database project, can someone help me fix it?

[Godis]

I am working on a database project using Microsoft Visual Studio 2013, and also Microsoft SQL Server 2014 respectively. I have about four tables in my project named UsersDataSet. I want to create filter controls for each form application using combo boxes and textboxes. I want to use the UsersRegister form as an example.

In the loginDataSet filter column I configured the SQL query;
"SELECT Username, Password, Role
FROM Login
WHERE (Username = @username)"

Likewise;

SELECT Username, Password, Role
FROM Login
WHERE (Role = @Role)

Then in the code window;

  private void txtSearch_KeyUp(object sender, KeyEventArgs e)
        {
            if(txtSearch.Text == "")
            {
              
                this.loginTableAdapter.Fill(this.usersDataSet.Login);
            }
            else if(combSearch.SelectedIndex == 0)
            {
             
                this.loginTableAdapter.FillByUsername(this.usersDataSet.Login, usernameTextBox.Text);
            }
            else if(combSearch.SelectedIndex == 1)
            {
              
                this.loginTableAdapter.FillByRole(this.usersDataSet.Login, combRole.Text);
            }
        }

But when I run the application and try to filter for any record, only the first row is filtered; unless I place the cursor on a specific username or role before it can be filtered. I don't know where the error is. This error occurs to all the other applications in the project.

Please I would appreciate if someone can help me fix it.

Thanks.

Godis

 

[Skydiver]

Let me see if I understand your code correctly. You are waiting for key up events in your txtSearch text box.

For lines 3-6, If the text box is empty, you fill the data set with all the available data.

If the text box is not empty (lines 8-17), then you fill the data set with the matching username found in usernameTextBox textbox if the combSearch is selecting the first item. Otherwise, you fill the data set with the matching role found in combRole combobox if the combSearch is selecting the second item.

Are you are saying that only one time is return in all cases? Or is it returning only one item for some of the cases?

 

[Godis]

Let me see if I understand your code correctly. You are waiting for key up events in your txtSearch text box.

For lines 3-6, If the text box is empty, you fill the data set with all the available data.

If the text box is not empty (lines 8-17), then you fill the data set with the matching username found in usernameTextBox textbox if the combSearch is selecting the first item. Otherwise, you fill the data set with the matching role found in combRole combobox if the combSearch is selecting the second item.

Are you are saying that only one time is return in all cases? Or is it returning only one item for some of the cases?

Hi Skydiver,

"Are you are saying that only one time is return in all cases? Or is it returning only one item for some of the cases?"

Only the first row is returned in all cases. Then again, let me say for example; Godis is Admin and on any other row. When I click on that row and filter all Godis will display. Or, when I want to filter for User1 in Role column; if I click on any row that contains User1 then all User1 will display.

Actually, I don't know where the error is.

 

[Skydiver]

You keep on saying: "When I click on that row and filter" Can you show us the event handler that handles this row selection and setting of filters? The code you presented in your original post is for a keyup event. Keyup events are usually associated with textboxes where you type in things. You keep on talking about selecting a row and then filtering. Do you have a selected row event handler? Do you have a separate filter button? If so, can you show us the button click event handler?

 

[Godis]

Really I don't have any separate filter button, and I don't intend to have one. I rather intend to use the Keyup event for the filter textbox, to achieve my objective but the result is strange to me. When I became confused was when I put the cursor in a row then typed in the keyup textbox; and that was where I used the sentence; "When I click on that row and filter". Sorry, I might be wrong using it in the context. My expectation, and as it supposed to be, is that when I select an item in the combo box and type in the textbox it should filter accordingly. My query in the DataSet and the code in window is what I have presented originally, but I don't know what went wrong, where and why.

 

[Skydiver]

My expectation, and as it supposed to be, is that when I select an item in the combo box and type in the textbox it should filter accordingly.

Why would that be your expectation? That is not how a standard Windows Combobox works. That behaviour you are expecting is how some web sites work.

 

[Skydiver]

Moving this thread into WinForms...

 

[JohnH]

From Fill datasets by using TableAdapters - Visual Studio

ClearBeforeFill property
By default, every time you run a query to fill a TableAdapter's data table, the existing data is cleared, and only the results of the query are loaded into the table.

That means that if your KeyUp filtering is hit the results in table should start from empty and fill according to each if statement. Also use debugging to make sure you get/see correct filtering for different conditions, add else while at it to see if conditions is not met.