Problem Adding Query To Database 关注

0
Avatar
Legacy Poster

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).

3 评论

0
Avatar
Shotaro Ito
版主
评论操作 固定链接

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
Avatar
Legacy Poster
评论操作 固定链接

[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
Avatar
Ian Cummings
版主
评论操作 固定链接

Drop the quotes from around the query prompt name:

[code]
SELECT EXAMREF,NAME,BIRTHDAY,BARCODE,PHOTO FROM `cards$` WHERE `EXAMREF` = ?Examref
[/code]

请先登录再写评论。