I have a DRAW document and BASE document. The BASE document has tables that contain data which relates to different shapes in the DRAW document.
I have written a macro that will display data related to a selected shape and display it in a Dialog.
Clicking the "Cancel button on the Dialog causes it to close, so all good up to that point.
I want to be able to edit the TextBoxes on the Dialog and then click the OK button to save the updated data back to the BASE document but when I try to do this I get an error message that the “Result Set is Read Only”.
I’ve used MRI to examine the Connection to determine that it is Read/Write, but do not see a Read Only property for the Result Set.
I have manually tested the SQL statement in the BASE GUI and I can update the fields that way, so I think my SQL is OK.
The OOo BASIC Guide tells me there is a ResultSetConcurrency Variant that controls whether the Result Set can be modified (but doesn’t go so far as to explain how to modify it). Using MRI I can see that ResultSetConcurrency is a number which is ReadOnly. I’m guessing that I need to set this value to something else, somehow, as part of the ExecuteQuery method. Half a day of looking through various documents and forums has not enlightened me
I’ve attached my Macro(s) that relate to the problem I’m stuck at, so perhaps someone can point me in the right direction (please!)
BTW - these macros are still in very early development, so maybe there is some fundamental flaw here somewhere.
Option Explicit Global FibreDoc As Object Global Connection As Object Global DataSource As Object Global DatabaseContext As Object Global bPitSaveFlag as Boolean Global oPitDialog as Object Sub SetDefaults Dim InteractionHandler as Object FibreDoc = ThisComponent DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext") DataSource = DatabaseContext.getByName("Fibre-data") If Not DataSource.IsPasswordRequired Then Connection = DataSource.GetConnection("","") Else InteractionHandler = createUnoService("com.sun.star.sdb.InteractionHandler") Connection = DataSource.ConnectWithCompletion(InteractionHandler) End If 'Connection.SetReadOnly(False) 'mri(Connection) End Sub Sub ShowPitDialog Dim oCurrentSelection As Variant Dim oPit As Variant Dim sPitName as String Dim iNameLength as Integer Dim iSepPos as Integer Dim iPitID as Integer Dim sPitSQL as String Dim sTypeSQL as String Dim oPitStatement as Object Dim oTypeStatement as Object Dim oResultSet as Object Dim oTypeResult as Object Dim oTypeList as Object Dim sTitle as String Dim iCount as Integer Globalscope.BasicLibraries.LoadLibrary( "MRILib" ) If (Not GlobalScope.BasicLibraries.isLibraryLoaded("Tools")) Then GlobalScope.BasicLibraries.LoadLibrary("Tools") End If bPitSaveFlag = False 'Indicator to save updated data 'mri(Connection) oCurrentSelection = FibreDoc.getCurrentSelection() oPit = oCurrentSelection.getByIndex(0) sPitName = oPit.Name 'The name of the object that is selected iNameLength = len(sPitName) iSepPos = InStr(sPitName,":") 'Location of the separating ":" in the name iPitID = Right(sPitName,(iNameLength-iSepPos)) 'The number to the right of the separating ":" sPitSQL = "SELECT ""ID"", ""Lat"", ""Long"", ""Type"", ""Comments"" FROM ""Pits"" WHERE ""ID"" = " & iPitID & "" oPitStatement = Connection.createStatement() oResultSet = oPitStatement.executeQuery(sPitSQL) ' Get the record relating to the selected Pit mri(oResultSet) If Not IsNull(oResultSet) Then While oResultSet.next DialogLibraries.LoadLibrary("Lib_pits_pipes") oPitDialog = CreateUnoDialog(DialogLibraries.Lib_pits_pipes.Pit_info) oPitDialog.setTitle("Pit " & iPitID & " Information") oPitDialog.getControl("txtLat").Text = oResultSet.getString(2) oPitDialog.getControl("txtLong").Text = oResultSet.getString(3) oPitDialog.getControl("txtComments").Text = oResultSet.getString(5) oTypeList = oPitDialog.getControl("cbxPitType") oTypeList.Text = oResultSet.getString(4) sTypeSQL = "SELECT DISTINCT ""Pits"".""Type"" AS ""PitTypes"" FROM ""Pits"" ""Pits"" ORDER BY ""Type""" oTypeStatement = Connection.createStatement() oTypeResult = oTypeStatement.executeQuery(sTypeSQL) ' Get the list of pit types If Not IsNull(oTypeResult) Then iCount = 1 While oTypeResult.next oTypeList.addItem(oTypeResult.getString(1),iCount) ' add each Pit type to the Combo List iCount = iCount + 1 Wend End If oPitDialog.Execute() If bPitSaveFlag Then REM ************** The next line generates the error **************** oResultSet.UpdateString(2, (oPitDialog.getControl("txtLat").Text) ) oResultSet.UpdateRow() End If Wend End If End Sub