asked on

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

Juan_Barrera

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

Éric Moreau

You should keep ADO Recordset RIP. In .Net, you really should use DataSets object.

amir_ef

ASKER

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.

Éric Moreau

are you sure that your query returns data? have you tried it directly on the database?

amir_ef

ASKER

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.

Éric Moreau

can you convert your code to a DataSet then I will help you debug it.

amir_ef

ASKER

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()

gisTimmy

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.

ASKER CERTIFIED SOLUTION

Éric Moreau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial