I am trying to write a windows application in vb.net to import data from excel to sql server. I got "object not set to an instance of an object" error. The data actually have been inserted into the sql. The following is my code. I have a button when the button is clicked, it does frmProcess().
The first row is header in excel. I start to read data from 2nd row.
----------------------------------
Private Sub frmProcess()
Dim app As New Application()
Dim book As Workbook
Dim sheet As Worksheet
Dim Range As Range
app.Visible = False
app.ScreenUpdating = False
app.DisplayAlerts = False
book = app.Workbooks.Open(Me.txtFilePath.Text, , True, 5, "", "", True, XlPlatform.xlWindows, "\t", False, False, 0, True, 1, 0)
' Roster sheet
sheet = book.Worksheets(1)
sheet.Activate()
Range = sheet.Range("A1", "F1")
Dim col() As String = {"", "SeqID" _
, "DOB" _
, "Gender" _
, "Race" _
, "FirstName" _
, "LastName" }
Dim strSQL As String = ""
Dim TableName As String = "tblTmp"
Dim insertCnt As Integer = 0
Dim exisitingCnt As Integer = 0
Dim cnt As Integer = 0
' Try
Do
Dim seq As String = Range.Cells(row, 1).value.ToString.Trim
If Integer.TryParse(seq, 0) Then
For i = 2 To 6
If Range.Cells(2, i).Value Is Nothing Then
col(i) = " "
Else
col(i) = CType(Range.Cells(row, i), Range).Value.ToString.Trim.Replace("'", "''")
End If
Next
strSQL = "SELECT count(*) As cnt From " & TableName & _
" Where SeqID='" & seq & "'"
cnt = CInt(GetDBValue(strSQL))
If cnt = 0 Then
strSQL = "Insert into " & TableName & " (SeqID " & _
" ,DOB " & _
" ,Gender " & _
" ,Race " & _
" ,FirstName " & _
" ,LastName " ) " & _
" Values ('" & seq & "', '" & col(2) & "'" & _
",'" & col(3) & "' ,'" & col(4) & "'" & _
",'" & col(5) & "','" & col(6) & "')"
DBExecute(strSQL)
insertCnt = insertCnt + 1
Else
exisitingCnt = exisitingCnt + 1
End If
row = row + 1
Else
Exit Do
End If
Loop
lblMsg.Visible = True
lblMsg.Text = insertCnt & " rows have been imported to database table successfully! " & vbCrLf & _
exisitingCnt & " rows are already existing! "
CleanUp(sheet)
book.Close(False)
CleanUp(book)
app.Workbooks.Close()
CleanUp(app.Workbooks)
'quit and dispose app
app.Quit()
CleanUp(app)
GC.Collect()
End SubThanks so much.
