Page 2 of 2
Re: Need help with another tricky query
Posted: Sun Sep 04, 2011 11:46 pm
by 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
Re: Need help with another tricky query
Posted: Sun Sep 04, 2011 11:54 pm
by 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.
Re: Need help with another tricky query
Posted: Mon Sep 05, 2011 12:29 am
by 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.
Re: Need help with another tricky query
Posted: Mon Sep 05, 2011 12:42 am
by 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
Re: Need help with another tricky query
Posted: Mon Sep 05, 2011 2:30 am
by 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