1,105,232 Community Members

Connection string problem using a combobox

Member Avatar
ImZick
Posting Whiz in Training
201 posts since Dec 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 4 [?]
Skill Endorsements: 4 [?]
 
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.

Member Avatar
tinstaafl
Postaholic
2,012 posts since Jun 2010
Reputation Points: 559 [?]
Q&As Helped to Solve: 402 [?]
Skill Endorsements: 35 [?]
 
1
 

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

Member Avatar
G_Waddell
Practically a Posting Shark
821 posts since Nov 2009
Reputation Points: 131 [?]
Q&As Helped to Solve: 137 [?]
Skill Endorsements: 13 [?]
 
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
Member Avatar
Begginnerdev
Veteran Poster
1,142 posts since Apr 2010
Reputation Points: 251 [?]
Q&As Helped to Solve: 191 [?]
Skill Endorsements: 15 [?]
 
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.

Member Avatar
G_Waddell
Practically a Posting Shark
821 posts since Nov 2009
Reputation Points: 131 [?]
Q&As Helped to Solve: 137 [?]
Skill Endorsements: 13 [?]
 
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!!

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