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'


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

        'extract data

        'I start extracting rows
        Dim CurrentRow As New MyExcelRows

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

            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

                Data_IsCopied = True
                Exit Do
            End If


        'close workbook
        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


            '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})


            End If

            RemoveDuplicates(LvStuds) ' function to remove any data duplicates

            lblMsg.Text = "Importing " & LvStuds.Items.Count & " students into " & cboStudClass.Items(cboStudClass.SelectedIndex)(1)
            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
            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)
            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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.