none
I have an access database and I want to display all the rows that contain the same information in the first column into 4 datagridviews

    Question

  • I have an access database and  I want to display all the rows that contain the same information in the first column into 4 datagridviews. The first DGV is for any row that contains a date of 2014 in 5th column. The second DGV is for any row that contains a date of 2015 in 5th column. The third DGV is for any row that contains a date of 2016 in 5th column. The second DGV is for any row that contains a date of 2017 in 5th column.
    Monday, May 01, 2017 2:07 AM

All replies

  • First things first, explain how you are loading these DataGridView controls e.g. using a TableAdapter/BindingSource/DataSet or perhaps a DataTable or no data source etc.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, May 01, 2017 2:53 AM
    Moderator
  • At the moment I just have a form with the four DGV and they are not binding with anything. I would like to use a combobox to select any part number that are in the first column of the dataset. When I click on a button the form with the datagridview opens it would sort the rows containing that part number according to year.
    Monday, May 01, 2017 11:43 AM
  • You would be better off using a DataSource. I don't work with DataGridView controls without a DataSource so I have nothing for you in this case.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, May 01, 2017 11:46 AM
    Moderator
  • I have a dataset. Won't that work for a data source?

    Monday, May 01, 2017 11:49 AM
  • I would suggest creating a class for the data operations. One method to obtain data for the ComboBox which the data source might be a DataTable, two fields (or a list from a class with two fields), the primary key and the text to display. The key would be set to the DataMember and the text to the DisplayMember. User presses the button, you cast the current ComboBox Selected item to a integer (for obtaining the key), use the key to get items by primary key with the key just obtained from the selected item in the ComboBox.

    If you don’t need to get items by primary key then read data from the database table into a List(Of String) and set the list as the data source for the ComboBox.

    In regards to sorting, this should be done when retrieving data from the database table otherwise sort via the DataView of the DataTable you retrieved backend data from. Sorting e.g. SELECT SomeField, SomeYear FROM Parts ORDER BY SomeYear.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, May 01, 2017 12:37 PM
    Moderator
  • I have a dataset. Won't that work for a data source?


    Yes it would work. So why don't you create four DataTables within that DataSet, each created from a different Access query. Then you can bind each DataTable from that DataSet to each DataGridView. It would be easier than filtering from one DataTable into four.

    Paul ~~~~ Microsoft MVP (Visual Basic)


    Monday, May 01, 2017 12:50 PM
  • Hi VBShaper,

    Based on your description, I'm not sure exactly what you want to do. I try to do a sample you can refer to.

    I create one table, you can see it below.

    The I put one Combobox and DataGridView control in Form, when you change combobox text, the datagridView will display related date.

    Imports System.Data.SqlClient
    
    Public Class Form6
        Private Sub Form6_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            loaddate()
        End Sub
        Private Sub loaddate()
            Dim dt As New DataTable()
            Dim strConn As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\C# and VB Support\Example(VB)\Demo\Database2.mdf;Integrated Security=True"
            Using conn As New SqlConnection(strConn)
                Dim selectSql = "select a.Id,a.ColumnName as Name, a.ColumnSex as Sex, a.ColumnAge as Age, year(a.ColumnDate) as Birthdate from test2 a"
                Using com As New SqlCommand(selectSql, conn)
                    Try
                        conn.Open()
                        Dim adapter As New SqlDataAdapter(com)
                        adapter.Fill(dt)
                        DataGridView1.DataSource = dt
                        conn.Close()
                    Catch ex As Exception
                        MessageBox.Show(ex.ToString())
                    End Try
                End Using
            End Using
        End Sub
    
        Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
            DataGridView1.DataSource = Nothing
            Dim dt As New DataTable()
            Dim strConn As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\C# and VB Support\Example(VB)\Demo\Database2.mdf;Integrated Security=True"
            Using conn As New SqlConnection(strConn)
                Dim selectSql = "select a.Id,a.ColumnName as Name, a.ColumnSex as Sex, a.ColumnAge as Age, year(a.ColumnDate) as Birthdate from test2 a where year(a.ColumnDate)='" + ComboBox1.Text + "'"
                Using com As New SqlCommand(selectSql, conn)
                    Try
                        conn.Open()
                        Dim adapter As New SqlDataAdapter(com)
                        adapter.Fill(dt)
                        DataGridView1.DataSource = dt
                        conn.Close()
                    Catch ex As Exception
                        MessageBox.Show(ex.ToString())
                    End Try
                End Using
            End Using
    
        End Sub
    End Class

    Hope it is helpful to you.

    Best Regards,

    Cherry Bu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 04, 2017 6:44 AM
    Moderator
  • Hi VBShaper,

    Based on your description, I'm not sure exactly what you want to do. I try to do a sample you can refer to.

    I create one table, you can see it below.

    The I put one Combobox and DataGridView control in Form, when you change combobox text, the datagridView will display related date.

    Imports System.Data.SqlClient
    
    Public Class Form6
        Private Sub Form6_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            loaddate()
        End Sub
        Private Sub loaddate()
            Dim dt As New DataTable()
            Dim strConn As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\C# and VB Support\Example(VB)\Demo\Database2.mdf;Integrated Security=True"
            Using conn As New SqlConnection(strConn)
                Dim selectSql = "select a.Id,a.ColumnName as Name, a.ColumnSex as Sex, a.ColumnAge as Age, year(a.ColumnDate) as Birthdate from test2 a"
                Using com As New SqlCommand(selectSql, conn)
                    Try
                        conn.Open()
                        Dim adapter As New SqlDataAdapter(com)
                        adapter.Fill(dt)
                        DataGridView1.DataSource = dt
                        conn.Close()
                    Catch ex As Exception
                        MessageBox.Show(ex.ToString())
                    End Try
                End Using
            End Using
        End Sub
    
        Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
            DataGridView1.DataSource = Nothing
            Dim dt As New DataTable()
            Dim strConn As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\C# and VB Support\Example(VB)\Demo\Database2.mdf;Integrated Security=True"
            Using conn As New SqlConnection(strConn)
                Dim selectSql = "select a.Id,a.ColumnName as Name, a.ColumnSex as Sex, a.ColumnAge as Age, year(a.ColumnDate) as Birthdate from test2 a where year(a.ColumnDate)='" + ComboBox1.Text + "'"
                Using com As New SqlCommand(selectSql, conn)
                    Try
                        conn.Open()
                        Dim adapter As New SqlDataAdapter(com)
                        adapter.Fill(dt)
                        DataGridView1.DataSource = dt
                        conn.Close()
                    Catch ex As Exception
                        MessageBox.Show(ex.ToString())
                    End Try
                End Using
            End Using
    
        End Sub
    End Class

    Hope it is helpful to you.

    Best Regards,

    Cherry Bu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    I appreciate all the answers, I tried to convert the Code given. I use an access DB, It populates the DGV, but I cannot get the Combo Box to fill.  I don't need the Year in the Combo Box. I need a column called PN.

        Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
            DataGridView1.DataSource = Nothing
            Dim dt As New DataTable()
            Dim connstring = ConfigurationManager.ConnectionStrings("DBTest.My.MySettings.Database3ConnectionString")
            Using conn As New OleDbConnection("connstring")
    
                Dim SelectOLEDB = "SELECT a.PN" + ComboBox1.Text + "'"
                Using com As New OleDbCommand("SelectOLEDB", conn)
                    Try
                        conn.Open()
                        Dim adapter As New OleDbDataAdapter(com)
                        adapter.Fill(dt)
                        DataGridView1.DataSource = dt
                        conn.Close()
                    Catch ex As Exception
                        MessageBox.Show(ex.ToString())
                    End Try
                End Using
            End Using
    
        End Sub
    End Class


    • Edited by VBShaper Thursday, May 04, 2017 9:35 PM
    Thursday, May 04, 2017 2:12 PM
  • The following is overkill but does show how to setup a DataGridViewComboBox.

    https://code.msdn.microsoft.com/DataGridView-ComboBox-with-b62fe359


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, May 05, 2017 12:00 AM
    Moderator