Problem Adding Query To Database
Hi, we're having a problem connecting Bartender 8.01 to XLS or CSV data. We go through the database connection wizard and if we use standard settings, the connection looks fine - i.e. list of data can be seen in browse. When we add "SELECT * FROM `Sheet1` WHERE `REF`='?ref'" (for Excel spreadsheet) or formulate a query "file.REF = ?ref" (for CSV file) & then try to view data or print we get the query dialogue OK, but when we enter a REF that exists in the data file, we either get error message #3239 with OLE DB error 0x80040E10 IDispatch error #3088 (XLS) or 'no data' (CSV) on trying to view or print data.
We are working round by using an existing data file and adapting it with new data, but would like to know how to get this setup properly. (Technician who setup last year has now left).
We are working round by using an existing data file and adapting it with new data, but would like to know how to get this setup properly. (Technician who setup last year has now left).
0
-
Shotaro Ito
★ BarTender Hero ★
Hi WestNab,
do you use custom query?
Usually Excel sheet name comes with '$' char after sheet name 'Sheet1$' in OLEDB, So populated query will be like
[sql]SELECT * FROM `Sheet1$` WHERE `REF` = '?ref'[/sql]
(when query prompt "ref" is specified.)
also make sure you closed MS Excel (or at least no one else open the excel file) when connect / print by BarTender.
For CSV data, try quote value by ""(double quotation) to prevent incorrect import.
[sql]"FNAME","LNAME","AGE"
"John","doe","28"[/sql]
Hope that helps!0 -
[quote name='Shotaro I -Seagull Support' timestamp='1314325353' post='451']
Hi WestNab,
do you use custom query?
Usually Excel sheet name comes with '$' char after sheet name 'Sheet1$' in OLEDB, So populated query will be like
[sql]SELECT * FROM `Sheet1$` WHERE `REF` = '?ref'[/sql]
(when query prompt "ref" is specified.)
also make sure you closed MS Excel (or at least no one else open the excel file) when connect / print by BarTender.
For CSV data, try quote value by ""(double quotation) to prevent incorrect import.[attachment=293:rioPro-a-2012.btw]
[sql]"FNAME","LNAME","AGE"
"John","doe","28"[/sql]
Hope that helps!
[/quote]
No, sorry it didn't ... and we have the same problem this year! Excel is closed. DB connection is linked to Excel (97-2003 format) file "cards12a.xls" with sheet called "cards":
[sql]SELECT EXAMREF,NAME,BIRTHDAY,BARCODE,PHOTO FROM `cards$` WHERE `EXAMREF`='?Examref'[/sql]
If I untick 'Use custom SQL query' then I can browse the data fine. But I need to define the user prompt for efficient workflow when processing 30 students tomorrow & 1000 students on Tuesday 28th!0 -
Drop the quotes from around the query prompt name:
[code]
SELECT EXAMREF,NAME,BIRTHDAY,BARCODE,PHOTO FROM `cards$` WHERE `EXAMREF` = ?Examref
[/code]0
请先登录再写评论。
评论
3 条评论