asked on

Oledbconnection

Hi,

I am using oledbconnection to get excel sheet in a dataset and bind it to datagridview.
For the rest of the database connectivity I am using SQLclient.

When i try to open oledb connection I get error :system.data.oledb.oledbexception;invalid argument
and some reference regarding connection pooling...

I have taken precautions to close my sql connection whereever applied to.

Regards.

anusdesai

ASKER

' Dim dv1 As New DataView

Dim arr As New ArrayList

Dim j As Integer = 0

Dim strSQL As String

'------------------

Dim strFileName As String
strFileName = "C:\Payroll_Aug.xls"
Dim MyConnection As System.Data.OleDb.OleDbConnection
MyConnection = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=" + strFileName + "; " & _
"Extended Properties=""Excel 8.0;""")
Try

MyConnection.Open()

Dim x As String
x = Me.cmbSheetName.Text

Dim Sqlstring As String = "select * from [" & x & "] "
'Dim Sqlstring As String = "select * from [" & x & "] where '" & TextBox1.Text & "'='00H00053' "

Dim MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
Sqlstring, MyConnection)

MyCommand.Fill(dt)
'" & TextBox1.Text & "'

DataGridView1.DataSource = dt
DataGridView1.AllowUserToAddRows = False

Catch ex As Exception

MsgBox(ex.ToString)
Finally
MyConnection.Close()

End Try

vbturbo

Hi anusdesai

Seems like it cant find/interpret the file name.

MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source='" & strFileName & "'; Extended Properties=""Excel 8.0;""")

Or

MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source=" & strFileName & "; Extended Properties=""Excel 8.0;""")

else try paste the inner exception data here

vbturbo

vbturbo

If you try add the entire path to file and then see if the connection opens.

MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source=C:\Payroll_Aug.xls; Extended Properties=""Excel 8.0;""")

else you might have some typo error.

vbturbo

Autoeforms

look at the strings in www.connectionstrings.com

good luck

greg

anusdesai

ASKER

Hi,

Will try...and get back immediately...thanks experts....

anusdesai

ASKER

Hi,

I tried
MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source=C:\Payroll_Aug.xls; Extended Properties=""Excel 8.0;""")

it works fine....but when
MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source='" & strFileName & "'; Extended Properties=""Excel 8.0;""")

Or

MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source=" & strFileName & "; Extended Properties=""Excel 8.0;""")

same problem...

But,I have to work on & strFileName & as cant do hard coding of the file....

It depends on combobob selected text.

Regards.

vbturbo

When you select from the combobox do the get the file extension also .xls into your string " & strFileName & "; or only the file name ?

vbturbo

vbturbo

sorry

do you get the file extension also ? when doing the select

anusdesai

ASKER

hi,

yup i get file extention....

but when i want to get sheets of the worksheets which i bind to textbox its

Dim x As String
x = Me.txtsheet.Text + "$"

Regards.

anusdesai

ASKER

Dim arr As New ArrayList

Dim j As Integer = 0

Dim strSQL As String
Dim dt As New DataTable

'------------------

Dim strFileName As String
strFileName = Me.txtinp.Text '"C:\Payroll_Aug.xls"
Dim MyConnection As System.Data.OleDb.OleDbConnection
MyConnection = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=" + strFileName + "; " & _
"Extended Properties=""Excel 8.0;""")
Try

MyConnection.Open()

Dim x As String
x = Me.txtsheet.Text + "$"

Dim Sqlstring As String = "select * from [" & x & "] "
'Dim Sqlstring As String = "select * from [" & x & "] where '" & TextBox1.Text & "'='00H00053' "

Dim MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
Sqlstring, MyConnection)

MyCommand.Fill(dt)
'" & TextBox1.Text & "'

DataGridView1.DataSource = dt
DataGridView1.AllowUserToAddRows = False

Catch ex As Exception

MsgBox(ex.ToString)
Finally
MyConnection.Close()

End Try

vbturbo

The + sign is for C# syntax

Replace it with the & and sign

Dim x As String
x = Me.txtsheet.Text & "$"

vbturbo

anusdesai

ASKER

Hi,

replace...no luck..

the file opens but,it throws error before and then opens

vbturbo

And i mean - are the values in the combobox

Payroll_Aug.xls
Payroll_Sep.xls

or without extension

Payroll_Aug
Payroll_Sep

strFileName = cbomyexcellfiles.selected.value.tostring()

anusdesai

ASKER

Hi,

they are
Payroll_Aug.xls
Payroll_Sep.xls

Regards.

vbturbo

And you have the full path also ?

MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source="C:\" & '" & strFileName & "'; Extended Properties=""Excel 8.0;""")

Or

MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source="C:\" & "& strFileName & "; Extended Properties=""Excel 8.0;""")

vbturbo

vbturbo

I just tested this , and it works fine

Dim str As String = "C:\postnr.xls"

Dim sConnString1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & str & ";Extended Properties=""Excel 8.0;HDR=No;"""

ASKER CERTIFIED SOLUTION

vbturbo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anusdesai

ASKER

Excellant!!!!! works gr8!!!!!!

Cheers.