Help with "Refrence not set to instance of an object" Error on

[arcite]

I have spent all day trying to figure out why I keep getting this error: "Reference not set to instance of an object" on the line:

Dim dr As Data.OleDb.OleDbDataReader = OleDbCmd.ExecuteReader

You'll have to excuse all my message boxes. I am new to Visual Web Developer and VB.net and havn't quite figured out how to use its debugging so I have resorted to using message boxes to figure out what line is throwing the error. I also thought it was strange that it is in a try - catch block but the block is not catching it. I get a regular "ok" message box with just the error "Object refrence not set to instance of an object."

Any help - what I am doing wrong - a point in the right direction - would be really appreciated.

Public Function importTransfersheet(ByVal TransfersheetID As String, ByVal srcPath As String, ByVal srcFile As String, ByVal srcSheet As String, ByVal destDB As String, ByVal destTbl As String)

        'Declare Variables

        Dim sSQLTable As String = destTbl

        Dim sExcelFile As String = srcFile

        Dim sWorkbook As String = "[" & srcSheet & "$]"

 

        'Create Connection Strings

        'Dim sExcelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & RTrim(srcPath) & "\" & RTrim(sExcelFile) & ";Extended Properties=""Excel 8.0;HDR=YES;"""

        Dim sExcelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(RTrim(sExcelFile)) & ";Extended Properties=""Excel 8.0;HDR=YES;"""

        MsgBox(sExcelConnectionString)

        Dim sSqlConnectionString As String = "server=HGISERVER\DEVELOPEREDITION;user id=blablabla;password=blablabla;database=" & destDB

 

        'Execute query to erase any previous data from the Rates_TransfersheetsImport Table

        Dim sClearSQL = "DELETE FROM " & destTbl

        Dim sqlConn As Data.SqlClient.SqlConnection = New Data.SqlClient.SqlConnection(sSqlConnectionString)

        Dim sqlCmd As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand(sClearSQL, sqlConn)

 

        Try

            sqlConn.Open()

            sqlCmd.ExecuteNonQuery()

            sqlConn.Close()

        Catch ex As Exception

            'Add Code here to log error and send user to error screen

            MsgBox(ex.Message.ToString & ":" & ex.Source.ToString & ":" & ex.StackTrace.ToString & ":" & ex.InnerException.ToString)

        End Try

 

        MsgBox("Table Cleared")

 

        'Commands to Bulk Copy Data from the Excel File into our SQL Table

        Dim OleDbConn As Data.OleDb.OleDbConnection = New Data.OleDb.OleDbConnection(sExcelConnectionString)

        MsgBox("OleDbConn Created")

        Dim OleDbCmd As Data.OleDb.OleDbCommand

        OleDbCmd = New Data.OleDb.OleDbCommand

        OleDbCmd.CommandText = "SELECT * FROM " & sWorkbook

        OleDbCmd.Connection = OleDbConn

        MsgBox("OleDbCmd Created")

 

        Try

            OleDbConn.Open()

            MsgBox("Connection Opened")

            Dim dr As Data.OleDb.OleDbDataReader = OleDbCmd.ExecuteReader  [B][I]'ERROR HERE[/I][/B]

            MsgBox("Data Reader Populated")

            Dim bulkCopy As Data.SqlClient.SqlBulkCopy = New Data.SqlClient.SqlBulkCopy(sSqlConnectionString)

            MsgBox("Data Reader and BulkCopy Objects Created")

            bulkCopy.DestinationTableName = sSQLTable

            MsgBox("Destination Table Assigned")

            bulkCopy.WriteToServer(dr)

            MsgBox("Data Written To Server")

            OleDbConn.Close()

            MsgBox("Connection Closed")

        Catch ex As Exception

            'Add Code here to log error and send user to error screen

            MsgBox(ex.Message.ToString & ":" & ex.Source.ToString & ":" & ex.StackTrace.ToString & ":" & ex.InnerException.ToString)

        End Try

 

        'Test to see if data imported successfully

        'Get Count of Records in destTbl

        'If good return 1 - table has records

        'If bad return 0 - table has no records

 

        'if return 1 then move records into correct channel table

        'Clear destTbl

        'Update transfersheet table with import date & Time

        'Update Processing Log table

        'Update Status & Alerts

        'Update Import Checklist

 

        'If Return 0 then

        'Update Processing Log Table with failed process

        'Update Status & Alerts with failed process

         Return 1

 

    End Function

 

[cjard]

I ... havn't quite figured out how to use its debugging

Please do:
How to use the visual studio debugger - Google Search