I created the code below to import an Excel file into an existing M'soft Access database. The attributes present in the Access database are CWNO(Text), Name(Text), ICNO(Text), Nasion(Text), Company(Text), JoinDT(DateTime), ExpDT(DateTime), ResBT(Boolean), ResDT(DateTime), Note(Text), PBIT(boolean). The final four columns are not present in the Excel sheet. (ResBT, ResDT, Note, PBIT).But I seem to have problems with the code, but do not know why. I've been trying to find out what's wrong since yesterday, but I do not know why. Please help. The error message that I get is "Conversion from type 'DBNull' to type 'String' is not valid". How do I correct this error?
Private Sub loadDataFromExcel()
Dim excelFile As String = "C:\Documents and Settings\SL\Desktop\Project\CW 2008I-Update.xls" 'replace with valid path - file
Dim ExcelSheet As String = "[Sheet4$]"
Dim sqlExcelData = "SELECT * FROM" & ExcelSheet
Dim TableToImportTo As String = "CwMaster"
Dim NumberOfRows As Integer 'CurrentRowPosition As Integer
NumberOfRows = cwmasterTableAdapter.GetData.Rows.Count
Try
Using ExcelConnection As New OleDb.OleDbConnection
ExcelConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & excelFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=NO"""
Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(sqlExcelData, ExcelConnection)
Dim I As Int32
Dim K As DateTime
ExcelConnection.Open()
da.Fill(cwdbDataSet, TableToImportTo)
For Each Row As DataRow In cwdbDataSet.Tables(TableToImportTo).Rows
Dim masterCwRow As CwDbDataSet.CwMasterRow = cwdbDataSet.CwMaster.NewCwMasterRow()
With masterCwRow
' Some columns may require changing the Datatype
' Excel columns that contain numbers and blanks are considered STRINGS so they
' Need to be converted
If Not IsDBNull(Row(0)) Then
If Int32.TryParse(Row(0), I) Then
.CWNO = I
Else
.CWNO = 0
End If
.CWNO = Row(0)
End If
.NAME = Row("Name")
.NASION = Row("Nasion")
.COMPANY = Row("Company")
If Not IsDBNull(Row(2)) Then
If Int32.TryParse(Row(2), I) Then
.ICNO = I
Else
.ICNO = 0
End If
.ICNO = Row(2)
End If
If Not IsDBNull(Row(5)) Then
If DateTime.TryParse(Row(5), K) Then
.JOINDT = K
Else
.JOINDT = Nothing
End If
.JOINDT = Row(5)
End If
If Not IsDBNull(Row(6)) Then
If DateTime.TryParse(Row(6), K) Then
.EXPDT = K
Else
.EXPDT = Nothing
End If
.EXPDT = Row(6)
End If
End With
'Add the next row
cwdbDataSet.CwMaster.AddCwMasterRow(masterCwRow)
Next
'Apply changes to the physical database
cwmasterTableAdapter.Update(cwdbDataSet.CwMaster)
cwdbDataSet.AcceptChanges()
'Close connection and clear the dataset to avoid duplicate rows in table adapter
cwdbDataSet.Tables(TableToImportTo).Rows.Clear()
ExcelConnection.Close()
ExcelConnection.Dispose()
End Using
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub