vb.net : sending data to excel : for each record

Cholotron 161 Reputation points
2021-02-13T21:17:41.213+00:00

Hello All
Please if you could tell me what I am doing wrong
This is my code 

                xlWorkSheet = xlWorkBook.Worksheets("Biweekly")  
                With xlWorkSheet  
                    'For Each Name on Table  
                    var_int = 8  
                    app_cmd.CommandText = "select distinct emp_id, emp_name from tc_print where week_id=" & week_id & " and emp_group='" & emp_group & "' order by emp_name;"  
                    app_dr = app_cmd.ExecuteReader()  
                    While app_dr.Read()  
                        the_emp = app_dr(0)  
                        .Cells(var_int, 1).Value = the_emp  
                        .Cells(var_int, 2).Value = app_dr(1)  
                        the_int = 9  
                        'For Each Record on each Emp  
                        bi_cmd.CommandText = "select col_type from tc_print where week_id=" & week_id & " and emp_group='" & emp_group & "' and emp_id=" & the_emp & " order by day_pos;"  
                        bi_dr = bi_cmd.ExecuteReader()  
                        While bi_dr.Read()  
                            .Cells(the_int, 3).Value = bi_dr(0)  
                            the_int = the_int + 1  
                            var_int = var_int + 1  
                        End While  
                        bi_dr.Close()  
                        the_int = var_int + 1  
                    End While  
                    app_dr.Close()  
                End With

This is what I am trying to accomplish
67755-capture.png

VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,714 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Cholotron 161 Reputation points
    2021-02-13T22:07:26.59+00:00

    Much better now but there is blank record on the first record 

                    xlWorkSheet = xlWorkBook.Worksheets("Biweekly")  
                With xlWorkSheet  
                    'For Each Name on Table  
                    var_int = 8  
                    app_cmd.CommandText = "select distinct emp_id, emp_name from tc_print where week_id=" & week_id & " and emp_group='" & emp_group & "' order by emp_name;"  
                    app_dr = app_cmd.ExecuteReader()  
                    While app_dr.Read()  
                        the_emp = app_dr(0)  
                        .Cells(var_int, 1).Value = the_emp  
                        .Cells(var_int, 2).Value = app_dr(1)  
  
                        'For Each Record on each Emp  
                        bi_cmd.CommandText = "select col_type,day_1,day_2,day_3,day_4,day_5,day_6,day_7,day_8,day_9,day_10,day_11,day_12,day_13,day_14,day_tt from tc_print where week_id=" & week_id & " and emp_group='" & emp_group & "' and emp_id=" & the_emp & " order by day_pos;"  
                        bi_dr = bi_cmd.ExecuteReader()  
                        While bi_dr.Read()  
                            .Cells(the_int, 3).Value = bi_dr(0)  
                            .Cells(the_int, 4).Value = bi_dr(1)  
                            .Cells(the_int, 5).Value = bi_dr(2)  
                            .Cells(the_int, 6).Value = bi_dr(3)  
                            .Cells(the_int, 7).Value = bi_dr(4)  
                            .Cells(the_int, 8).Value = bi_dr(5)  
                            .Cells(the_int, 9).Value = bi_dr(6)  
                            .Cells(the_int, 10).Value = bi_dr(7)  
                            .Cells(the_int, 11).Value = bi_dr(8)  
                            .Cells(the_int, 12).Value = bi_dr(9)  
                            .Cells(the_int, 13).Value = bi_dr(10)  
                            .Cells(the_int, 14).Value = bi_dr(11)  
                            .Cells(the_int, 15).Value = bi_dr(12)  
                            .Cells(the_int, 16).Value = bi_dr(13)  
                            .Cells(the_int, 17).Value = bi_dr(14)  
                            .Cells(the_int, 18).Value = bi_dr(15)  
                            the_int = the_int + 1  
                        End While  
                        var_int = the_int  
                        bi_dr.Close()  
  
                        var_int = var_int + 2  
                        the_int = var_int  
                    End While  
                    app_dr.Close()  
                End With  
            End With

    67803-capture.png

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.