SQL command "UPDATE" merged with libreoffice basic command

[DaviCaldas]

Hello guys, I would like to know how to use the SQL command “UPDATE” merged with libreoffice basic command. Since I want to update the record and a table (Diagnostic Dates) to the same value shown in the text box (from the “Identification” table).

This is the code I made so far (the error is in the last line):

if IsNull(ThisDatabaseDocument.CurrentController.ActiveConnection) then
ThisDatabaseDocument.CurrentController.connect 
endif

Dim oForm, oCtrlr, oField As Object
oForm = ThisComponent.Drawpage.Forms.getByName("Formulário")
oCtrlr = ThisComponent.getCurrentController()
oField = oForm.GetByName("Caixa de texto 1")
oCtrlr.getControl(oField).setFocus()

Dim oStatement As Object
oStatement = ThisDatabaseDocument.CurrentController.ActiveConnection.createStatement()
oStatement.execute("UPDATE ""Datas do Diagnostico"" SET ""IDAnamnese"" =  "+ oField +" ")

[Ratslinger]

Hello,

Although it should not affect this question, always include the database you are using. SQL can vary between databases. Specific LO version & OS are also welcomed.

The problem is compounded on the last line. You never retrieved the data from the text box. You do access the control as ‘oField’ so you need to get the text - oField.Text

The result is then used in the SQL. Also surround data with apostrophes. However, to avoid confusion, use & instead of + (my preference to also build SQL string separately):

Dim sMydata as String
Dim sSQL as String
sMyData = oField.Text
sSQL = "UPDATE ""Datas do Diagnostico"" SET ""IDAnamnese"" =  '" & sMyData & "'"
oStatement.execute(sSQL)

All this replaces just the last line of your code. Did not test this as posted but should work.

[DaviCaldas]

Thank you very much for the answer RATSLINGER, the code worked very well.

Follow the complete code for other users

if IsNull(ThisDatabaseDocument.CurrentController.ActiveConnection) then
ThisDatabaseDocument.CurrentController.connect
endif

Dim oForm, oCtrlr, oField As Object
oForm = ThisComponent.Drawpage.Forms.getByName("Identificacao")
oCtrlr = ThisComponent.getCurrentController()
oField = oForm.GetByName("IDAnamnese1")
oCtrlr.getControl(oField).setFocus()

   Dim oStatement As Object
   oStatement = ThisDatabaseDocument.CurrentController.ActiveConnection.createStatement()

   Dim sMydata as String
   Dim sSQL as String
   sMyData = oField.Text
   sSQL = "UPDATE ""Datas do Diagnostico"" SET ""IDAnamnese"" =  '" & sMyData & "'"
   oStatement.execute(sSQL)