We help IT Professionals succeed at work.

Oledbconnection

anusdesai
anusdesai asked
on
1,724 Views
Last Modified: 2008-01-09
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.
Comment
Watch Question

Author

Commented:

        ' 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

     

Commented:
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

Commented:
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
look at the strings in www.connectionstrings.com

good luck

greg

Author

Commented:
Hi,

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

Author

Commented:
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.

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

vbturbo

Commented:
sorry

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

Author

Commented:
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.

Author

Commented:

        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

Commented:
The + sign is for C# syntax

Replace it with the & and sign

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

vbturbo

Author

Commented:
Hi,

replace...no luck..

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

Commented:
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()


Author

Commented:

Hi,

they are
Payroll_Aug.xls
Payroll_Sep.xls

Regards.

Commented:
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

Commented:
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;"""
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Excellant!!!!! works gr8!!!!!!


Cheers.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.