Click here to Skip to main content
15,664,619 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am a newbie in programming and I just learn programming by watching tutorial videos and reading articles. I have been searching for this but failed to find it. As a newbie like me we really appreciate any kind of help from the expert like you guys.

I created a form and use datagridview where my database data goes and i am able to search using textbox and the Button that I created. But I want to put a Filter on it so If a user wants to search the IT Staff from japan, so they choose Country in the first combobox and japan in 2nd combobox2. now they want to search IT Staff from japan whose status is Hired, so they enter Hired in the second text box , and the data grid shows IT Staff from japan whose status is Hired.

This is my Table
Position       Country          Status

IT Staff        Japan            Hired
Teacher        Germany           Not Hired
IT Staff        Mexico           Hired

when i type on my textbox "Hired" in my Datagridview shows

IT Staff          Japan            Hired
IT Staff          Mexico           Hired

I only want to show in my datagridview all IT Staff in japan whose hired.

What I have tried:

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
      If ComboBox1.Text = "Country" Then
          If ComboBox2.Text = "Japan" Then
          End If
      End If
  End Sub

Public Sub Status()
          Dim sCon As String = String.Format("Provider= Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\db.accdb")
          Dim str As String = "SELECT * FROM dbtable WHERE Status = @S"
          Dim dt As New DataTable

          Using con1 As OleDbConnection = New OleDbConnection(sCon)
              Using cmd1 As OleDbCommand = New OleDbCommand(str, con1)
                  With cmd1.Parameters
                      .Add("@S", OleDbType.VarChar).Value = TextBox1.Text
                  End With
                  Dim adp As New OleDbDataAdapter(cmd1)
                  DataGridView1.DataSource = dt
              End Using
          End Using
      Catch ex As Exception
      End Try
  End Sub
Updated 19-Jul-18 3:47am
CHill60 18-Jul-18 3:35am    
Is your dbtable where you hold your "staff" information - what does that table look like?
Are you looking to set up the Datasource for DataGridView1 or to Filter DataGridView1
I think we need a little more detail about what you are trying to do before we can help you
Member 13890537 18-Jul-18 4:38am    
Thank you for replying now I know why no one answering my question. I already updated some details and I am looking on how to filter the DataGridview1
Member 13890537 18-Jul-18 21:05pm    
oh no! nobody want to answer my question.

1 solution

Try something like this:
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
End Sub

Public Sub Status()
    ' TODO: Move this to the application's configuration file:
    Const sCon As String = "Provider= Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\db.accdb"
    Using con As OleDbConnection = New OleDbConnection(sCon)
        Using cmd As OleDbCommand = New OleDbCommand("", con)
            Dim str As String = "SELECT * FROM dbtable WHERE Status = @S"
            cmd.Parameters.Add("@S", OleDbType.VarChar).Value = TextBox1.Text
            If ComboBox1.Text = "Country" Then
                str = str & " AND Country = @Country"
                cmd.Parameters.Add("@Country", OleDbType.VarChar).Value = ComboBox2.Text
            End If
            cmd.CommandText = str
            Dim dt As New DataTable
            Dim adp As New OleDbDataAdapter(cmd)
            DataGridView1.DataSource = dt
        End Using
    End Using
End Sub
Share this answer
Member 13890537 19-Jul-18 20:39pm    
Thank you Mr. Richard Deeming It works. Before I don't know how to do apply this str = str & " AND Country = @Country" Until you show me how, so I try to use this code.

Dim dv As New DataView(dt)
dv.RowFilter = "Country ='Japan' and Status='Hired'"
DataGridView1.DataSource = dv

and It works
But I want the combobox1 and the combobox2 to be the Identifier and the textbox1 and textbox2 to be the data of the columnName.
I not sure on how to put this on my 2 combobox = ColumnName and in my 2 textbox = Data of the column. Should I Use IF condition to make it work?

So i Try and error
this code works. I know using String is bad.
dv.RowFilter = "Country LIKE '%" + TextBox1.Text + "%' And Status LIKE '%" + TextBox2.Text + "%'"
But this, not sure how to do this code. Im trying to look for the short way if there is?
dv.RowFilter = ComboBox1.Text & " = " & TextBox1.Text & "And" & ComboBox2.Text & " = " & TextBox2.Text it say System.Data.SyntaxErrorException: 'Syntax error: Missing operand after 'StaffAndCountry' operator.'

I'm planning to spent > 8hours to try and Error to figure this problem until I Read your code. Again Thank you very much Mr and have a good and healthy day. But still I'm trying to figure it out if it is possible?

I have a little bit question about the method you use. What is the difference of that and using .RowFilter ?
Note: It is okay if you don't want to answer this question because someday i'll figure it out. ^_^ Thanks again Mr.
Richard Deeming 20-Jul-18 7:17am    
dv.RowFilter = ComboBox1.Text & " = " & TextBox1.Text & "And" & ComboBox2.Text & " = " & TextBox2.Text

You're missing spaces around the And operator, and quotes around the values. Try:
dv.RowFilter = ComboBox1.Text & " = '" & TextBox1.Text & "' And " & ComboBox2.Text & " = '" & TextBox2.Text & "'"

You're correct that using string concatenation in a query is a bad thing. But in this case, the filter is evaluated locally; there's no way for invalid input to issue a rogue query against your database. And the RowFilter doesn't have a concept of parameters, so you don't have an alternative.
Member 13890537 22-Jul-18 21:44pm    
This mean that it is safe to use string concatenation using RowFilter.
Thanks again it works! I'll put this on my error screenshot folder for the record.

In Mr.Deeming point of view which is better to use or good to practice? The RowFilter or the Solution you gave using parameters?

I am happy that I learn again today.
How did you learn this thing or did you gain it by experience?
Have a Great and healthy day Mr.
Richard Deeming 23-Jul-18 7:36am    
If you're filtering data that's already been loaded, and you're not worried about records which have been added to the database since you loaded the data, then the RowFilter is probably better.
Member 13890537 24-Jul-18 22:39pm    
I found out a problem of this RowFilter when I try to input this data on my database 'Japan and the postion I'T Staff. Then try to search this using rowfilter it say There is an error says missing operand After 'Japan'. There is no error if I search a word without a single string.
Btw is it possible to do this short code dv.RowFilter = ComboBox1.Text + " LIKE '%" + TextBox1.Text + "%' And " + ComboBox2.Text + " LIKE '%" + TextBox2.Text + "%' And DateandTime1 >='" + DateTimePicker1.Value + "' And DateandTime1 <='" + DateTimePicker2.Value + "'" using Parameters?

Thanks again Mr.Deeming
Have a Great and healthy day Mr.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900