[Solved] Another simple query problem - please help.

Creating tables and queries
Post Reply
Zeko
Posts: 14
Joined: Mon Jul 14, 2008 5:23 pm

[Solved] Another simple query problem - please help.

Post by Zeko »

I'm a newbie reasonably competent in Basic; but new to OpenOffice.
I reviewed other threads about using a query with a variable but I can't seem to make it work.
I have a registered database "Words" with a table "Clients". I'm trying to just supply a name and retrieve the record from Clients that matches. Without a test variable I can find all records OK; but when I use a variable and run the macro I get this error message:
Type: com.sun.star.sdbc.SQLException
Message: Column not found: Yukio in statement [Select * FROM "Clients" WHERE "FirstName" = "Yukio"].

It seems to interpret my variable as a column name. How do I get around this?

Here's my code:

Code: Select all

Sub DBQUERY
Dim DatabaseContext As Object
Dim DataSource As Object
Dim Connection As Object
Dim InteractionHandler as Object
Dim Statement As Object
Dim ResultSet As Object
dim Fname as string
dim SearchString as string
  Fname = "Yukio"
 SearchString = "Select * from Clients where FirstName = " & Fname

 DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource = DatabaseContext.getByName("Words")
 
If Not DataSource.IsPasswordRequired Then
  Connection = DataSource.GetConnection("","")
Else
  InteractionHandler = createUnoService("com.sun.star.sdb.InteractionHandler")
  Connection = DataSource.ConnectWithCompletion(InteractionHandler)
End If
 
Statement = Connection.createStatement()
ResultSet = Statement.executeQuery(SearchString)   ' I GET AN ERROR MESSAGE WHEN EXECUTING THIS LINE
 MsgBox "Starting While"
If Not IsNull(ResultSet) Then
  While ResultSet.next
    MsgBox ResultSet.getString(1)
  Wend
  else MsgBox "None Found"
End If
End Sub
I appreciate any help anyone can offer.
Thanks
Last edited by Hagar Delest on Tue Jul 15, 2008 10:40 am, edited 1 time in total.
Reason: tagged the thread as Solved.
OOo 2.4.X on MS Windows Vista
User avatar
squenson
Volunteer
Posts: 1885
Joined: Wed Jan 30, 2008 9:21 pm
Location: Lausanne, Switzerland

Re: Another simple query problem - please help.

Post by squenson »

My first idea --without being able to test it right now-- would be to write Fname = " 'Yukio' " (note the single quotes. The spaces can be removed they are just here for lisibility.
LibreOffice 4.2.3.3. on Ubuntu 14.04
Zeko
Posts: 14
Joined: Mon Jul 14, 2008 5:23 pm

Re: Another simple query problem - please help.

Post by Zeko »

That' s IT! It works. Thanks so much. I didn't realize single vs double quotes mattered. I guess I have a lot to learn here.
I appreciate the quick reply too Squenson.
OOo 2.4.X on MS Windows Vista
Post Reply