get data from query?

  • Thread starter Thread starter Graeme
  • Start date Start date
G

Graeme

Hi

How do I get data from an MS Access QUERY rather than a table? Below works
OK for a table ...
Dim myFeedersTable As DataTable = myFeedersDS.Tables("Network_Data_Feeder")

Tried a few things, but no go.

Thanks
Graeme
 
Hi,

You should configure an OleDbCommand, specifying the name of the query as
the CommandText and setting the CommandType to StoredProcedure. Then, set
this command as the SelectCommand for an OleDbDataAdapter and use it's Fill
method to populate the table in the dataset.

The above obviously assumes you have a configured OleDbConnection to the MS
Access database.
 
Thanks Dmitriy. This is what I did ...

Private Sub FillGrid_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles FillGrid.Click

Dim myFeedersDA As OleDbDataAdapter = New OleDbDataAdapter()

Dim myFeedersDS As DataSet = New DataSet()

Dim myConnectString As OleDbConnection = New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
tbxDataPath.Text)

Dim myFeedersSQL As OleDbCommand = New OleDbCommand("tempgnm",
myConnectString)

myFeedersSQL.CommandType = CommandType.StoredProcedure

myFeedersSQL.CommandTimeout = 30

myFeedersDA.SelectCommand = myFeedersSQL

myConnectString.Open()

myFeedersDA.Fill(myFeedersDS, "tempgnm")

myConnectString.Close()

DataGrid1.DataSource = myFeedersDS

End Sub

This displays headers in grid, but no data - Only one row displays as (null)
and my other records don't display at all.

Can you or anyone else help further?
Thanks again
Graeme

Dmitriy Lapshin said:
Hi,

You should configure an OleDbCommand, specifying the name of the query as
the CommandText and setting the CommandType to StoredProcedure. Then, set
this command as the SelectCommand for an OleDbDataAdapter and use it's Fill
method to populate the table in the dataset.

The above obviously assumes you have a configured OleDbConnection to the MS
Access database.

--
Dmitriy Lapshin [C# / .NET MVP]
X-Unity Test Studio
http://www.x-unity.net/teststudio.aspx
Bring the power of unit testing to VS .NET IDE

Graeme said:
Hi

How do I get data from an MS Access QUERY rather than a table? Below works
OK for a table ...
Dim myFeedersTable As DataTable = myFeedersDS.Tables("Network_Data_Feeder")

Tried a few things, but no go.

Thanks
Graeme
Click to expand...
Click to expand...
 
Graeme,
DataGrid1.DataSource = myFeedersDS
Click to expand...

Since you bind the grid to a dataset, you have to specify the DataMember
property value as well. You can do so by using the SetDataBinding method:

DataGrid1.SetDataBinding(myFeedersDS, "tempgnm")

P.S.: you don't need to open and close the connection manually when you use
the DataAdapter. It manages the connection state automatically behind the
scenes.

--
Dmitriy Lapshin [C# / .NET MVP]
X-Unity Test Studio
http://www.x-unity.net/teststudio.aspx
Bring the power of unit testing to VS .NET IDE

Graeme said:
Thanks Dmitriy. This is what I did ...

Private Sub FillGrid_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles FillGrid.Click

Dim myFeedersDA As OleDbDataAdapter = New OleDbDataAdapter()

Dim myFeedersDS As DataSet = New DataSet()

Dim myConnectString As OleDbConnection = New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
tbxDataPath.Text)

Dim myFeedersSQL As OleDbCommand = New OleDbCommand("tempgnm",
myConnectString)

myFeedersSQL.CommandType = CommandType.StoredProcedure

myFeedersSQL.CommandTimeout = 30

myFeedersDA.SelectCommand = myFeedersSQL

myConnectString.Open()

myFeedersDA.Fill(myFeedersDS, "tempgnm")

myConnectString.Close()

DataGrid1.DataSource = myFeedersDS

End Sub

This displays headers in grid, but no data - Only one row displays as (null)
and my other records don't display at all.

Can you or anyone else help further?
Thanks again
Graeme

Dmitriy Lapshin said:
Hi,

You should configure an OleDbCommand, specifying the name of the query as
the CommandText and setting the CommandType to StoredProcedure. Then, set
this command as the SelectCommand for an OleDbDataAdapter and use it's Fill
method to populate the table in the dataset.

The above obviously assumes you have a configured OleDbConnection to the MS
Access database.

--
Dmitriy Lapshin [C# / .NET MVP]
X-Unity Test Studio
http://www.x-unity.net/teststudio.aspx
Bring the power of unit testing to VS .NET IDE

Graeme said:
Hi

How do I get data from an MS Access QUERY rather than a table? Below works
OK for a table ...
Dim myFeedersTable As DataTable = myFeedersDS.Tables("Network_Data_Feeder")

Tried a few things, but no go.

Thanks
Graeme
Click to expand...
Click to expand...
Click to expand...
 
Thanks Dmitriy. I'll get on with it tomorrow!
Dmitriy Lapshin said:
Graeme,
DataGrid1.DataSource = myFeedersDS
Click to expand...

Since you bind the grid to a dataset, you have to specify the DataMember
property value as well. You can do so by using the SetDataBinding method:

DataGrid1.SetDataBinding(myFeedersDS, "tempgnm")

P.S.: you don't need to open and close the connection manually when you use
the DataAdapter. It manages the connection state automatically behind the
scenes.

--
Dmitriy Lapshin [C# / .NET MVP]
X-Unity Test Studio
http://www.x-unity.net/teststudio.aspx
Bring the power of unit testing to VS .NET IDE

Graeme said:
Thanks Dmitriy. This is what I did ...

Private Sub FillGrid_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles FillGrid.Click

Dim myFeedersDA As OleDbDataAdapter = New OleDbDataAdapter()

Dim myFeedersDS As DataSet = New DataSet()

Dim myConnectString As OleDbConnection = New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
tbxDataPath.Text)

Dim myFeedersSQL As OleDbCommand = New OleDbCommand("tempgnm",
myConnectString)

myFeedersSQL.CommandType = CommandType.StoredProcedure

myFeedersSQL.CommandTimeout = 30

myFeedersDA.SelectCommand = myFeedersSQL

myConnectString.Open()

myFeedersDA.Fill(myFeedersDS, "tempgnm")

myConnectString.Close()

DataGrid1.DataSource = myFeedersDS

End Sub

This displays headers in grid, but no data - Only one row displays as (null)
and my other records don't display at all.

Can you or anyone else help further?
Thanks again
Graeme

in message news:%[email protected]...
Hi,

You should configure an OleDbCommand, specifying the name of the query as
the CommandText and setting the CommandType to StoredProcedure. Then, set
this command as the SelectCommand for an OleDbDataAdapter and use it's Fill
method to populate the table in the dataset.

The above obviously assumes you have a configured OleDbConnection to
Click to expand...
the
MS
Access database.

--
Dmitriy Lapshin [C# / .NET MVP]
X-Unity Test Studio
http://www.x-unity.net/teststudio.aspx
Bring the power of unit testing to VS .NET IDE

Hi

How do I get data from an MS Access QUERY rather than a table? Below works
OK for a table ...
Dim myFeedersTable As DataTable =
myFeedersDS.Tables("Network_Data_Feeder")

Tried a few things, but no go.

Thanks
Graeme
Click to expand...
Click to expand...
Click to expand...
 
Back
Top