[Solved] SQL error in base update macro

[gkick]

Hi,

I am stuck with variable not defined or insufficient rights.
LO 7.1.2 HSQL 2.51 on Win10

Cant figure the error in the statement trying to run an update using 2 variables.

Code: Select all

Sub moveItem
dim oForm1 as object
dim scurr 
msgbox "Selected Item will be transferred to the location chosen"
oForm1=ThisComponent.Drawpage.Forms.act.Oselect.Area.SubDiv.qrfilter.getByName("tcq")
scurr = oForm1.getByName("sub_fk").currentvalue
'if not scurr Then
'Exit Sub
'End If
print " " & scurr


	dim snew AS VARIANT 
	oConn = ThisDatabaseDocument.DataSource.getConnection("","") 		
	SQL=oConn.createStatement()
	result=SQL.executeQuery("SELECT ""ifrto"" FROM ""tblFilter""" )
	result.next
	snew = result.getInt(1)
msgbox " " & snew

			oConn = ThisDatabaseDocument.DataSource.getConnection("","") 		
			SQL=oConn.createStatement()			
			result=SQL.executeQuery("UPDATE ""tblInventory""  SET ""sub_fk"" = " & snew  & "WHERE ""sub_fk"" = & scurr ")  


	msgbox "Item has been transferred successfully"
	'reload forms	
End Sub

The error occurs on the last statement.
Hope someone has a better eye, thanks

[Villeroy]

Code: Select all

sUpdate = "UPDATE ""tblInventory""  SET ""sub_fk"" = " & snew  & "WHERE ""sub_fk"" = & scurr "
msgbox sUpdate

[gkick]

@Villeroy

Thanks for that, yet I still get the ... word message. It does not seem to like the second variable because if I substitute with a number it works.

[Villeroy]

You include the last part with the variable name in the outer pair of quotes, so the expression ends literally with
= & scurr
Put the final quote behind the =

Code: Select all

sUpdate = "UPDATE ""tblInventory""  SET ""sub_fk"" = " & snew  & "WHERE ""sub_fk"" =" & scurr 
msgbox sUpdate

[gkick]

Thank you,

also got it to work with a pair of ticks


sUpdate = "UPDATE ""tblInventory"" SET ""sub_fk"" = " & snew & "WHERE ""sub_fk"" = '" & scurr &"'"

[Villeroy]

If "sub_fk" is a character field, the ticks are necessary.