I try to import data from an excel file, then save the data to a database, however, some cell always empty when read, whose cell combine with digit and alphabet, eg. "92FE0". below are the VB code
Code:
Private Sub OpenExcelFile_FileOk(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles OpenExcelFile.FileOk
Name = OpenExcelFile.FileName
Dim i, j, cnt As Integer
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source='" & Name & " '; " & "Extended Properties=Excel 8.0;")
MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [sheet1$]", MyConnection)
Dim DtSet As System.Data.DataSet
Try
MyCommand.TableMappings.Add("Table", "Attendence")
DtSet = New System.Data.DataSet
MyCommand.Fill(DtSet)
cnt = DtSet.Tables(0).Rows.Count
EmployeeDataGridView.DataSource = DtSet.Tables(0)
MyConnection.Close()
Dim record As System.Data.DataRow
For i = 0 To cnt
record = Employee_DataSet.Employee.NewRow()
For j = 0 To 11
record.Item(j) = DtSet.Tables(0).Rows.Item(i).Item(j)
Next
Try
Employee_DataSet.Employee.Rows.InsertAt(record, i)
EmployeeTableAdapter.Update(Employee_DataSet.Employee)
EmployeeTableAdapter.Update(Employee_DataSet)
Catch ex As Exception
MsgBox(ex.ToString)
End Try
Next
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub