Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Index out of range error

Posted on 2016-07-31
5
Medium Priority
?
66 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 64

Assisted Solution

by:Fernando Soto
Fernando Soto earned 1000 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 64

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 1000 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
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…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

730 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