Hi Experts
my excel data is copied successfully to the listview.
But on importing to database, I get this error.

InvalidArgument=Value of '5' is not valid for 'index'
ParameterName:Index

=================

I have 2 problems

Problem 1:
What should be the problem when I importing from listview(I think it is a listview column index problem.

Problem 2:
What statement can I replace
"If ThisExcelFile.ActiveCell.Value > Nothing Or ThisExcelFile.ActiveCell.Text > Nothing Then"
so that empty cells are copied.(some 'repeater' column cells are empty) There are 5 columns.

Here is my code:

Private Structure MyExcelRows
        Dim RollNo As String
        Dim StudentsNames As String
        Dim Gender As String
        Dim BirthDate As String
        Dim Repeater As String

End Structure

 Private MyExcelRowList As List(Of MyExcelRows) = New List(Of MyExcelRows)


 Private Function GetExcelData() As Boolean

        Dim Data_IsCopied As Boolean = False
        'open workbook
        Dim ThisExcelFile As New Excel.Application
        ThisExcelFile.Workbooks.Open(Me.txtThisExcelFilePath.Text)

        'extract data
        ThisExcelFile.Sheets("Sheet1").activate()
        ThisExcelFile.Range("A2").Activate()

        'I start extracting rows
        Dim CurrentRow As New MyExcelRows

        'If Len(wks.Cells(1, "A").Value) = 0 Then Exit Sub 
        Do

            If ThisExcelFile.ActiveCell.Value > Nothing Or ThisExcelFile.ActiveCell.Text > Nothing Then

                CurrentRow.RollNo = ThisExcelFile.ActiveCell.Value                'add rollno
                ThisExcelFile.ActiveCell.Offset(0, 1).Activate()

                CurrentRow.StudentsNames = ThisExcelFile.ActiveCell.Value         'add StudentsNames 
                ThisExcelFile.ActiveCell.Offset(0, 1).Activate()

                CurrentRow.Gender = ThisExcelFile.ActiveCell.Value                'add gender
                ThisExcelFile.ActiveCell.Offset(0, 1).Activate()

                CurrentRow.BirthDate = ThisExcelFile.ActiveCell.Value             'add dob
                ThisExcelFile.ActiveCell.Offset(0, 1).Activate()

                CurrentRow.Repeater = ThisExcelFile.ActiveCell.Value              'add repeater
                ThisExcelFile.ActiveCell.Offset(0, 1).Activate()

                ExcelRowList.Add(CurrentRow)                         'add above row to my list
                ThisExcelFile.ActiveCell.Offset(1, -5).Activate()    'move to the next row

            Else
                Data_IsCopied = True
                Exit Do
            End If

        Loop


        'close workbook
        ThisExcelFile.Workbooks.Close()
        ThisExcelFile = Nothing

        Return Data_IsCopied

    End Function

'the following sub pastes the data to listview successfully

 Private Sub btnPaste_Click(sender As Object, e As EventArgs) Handles btnPaste.Click
        Dim excelFile As String
        excelFile = Me.txtExPath.Text
        If (excelFile.IndexOf("xlsx") <> -1) Or (excelFile.IndexOf("xls") <> -1) Then


            LvStuds.Items.Clear()
            LvStuds.Columns.Clear()

            'add columns to LvStud listview
            LvStuds.Columns.Add("RollNo", 40, HorizontalAlignment.Center)
            LvStuds.Columns.Add("StudentsNames", 200, HorizontalAlignment.Left)
            LvStuds.Columns.Add("Gender", 45, HorizontalAlignment.Center)
            LvStuds.Columns.Add("BirthDate", 75, HorizontalAlignment.Right)
            LvStuds.Columns.Add("ClassID", 55, HorizontalAlignment.Center)
            LvStuds.Columns.Add("Repeater", 40, HorizontalAlignment.Center)

            LvStuds.Font = New Font(New FontFamily("times new roman"), 9, FontStyle.Regular)

            If GetExcelData() = True Then   'add it to listview

                For Each xitem In MyExcelRowList

                    Dim LvItem As ListViewItem
                    LvItem = Me.LvStuds.Items.Add(xitem.RollNo)
                    LvItem.SubItems.AddRange(New String() {xitem.StudentsNames, xitem.Gender, xitem.BirthDate, _
                                                          lblClassID.Text, xitem.Repeater})

                Next

            End If

            RemoveDuplicates(LvStuds) ' function to remove any data duplicates

            lblMsg.Show()
            lblMsg.Text = "Importing " & LvStuds.Items.Count & " students into " & cboStudClass.Items(cboStudClass.SelectedIndex)(1)
        Else
            MsgBox("Wrong Spreadsheet file !" & vbCrLf & vbCrLf & "Browse for the file containing the students.", MsgBoxStyle.Critical, "Wrong Spreadsheet File")

        End If

    End Sub

'the following sub imports listview data into database.exception above is thrown

Private Sub btnImport_Click(sender As Object, e As EventArgs) Handles btnImport.Click
If Not LvStuds.Items.Count = 0 Then
            'Check_Initial_Table_Reseed()
            Dim i As Integer

            For i = 0 To LvStuds.Items.Count - 1
                Dim cmd As New SqlCommand

                cmd.Connection = Conn
                cmd.CommandText = "INSERT INTO tblStudents ([RollNo], [StudentNames],     [Gender], [BirthDate], [ClassID], [Repeater]) VALUES " & _
                    "(@RollNo,@StudentNames,@Gender,@BirthDate,@ClassID,@Repeater) "
                cmd.CommandType = CommandType.Text
                cmd.Parameters.AddWithValue("@RollNo", LvStuds.Items(i).SubItems(0).Text)
                cmd.Parameters.AddWithValue("@StudentNames", LvStuds.Items(i).SubItems(1).Text)
                cmd.Parameters.AddWithValue("@Gender", LvStuds.Items(i).SubItems(2).Text)
                cmd.Parameters.AddWithValue("@BirthDate", LvStuds.Items(i).SubItems(3).Text)
                cmd.Parameters.AddWithValue("@ClassID", LvStuds.Items(i).SubItems(4).Text)
                cmd.Parameters.AddWithValue("@Repeater", LvStuds.Items(i).SubItems(5).Text)
                Conn.Open()
                cmd.ExecuteNonQuery()
                cmd.Dispose()
                Conn.Close()
            Next i

            MessageBox.Show("" & LvStuds.Items.Count & " Students Imported into" & vbCrLf & "" & cboStudClass.Text & ".", "" & LvStuds.Items.Count & " " & cboStudClass.Text & " Students", MessageBoxButtons.OK, MessageBoxIcon.Information)


End Sub

Your help is highly appreciated
Newbie

Edited 3 Years Ago by gbhs

This article has been dead for over six months. Start a new discussion instead.