Solved

Populating DataGrid from table or query in MS Access

Posted on 2006-06-11
5
333 Views
Last Modified: 2008-02-01
Hello

I’m using a VB.Net front to an MS Access database.

I’d like to connect to the Access database, get all the data from a table or query in MS Access and display it in a DataGrid.

I have actually managed it, but it involves 2 connections to the database - which I’m pretty sure is not right.

Imports ADODB
Imports System.Data.OleDb

Dim Conn As Connection
Dim rs As ADODB.Recordset

Conn = New Connection
rs = New ADODB.Recordset

Dim connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=c:\Projects.mdb;" & _
                        "Persist Security Info=False")

        Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=c:\Projects.mdb;" & _
                                "Persist Security Info=False"

        Conn.Open()

Dim adapter As New OleDbDataAdapter("QueryName", connection)

rs.Open("QueryName ", Conn)

Dim customersTable As New DataTable("Customers")

adapter.Fill(customersTable, rs)

DataGrid1.DataSource = customersTable

Conn.Close()


Thanks.
Iain
0
Comment
Question by:IainTheVBALearner
  • 3
  • 2
5 Comments
 
LVL 34

Expert Comment

by:Sancler
ID: 16882312
Iain

You're right, it's not necessary to do everything twice.  The OleDb dataadapter and the datatable REPLACE the ADOBD recordset.  You should use the former in VB.NET.  Your code should work like this

'''Imports ADODB
Imports System.Data.OleDb

'''Dim Conn As Connection
'''Dim rs As ADODB.Recordset

'''Conn = New Connection
'''rs = New ADODB.Recordset

Dim connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=c:\Projects.mdb;" & _
                        "Persist Security Info=False")

'''        Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=c:\Projects.mdb;" & _
                                "Persist Security Info=False"

'''        Conn.Open()

Dim adapter As New OleDbDataAdapter("QueryName", connection)

'''rs.Open("QueryName ", Conn)

Dim customersTable As New DataTable("Customers")

adapter.Fill(customersTable) ''', rs)

DataGrid1.DataSource = customersTable

'''Conn.Close()

Roger

0
 
LVL 3

Author Comment

by:IainTheVBALearner
ID: 16887715
Hello Roger.  Thanks for the response.

If I put a simple SQL statement in "QueryName" then it works.  But If I put the name a query or table from the database it's connected it, then it doesn't work.  It fails on the adapter.Fill line with this error - no other details:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll

So this works:
Dim adapter As New OleDbDataAdapter("SELECT * FROM [tbl Apps]", connection)

But this doesn't:
Dim adapter As New OleDbDataAdapter("qryProjectsBasicByTitle", connection)

qryProjectsBasicByTitle is a valid SELECT query in the database, with no parameters.

Do you know how to make it fill the DataGrid from a query?

Thanks
Iain
0
 
LVL 34

Accepted Solution

by:
Sancler earned 125 total points
ID: 16888222
If you want to use a dataadapter then you will need to use a SELECT statement, although you can do so with "SELECT * FROM MyAccessQuery".

If you want to call an Access Query directly, you will need to use a DataReader.  On these lines

Dim connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=c:\Projects.mdb;" & _
                        "Persist Security Info=False")

Dim customersTable As New DataTable("Customers")
'code needed to structure datatable

Dim cmd As New OleDbCommand("MyAccessQuery")
cmd.Connection = connection
cmd.CommandType = CommandType.StoredProcedure
connection.Open
Dim dr As OleDbDataReader = cmd.ExecuteReader()
While dr.Read
   'code needed to write values from single record returned into row of datatable
End While
Connection.Close()

Sorry, in my initial response, I hadn't picked up the point about using an MS Access query directly.

As you can see, it is far simpler to use a dataadapter - selecting from a Query, rather than from a Table, if necessary - because (a) it will create the datatable structure for you and (b) will bring over all the necessary data without you needing to code putting it in the datatable.  But the other route (which is closer to the ADOBD recordset approach, perhaps) is available if you want to use it.

Roger
0
 
LVL 3

Author Comment

by:IainTheVBALearner
ID: 16889030
mmm....okay.  Thanks.  I guess that answers the question, although I'm not sure how to populate the DataTable by moving thought the DataReader...

I'm really struggling with this...I've done loads of MS Access - recordsets & multicolumn listboxes and they work great.

I'm teaching myself VB.NET but I'm struggling to see what's better...it seems that a DataGrid is an quivalent to a multicolumn listbox, as there is no multicolumn in VB.NET, but populating a DataGrid is such a palava - and I've not even begun editing DataGraid data and updating the Access tables based on the new DataGrid data...

I'm pretty new to this though, and I'm sure I'll look back on this and realise it's only because there were a bunch of things I didn't know...

Thanks.
Iain
0
 
LVL 34

Expert Comment

by:Sancler
ID: 16890019
Iain

It will get easier, but there'll be a lot of swearing at it first ;-)

Thanks for the points

Roger

0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VB.NET - Error - Object not set in Module 4 41
VB.Net WebRequest Code from JSON curl 7 53
VB.net and sql server 4 45
Crystal reports login after closing form 3 19
Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question