[ZRonZ]

I have an application that downloads about 19,000 records from a SQL table and I need to step through all the records doing a calculation on each then create a new Excel workbook with the data.

m_cnBOMConn.ConnectionString = _
            "Data Source=KEMI-FJ89SF1\RONSQL;Initial Catalog=BillsOfMaterials;Integrated Security=True"
                Dim m_Joe As String = "Select * from billsofmaterials.dbo.billoutput"
                m_daXLRPTadapter = New SqlDataAdapter(m_Joe, m_cnBOMConn)

                m_daXLRPTadapter.Fill(m_dtXLRPT)

The connection string works and the datatable fill statement works.

m_rows = m_dtXLRPT.Rows.Count()

m_rows does accurately report that the number of records in the datatable is the same as in the SQL table

I can add records to the XLS file with a for/next loop but they are all the first record of the datatable.

So now that I have the data, how do I go through each record sequentially?

[shadachi]

There are many ways of going through each data record sequentially .. you can loop it , use Linq etc .

Are you sure that the dataTable is properly filled ?
How bout providing us the for/next loop portion of code . =)

[DimitriV]

Quote

So now that I have the data, how do I go through each record sequentially?

I think that's why he came here and posted a topic…

[shadachi]

Quote

I can add records to the XLS file with a for/next loop but they are all the first record of the datatable.

at least we could see the code and learn something from it ? =P , if he does not want to continue with a for loop then how bout using Linq to sort it for you.. =)

[ZRonZ]

DimitriV, on 02 March 2012 - 12:04 AM, said:

Quote

So now that I have the data, how do I go through each record sequentially?

I think that's why he came here and posted a topic…

Thank you DimitriV, you understand the issue.

It's not about the loop. I can do the loop, no problem. It's not about sorting because I need it in natural order.

As you can see from the posted code, I have the data in a datatable. I can pull the first record and it matches the SQL table. The number of records in the data table as filled match the number of records in the SQL table.

I can't tell if the data downloaded is 100% correct because I can't figure out how to get to record 2, record 10,000, record whatever.

It all boils down to this: What code (probably one line) do to I need that will move from record 1 to record 2? If I can figure that out, I can loop it and I've got it.

Thanks!

[CharlieMay]

I think this line gives a good clue

m_rows = m_dtXLRPT.Rows.Count()

This means your dt contains rows ( a collection of rows). To find any given item in a collection you can pass it the index.


What do you see when you do a:

MessageBox.Show(m_dtXLRPT.Rows(1)(0).ToString)

Remember the 1 can be replaced by your Loop iterator variable to change with each iteration of the loop.

This post has been edited by CharlieMay: 02 March 2012 - 10:19 AM

[demausdauth]

Or is it possible that maybe you want to do something For Each row in the m_dtXLRPT.Rows collection. Similar to what CharlieMay is talking about.

[ZRonZ]

Quote

This means your dt contains rows ( a collection of rows). To find any given item in a collection you can pass it the index.


What do you see when you do a:

MessageBox.Show(m_dtXLRPT.Rows(1)(0).ToString)

Remember the 1 can be replaced by your Loop iterator variable to change with each iteration of the loop.

Thanks CharlieMay, that does step it through the datatable. I think I'm good now! Much appreciated.

[ZRonZ]

Here is my resolved issue in my application:

                Dim m_range As String
                Dim m_rows As Integer = 0
                m_rows = m_dtXLRPT.Rows.Count()

                For i As Integer = 1 To m_rows  
                    m_range = "A" & i.ToString
                    xlRange = CType(xlWorksheet.Range(m_range), excel.Range)
                    xlRange.Value = (m_dtXLRPT.Rows(i - 1)(1).ToString())


                    m_range = "B" & i.ToString
                    xlRange = CType(xlWorksheet.Range(m_range), excel.Range)
                    xlRange.Value = (m_dtXLRPT.Rows(i - 1)(2).ToString())

                    m_range = "C" & i.ToString
                    xlRange = CType(xlWorksheet.Range(m_range), excel.Range)
                    xlRange.Value = (m_dtXLRPT.Rows(i - 1)(3).ToString())
               Next

I am reading the data from the datatable one record at a time and then adding that information into columns A, B and C of an Excel spreadsheet.

Navigating through the datatable help from CharlieMay, help generating the Excel spreadsheet came from a tutorial on dreamincode.net