Ask Your Question
0

SQL command "UPDATE" merged with libreoffice basic command

asked 2021-01-04 16:35:50 +0100

DaviCaldas gravatar image

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 +" ")
edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2021-01-04 20:19:31 +0100

Ratslinger gravatar image

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.

edit flag offensive delete link more

Comments

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)
DaviCaldas gravatar imageDaviCaldas ( 2021-01-04 21:25:13 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2021-01-04 16:35:50 +0100

Seen: 20 times

Last updated: Jan 04