Loading datagridview in VB.NET programmatically using dataset

3
votes

I've created a datagridview, dataGridReport in the Designer view of VB 2010. I'm then using a query to fill a dataset, and I want this dataset to populate the datagridview... but it doesn't seem to work.

This is my code:

Dim con As New OleDb.OleDbConnection
con.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;
                         Data Source=C:\Users\Administrator\Documents\MenuDB.accdb"
con.Open()
Dim ds As DataSet = New DataSet
Dim adapter As New OleDb.OleDbDataAdapter
Dim sql As String

sql = @"SELECT OrderDate, MenuItem 
        FROM   MenuItems, Orders 
        WHERE  Orders.itemID = MenuItems.ID 
           AND Format(Orders.OrderDate,'mm/dd/yyyy') >= #" + fromDate + "# 
           AND Format(Orders.OrderDate,'mm/dd/yyyy') <= #" + toDate + "#"

adapter.SelectCommand = New OleDb.OleDbCommand(sql, con)
adapter.Fill(ds)
dataGridReport.DataSource = ds

I know something's missing now - I've looked around and most people seem to use something like dataGridReport.DataSource = ds.Tables('somereference')

But mine is a dynamically created dataset from a query joining two tables, it's not something stored among the project Data Sources. I also haven't bound the datagridview to any datasource via its properties. What am I missing?

(By the way, the sql query is correct, I've tested it and returns expected results).

3
vb.netdatagridviewdatasetpopulate

3 Answers

12
votes

You can get the table from the data source via the index:

dataGridReport.DataSource = ds.Tables(0)
1
votes
dataGridReport.DataSource = ds.Tables("Tablename")
0
votes
Dim con As New OleDb.OleDbConnection
con.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;
DataSource=C:\Users\Administrator\Documents\MenuDB.accdb"
con.Open()
Dim ds As DataSet = New DataSet
Dim adapter As New OleDb.OleDbDataAdapter
Dim sql As String

sql = @"SELECT OrderDate, MenuItem 
      FROM   MenuItems, Orders 
      WHERE  Orders.itemID = MenuItems.ID 
      AND Format(Orders.OrderDate,'mm/dd/yyyy') >= #" + fromDate + "# 
      AND Format(Orders.OrderDate,'mm/dd/yyyy') <= #" + toDate + "#"

adapter.SelectCommand = New OleDb.OleDbCommand(sql, con)
adapter.Fill(ds,sql)
dataGridReport.DataSource = ds.tables(sql)