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