?
Solved

'System.IndexOutOfRangeException' occurred in system.data.dll   --There is no row at position 10.  Pls help

Posted on 2006-06-05
13
Medium Priority
?
1,166 Views
Last Modified: 2012-05-05
Here is the part of my code where i am opening the excel seet as a datatable and comparing with text filename in my temp folder. Suddently when it excel sheet 10 position system throwing an exception when cursor looping thowing the the code Tbl.Rows.RemoveAt(f) and error message is

" An unhandled exception of type 'System.IndexOutOfRangeException' occurred in system.data.dll

Additional information: There is no row at position 10.


---Pls help (no problem with my excel)
 
Dim Tbl As DataTable = New DataTable

        Dim path As String = strArchFiles + "\file.xls"
        Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                              "Data Source=" & path & ";Extended Properties=""Excel 8.0;HDR=NO"""
        Dim ExcelConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)
        ExcelConnection.Open()
        Dim sql As String = "select * from [sheet1$] "
        Dim da As New System.Data.OleDb.OleDbDataAdapter(sql, ExcelConnection)
        ds = New DataSet
        da.Fill(ds, "sheet1")
        ExcelConnection.Close()
        If tempDir.GetFiles("*.txt").Length > 0 Then
            If ds.Tables(0).Rows.Count = 0 Then
                MsgBox("Nothing in Excel file", MsgBoxStyle.OKOnly, "Excel empty")
                Exit Sub
            End If

            For i = 0 To ds.Tables(0).Rows.Count - 1
                If IsDBNull(ds.Tables(0).Rows.Item(i)(0)) Then
                    ds.Tables(0).Rows.RemoveAt(i)
                ElseIf Trim(ds.Tables(0).Rows.Item(i)(0)) = "" Then
                    ds.Tables(0).Rows.RemoveAt(i)
                End If
            Next


            Tbl = ds.Tables(0).Copy

            ProgressBar1.Value = 0
            Dim fils As String
            If tempDir.GetFiles("*.txt").Length = ds.Tables(0).Rows.Count Then
                item = "Non identical files are "
                x = item.Length
                pbinc = 10 / tempDir.GetFiles("*.txt").Length
                For f = 0 To ds.Tables(0).Rows.Count - 1
                    ProgressBar1.Value = ProgressBar1.Value + pbinc
                    Dim strg As String = ds.Tables(0).Rows.Item(f)(0)
                    fl = Trim(ds.Tables(0).Rows.Item(f)(0))
                    If tempDir.GetFiles(fl.Trim() + "*.txt").Length = 0 Then
                        item = item + fl.Trim() + ".txt, "
                        ''Adding into the Array List

                    Else
                        Tbl.Rows.RemoveAt(f) ---------------------Error
                    End If

                Next
0
Comment
Question by:nyee84
  • 7
  • 5
13 Comments
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 16832104
Hi nyee84
You can not loop through the Records and Delete them at the same time
When you say

 For f = 0 To ds.Tables(0).Rows.Count - 1

it gets the Row Count as say 10
now when you say

Tbl.Rows.RemoveAt(f)

one row is Removed now the count is 9 where as our program attempts to go to the 10th element as well so the Error

So you need to take care of this situation

0
 

Author Comment

by:nyee84
ID: 16832142
My excel got 20 rows ...

I am getting this issue in the middle row..

0
 

Author Comment

by:nyee84
ID: 16832171
my row count and removal doing  a different datatable actually, means

i am coping my datatable into another datatable. pLs refer to the above code

   Tbl = ds.Tables(0).Copy

The row count is based on the ds datatable and my removal is fom another datatable Tbl.

Kindly help..
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 19

Expert Comment

by:arif_eqbal
ID: 16832176
Instead of For Loop if you use a For Each Loop
it will complain as soon as you try to Remove the Item
However with a For Loop it will complain as soon as the Index it is looking for is not found, if your If Clause is such that it Removes more than One Row it might complain somewhere in the middle as well

So one approach here is Do not say RemoveAt(f)
instead say
Tbl.Rows(f).Delete
or
 ds.Tables(0).Rows(i).Delete (in the upper loop)

