Display data from database into DataGridView

Question

I have two tables in my Access Database where I used to store the information of Item In and Item Out. I am displaying those data from both table in a DataGridView using a DataSet. Item In displayed in DataGridView1 and Item Out displayed in DataGridView2.

Here is my Module Function

Function to display Item In

Public Sub load_item_in()
    ds_i.Reset()
    Dim i_sql As String
    Dim conn_i As New OleDb.OleDbConnection("PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source = access-database-file-dir")
    i_sql = "SELECT * FROM item_in"
    da_i = New OleDb.OleDbDataAdapter(i_sql, conn_i)
    da_i.Fill(ds_i, "InventoryIn")
    Form6.DataGridView1.DataSource = ds_i.Tables("InventoryIn")
    Form6.DataGridView1.ReadOnly = True
End Sub

Function to display Item Out

Public Sub load_item_out()
    ds_i.Reset()
    Dim i_sql As String
    Dim conn_i As New OleDb.OleDbConnection("PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source = access-database-file-dir")
    i_sql = "SELECT * FROM item_out"
    da_i = New OleDb.OleDbDataAdapter(i_sql, conn_i)
    da_i.Fill(ds_i, "InventoryOut")
    Form6.DataGridView2.DataSource = ds_i.Tables("InventoryOut")
    Form6.DataGridView2.ReadOnly = True
End Sub

Calling out the function when button clicked

myModule.load_item_in()
myModule.load_item_out()

As you can see there, I am displaying two different DataGridView in a same form.

The problem I faced here is, I have table displayed in the DataGridView1 but no data displayed there. My DataGridView2 is able to display all data. So I am wondering either the system get confused of which data to called out because unable to display data at DataGridView1

Answer 1

Looks like you're using the same DataSet in both functions and resetting it before each call.

The ds_i.Reset() method in load_item_out() is clearing the data that the first method load_item_in() retrieved. Remove your DataSet "reset" and add a DataTable clear before filling the DataAdapter.

Answer 2

It has been awhile since I have worked in VB - though you have these in two separate sub routines I do not see where you are closing the connection to the DB. Each sub should have a connection start and a connection stop.

Answer 3

This is a datagridview example from a project I did in school - the main form had 4 dgv's and each had this code block with a different select statement. They all ran in the same sub but had their own try calls -

   Private Sub Sponsor_Load()
    '-----gridviewB----++++++
    'required try catch +++++
    Try
        'declare variables
        Dim strSelect As String = ""
        Dim cmdSelect As OleDb.OleDbCommand
        Dim drSourceTable As OleDb.OleDbDataReader
        Dim dt As DataTable = New DataTable

        'database opened
        If OpenDatabaseConnectionSQLServer() = False Then

            'database fail - alert user and exit program
            MessageBox.Show(Me, "Database connection error." & vbNewLine &
                                "The application will now close.",
                                Me.Text + " Error",
                                MessageBoxButtons.OK, MessageBoxIcon.Error)
            Me.Close()

        End If

        'select statement
        strSelect = "SELECT * FROM TSponsors WHERE intSponsorID= " & cboSponsor.SelectedValue.ToString

        'pull records from sourcetable
        cmdSelect = New OleDb.OleDbCommand(strSelect, m_conAdministrator)
        drSourceTable = cmdSelect.ExecuteReader

        'load data table
        dt.Load(drSourceTable)

        'populate the datagrid view
        dgvSponsor.DataSource = dt

        'close source table
        drSourceTable.Close()

        'close db connection
        CloseDatabaseConnection()

        'requried try catch - +++++
    Catch excError As Exception

        'display error message
        MessageBox.Show(excError.Message)

    End Try

End Sub