0

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 Sub

Thanks so much.

Edited by newvbnetuser: n/a

2
Contributors
1
Reply
5
Views
5 Years
Discussion Span
Last Post by bluehangook629
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.