Question Populate DataGridView with Access database programmatically

[MidnightYell2003]

Howdy,

I am new to these forums and sort of new to VB.NET programming. I made a form in .NET and added a DataGridView object to it. I want to programmatically populate this DataGridView with a particular table from a particular Access database. I used an ADODB connection object to open my database and an ADODB recordset to get my table. Is there any simple way of doing this? Or any sample code out there?

Thanks!

 

[daniel02_0403]

just try these codes ...

Dim conn As New OleDb.OleDbConnection
        Dim cmd As New OleDb.OleDbCommand
        Dim da As New OleDb.OleDbDataAdapter
        Dim ds As New DataSet

        conn.ConnectionString = "<your connection string>"
        cmd.Connection = conn
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "<your sql statement here>"
        da.SelectCommand = cmd
        ds.Tables.Add("sample")
        ds.Tables("sample").Clear()
        conn.Open()
        da.SelectCommand.ExecuteNonQuery()
        da.Fill(ds.Tables("sample"))
        conn.Close()

        Me.DataGridView1.DataSource = ds.Tables("sample")

 

[jmcilhinney]

just try these codes ...

Dim conn As New OleDb.OleDbConnection
        Dim cmd As New OleDb.OleDbCommand
        Dim da As New OleDb.OleDbDataAdapter
        Dim ds As New DataSet

        conn.ConnectionString = "<your connection string>"
        cmd.Connection = conn
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "<your sql statement here>"
        da.SelectCommand = cmd
        ds.Tables.Add("sample")
        ds.Tables("sample").Clear()
        conn.Open()
        da.SelectCommand.ExecuteNonQuery()
        da.Fill(ds.Tables("sample"))
        conn.Close()

        Me.DataGridView1.DataSource = ds.Tables("sample")

In principle that's correct but there's redundancy in there that could be cleaned up. Why clear a Datatable that you just created and, therefore, must be empty? What's the ExecuteNonQuery call for? What's the DataSet for if all you need is one DataTable?

Using connection As New OleDbConnection("connections string here")
    Using adapter As New OleDbDataAdapter("SELECT * FROM MyTable", conection)
        Dim table As New DataTable

        adapter.Fill(table)
        myDataGridView.DataSource = table
    End Using
End Using

There are various other ways you could, or would, do it depending on whether you need to save the data after editing but that basically it.

 

[cjard]

Howdy,

I am new to these forums and sort of new to VB.NET programming. I made a form in .NET and added a DataGridView object to it. I want to programmatically populate this DataGridView with a particular table from a particular Access database. I used an ADODB connection object to open my database and an ADODB recordset to get my table. Is there any simple way of doing this? Or any sample code out there?

Thanks!

Please read the DW2 link in my signature, section "Creating a Simple Data App". Avoid following daniel02_0403's advice if possible; code like that is a seriously bad habit from a modern, Object Oriented point of view

 

[MidnightYell2003]

thanks for the replies. Daniel, that code got me started and worked well, but I was curious about why it looked a little redundant and why all the steps were needed.

jmc, that code works great and is short and concise. I have no experience with "Using" statements but this seems to work well. I do have one more question about that. Some of my tables take a long time to load into my DataGridView. Is there a way to show a progress bar with this?

 

[jmcilhinney]

I do have one more question about that. Some of my tables take a long time to load into my DataGridView. Is there a way to show a progress bar with this?

Yes and no. You can display a ProgressBar but it won't actually represent any real progress. You get no feedback from ADO.NET so you can't give any feedback to the user. You'd just have to use a Marquee ProgressBar, to just indicate that something was happening. You's also have to load the data in a background thread.

Unless the wait is very long, I'd suggest just changing the cursor to the WaitCursor.

 

[MidnightYell2003]

thanks jmcilhinney, the waitcursor is a great idea!