Solved

Index out of range error

Posted on 2016-07-31
5
49 Views
Last Modified: 2016-08-02
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.
0
Comment
Question by:RIAS
  • 2
  • 2
5 Comments
 
LVL 63

Assisted Solution

by:Fernando Soto
Fernando Soto earned 250 total points
ID: 41736456
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
 

Author Comment

by:RIAS
ID: 41736476
Any code change recommendation
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 41736480
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
 
LVL 11

Accepted Solution

by:
ROMA CHAUHAN earned 250 total points
ID: 41738910
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
 

Author Closing Comment

by:RIAS
ID: 41738916
Cheers!
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question