DBCONNECT SQL QUERY Excel File Display Result Please Help

[IKnowNothing]

Please Help.
I need to query an excel file for the Name given the AccountNumber.
Then display the Name in a message box onscreen.
I think I am either not connected to the Excel file , or not successfully passing values between arrays and variables from search results.
Is there an easy way to test a DBConnection?
Also, here is my Excel data

EXCEL FILE CONTENTS:
Name AccountNumber
Electric 500
Building Maint 1000
Water 2000
Painting 3000
Landscaping 4000
Office Expenses 5000

Code: Select all

[code]
//Connect to Datasource
DBConnect>Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Users\My\Desktop\Accounts.xls; Extended Properties=â€

[Marcus Tettmar]

Is there an easy way to test a DBConnection?

Yes, use the debugger. Step past the DBConnect line and look at the return value.

[JRL]

I don't know where you found the connection string you're using but it has some issues. First remove the spaces between the semicolon delimited parameters. Then remove the quotes around Excel etc. Then eliminate the Header exists parameter. I don't think that works with excel.

Then in you SQL string, since you are requesting a number as user input, the %myval% variable cannot have quotes around it.

The following works for me

Code: Select all

//Connect to Datasource
DBConnect>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\My\Desktop\Accounts.xls;Extended Properties=Excel 8.0;,dbH

//Display Input Ask User For Account Number
Input>myval,What is the Account Number to search?

//Perform SELECT query
Let>SQL=select NAME from [Sheet1$] where AccountNumber=%myval%

DBQuery>dbh,SQL,Sheet1Results,numrecs,numfields

//Display message with Name of AccountNumber 
Message>'%Sheet1Results_1_1%'
 
//Close database connection
DBClose>dbH

[IKnowNothing]

Thanks for the timely response.
It now works.
Keep up the good work.