1.11M Members

Connection string problem using a combobox

 
0
 

Hello i have a problem on connecting my database.
i have this kind of code which would get the item in the combobox and use it as my connection

Dim ConFunnelLocation As String = "C:\Database\Funnel\" & Form_Funnel_Report.Combo_Funnel_EBU.Text & ".xls"

and a table

Dim TblFunnelCon As String = Form_Funnel_Report.Combo_Funnel_AM.Text & "$"

so this could be my code.

con.ConnectionString = ("provider=Microsoft.Jet.OLEDB.4.0; Data Source='" & ConFunnelLocation & "';Extended Properties=Excel 8.0;")
    con.Open()
    dt.Clear()

    ds.Tables.Add(dt)
    Try

        With Form_Funnel_Report

            Dim da As New OleDbDataAdapter("Select * from [" & TblFunnelCon & "]", con)
            da.Fill(dt)

But it cannot open my database.

unless i change my declaration to the specific name of my Table and my Excel file like this

Dim ConFunnelLocation As String =  "C:\Database\Funnel\EBU 1.xls"
Dim TblFunnelCon As String = "Sheryl Manuel$"

this work fine but i need to use combobox so it will lessen my code and it can be more flexible.

please help me. thank you in advanced.

 
1
 

set a break point and check the values of the strings.

 
1
 

Hi,

Try doing something like this:

Dim ConFunnelLocation As String

If trim(Form_Funnel_Report.Combo_Funnel_EBU.Text) <> "" then
    ConFunnelLocation = "C:\Database\Funnel\" & trim(Form_Funnel_Report.Combo_Funnel_EBU.Text) & ".xls"
else
     msgbox("Please select the excel sheet")
     exit sub
end if
 
0
 

I think your problem may be just as George and tins have stated.

I think you are simply passing in a string that may have an extra space or a character that's not expected.

Step through and look at the autos to see what is happening with your code.

 
0
 

Just occurred to me, if there's a space in the filename you may have to do this:

Dim ConFunnelLocation As String

If trim(Form_Funnel_Report.Combo_Funnel_EBU.Text) <> "" then
    ConFunnelLocation = "C:\Database\Funnel\[" & trim(Form_Funnel_Report.Combo_Funnel_EBU.Text) & "].xls"
else
     msgbox("Please select the excel sheet")
     exit sub
end if

ACTUALLY FORGET that - rush of blood to the head!!

Isn't it about time forums rewarded their contributors?

Earn rewards points for helping others. Gain kudos. Cash out. Get better answers yourself.

It's as simple as contributing editorial or replying to discussions labeled or OP Kudos

You
This article has been dead for over six months: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: