[Solved] Need help with another tricky query

[RPG]

Hello

I have read the comment of rudolfo and I think he has a good point. What he tells about o'Henry seems for me the most important part of this thread

I did early post an example for updating with macros in a control. I have copy the macros in the example of Arineckaig and they work without adjusting. I have do by hand make a listbox and then also I can update in a gridcontrol.

Romke

[RoryOF]

It is surprising how often an apostrophe in a name causes problems. Irish naming often uses O', as in O'Kelly, (meaning "from the family of") and Dutch can have something like van T'Hof (sorry if I've got the capitalisation wrong). I find it very annoying to have to leave out my apostrophe. Even worse is when the O is taken as a middle initial and I am filed under F.

[Arineckaig]

I have read the comment of rudolfo and I think he has a good point

I agree that it is a good point and can only plead that my original suggestion was 'crude'. I still wonder whether a Prepared statement is the simplest solution. I have updated my previously posted macro to handle the issue, but have no desire to go to the stake on it.

[RPG]

Hello

It want not blame you Arineckaig. I did never think about those things what rudolfo told. It make me clear that the prepared statement was more important then I know.

Romke

[erf9201]

Ok, guys,

I tested the code - it seems to be working as it should.

Unless any of you would like to make any other suggestions, I think I can safely mark this thread as SOLVED.

Just wanted to thank rudolfo, Sliderule and DACM and everyone else who had contributed and helped me to solve this issue.

I really appreciate your help and selfless dedication. Keep up the good work.

Here is the final code that works:

Code: Select all

REM  *****  BASIC  *****

Sub btnClick_updateColors(oEvent As Object)
    Dim oForm As Object
    Dim oCon As Object, oStmt As Object
    Dim oDescriptionCtrlNew As Object
    Dim oDescriptionCtrlOld As Object
   

   ' Retrieve the current connection from the event and form
   oForm = oEvent.Source.Model.getParent()
   oCon = oForm.ActiveConnection

   oDescriptionCtrlNew = oForm.getByName("NewValue")
   'oDescriptionCtrl.Text has the new value of color/description field (using TextBox box - not MultiSelect box)
   
   oDescriptionCtrlOld = oForm.getByName("OldValue")
   'oDescriptionCtrl.Text has the old value of color/description field (using TextBox box - not MultiSelect box)
   
   'Creating SQL statement, updating relevant records and displaying number of updates.
   oStmt = oCon.prepareStatement("UPDATE ""Data"" SET ""Description""=? WHERE ""Description""=?")
   'Print sSQLString
   oStmt.setString(1, oDescriptionCtrlNew.Text)
   oStmt.setString(2, oDescriptionCtrlOld.Text)
   iNumUpdated = oStmt.executeUpdate()
   MsgBOx( "Number of Records Updated: " + iNumUpdated,0,"Num Updated")
   oStmt.close()  
   
   'Refreshing form and returning it to the parent record number.
   oBkMark = oForm.getBookmark 
   oForm.reload 
   oForm.moveToBookmark( oBkMark )    
   
End Sub