System.Data.OleDb.OleDbException无效参数 [英] System.Data.OleDb.OleDbException invalid argument
问题描述
我正在上载Excel工作表,并希望将其数据移动到数据库中的另一个表中.最近两天,我一直遇到错误(System.Data.OleDb.OleDbException invalid argument
),但是,如果我设计一个非常简单的应用程序,则可以正常工作.请帮助我
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
推荐答案
首先,您将excel数据加载到datagridview中.之后,所有来自gridview的数据,必须将它们插入到表数据库中.它会解决您的问题. 您必须尝试一下!
First you load the excel data into datagridview. after that, all the data from gridview, you must insert them into table database. it will solve your problem. You must try this!
首次认沽-
1个按钮-btnOpen-
1 button - btnOpen -
1个文本框-txtOpen-
1 textbox - txtOpen -
1个按钮-btnLoad-
1 button again - btnLoad-
1个datagridview-DataGridView3-
1 datagridview - DataGridView3-
1个组合框-cboSheet
1 combobox - cboSheet
在OpenButton中.输入此代码
in OpenButton. Put this code
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
在加载"按钮中将此代码放在下面
in Load button Put this code below
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
然后将所有数据保存到表数据库中.在下面使用此代码
Then save all the data into table database. Use this code below
'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")
我希望它能起作用! :)
I hope it will works! :)
这篇关于System.Data.OleDb.OleDbException无效参数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!