Apache OpenOffice Community Forum

Posted: **Sun Nov 06, 2022 5:17 pm**

I'm new to OpenOffice Base and find the extension access2base very usefull since I was programming Access DB a while ago.
I'm trying now to implement the UseVariablesInSQL as described in the http://www.access2base.com/access2base. ... lesInSQL]].

Code: Select all

Sub updateArt_click(e as Object)
    Dim rs As Object
    Dim frm As Object
    Dim sName As String  'search Parameter
    Set frm = Application.Forms("frm_Etiketten_drucken")
    sName = frm.Controls("cmbSorte").Value
    MsgBox sName   'shows the selected value from cmbSorte
    
    Set rs = Application.CurrentDb().RunSQL("SELECT art FROM artikel WHERE sorte_de = " & sName )
    MsgBox rs  'Returns Error#1523 
    
    'further code will follow, when the RunSQL-code works
End Sub

as marked in the code, the RunSQL code gives an Error:

Code: Select all

Error #1523 (SQL Error, SQL statement = 'SELECT art FROM artikel WHERE sorte_de = Bittenfelder') aufgetreten in ein Funktionsaufruf 'Database.RunSQL'

As far as I understand is this the cause of the missing quotes around the string 'Bittenfelder'?
In the original description on access2base the following code is used to add quotes :

Code: Select all

Sub updateArt_click(e as Object)
    Dim rs As Object
    Dim frm As Object 
    Dim sName As String  'search Parameter
    Const cstQuote = "'"		'	Single quote
    Set frm = Application.Forms("frm_Etiketten_drucken")
    sName = Join(Split(frm.Controls("cmbSorte").Value, cstQuote), cstQuote & cstQuote)   'Replace single ' by ' '
    MsgBox sName  'shows the selected value from cmbSorte
    Set rs = Application.CurrentDb().RunSQL("SELECT art FROM artikel WHERE sorte_de = " & cstQuote & sName & cstQuote)
    MsgBox rs
    
    'further code will follow, when the RunSQL-code works
End Sub

In this case I get the following error: No access to object. Incorrect use of object.
I've tried already all possible variations to quote out the string, but without success.

Posted: **Mon Nov 07, 2022 10:05 pm**

geom wrote: ↑Sun Nov 06, 2022 5:17 pm SQL Error, SQL statement = 'SELECT art FROM artikel WHERE sorte_de = Bittenfelder'

Did you understand that WHERE clause must be quoted (") with string?

Posted: **Mon Nov 07, 2022 11:39 pm**

This line:
sName = Join(Split(frm.Controls("cmbSorte").Value, cstQuote), cstQuote & cstQuote) 'Replace single ' by ' '
is used to replace a single quote into a double single quote in case the Value contains a single quote. Is there error in this line?

But what happens when you use:

Code: Select all

Set rs = Application.CurrentDb().RunSQL("SELECT art FROM artikel WHERE sorte_de = '" & sName & "'")

Note the added single quotes.

Keep in mind that there are many type of quotes. Sometimes quotes are transformed by some MS editors into the wrong types. So be careful. But the code you copied here looks OK.

Note: In the page you are referring to, they use [ ] around field names in the where clause. I just noticed, not sure if this is mandatory...

Apache OpenOffice Community Forum

UseVariablesInSQL in access2base

UseVariablesInSQL in access2base

Re: UseVariablesInSQL in access2base

Re: UseVariablesInSQL in access2base