PureBasic Forums - English

hello!

i am just trying to read an excel-sheet (name: "kv.xls", first table = "Tabelle1" ) (MS office 2000), but it does not work.

i can open the 'database', but my select-query is not accepted.

is there anyone who has an idea?

thanks a lot!

kimio

----------------------------------------------------


pw.s:pw=""
user.s:user=""
db_name.s:db_name="kv"
odbc.s:odbc="kv" ;ODBC-driver
conn.b:conn=4
;Gosub init_db
Gosub open_db

query.s = "Select * FROM Tabelle1"
Gosub db_query

While NextDatabaseRow() <> 0 ; gefundene einträge abarbeiten

sa$=GetDatabaseString(0) ;reihe 0 = pho NN
;sb$=GetDatabaseString(1) ;reihe 1 = pho VN+NN
;sc$=GetDatabaseString(2) ;reihe 2 = pho Fa.
Debug sa$
;Debug sb$
;Debug sc$

Wend



;CloseDatabase(3)
CloseDatabase(4)

End


; >>>>>>>> subroutinen

db_query: ;führt in geöffneter db-verbindung die db-query query.s aus
If DatabaseQuery(query.s) = 0
MessageRequester("db_query:", "fehlgeschlagen",#PB_MessageRequester_Ok)
End
EndIf
Return


open_db: ;subroutine - öffnet datenbankverbindung
; pw.s -> password
; user.s -> user
; db_name.s -> db.name
; odbc.s -> odbc-name
; conn.b -> nummer der db-verbindung

Gosub init_db ;initialisieren (auswählen der db)

If OpenDatabase(conn, db_name, user,pw) = 0
MessageRequester("OpenDB fehlgeschlagen", "Fehler",#PB_MessageRequester_Ok)
EndIf

Return

init_db:
InitDatabase()
If ExamineDatabaseDrivers()
While NextDatabaseDriver()
If DatabaseDriverName() = odbc
Base_OK.b = 1
EndIf
Wend
EndIf
If Base_OK = 0
MessageRequester("ODBC-driver:", "not found",#PB_MessageRequester_Ok)
EndIf
Return

Long time since I have done any SQL but is it case-sensitive? If so then maybe "SELECT..."?

try tablename with 'tabelle1$'

thanks a lot!

ok, the SELECT statement has to be written in upper cases....

and the statement doesnt need a $ behind 'tabelle1'. this is because the whole query is a string, so you dont have to use a '$' behind the table-name (i have also tried it with $ behind tabelle1).

my query still does not work.

my question is now:
if you use the ODBC-interface for communication with an excel-sheet do i use common SQL-syntax? i have not found any hint for this, only for opening a connection to the excel-sheet via ODBC

thanks in advance!

kimio

What is the value of the result? Maybe it indicates an SQL/ODBC error. Have you actually set up an ODBC data source to the sheet? Have you tried accessing it via MS Query?

Hi,
This is you code:
query.s = "Select * FROM Tabelle1"

nad this is my code:

query.s = "Select * FROM Tabelle1;"

ByBy
Manolo

I had a go at this last night. I didn't need the semi-colon but I did need to name the table for the Excel ODBC driver to see it. After this it worked fine.

Win98se, Off97, ODBC32 3.5

ok....
my ODBC looks like:

DSN (data source name): kv (excel-table='kv.xls'; sheet='Tabelle1', i use excel 2000)

version: excel 97-2000

path to excel-folder: ok

write protected

look at my example on top: the database-connection is opened correctly (sub open_db:), i get no error-message.

can you perhaops show me a working pb-code (opendatabase and sql-query)?


thanks a lot!

kimio

Kimio, I think all you need to do is select the region on the worksheet containing the table and enter a name for the table in the edit box on the left of the toolbar. Then use this name in your SQL statement, ie, XXX in "... FROM XXX".