8 Replies - 2022 Views - Last Post: 03 March 2012 - 05:48 PM Rate Topic: -----

#1 ZRonZ  Icon User is offline

  • D.I.C Head

Reputation: 28
  • View blog
  • Posts: 206
  • Joined: 09-January 09

DataTable navigation

Posted 01 March 2012 - 09:46 PM

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?

Is This A Good Question/Topic? 0
  • +

Replies To: DataTable navigation

#2 shadachi  Icon User is offline

  • D.I.C Head

Reputation: 15
  • View blog
  • Posts: 141
  • Joined: 25-January 08

Re: DataTable navigation

Posted 01 March 2012 - 11:02 PM

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 . =)
Was This Post Helpful? 0
  • +
  • -

#3 DimitriV  Icon User is offline

  • They don't think it be like it is, but it do
  • member icon

Reputation: 584
  • View blog
  • Posts: 2,738
  • Joined: 24-July 11

Re: DataTable navigation

Posted 01 March 2012 - 11:04 PM

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…
Was This Post Helpful? 1
  • +
  • -

#4 shadachi  Icon User is offline

  • D.I.C Head

Reputation: 15
  • View blog
  • Posts: 141
  • Joined: 25-January 08

Re: DataTable navigation

Posted 01 March 2012 - 11:09 PM

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.. =)
Was This Post Helpful? 0
  • +
  • -

#5 ZRonZ  Icon User is offline

  • D.I.C Head

Reputation: 28
  • View blog
  • Posts: 206
  • Joined: 09-January 09

Re: DataTable navigation

Posted 02 March 2012 - 07:22 AM

View PostDimitriV, 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!
Was This Post Helpful? 0
  • +
  • -

#6 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: DataTable navigation

Posted 02 March 2012 - 10:17 AM

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

Was This Post Helpful? 1
  • +
  • -

#7 demausdauth  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 177
  • View blog
  • Posts: 645
  • Joined: 03-February 10

Re: DataTable navigation

Posted 02 March 2012 - 10:55 AM

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.
Was This Post Helpful? 0
  • +
  • -

#8 ZRonZ  Icon User is offline

  • D.I.C Head

Reputation: 28
  • View blog
  • Posts: 206
  • Joined: 09-January 09

Re: DataTable navigation

Posted 02 March 2012 - 01:45 PM

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.
Was This Post Helpful? 0
  • +
  • -

#9 ZRonZ  Icon User is offline

  • D.I.C Head

Reputation: 28
  • View blog
  • Posts: 206
  • Joined: 09-January 09

Re: DataTable navigation

Posted 03 March 2012 - 05:48 PM

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
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1