SQL Datareader Skips First Row

[jmmille]

Hey guys. I have a program that does a SQL query and parses the info into a text file. The problem is it misses the first row every time it loops. I know the root cause here is my double use of the read function. I'm hoping to gain some help in how I should use these two loops without the double read.

Here's the code:

For i As Integer = 0 To arrDept.Count - 1
        vDeptPO = vPO & "-" & arrDept(i)
        For k As Integer = 0 To arrStation.Count - 1
            vStationDeptPO = vDeptPO & "-" & arrStation(k)

            qImportDet = New SqlCommand("SELECT Department, Station, VendorItem, . . .", DBconn)
            dr = qImportDet.ExecuteReader()
            Dim fnextresult As Boolean = True

            'Write Data
            Do Until Not fnextresult
                While dr.Read()
                    'Write Header
                    If dr.GetValue(1) = "01" Then
                        writer.WriteLine("HDR~" & vStationDeptPO & "~" & "cust3" . . .))
                    Else
                        If dr.GetValue(1) = "02" Then
                            writer.WriteLine("HDR~" & vStationDeptPO & "~" & "cust4" . . .))
                        Else
                            If dr.GetValue(1) = "07" Then
                                writer.WriteLine("HDR~" & vStationDeptPO & "~" & "cust5" . . .))
                            Else
                                writer.WriteLine("HDR~" & vStationDeptPO & "~" & "cust2" . . .))
                            End If
                        End If
                    End If
                     While dr.Read()
                        'Write Detail Line (loop)
                        writer.WriteLine("DET~" & dr.GetValue(0) & "~" & dr.GetValue(1) . . .)
                    End While
                    writer.WriteLine("")
                End While
                fnextresult = dr.NextResult()
            Loop
            dr.Close()
        Next
Next

Thanks guys!

[jmmille]

And by the way. This should output a header line followed by several detail lines. Currently, the code leaves off the first of the detail lines. The other lines export fine. It is just the first one that does not.

Thanks

[sknake]

Why not have a boolean "headerWritten" and set it to false. Do the dr.Read(). If headerWritten is false then write the header and set it to true. Then continue writing the detail

[jmmille]

Why not have a boolean "headerWritten" and set it to false. Do the dr.Read(). If headerWritten is false then write the header and set it to true. Then continue writing the detail

I don't think I understand. I added the boolean and set it to false. Do I change the while statements to "while headerwritten = false" for the header and true for the detail? I tried this and the data reader never actually starts. So, before the first while loop, I added "dr.read()" which didn't help.

Could you please give me an example of where you mean to add the boolean so that it will get changed correctly and the loops will write the correct lines?

Thanks,
JM

[jmmille]

Bump -- Hoping someone will be able to give me some clues today.

[sknake]

Are you returning multiple tables in a single query? Since you're calling .NextResult() that would indicate yes. But the sample query you gives indicates no.

Please clairfy...

[jmmille]

No. All of the data comes from one table. I was under the impression that the use of .NextResult() prevented this skipping of the first row of data when I added it in there.

[sknake]

No it advances the reader to the next table in the result. Such as if your query was

Select * From Table1
Select * From Table2

[jmmille]

Ahh. Thanks for pointing that out. Is there some other method of looping it all together that I should be using?

Currently, I use "While dr.Read()". Is there another loop that does not advance the reader that should be used in this place?

[sknake]

You could create a new DataTable and call .Load(dataReader) and work with it as a table so you have index values of the row. This could be a problem if the data is *huge* and you need to process rows individually like you are.

It looks like you're wanting to repeat the headers on certain conditions or change as you traverse the data and I don't understand what you're really trying to do.

[jmmille]

A DataTable would be doable. The program outputs a 11KB file so we're not talking about alot of data.

Basically, I've been working on this program to speed up billing of one particular customer. That want to be billed by departments (they have 34 different departments) and our ordering system will only allow us to bill for items coming out of a single warehouse at a time (cust1, cust2, cust3, cust4).

So, my thought was to do a query to get the departments included in a provided daterange and place them in an array, which works fine. Loop the department array, and then the warehouse array for individual orders.

So, I need there to be a HDR line so the ordering system knows which PO# to use, and which warehouse it's coming from. I need there to be a DTL line under each HDR for each item/quantity that fits the criteria (department, warehouse, order date).

That's pretty much it. I've gotten this far, but if it's skipping the first line makes this unusable.

Hopefully this clears it up a bit.

[sknake]

Then change your code to

Dim dt as DataTable = new DataTable()
dt.Load(dr)
For i As Integer = 0 To dt.Rows.Count - 1
Dim row As DataRow = dt.Rows(i)
'if (i == 0) print header
'process detail...

[jmmille]

Thank you for your help sknake!

I made the changes you suggested and it seems to work great now. I will test it in a few days to verify, but it's looking much better than my old program.

I really appreciate it.
JM

[sknake]

You're welcome

Please don't forget to mark this thread as solved if you have found an answer to your question and good luck!