|
code
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
The Elements of Style revisited.a physician name and credentials tables. This allows the user to input new rows as well as change fields within existing rows. While it is a very simple process it meets the needs of the design and all is well. The recordset, rs, is defined as an ADODB.recordset in the form module. It is available to any function in the module. In this particular situation it is set up in the load event of the form as follows: Private Sub Form_Load() Dim sSQL As String 10 On Error GoTo Form_Load_Error 20 Me.Move (Screen.Width - Me.Width) \ 2, (Screen.Height - Me.Height) \ 2 30 Set rs = New ADODB.Recordset 40 sSQL = "select * from tblproviders" 50 rs.CursorLocation = adUseClient 60 rs.Open sSQL, objAccessCNN, adOpenDynamic, adLockOptimistic 70 Set Me.dgProviders.DataSource = rs Form_Load_Error: 80 If Err <> 0 Then 90 frmError.ErrMsg "frmProviders", "Form_Load", Erl, Err 100 End If End Sub It works, no problems. In the interest of style I want to get rid of the recordset when it is no longer needed. I have tried the following code under all of the following events one at a time: form unload event, form terminate event, and form queryunload event with the following code. If rs.state = adStateOpen then rs.close Set rs = nothing Endif Under all the attempted events, I get the following run time error. No run time error number is ever given; only the text message. "The current row is not available". I did find using Google a number of discussions over this situation but none really relevant to what I was experiencing. If I remove those events and simply let Visual Basic close the recordset for me when the form is destroyed there is no error message. I realize full well that technically there is nothing wrong with allowing VB to handle this bit of housekeeping. However, I really would like to do it myself. It is,after all, a matter of style. Any comments or suggestion that will eliminate this situation are welcomed and appreciated. Robert Berman V.S.S. Longwood, Florida You don't have "Exit Sub" before your error handler, so it's executing your
error handler even if there was no error. If you want to remember if Exit Sub was used or not, I recommend that you put "Exit Sub" in the line preceding to your error handler label without blank lines, so it appears as if it was an integral part of the error handler as it supposed to be. Example: ' Last line of code here, followed by empty line Exit Sub ErrorHandler: If Err.Number <> 0 Then MsgBox "Error " & Err.Number & ": " & Err.Description End If This will make it easy to notice if "Exit Sub" was missing or not. Also, see my post here about how to add error handling to all routines: http://groups.google.com/group/microsoft.public.vb.general.discussion/msg/da28c493c61c6b60 > If rs.state = adStateOpen then You forgot about adStateExecuting. One way to do it is:> rs.close > Set rs = nothing > Endif If rs.State <> adStateClosed Then rs.Close End If Set rs = Nothing However, I prefer: rs.Close Set rs = Nothing con.Close Set con = Nothing > 60 rs.Open sSQL, objAccessCNN, adOpenDynamic, adLockOptimistic adOpenDynamic is not supported when using adUseClient. The provider would use adOpenStatic instead. After you open the recordset, add the following lines: Debug.Print rs.CursorLocation Debug.Print rs.CursorType Debug.Print rs.LockType The DB Engine is free to change the last 2 properties if it doesn't support them without issuing any errors or warnings. This is normal. After opening the recordset, these properties reflect the changes made by the DB Engine if any. This is what MSDN documentation says. Show quoteHide quote "Robert Berman" <BermanRL**SpamKiller**@earthlink.net> wrote in message news:Xns96F6A71DA32D1Thornmastrearthlinkn@207.217.125.201... >I have a modal form consisting of a datagrid used to display the rows of > a physician name and credentials tables. This allows the user to input > new rows as well as change fields within existing rows. While it is a > very simple process it meets the needs of the design and all is well. > The recordset, rs, is defined as an ADODB.recordset in the form module. > It is available to any function in the module. In this particular > situation it is set up in the load event of the form as follows: > > Private Sub Form_Load() > Dim sSQL As String > > 10 On Error GoTo Form_Load_Error > 20 Me.Move (Screen.Width - Me.Width) \ 2, (Screen.Height - > Me.Height) \ 2 > 30 Set rs = New ADODB.Recordset > 40 sSQL = "select * from tblproviders" > 50 rs.CursorLocation = adUseClient > 60 rs.Open sSQL, objAccessCNN, adOpenDynamic, adLockOptimistic > 70 Set Me.dgProviders.DataSource = rs > Form_Load_Error: > 80 If Err <> 0 Then > 90 frmError.ErrMsg "frmProviders", "Form_Load", Erl, Err > 100 End If > End Sub > > It works, no problems. > > In the interest of style I want to get rid of the recordset when it is > no longer needed. I have tried the following code under all of the > following events one at a time: form unload event, form terminate event, > and form queryunload event with the following code. > > If rs.state = adStateOpen then > rs.close > Set rs = nothing > Endif > > Under all the attempted events, I get the following run time error. No > run time error number is ever given; only the text message. "The current > row is not available". I did find using Google a number of discussions > over this situation but none really relevant to what I was experiencing. > If I remove those events and simply let Visual Basic close the recordset > for me when the form is destroyed there is no error message. I realize > full well that technically there is nothing wrong with allowing VB to > handle this bit of housekeeping. However, I really would like to do it > myself. It is,after all, a matter of style. > > Any comments or suggestion that will eliminate this situation are > welcomed and appreciated. > > > Robert Berman > V.S.S. > Longwood, Florida "Robert Berman" <BermanRL**SpamKiller**@earthlink.net> wrote in message You have assigned a reference to the recordset to the data control; thatnews:Xns96F6A71DA32D1Thornmastrearthlinkn@207.217.125.201 > 70 Set Me.dgProviders.DataSource = rs doesn't make a copy, it makes the control use the same recordset object. If you close the recordset it's closed for every reference to it. You can do "Set rs=Nothing" to remove the extra reference and leave it being held only by the data control if you want. -- Reply to the group so all can participate VB.Net: "Fool me once..." I usually do this:
Set Me.dgProviders.DataSource = rs.Clone Hmmm, is this going to work when the rs is for a data bound control? Saga Show quoteHide quote "Bob Butler" <tiredofit@nospam.com> wrote in message news:eL$piLo1FHA.1568@TK2MSFTNGP10.phx.gbl... > "Robert Berman" <BermanRL**SpamKiller**@earthlink.net> wrote in > message > news:Xns96F6A71DA32D1Thornmastrearthlinkn@207.217.125.201 >> 70 Set Me.dgProviders.DataSource = rs > > You have assigned a reference to the recordset to the data control; > that > doesn't make a copy, it makes the control use the same recordset > object. If > you close the recordset it's closed for every reference to it. > > You can do "Set rs=Nothing" to remove the extra reference and leave it > being > held only by the data control if you want. > > -- > Reply to the group so all can participate > VB.Net: "Fool me once..." > "Saga" <antiSpam@somewhere.com> wrote in message don't see why not, bit then again I don't use bound controlsnews:uYmtwio1FHA.612@TK2MSFTNGP10.phx.gbl > I usually do this: > > Set Me.dgProviders.DataSource = rs.Clone > > Hmmm, is this going to work when the rs is for a data bound control? -- Reply to the group so all can participate VB.Net: "Fool me once..." "Bob Butler" <tiredofit@nospam.com> wrote in message Unless dgProviders is not a data grid, the linenews:O5qkCpo1FHA.3300@TK2MSFTNGP15.phx.gbl... > "Saga" <antiSpam@somewhere.com> wrote in message > news:uYmtwio1FHA.612@TK2MSFTNGP10.phx.gbl >> I usually do this: >> >> Set Me.dgProviders.DataSource = rs.Clone >> >> Hmmm, is this going to work when the rs is for a data bound control? > > don't see why not, bit then again I don't use bound controls > Set Me.dgProviders.DataSource = rs or Set Me.dgProviders.DataSource = rs.Clone is binding a control (the datagrid) to a recordset. I would bet that your snag is that you have closed the recordset that is still bound to the datagrid, and some process in the datagrid is unhappy that it can no longer find a current record. Perhaps adding Set Me.dgProviders.DataSource = Nothing would "unbind" the recordset, allowing you to close and dispose. First I would like to thank everyone who replied. All of the replies were
interesting, and those of you who were so kind as to post reference type articles, I enjoyed reading them. I do have some observations to some rather specific posts. To the person called "Someone" which I guess is certainly as valid a name as "NoMan" I found your comments pertaining to my lack of use of the exit sub statement before my error routine interesting but just a tad perplexing in that if you will note my error routine simply falls through if there is no value for the "Err" variable and so my response to you is: Why do I need to exit the sub when entering the error routine does nothing at all unless there is an error? Mr. Butlers commentary about simply setting rs to nothing simply makes coding much easier, and having tried it I am delighted with the results. It took me a few moments to realize how much sense this really makes until I realized that rs in simply a pointer to what appears to be a data control block. Again, to both Bob Butler and Saga, it is not my general practice to use bound controls. In this particular application, I have a few tables that lend themselves to very simple data grid manipulation and I took full advantage of that situation. And of course to Steve G, the best I can possibly say is "Thank You". Your answer was the solution. I used the following code and it worked very well with no error messages at all. Private Sub Form_Unload(Cancel As Integer) Set Me.dgProviders.DataSource = Nothing Set rs = Nothing End Sub Robert Berman Show quoteHide quote "Steve Gerrard" <mynameh***@comcast.net> wrote in message news:KZ6dnUTgnoEaD8TeRVn-tA@comcast.com... > > "Bob Butler" <tiredofit@nospam.com> wrote in message > news:O5qkCpo1FHA.3300@TK2MSFTNGP15.phx.gbl... >> "Saga" <antiSpam@somewhere.com> wrote in message >> news:uYmtwio1FHA.612@TK2MSFTNGP10.phx.gbl >>> I usually do this: >>> >>> Set Me.dgProviders.DataSource = rs.Clone >>> >>> Hmmm, is this going to work when the rs is for a data bound control? >> >> don't see why not, bit then again I don't use bound controls >> > > Unless dgProviders is not a data grid, the line > Set Me.dgProviders.DataSource = rs > or > Set Me.dgProviders.DataSource = rs.Clone > > is binding a control (the datagrid) to a recordset. > > I would bet that your snag is that you have closed the recordset that is > still bound to the datagrid, and some process in the datagrid is unhappy > that it can no longer find a current record. > > Perhaps adding > Set Me.dgProviders.DataSource = Nothing > would "unbind" the recordset, allowing you to close and dispose. > > My suggestion would be to use local recordsets so that you can open and
close them inside the procedure. (more below) Show quoteHide quote "Robert Berman" <BermanRL**SpamKiller**@earthlink.net> wrote in message I would place it in the form unload.news:Xns96F6A71DA32D1Thornmastrearthlinkn@207.217.125.201... >I have a modal form consisting of a datagrid used to display the rows >of > a physician name and credentials tables. This allows the user to input > new rows as well as change fields within existing rows. While it is a > very simple process it meets the needs of the design and all is well. > The recordset, rs, is defined as an ADODB.recordset in the form > module. > It is available to any function in the module. In this particular > situation it is set up in the load event of the form as follows: > > Private Sub Form_Load() > Dim sSQL As String > > 10 On Error GoTo Form_Load_Error > 20 Me.Move (Screen.Width - Me.Width) \ 2, (Screen.Height - > Me.Height) \ 2 > 30 Set rs = New ADODB.Recordset > 40 sSQL = "select * from tblproviders" > 50 rs.CursorLocation = adUseClient > 60 rs.Open sSQL, objAccessCNN, adOpenDynamic, adLockOptimistic > 70 Set Me.dgProviders.DataSource = rs > Form_Load_Error: > 80 If Err <> 0 Then > 90 frmError.ErrMsg "frmProviders", "Form_Load", Erl, Err > 100 End If > End Sub > > It works, no problems. > > In the interest of style I want to get rid of the recordset when it is > no longer needed. I have tried the following code under all of the > following events one at a time: form unload event, form terminate > event, > and form queryunload event with the following code. > The set rs = nothing can go outside the IF statement, as it does not depend on whether the rs is open or not. Also, upon looking at this page: http://www.devguru.com/Technologies/ado/quickref/recordset_state.html It mentions that the state property is the sum of one or more of the possible values, so I believe that here you just want to check the low bit which tells you whether it is open or not, so the below IF shoul dchange to If rs.state and adStateOpen = adStateOpen then This ANDs the adStateOpen value (1) with the property and if it still equal to the adStateOpen value then the recordset is open. I hope this helps somewhat, asssuming that the info on that page is correct and I understood it <g>. Regards, Saga Show quoteHide quote > If rs.state = adStateOpen then > rs.close > Set rs = nothing > Endif > > Under all the attempted events, I get the following run time error. No > run time error number is ever given; only the text message. "The > current > row is not available". I did find using Google a number of discussions > over this situation but none really relevant to what I was > experiencing. > If I remove those events and simply let Visual Basic close the > recordset > for me when the form is destroyed there is no error message. I > realize > full well that technically there is nothing wrong with allowing VB to > handle this bit of housekeeping. However, I really would like to do it > myself. It is,after all, a matter of style. > > Any comments or suggestion that will eliminate this situation are > welcomed and appreciated. > > > Robert Berman > V.S.S. > Longwood, Florida 
Other interesting topics
Intergating Multiple VB6.0 Projects into one: Project Grouping
Parsing UNIX text files Return an error code/message from a VB6 app Is this possible ? How to access related documents in VB through source code? Lost_Focus event of controls doesn't fire Please Explain! Intercept the Min/Max button Press Error 453: Can't find DLL entry point (VB6) Order of forms |
|||||||||||||||||||||||
