System.Data.OleDb.OleDbException无效参数-Java 学习之路

我'm uploading an excel sheet, and want to move its data to another table in the database. The last two days I'已经收到错误（ 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

2 回答

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

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

如果这不起作用，请尝试一次更改/添加 Extended Properties 和 Persist Security Info ，因为您看到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

回复于 2024-05-03T12:59:00+08:00

首先，将excel数据加载到datagridview中 . 之后，来自gridview的所有数据，必须将它们插入到表数据库中 . 它会解决你的问题 . 你必须试试这个！

第一次投入 -

1个按钮 - btnOpen -

1个文本框 - txtOpen -

再次按下1个按钮 - btnLoad-

1 datagridview - DataGridView3-

1个组合框 - cboSheet

在OpenButton中 . 把这段代码

Dim OpenFileDialog1 As New OpenFileDialog()
    Dim constr As String
    Dim con As OleDb.OleDbConnection

    Try

        OpenFileDialog1.Filter = "Excel Files | *.xlsx; *.xls; *.xlsm;"

        If OpenFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
            Me.txtOpen.Text = OpenFileDialog1.FileName

            constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + txtOpen.Text + ";Excel 12.0 Xml;HDR=YES"
            con = New OleDb.OleDbConnection(constr)
            con.Open()

            cboSheet.DataSource = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
            cboSheet.DisplayMember = "TABLE_NAME"
            cboSheet.ValueMember = "TABLE_NAME"
        End If
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try

在加载按钮中将此代码放在下面

Dim constr As String
    Dim dt As DataTable
    Dim con As OleDbConnection
    Dim sda As OleDbDataAdapter
    Dim row As DataRow

    Try
        constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + txtOpen.Text + ";Excel 12.0 Xml;HDR=YES"
        con = New OleDbConnection(constr)
        sda = New OleDbDataAdapter("Select * from [" + cboSheet.SelectedValue + "]", con)
        dt = New DataTable
        sda.Fill(dt)

        For Each row In dt.Rows
            DataGridView3.DataSource = dt

        Next
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try

然后将所有数据保存到表数据库中 . 请使用以下代码

