[Solved] Help With embedded sql syntax

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

[Solved] Help With embedded sql syntax

Post by gkick »

Hi All,

Having trouble with some validation macro receiving a syntax error message. Can not figure out whats incorrect or missing.

Code: Select all

Sub validateBilling
oForm = ThisComponent.DrawPage.Forms.getByName("frmMain")
iValue = oForm.Columns.getByName("groupid").Value    
msgbox " " & iValue

oConn = ThisDatabaseDocument.DataSource.getConnection("","") 		
dim SQL as object
dim myVar AS VARIANT 
SQL=oConn.createStatement()
result=SQL.executeQuery("SELECT COUNT(""billto"") FROM ""tblmembers""  WHERE ""billto"" = true AND  ""accID_fk"" = '" & iValue & '"" ) 
result.next
myVar = result.getInt(1)
msgbox " " & myVar
Maybe someone has better eyes, thank you
Last edited by gkick on Thu Sep 15, 2022 5:57 am, edited 1 time in total.
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Help With embedded sql syntac

Post by robleyd »

I can see what appears to be a mismatched double quote '" & iValue & '""

If that isn't the problem, show the error you get and the associated line if needed.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Help With embedded sql syntac

Post by gkick »

Thanks,
Figured out the quotes, but still stuck with a WHERE problem as there are 2 where conditions.
2022-09-15 00 32 33.png
2022-09-15 00 32 33.png (27.92 KiB) Viewed 2251 times
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Help With embedded sql syntac

Post by gkick »

Got it, removed the second WHERE clause,

result=SQL.executeQuery("SELECT COUNT(""billto"") FROM ""tblMembers"" WHERE ""billto"" = true AND ""accID_fk"" = '" & iValue & "'" )

Thanks you!
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
Post Reply