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
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
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!
First Put --
1 button - btnOpen -
1 textbox - txtOpen -
1 button again - btnLoad-
1 datagridview - DataGridView3-
1 combobox - cboSheet
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! :)
