Using a DataReader to populate a dataset

[The_Grudge]

I'm having a bit of an issue with the following code. Basically, I have an oracle command that queries a database and returns records - that part works and is located above the snippet I've posted.

It loads the records into a datareader, and I can loop through the datareader - that works also.

Where I'm having a problem is - I want to loop through the datareader and load records into a datatable I've created (by dragging a Datatable object onto my form). I've then linked a datagridview on my form to that datatable.

The code below runs without error, but the datagridview doesn't populate with anything - wondering why?

The highlighted line below shows where I'm trying to see if there are any rows in my datatable. This always seems to be zero so the issue is somewhere in here I suspect.

Dim OracleCommand As New OracleCommand
        OracleCommand.Connection = con
        OracleCommand.CommandText = IP_SQL
        OracleCommand.CommandType = CommandType.Text

        Dim DataReader As OracleDataReader = OracleCommand.ExecuteReader()

        If DataReader.HasRows Then
            Do While DataReader.Read()
                Dim anyRow As DataRow = dtNRCWorking.NewRow
                anyRow("MRN") = Mid((DataReader.Item("CR_Number")), 3, 7)
                Dim intwow As Integer = dtNRCWorking.Rows.Count

            Loop

        End If

        DataGridView1.DataSource = dsNRCPicker
        DataGridView1.DataMember = "dtNRCWorking"
        DataGridView1.Refresh()
        DataReader.Close()

Now I'm sure some of you are wondering why I'm doing this in the first place. Well - in short, I need to query data, massage it, and create a tab delimited file with it.

The DataReader let's me query, the dataset lets me loop through and massage the records/fields that I need to massage, and the datagridview is just for testing purposes so I can have a quick look at the data I'm building.

[dunfiddlin]

If DataReader.HasRows Then
            Do While DataReader.Read()  
                Dim anyRow As DataRow = dtNRCWorking.NewRow ' you want an empty datarow?
 
                anyRow("MRN") = Mid((DataReader.Item("CR_Number")), 3, 7) ' now you have a datarow with only one value in the MRN column
' and now you completely ignore it by failing to add it to the table
                Dim intwow As Integer = dtNRCWorking.Rows.Count ' so this has to be 0
 
            Loop
 
        End If

[techgnome]

use the data reader to fill the data table... thusly:

datatable.fill(yourDataReader)

now do what you need to do with the datatable. You don't need to loop through the reader yourself.

-tg

[The_Grudge]

use the data reader to fill the data table... thusly:

datatable.fill(yourDataReader)

now do what you need to do with the datatable. You don't need to loop through the reader yourself.

-tg

My purpose for looping through the datareader was so that I could check the fields in each record and modify them as needed (e.g. date formats, length). Are you suggesting I loop through the datatable instead?

I tried your code - it says "fill is not a member of 'System.Data.DataTable".

At this point I feel like I should mention I'm relatively new to VB.NET

I appreciate your help.

[The_Grudge]

If DataReader.HasRows Then
            Do While DataReader.Read()  
                Dim anyRow As DataRow = dtNRCWorking.NewRow ' you want an empty datarow?
 
                anyRow("MRN") = Mid((DataReader.Item("CR_Number")), 3, 7) ' now you have a datarow with only one value in the MRN column
' and now you completely ignore it by failing to add it to the table
                Dim intwow As Integer = dtNRCWorking.Rows.Count ' so this has to be 0
 
            Loop
 
        End If

1) I thought I wanted an empty data row to put my new record into?
2) That's okay for testing purposes -- I'll add more cols later
3) Thanks. The dsNRCPicker.Tables(0).Rows.Add(anyRow) seemed to have slipped my mind.

[dunfiddlin]

I tried your code - it says "fill is not a member of 'System.Data.DataTable".

At this point I feel like I should mention I'm relatively new to VB.NET

No, it's not. Generally speaking we don't use DataReader so I think tg's got a bit confused.

[techgnome]

Only sort of... I think I forgot the adaptor bit... youradaptor.fill(yourDataTable, yourDataReader) ... I *KNOW* there is a way to fill the DT with a DR...

-tg

[dunfiddlin]

You were closer the first time ... dt.Load(dr)

[The_Grudge]

Thanks to you both. I have this working - almost.

Dim OracleCommand As New OracleCommand
        OracleCommand.Connection = con
        OracleCommand.CommandText = IP_SQL
        OracleCommand.CommandType = CommandType.Text
 
        Dim DataReader As OracleDataReader = OracleCommand.ExecuteReader()
 
       
        If DataReader.HasRows Then
            Do While DataReader.Read()
                Dim anyRow As DataRow = dtNRCWorking.NewRow
                anyRow("MRN") = Mid((DataReader.Item("CR_Number")), 3, 7)
                anyRow("VisitNum") = DataReader.Item("PCS_Visit")
                dsNRCPicker.Tables(0).Rows.Add(anyRow)
                ' Dim intwow As Integer = dtNRCWorking.Rows.Count
 
            Loop
 
        End If
 
        DataGridView1.DataSource = dsNRCPicker
        DataGridView1.DataMember = "dtNRCWorking"
        DataGridView1.Refresh()
        DataReader.Close()

It populates my datagridview, but only with the first MRN column, not the VisitNum column. I know dunfiddlin asked about my "Dim anyRow As DataRow" line of code. As I mentioned, I'm new to VB.NET, but what I thought this was doing was creating a new row within my datatable that the subsequent lines of code would populate.

Here's what I'm trying to do - this may make things more clear and someone may have a better solution.

I need to submit a data file to an agency on a monthly basis. They've provided me with the layout of the file (tab delimited). The data they've requested comes from a few different databases I have access to, and they've specified that the fields must be in certain formats.

The method of my madness was this....I'm going to run a query, return the data, and loop through it so that I may
a) validate the data
b) format the data

From here, I will fire the "massaged" data into a dataset that builds as more data sources are queried. I'm basically building the text file as I go and putting it into the datagridview so I can see my progress.

So, in the above program I've queried the data with a datareader and then I want to loop through the datareader so that I can look at each field/each record and throw the massaged values into my dataset.

[techgnome]

set a breakpoint on this line:
Dim anyRow As DataRow = dtNRCWorking.NewRow

and then step through the code one line at a time and check your values... if the other field isn't getting set, there's either nothing in the source, or there's a problem with the types and it's not converting properly... or.... ???

another possiblility is to write the query and data extraction in the SQL itself... then you'd only have to execute it and stuff the results into the datatable directly... that would be the way I'd do it...

As for the Dim anyRow...
Dim anyRow As DataRow = dtNRCWorking.NewRow

that's fine... but this
dsNRCPicker.Tables(0).Rows.Add(anyRow)

should be changed to this:
dtNRCWorking.Rows.Add(anyRow)

just for consistency sake. And to make sure you're going against the right table.

-tg

[kareninstructor]

Seems like you would load the reader data using DataTable Load method

Of course for you the data provider would change.

Public Sub demo()
    Using cn As New OleDb.OleDbConnection With {.ConnectionString = "Your connection string"}
        Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
            cmd.CommandText = "SELECT * FROM SomeTable"
            Dim dt As New DataTable
            cn.Open()
            dt.Load(cmd.ExecuteReader)
        End Using
    End Using
End Sub

Then cycle thru the rows as needed to inspect your data.

[The_Grudge]

I got it working. The data was in the datatable, but I needed to create the corresponding column in the DataGridView. Once I did that it had somewhere to put the data.

I would have thought that binding the datagridview to the dataset would be more automatic. As fields come and go the datagridview would grow and shrink accordingly.