'Dim nu As NullReferenceException
    Dim cmd As OleDbCommand
    connection.Open()
    For i As Integer = 0 To DataGridView3.Rows.Count - 2 Step +1

        'cmd = New OleDbCommand("INSERT INTO QAtable([CompanyCode],[Vendor],[G/L_Account],[DocumentType],[DocumentNumber],[DocumentDate],[EntryDate],[PostingDate],[NetDueDate],[ClearingDate],[ClearingDocument],[DocumentCurrency],[AmountInDocumentCurrency],[LocalCurrency],[AmountInLocalCurrency],[LocalCurrency2],[AmountInLocalCurrency2],[LocalCurrency3],[AmountInLocalCurrency3],[PartnerBankType],[PaymentBlock],[PaymentMethod],[Text],[DocumentHeaderText],[Assignment],[Username],[ABSvalueInAUD],[High/Low],[AmountRange]) VALUES (@CompanyCode,@Vendor,@G/L_Account,@DocumentType,@DocumentNumber,@DocumentDate,@EntryDate,@PostingDate,@NetDueDate,@ClearingDate,@ClearingDocument,@DocumentCurrency,@AmountInDocumentCurrency,@LocalCurrency,@AmountInLocalCurrency,@LocalCurrency2,@AmountInLocalCurrency2,@LocalCurrency3,@AmountInLocalCurrency3,@PartnerBankType,@PaymentBlock,@PaymentMethod,@Text,@DocumentHeaderText,@Assignment,@Username,@ABSvalueInAUD,@High/Low,@AmountRange)", connection)

        cmd = New OleDbCommand("INSERT INTO QAtable([CompanyCode],[Vendor],[GLAccount],[DocumentType],[DocumentNumber],[Reference],[DocumentDate],[EntryDate],[PostingDate],[NetDueDate],[ClearingDate],[ClearingDocument],[DocumentCurrency],[AmountInDocumentCurrency],[LocalCurrency],[AmountInLocalCurrency],[LocalCurrency2],[AmountInLocalCurrency2],[LocalCurrency3],[AmountInLocalCurrency3],[PartnerBankType],[PaymentBlock],[PaymentMethod],[Text],[DocumentHeaderText],[Assignment],[Username],[IncludeExclude],[GBSNonGBS],[ABSvalueInAUD],[HighLow],[AmountRange],[User]) VALUES (@CompanyCode,@Vendor,@GLAccount,@DocumentType,@DocumentNumber,@Reference,@DocumentDate,@EntryDate,@PostingDate,@NetDueDate,@ClearingDate,@ClearingDocument,@DocumentCurrency,@AmountInDocumentCurrency,@LocalCurrency,@AmountInLocalCurrency,@LocalCurrency2,@AmountInLocalCurrency2,@LocalCurrency3,@AmountInLocalCurrency3,@PartnerBankType,@PaymentBlock,@PaymentMethod,@Text,@DocumentHeaderText,@Assignment,@Username,@IncludeExlcude,@GBSNonGBS,@ABSvalueInAUD,@HighLow,@AmountRange,@User)", connection)
        cmd.Parameters.Add("@CompanyCode", OleDbType.VarChar).Value = DataGridView3.Rows(i).Cells(0).Value.ToString()
        cmd.Parameters.Add("@Vendor", OleDbType.VarChar).Value = DataGridView3.Rows(i).Cells(1).Value.ToString()
        cmd.Parameters.Add("@GLAccount", OleDbType.VarChar).Value = DataGridView3.Rows(i).Cells(2).Value.ToString()
        cmd.Parameters.Add("@DocumentType", OleDbType.VarChar).Value = DataGridView3.Rows(i).Cells(3).Value.ToString()
        cmd.Parameters.Add("@DocumentNumber", OleDbType.VarChar).Value = DataGridView3.Rows(i).Cells(4).Value.ToString()

        cmd.Parameters.Add("@Reference", OleDbType.VarChar).Value = DataGridView3.Rows(i).Cells(5).Value.ToString()

        cmd.Parameters.Add("@DocumentDate", OleDbType.VarChar).Value = DataGridView3.Rows(i).Cells(6).Value.ToString()
        cmd.Parameters.Add("@EntryDate", OleDbType.VarChar).Value = DataGridView3.Rows(i).Cells(7).Value.ToString()
        cmd.Parameters.Add("@PostingDate", OleDbType.VarChar).Value = DataGridView3.Rows(i).Cells(8).Value.ToString()
        cmd.Parameters.Add("@NetDueDate", OleDbType.VarChar).Value = DataGridView3.Rows(i).Cells(9).Value.ToString()
        cmd.Parameters.Add("@ClearingDate", OleDbType.VarChar).Value = DataGridView3.Rows(i).Cells(10).Value.ToString()
        cmd.Parameters.Add("@ClearingDocument", OleDbType.VarChar).Value = DataGridView3.Rows(i).Cells(11).Value.ToString()
        cmd.Parameters.Add("@DocumentCurrency", OleDbType.VarChar).Value = DataGridView3.Rows(i).Cells(12).Value.ToString()
        cmd.Parameters.Add("@AmountInDocumentCurrency", OleDbType.VarChar).Value = DataGridView3.Rows(i).Cells(13).Value.ToString()
        cmd.Parameters.Add("@LocalCurrency", OleDbType.VarChar).Value = DataGridView3.Rows(i).Cells(14).Value.ToString()
        cmd.Parameters.Add("@AmountInLocalCurrency", OleDbType.VarChar).Value = DataGridView3.Rows(i).Cells(15).Value.ToString()
        cmd.Parameters.Add("@LocalCurrency2", OleDbType.VarChar).Value = DataGridView3.Rows(i).Cells(16).Value.ToString()
        cmd.Parameters.Add("@AmountInLocalCurrency2", OleDbType.VarChar).Value = DataGridView3.Rows(i).Cells(17).Value.ToString()
        cmd.Parameters.Add("@LocalCurrency3", OleDbType.VarChar).Value = DataGridView3.Rows(i).Cells(18).Value.ToString()
        cmd.Parameters.Add("@AmountInLocalCurrency3", OleDbType.VarChar).Value = DataGridView3.Rows(i).Cells(19).Value.ToString()
        cmd.Parameters.Add("@PartnerBankType", OleDbType.VarChar).Value = DataGridView3.Rows(i).Cells(20).Value.ToString()
        cmd.Parameters.Add("@PaymentBlock", OleDbType.VarChar).Value = DataGridView3.Rows(i).Cells(21).Value.ToString()
        cmd.Parameters.Add("@PaymentMethod", OleDbType.VarChar).Value = DataGridView3.Rows(i).Cells(22).Value.ToString()
        cmd.Parameters.Add("@Text", OleDbType.VarChar).Value = DataGridView3.Rows(i).Cells(23).Value.ToString()
        cmd.Parameters.Add("@DocumentHeaderText", OleDbType.VarChar).Value = DataGridView3.Rows(i).Cells(24).Value.ToString()
        cmd.Parameters.Add("@Assignment", OleDbType.VarChar).Value = DataGridView3.Rows(i).Cells(25).Value.ToString()
        cmd.Parameters.Add("@Username", OleDbType.VarChar).Value = DataGridView3.Rows(i).Cells(26).Value.ToString()

        cmd.Parameters.Add("@IncludeExclude", OleDbType.VarChar).Value = DataGridView3.Rows(i).Cells(27).Value.ToString()
        cmd.Parameters.Add("@GBSNonGBS", OleDbType.VarChar).Value = DataGridView3.Rows(i).Cells(28).Value.ToString()

        cmd.Parameters.Add("@ABSvalueInAUD", OleDbType.Double).Value = DataGridView3.Rows(i).Cells(29).Value()
        cmd.Parameters.Add("@HighLow", OleDbType.VarChar).Value = DataGridView3.Rows(i).Cells(30).Value.ToString()
        cmd.Parameters.Add("@AmountRange", OleDbType.VarChar).Value = DataGridView3.Rows(i).Cells(31).Value.ToString()
        cmd.Parameters.AddWithValue("@User", txtUser.Text)

        cmd.ExecuteNonQuery()




    Next
    connection.Close()
    MessageBox.Show("All Data Inserted")

我希望它会奏效！ :)

回复于 2024-05-03T12:59:00+08:00

System.Data.OleDb.OleDbException无效参数

2 回答

相关问题