Writing a Access Query in Visual Studio

[jtodd]

Hey guys! I have limited experience with Visual Studio and have a question about Querying an Access database. I have created a Visual Studio application that displays and modifies an access database. I have use datagridview and detailed view to insert data and view data in the Access database. I am now trying to figure out the best way to query the data in the database. I have tried right clicking a table in the database explorer and creating a new query. Everything works and I can view the data but I am unsure how to display this query result in datagridview. So my question is how do I view this result in datagrid view? Thank you all in advance.

[catherine sea]

Here is a C# sample for your reference:

using System.Data.OleDb;

OleDbConnection conn = new OleDbConnection(@"Provider = Microsoft.Jet.OLEDB.4.0;User Id=;Password=;Data Source=" + fileName);
conn.Open();
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query_txt.Text, conn);
DataSet ds = new DataSet();
dataAdapter.Fill(ds);
dataGridView.DataSource = ds.tables[0];
conn.Close();

[jtodd]

Thank you for you reply Catherine! Do you have an example in VB?

[Phasma]

Here's the code in VB.

Dim ds As New DataSet
Dim AccessConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=access.mdb;")
Dim sql As String = "Your_Query"
Dim AccessCommand As New OleDbCommand(sql, AccessConn)
Dim da As New OleDbDataAdapter(AccessCommand)

da.Fill(ds)

DataGridview1.DataSource = ds.Tables(0)

[jtodd]

Thank Plasma! I tried the code below and when I try to run it the datagrid is still blank. Any thoughts? Am I doing something wrong? Thanks again for your help!

Imports System.Data.OleDb

Public Class Form1

    Private Sub DataGridView1_Click(sender As Object, e As System.EventArgs) Handles DataGridView1.Click

        Dim ds As New DataSet
        Dim AccessConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Todd\My Documents\ProjectsDatabase.accdb;")
        Dim sql As String = "Select [Project Number], [Company Name], Description FROM Projects"
        Dim AccessCommand As New OleDbCommand(sql, AccessConn)
        Dim da As New OleDbDataAdapter(AccessCommand)

        da.Fill(ds)

        DataGridView1.DataSource = ds.Tables(0)


    End Sub

[jtodd]

Also, is there a better way to query Access data in Visual Studio? I am not entirely sure I am going about this the best way. Thanks!

[adam_k]

Thank Plasma! I tried the code below and when I try to run it the datagrid is still blank. Any thoughts? Am I doing something wrong? Thanks again for your help!

Imports System.Data.OleDb

Public Class Form1

    Private Sub DataGridView1_Click(sender As Object, e As System.EventArgs) Handles DataGridView1.Click

        Dim ds As New DataSet
        Dim AccessConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Todd\My Documents\ProjectsDatabase.accdb;")
        Dim sql As String = "Select [Project Number], [Company Name], Description FROM Projects"
        Dim AccessCommand As New OleDbCommand(sql, AccessConn)
        Dim da As New OleDbDataAdapter(AccessCommand)

        da.Fill(ds)

        DataGridView1.DataSource = ds.Tables(0)


    End Sub

Did you click in the datagrid?
By best way, do you mean an easier / drag&drop one?

[jtodd]

Yes I did click in the datagrid and no I don't mean drag and drop. Thanks for your input on this thread.

[Phasma]

jTodd you need to use the

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\database1.accdb;Persist Security Info=False;"

or if passworded

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\database1.accdb;Jet OLEDB:Database Password=MyPassword;"

for your connection string since yu are using an Access 2007/2010 database. I am suprised you didn't get an error when you ran it. You should wrap it in a try/catch block.
Anyway give that a try.....

[jtodd]

Awesome! That got it working. Thanks for your help Phasma!!!