Displaying data from a recordset in a datagrid

Hi all,

I'm trying to display data queried from an Oracle database in a VB 2008 datagrid.

No matter what I do, the grid remains blank.

As you can see in my code sample below, I'm using the connection from the login form (looked wasteful to create a new connection per form).

The connection works well on the login form. The query returns results when run in sql.

Any ideas?

Thanks,

Amir
Public Class main
    Public mainRecord As New ADODB.Recordset
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim displayQuery As String
        displayQuery = "SELECT STUDENT.FIRSTNAME, STUDENT.LASTNAME, STUDENT.STUDENTID, RENTS.EQUIPMENTBARCODE, to_char(RENTS.CHECKOUTTIME) FROM STUDENT INNER JOIN RENTS ON RENTS.STUDENTID = STUDENT.STUDENTID INNER JOIN EQUIPMENT ON RENTS.EQUIPMENTBARCODE = EQUIPMENT.BARCODE WHERE RENTS.CHECKINTIME IS NULL"
        mainRecord.Open(displayQuery, LoginForm1.userConn)
        If (Not mainRecord.EOF) Then
            mainDG.DataSource = mainRecord
        End If
    End Sub
 
End Class

Open in new window

amir_efAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Juan_BarreraCommented:
Hi, try this:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim displayQuery As String
        displayQuery = "SELECT STUDENT.FIRSTNAME, STUDENT.LASTNAME, STUDENT.STUDENTID, RENTS.EQUIPMENTBARCODE, to_char(RENTS.CHECKOUTTIME) FROM STUDENT INNER JOIN RENTS ON RENTS.STUDENTID = STUDENT.STUDENTID INNER JOIN EQUIPMENT ON RENTS.EQUIPMENTBARCODE = EQUIPMENT.BARCODE WHERE RENTS.CHECKINTIME IS NULL"
        mainRecord.Open(displayQuery, LoginForm1.userConn)
        If (Not mainRecord.EOF) Then
            mainDG.DataSource = mainRecord
        End If
        mainDG.DataBind()
    End Sub

Open in new window

0
Éric MoreauSenior .Net ConsultantCommented:
You should keep ADO Recordset RIP. In .Net, you really should use DataSets object.
0
amir_efAuthor Commented:
Juan: mainDG is a datagrid, it does not have a databind method.

Emoreau: If I want to use a dataset, how would you query the DB then display it with a datagrid? I tried a dataset as well and it did not work.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Éric MoreauSenior .Net ConsultantCommented:
are you sure that your query returns data? have you tried it directly on the database?
0
amir_efAuthor Commented:
Yes, I've ran it directly on the DB as well as just trying to query a full table (select * from student).

Also, if I debug the application I can see that there's data in the recordset (I look at the first record), just the datagrid remains blank.
0
Éric MoreauSenior .Net ConsultantCommented:
can you convert your code to a DataSet then I will help you debug it.
0
amir_efAuthor Commented:
Ok, tried to switch it to a dataset, still getting a blank DG:
       
Dim connectionString As OracleClient.OracleConnectionStringBuilder = New OracleClient.OracleConnectionStringBuilder("data source = 127.0.0.1;Password = capstone;User id= system;")
       
Dim mainConn As OracleClient.OracleConnection = New OracleClient.OracleConnection(connectionString.ToString)
       
Dim selectCMD As OracleClient.OracleCommand = New OracleClient.OracleCommand("select * from student", mainConn)

        Dim custDS As DataSet = New DataSet
        Dim custDA As OracleClient.OracleDataAdapter = New OracleClient.OracleDataAdapter
        selectCMD.CommandTimeout = 30
        custDA.SelectCommand = selectCMD
        mainConn.Open()
        custDA.Fill(custDS, "Student")
        mainDG.DataSource = custDS
        mainDG.Update()



0
gisTimmyCommented:
Try explicitly setting the OracleCommand's command type to CommandType.Text.

Also, after setting the grid's DataSource, do you set the DataMember property to the table name Student?

I've seen a datagrid not show data until I click on a column header.

0
Éric MoreauSenior .Net ConsultantCommented:
You don't need this line for sure:
       mainDG.Update()


Change this line:
mainDG.DataSource = custDS

for
mainDG.DataSource = custDS.tables("Student")
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.