Display DAO.RecordSet in DataGridView

[aytemp]

Hi,

I am trying to display a DAO.RecordSet in my Datagridview but it is not working. The code compiles and runs but the data does not show in the datagridview. This is what I have tried:

DataGridView4.DataSource = rs
'rs is the recordSet

I would appreciate it if you can give ideas on how I can display this. Using Adodb or oledb is not an option. This is more code:

' Goes before the class
Imports DAO

' class member variables
Public dbE As DAO.DBEngine
Public db As DAO.Database
Public sql As String
Public rs As DAO.Recordset
Public accApp As Object
Public dbName As String

' This following code goes inside a sub (function)
'Initialize database
dbE = New DAO.DBEngine()
Dim a As String, b As Integer, c As String
a = Application.StartupPath
b = InStr(a, "\bin\Debug")
c = a.Substring(0, b)
dbName = c & "utility.mdb"
db = dbE.OpenDatabase(dbName)
accApp = GetObject(dbName)
accApp.docmd.setwarnings(False)

'Query and display
Dim demand() As Single = Nothing
sql = "Select patientName, patientAddress from patientsList where doctor = 'jones'"
rs = db.OpenRecordset(sql)
' Do something with the recordset
DataGridView4.DataSource = rs
rs.Close()

'disconnect from database
db.Close()
dbE = Nothing
accApp = Nothing

[kareninstructor]

Best to use
OleDbConnection -> OleDbCommand then load data into a data table.

Example

Dim cn As New OleDbConnection("your connection string to MDB")
Dim cmd As OleDbCommand = New OleDbCommand
cmd.CommandText = "Your SQL Statement"
Dim dt As New DataTable
cn.Open()
dt.Load(cmd.ExecuteReader())
YourDataGridView.DataSource = dt

If your MDB is in the debug folder there is no need to give the path to it.

[Shaggy Hiker]

Why is ADO.NET not an option? DAO is mighty old technology.

However, at the very worst, you could create a datatable, then use the recordset to populate the datatable, then set the datatable as the datasource for the DGV. A datatables isn't tied to any one technology (it isn't part of OleDB), so it will work. Kind of a long way around, as you would be looping through the recordset creating a datatable from it, but it would work.

It has been years (like about 10 of them) since I used DAO, but the recordset may populate on demand, so it wouldn't be suitable to binding to a DGV, as there wouldn't actually be any data other than the current row...or perhaps I am thinking of a different construct in DAO.

[aytemp]

well ado.net and oledb.net is not an option because this is for work and I am working on code that was written by someone else. If I change all instances of DAO usage, it will take forever. I already know how to use OleDB to query and display results in datagridview. However, in existing code, the application connects to the database using DAO when form is loading, queries it many times while running, and then closes it when the Gui is closed. The problem is that if I insert the OleDB code to display the result while the same database is already open using the DAO, one of them does not close properly and when I exit the application, an instance of MSAccess exists in system processes. I can't kill the MSAccess.exe process because that might kill other versions of the program that are running. Do you have any other solutions to how I can use both DAO and OleDb and have both databases closed on form closing.

Here is my code for OLEDb as well:

Dim ConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db1.mdb"
Dim SQLString As String = "SELECT * FROM Table1"
Dim OleDBConn1 As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection(ConnString)
Dim DataSet1 As New DataSet()
Dim OleDbDataAdapter1 As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter(SQLString, OleDBConn1)
OleDBConn1.Open()
OleDbDataAdapter1.Fill(DataSet1, "Table1")
DataGridView3.DataSource = DataSet1.Tables("Table1")
OleDBConn1.Close()

[Shaggy Hiker]

Ah, I understand now why you want to go with DAO. You may not be able to directly bind the recordset to a DGV, but you could do what I suggested and use it to populate a datatable that you then bind to the DGV. I'd understand if you didn't want to do that, though, as I wouldn't either. What a miserable approach.

OleDB won't be opening an instance of Access, so it must be DAO that is doing it. I'm surprised that the two conflict, but I think they will both be using the JET DB Engine, so that may have something to do with it. In any case, it sounds like you have accurately determined what the problem is, so you may now be left with nothing but bad choices.

[kareninstructor]

Take a look at the demo attached which opens a database using DAO, loops thru the recordset and while doing so populates a DataTable which in turn becomes the source for a DataGridView.

The original project came from here http://www.planet-source-code.com/vb...5112&lngWId=10 but was not very user friendly at all and was compiled with option strict off. The attached has option strict on. First run the Write project then the Read project. What you are interested in is the code in FormLoad of the Read project.

[aytemp]

Alright thanks for the code Kevin! I will take a look