System.Data.OleDb.OleDbException invalid argument

Question

I'm uploading an excel sheet, and want to move its data to another table in the database. The last two days I've been getting an error (System.Data.OleDb.OleDbException invalid argument), however, if I design a very simple application, it works fine. Please Help me

Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("TERAMSConnectionString").ConnectionString)
            Dim path As String = FileUpload1.PostedFile.FileName
            Dim excelConnectionString As String = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + (path + ";Extended Properties=Excel 12.0;Persist Security Info=False"))
            Dim excelConnection As OleDbConnection = New OleDbConnection(excelConnectionString)
            conn.Open()
            excelConnection.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("Select * from [Sheet1$]", excelConnection)
            'Clears any previous data
            Dim sClearSQL = "DELETE FROM Desktop_Compare "
            Dim SqlCmd As SqlCommand = New SqlCommand(sClearSQL, conn)
            SqlCmd.ExecuteNonQuery()
            Dim dReader As OleDbDataReader = cmd.ExecuteReader
            Dim sqlBulk As SqlBulkCopy = New SqlBulkCopy(conn)
            excelConnection.Close()
            conn.Close()
        End Using


Line 74:             Dim excelConnection As OleDbConnection = New OleDbConnection(excelConnectionString)
Line 75:             conn.Open()
Line 76:             **excelConnection.Open()**
Line 77:             Dim cmd As OleDbCommand = New OleDbCommand("Select * from [Sheet1$]", excelConnection)
Line 78:             'Clears any previous data

At which point are you getting the exception? AS a side note, I would recommend using & to concatenate strings instead of + (I've seen the + cause some strange issues as it tries to "add" the strings together instead of joining them) — Grahamvs
@Grahamvs Ok, I am getting error at "excelConnection.Open()" which tells user code is unhandled. — Miank

Grahamvs Grahamvs · Accepted Answer · 2013-10-24T10:04:29

Try changing your connection string to:

Dim excelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & ";"

If that doesn't work, try changing / adding the Extended Properties and Persist Security Info one at a time, as it appears either you are having an issue with the ACE driver, or one of the additional arguments are causing an issue

As for your users getting the unhandled error, you could try something like this:

        Dim excelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & ";"

        Using excelConnection As OleDb.OleDbConnection = New OleDb.OleDbConnection(excelConnectionString)
            Try
                excelConnection.Open()

                Using cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("Select * from [Sheet1$]", excelConnection)

                    'Clears any previous data
                    Dim sClearSQL As String = "DELETE FROM Desktop_Compare "
                    Using SqlCmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(sClearSQL, conn)
                        SqlCmd.ExecuteNonQuery()
                    End Using

                    Dim dReader As OleDb.OleDbDataReader = cmd.ExecuteReader
                    Dim sqlBulk As SqlClient.SqlBulkCopy = New SqlClient.SqlBulkCopy(conn)
                    excelConnection.Close()

                End Using

            Catch ex As Exception
                ' Handle errors here

            Finally
                If Not excelConnection.State = ConnectionState.Closed Then
                    Try
                        excelConnection.Close()
                    Catch ex As Exception
                    End Try
                End If

            End Try

        End Using

System.Data.OleDb.OleDbException invalid argument

2 Answers