How to filter datagridview using VB.NET and ms acces?

Question

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
              Status()
          End If
      End If
  End Sub

Public Sub Status()
      Try
          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)
              con1.Open()
              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)
                  adp.Fill(dt)
                  DataGridView1.DataSource = dt
              End Using
              con1.Close()
          End Using
      Catch ex As Exception
          MsgBox(ex.Message)
      End Try
  End Sub

Posted 17-Jul-18 18:13pm

Member 13890537

Updated 19-Jul-18 3:47am

v7

Add a Solution

Comments

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

Add a Solution

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

Richard Deeming · Accepted Answer · 2018-07-19T03:48:00

Solution 1

Try something like this:

VB.NET

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    Status()
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)
            adp.Fill(dt)
            
            DataGridView1.DataSource = dt
        End Using
    End Using
End Sub

Posted 19-Jul-18 3:48am

Richard Deeming

Comments

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.
but
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.

Richard Deeming 25-Jul-18 7:37am

Unfortunately, since the RowFilter doesn't support parameters, you'll need to "escape" single quotes by doubling them up.

Dates require specific formatting - they need to be wrapped in # characters, and use the US format "MM/dd/yyyy".

Try something like this:

dv.RowFilter = string.Format("{0} Like '%{1}%' And {2} Like '%{3}%' And DateandTime1 >= #{4:MM/dd/yyyy}# And DateandTime1 <= #{5:MM/dd/yyyy}#",
    ComboBox1.Text, TextBox1.Text.Replace("'", "''"),
    ComboBox2.Text, TextBox2.Text.Replace("'", "''"),
    DateTimePicker1.Value, DateTimePicker2.Value);

The filter syntax documentation is available in the DataColumn.Expression remarks:
https://docs.microsoft.com/en-gb/dotnet/api/system.data.datacolumn.expression?view=netframework-4.7.2#expression-syntax[^]

Member 13890537 25-Jul-18 22:36pm

It works like a parameter do.
I have been reading this
http://www.csharp-examples.net/dataview-rowfilter/
Article but not yet done reading. I don't know yet how to code this like the way you code it. By the way What is the meaning(purpose) of "{0}, '%{1}%', {2}, '%{3}%' ,4: ,5: on this line of code dv.RowFilter = string.Format("{0} Like '%{1}%' And {2} Like '%{3}%' And DateandTime1 >= #{4:MM/dd/yyyy}# And DateandTime1 <= #{5:MM/dd/yyyy}#",? So I can Understand more

Thanks Again Mr.Deeming
I really do appreciate your help
Have a Great and Healthy Day Mr.

Richard Deeming 26-Jul-18 7:25am

That's a composite format string:
Composite Formatting | Microsoft Docs[^]
Examples | String.Format Method (System) | Microsoft Docs[^]

Member 13890537 30-Jul-18 2:55am

Good Afternoon Mr Deeming
Thank you for the links and I already start reading it

If I have another question can I ask you again?

Have a Healthy and Great Day Mr.

Richard Deeming 30-Jul-18 16:44pm

Best to post it as a new question. That way, other people can help too. :)

Member 13890537 30-Jul-18 20:31pm

Okay ^_^ thank you very much for answering all my question. Your the best Mr.Deeming

Have a great and healthy day Mr.

How to filter datagridview using VB.NET and ms acces?

1 solution

Solution 1

Add your solution here

Preview 0

How to filter datagridview using VB.NET and ms acces?

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us