DataSet empty when populating from a stored procedure in Access

[WestAussie]

Hello

I'm using Visual Studio 2005 and Microsoft Access 2007 (database is saved in 2007 format).

The code which I have been using to display data in a DataGridView from a stored procedure in Microsoft Access comes from 'Beginning Visual Basic 2005 Databases' (Wrox publication).

From what I have found my code works but no records are displayed in the DataGridView. The column headings are visible but the acutal data is not. Running the query in Access displays two rows of data.

Any ideas as to why this is happening?

[balabaster]

Not without your code mate

[WestAussie]

Sorry.

objConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0 ;Data Source=C:\MyDatabase.accdb;Persist Security Info=False;")


'Initialize a new instance of the OleDbCommand class.
objCommand = New OleDbCommand

'Set command object properties.
objCommand.CommandText = "qryMyAccessQuery"
objCommand.CommandType = CommandType.StoredProcedure
objCommand.Connection = objConnection

'Initialize a new instance of the Adapter class.
objDataAdapter = New OleDbDataAdapter

'Initialize a new instance of the DataSet class.
objDataSet = New DataSet

'Set the SelectCommand for the DataAdapter.
objDataAdapter.SelectCommand = objCommand

'Populate the DataSet.
objDataAdapter.Fill(objDataSet, "DataSet")

Me.DataGridView.DataSource = objDataSet
Me.DataGridView.DataMember = "DataSet"

[balabaster]

Bah, I don't have DataGridView in 2.0...does it have a DataBind method? If it does add that after the last line of the code you posted and you should be there...

[WestAussie]

No, there is no DataBind method.
Binding the data is done through the following based on what I've found so far.

DataMember - Gets or sets the name of the list or table in the data source for which the DataGridView is displaying data.
DataSource - Gets or sets the data source that the DataGridView is displaying data for.

[WestAussie]

Since the column headings were shown I decided to test the procedure by substituting the stored procedure for a table.

When I use a DataTable instead of a DataSet the correct information is obtained from the Access database and displays fine.

The code I used is:

objConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0 ;Data Source=C:\MyDatabase.accdb;Persist Security Info=False;")

'Initialize a new instance of the OleDbCommand class.
objCommand = New OleDbCommand

'Set command object properties.
objCommand.CommandText = "tblMyAccessTable"
objCommand.CommandType = CommandType.TableDirect
objCommand.Connection = objConnection

'Initialize a new instance of the Adapter class.
objDataAdapter = New OleDbDataAdapter

'Initialize a new instance of the DataTable class.
dt = New DataTable

'Set the SelectCommand for the DataAdapter.
objDataAdapter.SelectCommand = objCommand

'Populate the DataTable.
objDataAdapter.Fill(dt)

Me.DataGridView.DataSource = dt

I must be missing a step in populating the data into a DataSet within my previous code. Any ideas as to what it might be?

[balabaster]

That would make sense I suppose what with a dataset being potentially multiple tables, relationships the works and a DataGridView really is only used for single tables...