System.Data.OleDb.OleDbException参数无效

Question

我正在上载excel工作表，并希望将其数据移动到数据库中的另一个表中。最近两天我收到了一个错误(system.data.oledb.oledbException invalid argument)，但是，如果我设计了一个非常简单的应用程序，它就可以正常工作。请帮帮我

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

Answer 1

尝试将连接字符串更改为：

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

如果这不起作用，请尝试一次更改/添加扩展属性和持久安全信息，因为您的ACE驱动程序出现了问题，或者某个附加参数导致了问题

至于您的用户得到未处理的错误,您可以尝试如下所示：

        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