System.Data.OleDb.OleDbException无效的参数
我正在上传一张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
尝试将连接字符串更改为：
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
首先将excel数据加载到datagridview中。 之后，所有来自gridview的数据，您必须将它们插入到表格数据库中。 它会解决你的问题。 你必须试试这个！
首先投入 -
1按钮 - btn打开 -
1文本框 - txtOpen -
1按钮 - btnLoad-
1 datagridview - DataGridView3-
1个combobox - 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
在Load按钮下面放置这个代码
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")
我希望它会起作用！ :)
链接地址: http://www.djcxy.com/p/44029.html上一篇: System.Data.OleDb.OleDbException invalid argument
下一篇: nested select statements taking too long to load on SQL server