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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Éric MoreauConnect With a Mentor Senior .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
 
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
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
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
 
É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
All Courses

From novice to tech pro — start learning today.