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
Thanks