No Data in DataGrid...

[xerexie]

Hi. I want to retrieve data from access to datagrid using the code below, but it just doesn't work. I'm using visual studio 2010 and access 2007.

Private Sub Form2_Activated(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Activated

Dim con = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\rex\Desktop\vbsample\vbdatabase.mdb")
con.open()
Dim q = New OleDbCommand("select * from information", con)
Dim info = New OleDbDataAdapter()
info.SelectCommand = q
info.Fill(DataSet1, "information")
grid.DataSource = DataSet1
con.close()
End Sub


BTW, DataSet1 is the name of my dataset component. I assume that it is similar with this:
Dim DataSet1 As Dataset = new Dataset

 

[jmcilhinney]

If you are going to assign the DataSet to the grid's DataSource then you need to assign the name of the DataTable to the DataMember. Alternatively, you can just assign the DataTable itself to the DataSource.

 

[xerexie]

How do I do that? I know it is stupid of me to ask this but I am not familiar with vb.net. I'm more knowledgeable about Delphi and I'm just trying out vb for experience. Hope you could help me.

-Xer

 

[rekhtar]

it would be like this:

grid.datasource = dataset1.tables("TABLE NAME") and also be sure that htere is no space between the d and b in the extension of DB name :

Data Source=C:\Users\rex\Desktop\vbsample\vbdatabase.md b

 

[xerexie]

This is weird. It still doesn't work. I tried putting these codes to check if my query returns a value, and it does:

Dim read As OleDbDataReader = q.ExecuteReader
While read.Read
MsgBox(read("password"))
End While

Here's the complete code:

Private Sub Form2_Activated(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Activated

Dim con = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Users\rex\Desktop\vbsample\vbdatabase.mdb")
con.open()
Dim q = New OleDbCommand("select * from information", con)

Dim read As OleDbDataReader = q.ExecuteReader
While read.Read
MsgBox(read("password"))
End While

Dim info = New OleDbDataAdapter()
info.SelectCommand = q
info.Fill(DataSet1, "information")
grid.DataSource = DataSet1.Tables("information")
con.close()
End Sub

 

[jmcilhinney]

The first issue is the fact that you're executing that code on the Activated event. That means that you will hit the database every time that form regains focus.

As for the issue, you are possibly actually causing the issue by using the DataReader. There's nothing in there that closes the DataReader, so the command can't be executed again because the connection is busy. Get rid of the DataReader. To check whether data is returned, test the value returned by Fill. A non-zero value means records were retrieved.

 

[xerexie]

It finally works. Thanks guys!