then AFTER the loop say
Tbl.AcceptChanges
or
ds.Tables(0).AcceptChanges

The effect here would be the same
0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 16832217
Ok I am sorry did not pick up this thing
Anyway you need to do what I said above for the Upper Loop
It might not be giving any Error now, because there might be no rows that are NULL, but this is how we leave bugs it will give Error as soon as it deletes a row.

As for the second loop, you are using the First Table's index to Remove Rows from second (copied) table
Imagine what happens after the First remove, After the first time a row is removed, say the second row, the Third row in the copied Table becomes second so now if you try to delete there is not one-to-one correspondence between the two tables so first thing it would be deleting wrong rows second at one stage it would give error as the indexes would not match, and that's what is happening.

So if you are making a Copy of the dataTable just for Deleting rows try the method I suggested
Inside the loop just say Row(f).Delete and then outside the loop AcceptChanges
0
 

Author Comment

by:nyee84
ID: 16832261
For f = 0 To ds.Tables(0).Rows.Count - 1
                    ProgressBar1.Value = ProgressBar1.Value + pbinc
                    Dim strg As String = ds.Tables(0).Rows.Item(f)(0)
                    fl = Trim(ds.Tables(0).Rows.Item(f)(0))
                    If tempDir.GetFiles(fl.Trim() + "*.txt").Length = 0 Then
                        item = item + fl.Trim() + ".txt, "
                        ''Adding into the Array List

                    Else
                        'Tbl.Rows.RemoveAt(f)
                        Tbl.Rows(f).Delete()                    -----new changes
                    End If
                    Tbl.AcceptChanges()                ----------new changes
                Next
Still the same error
0
 
LVL 19

Assisted Solution

by:arif_eqbal
arif_eqbal earned 1800 total points
ID: 16832318
For f = 0 To ds.Tables(0).Rows.Count - 1
                    ProgressBar1.Value = ProgressBar1.Value + pbinc
                    Dim strg As String = ds.Tables(0).Rows.Item(f)(0)
                    fl = Trim(ds.Tables(0).Rows.Item(f)(0))
                    If tempDir.GetFiles(fl.Trim() + "*.txt").Length = 0 Then
                        item = item + fl.Trim() + ".txt, "
                        ''Adding into the Array List

                    Else
                        ds.Tables(0).Rows(f).Delete()  '--> Use the same DataTable otherwise the index "f" would be different
                    End If
                    'Tbl.AcceptChanges()   ----> Not Here  but after the loop  
                Next
    ds.Tables(0).AcceptChanges()
0
 

Author Comment

by:nyee84
ID: 16832486
Hi Arif,

Seems to be fine now, need a detail check and will revert you.

Thanks alot.

0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 16832500
Sure, Thanks
0
 
LVL 34

Accepted Solution

by:
Sancler earned 200 total points
ID: 16833833
The standard approach for removing things from a list is to reverse the order of the count through it.  Instead of

   For i As Integer = 0 To CountOfList - 1

use

   For i As Integer = CountOfList - 1 to 0 Step -1

Otherwise, whenever you remove an item not only does the list count reduce but also the indexes of all subsequent items is reduced by one.  But doing it in the reverse order not only makes sure that the number of any iteration is less than the total count remaining in the list, it also leaves the indexes of all items which are still to be visited the same.

I imagine the approach you've been discussing will work OK.  So this is just a general tip, rather than for any points on this question.

Roger
0
 

Author Comment

by:nyee84
ID: 16839296
Sancler,

Thanks ,  point noted..

0
 

Author Comment

by:nyee84
ID: 16840997
Hi Arif,

Can help me on one of my other post "reading Excel using OLE db". Nobody has responded to the qn yest and kindly input your comments if you are free.

Hi Experts,

Pls look into my "read Excel using Oledb"  qn

Thanks
0
 

Author Comment

by:nyee84
ID: 16864483
Hi Arif,

Accidently by mistake i  have taken your answer as assisted answer, anyway i have given the deserved point for your comment.  Sorry for the inconvenience caused..

Experts,

Thanks a lot for your comments...
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

749 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