Index out of range error

Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick

        Dim Objwriter As New System.IO.StreamWriter(Filename, True)

        'Objwriter.WriteLine("Entering Timer1_Tick " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToShortTimeString())
        'Objwriter.Flush()

       
        Dim con As New SqlConnection(constring)
        Dim com As New SqlCommand
        Dim Req As New WinHttp.WinHttpRequest
        Dim ReqUrl As String
        'Dim t As Integer
        Dim tble As New DataTable
        Dim E_mail As New MailMessage()
        Dim smtp As New SmtpClient
        Dim Rangefrom As String = ""
        Dim Rangeto As String = ""


        Try

            com.CommandType = CommandType.StoredProcedure
            com.CommandText = "dbo.[StoredProcedure1]"
            com.Connection = con

            'Objwriter.WriteLine("Connection Setup " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToShortTimeString())
            'Objwriter.Flush()


            Dim adapter As New SqlDataAdapter(com)
            adapter.SelectCommand.CommandTimeout = 600

            'Objwriter.WriteLine("Adapter Setup " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToShortTimeString())
            'Objwriter.Flush()


            'Fill the dataset
            Dim DS As New DataSet

            adapter.Fill(DS)

            'Objwriter.WriteLine("Adapter Full of dataset " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToShortTimeString())
            'Objwriter.Flush()


            con.Close()

            'Objwriter.WriteLine("Connection Closed " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToShortTimeString())
            'Objwriter.Flush()

            If DS.Tables.Count > 0 Then
                Rangefrom = DS.Tables(0).Rows(0)(0)
                Rangeto = DS.Tables(0).Rows(0)(1)
            End If


            Objwriter.WriteLine("RangeFrom:" + Rangefrom + "::" + "RangeTo:" + Rangeto)
            'Objwriter.WriteLine("RangeTo:" + Rangeto + ":")
            Objwriter.Flush()

            'Now, read through your data:
            'For Each DR As DataRow In DS.Tables(0).Rows
            ' MsgBox("The value in Column ""ColumnName1"": " & CStr(DR("ColumnName1")))
            ' Next


            ' SELECT TOP 1 start_time [rangefrom_string], CONVERT(NVARCHAR(30), dateadd(d,1,start_time),126) [rangeto_string] FROM [M].[dbo].[Drivt] ORDER BY start_time DESC

        Catch ex As Exception

            Objwriter.WriteLine("Error " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToShortTimeString())
            Objwriter.WriteLine(ex.Message)
            Objwriter.Flush()


        End Try


        ReqUrl = CommonUrl
        'ReqUrl = ReqUrl & "&" & "range_pattern=d-1"
        'ReqUrl = ReqUrl & "&" & "action=showSpeedingEvents"
        'ReqUrl = ReqUrl & "&" & "separator=1"
        'ReqUrl = ReqUrl & "&" & "lang=en"
        'ReqUrl = ReqUrl & "&" & "objectgroupname=AllVehicles"
        ReqUrl = ReqUrl & "&" & "range_pattern=ud"
        ReqUrl = ReqUrl & "&" & "rangefrom_string=" & Rangefrom
        ReqUrl = ReqUrl & "&" & "rangeto_string=" & Rangeto
        'ReqUrl = ReqUrl & "&" & "&range_pattern=m-3"

        Objwriter.WriteLine("URL:" + ReqUrl)
        Objwriter.Flush()

        Req.Open("GET", ReqUrl)
        Req.Send()

        Dim T As String = Req.ResponseText

        T = T.Replace("""", String.Empty)

        Dim tab As New DataTable
        Dim Lines As String
        Dim afield As String()

        T = T.Replace("'", "''")

        T = T.Replace("~", "''")

        Dim StringReader As System.IO.StringReader = New StringReader(T.Replace("^", "''"))
            'Dim StringReader As System.IO.StreamReader = New StreamReader(ResultsRichTextBox.Text)

        Lines = StringReader.ReadLine()
        afield = Lines.Split(vbTab)

        Dim i As Integer = 0
        While i <= afield.Count() - 1
            Dim column As New DataGridViewTextBoxColumn()
            column.Name = afield(i)
            column.HeaderText = afield(i)
            DataGridView1.Columns.Add(column)
            i += 1
        End While
            'DataGridView1.DataSource = tab

        While StringReader.Peek() <> -1
            Lines = StringReader.ReadLine()
            afield = Lines.Split(vbTab)
            DataGridView1.Rows.Add(afield
)
        End While

        Try
            Try
                con.ConnectionString = constring
                Dim count As Integer
                con.Open()
                com.Connection = con
                com.CommandType = CommandType.Text

                For Each rw As DataGridViewRow In DataGridView1.Rows

                    com.CommandText = ("INSERT INTO [dbo].[SpQuery]([objectno],[objectname],[start_time],[start_longitude],[start_latitude],[start_postext],[end_time],[end_longitude],[end_latitude],[end_postext],[max_speed(km/h)],[min_speed(km/h)],[avg_speed(km/h)],[object_speedlimit(km/h)],[road_speedlimit(km/h)],[driverno],[drivername],[objectuid],[duration(ms)],[distance],[roadtype],[incity],[externalid])" & _
                                          " SELECT '" & rw.Cells(0).Value & "','" & rw.Cells(1).Value & "','" & rw.Cells(2).Value & "','" & rw.Cells(3).Value & "','" & rw.Cells(4).Value & "','" & rw.Cells(5).Value & "','" & rw.Cells(6).Value & "','" & rw.Cells(7).Value & "','" & rw.Cells(8).Value & "','" & rw.Cells(9).Value & "','" & rw.Cells(10).Value & "','" & rw.Cells(11).Value & "','" & rw.Cells(12).Value & "','" & rw.Cells(13).Value & "','" & rw.Cells(14).Value & "','" & rw.Cells(15).Value & "','" & rw.Cells(16).Value & "','" & rw.Cells(17).Value & "','" & rw.Cells(18).Value & "','" & rw.Cells(19).Value & "','" & rw.Cells(20).Value & "','" & rw.Cells(21).Value & "','" & rw.Cells(22).Value & "' " & _
                                           " EXCEPT SELECT [objectno],[objectname],[start_time],[start_longitude],[start_latitude],[start_postext],[end_time],[end_longitude],[end_latitude],[end_postext],[max_speed(km/h)],[min_speed(km/h)],[avg_speed(km/h)],[object_speedlimit(km/h)],[road_speedlimit(km/h)],[driverno],[drivername],[objectuid],[duration(ms)],[distance],[roadtype],[incity],[externalid] FROM [dbo].[Speeding_Event]")

                    count = com.ExecuteNonQuery
                    Objwriter.WriteLine("Total Rows:" + count.ToString)
                    Objwriter.Flush()

                    com.CommandTimeout = 600
                    com.ExecuteNonQuery()



                Next


                Dim counter As Integer = com.ExecuteNonQuery()
                'If counter < 1 Then
                'MsgBox("Insert Incomplete")
                'ElseIf counter = 1 Then
                'MsgBox("Insert Completed")
                'End If



                con.Close()
            Catch s As SqlException

                Objwriter.WriteLine("Error " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToShortTimeString())
                Objwriter.WriteLine(s.Message)

                Objwriter.WriteLine("Insert Error!")
                Objwriter.WriteLine(" ")
                'Objwriter.WriteLine(s.Message)


Falling with error system out of range.
RIASAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fernando SotoRetiredCommented:
It might be that the number of columns defined in the DataGridView are less then the number of fields being returned from the Lines.Split(vbTab)  or that this is an empty line. which would cause a Index out of range error on this line of code, DataGridView1.Rows.Add(afield), When the code throws an error check the value of afield to see that it has data and that the number of elements in afield does not exceed the number of columns in the DataGridView.
0
RIASAuthor Commented:
Any code change recommendation
0
Fernando SotoRetiredCommented:
Well that will depend on what is causing the issue, is it caused by a non-existing column on the upper bound or lower bound? Put in test code to check that the data is valid.

When the code gets the error what is the value of afield?
0
ROMA CHAUHANProject LeadCommented:
Hi Rias,
First of all, I want to give you one suggestion over here. You are doing so many things in your single function. Try to split it out as per the functionality to keep the code neat and easy to understand.

To the solution of your problem, try to debug your code and find the exact line where you are getting the error.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RIASAuthor Commented:
Cheers!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.