Run Time Error on a "MoveFirst" 3

[Ralph8]

What can cause a Run Time Error on a "adoTeacherPersonal.Recordset.MoveFirst" when there is one or more valid records in the Teacher Table?

I get the same result with an "In line" statement and with indirect statement in a function.

The Table is defined almost identically to two other personnel tables in my system that do not give the RTEs. The Table "Updates" OK.

TIA

Ralph D. Meredith

http://www.merecosoftware.com

 

[CCLINT]

What cursor type are you using (check the properties in the property window and change to adOpenKeyset if needed)

 

[Foada]

Are you using a forward only cursor? If the current RS position is somewhere in the RS and you call the rs.MoveFirst() method you will get a runtime error.

If you choose to battle wits with the witless be prepared to lose.

 

[Ralph8]

All my cursors were set to adOpenStatic. I changed them all and most of the problem seemed to clear up. I still get it intermittently. Is the cursor location important? They are all set to adUseClient.

If not that, is there something else that I might be overlooking?


TIA

Ralph D. Meredith

http://www.merecosoftware.com

 

[Ralph8]

I have educated myself quite a bit on cursors, etc. the last couple of days. Thanks for the prod in the right direction.

There are still a few problems: See another thread "Some fields do no upate"

Now all my cursor locations are set to 2-adUseServer, the Cursor types are set to 1-adOpenKeys, and the EOF Actions are set to 2-adDoAddNew.

Results are that a regular rs.AddNew transaction updates OK, BUT a rs.update shows all the individual fields having updated but I get an RTE "Consumers event handler called a non re-entrant method in the provider" when it gets to the rs.update line.

Can anyone shed any light on this?



TIA

Ralph D. Meredith

http://www.merecosoftware.com

 

[Ralph8]

Also, on another file set up in the same way, for the first rs.AddNew to the empty file, I get a RTE "Operation Cancelled".

TIA

Ralph D. Meredith

http://www.merecosoftware.com

 

[BobRodes]

I'm not sure what your difficulties are, but I'll give you a nickel tour of the different cursortypes in case you might find it helpful.

Default cursortype (ct) is forward only, sometimes called firehose cursor. It only supports movefirst and movenext, and is useful for situations where you are making top to bottom passes in a recordset, such as when populating listboxes with data from a lookup table. It's the most efficient since it doesn't have to support moving around in a table.

static ct is a fully-populated, non-updateable recordset, analogous to snapshot in DAO.

keyset ct is a non-populated, updateable recordset. By non-populated, they mean that it only returns a set of keys, and doesn't actually return the record associated with that key until you move to it. You might find it interesting to open a keyset recordset and check the recordcount property. It will not be accurate unless you do a movelast, which will populate the recordset. This is a very useful recordset when needing to return a large amount of data, but only look at and/or update a few records from that set. While updates and deletions are visible to other users of the db, insertions are not.

dynamic ct is fully-populated. All changes to the rs are visible to all users. Think TicketMaster, for example.

Now. All client side (cursorlocation=aduseclient) recordsets are static. If you set the locktype to adlockbatchoptimistic, they are disconnectable and batch updateable, too. This means that you can open a recordset in this way, close the connection, and still have the recordset. You can make changes to the local copy, and you can even save them locally to data files with the save method. When you are ready, you can reopen the connection and call the batchupdate method. There's more to know with this, esp. about conflict resolution. Anyway, think salesmen on the road with their laptops who check in in the morning and get their appointments, as well as uploading their notes about what happened to yesterday's appointments.

These are my favorite way to do things. I'm all for offloading functionality to the client and giving the poor old server a break. Main reason is that clients' hardware is typically underused, and servers overused. I should point out that client side solutions in general are inherently more scaleable, in that whenever you add a client, by definition you add processing power, which is of course not true on a server.

Hope this gives you some background.

Bob

 

[DonPeters]

Excellent Bob!

You should consider putting this into a FAQ!

Don

 

[BobRodes]

Somebody else just asked me about it, so I guess I will. Thanks for the kind words.

Bob

 

[Ralph8]

Thanks for the excellent blurb on cursors. At this point I am not sure the problem is because of cursors.

History: The project got very flaky after a young programmer left without explanation. After a great deal of research (Head aches) it appears the root cause was that MDAC 2.6 was loaded on the development server machine and then over laid with 2.5 in an attempt to correct the initial set of problems that caused. MS has a warning buried deep in the developers site to NOT use any MDAC greater than 2.5 on SQL Server 7 server machine. There were corrupted files, etc., so the warning was justified. I used MS ComCheck to straighten the MDAC out, but apparently rabbit trails were followed trying to "fix" things. I believe most of the properties, etc. are standardized and restored to pretty much what they were when the project was working well.
I have tried cursors both Client side and server side. There seems to be better functionality using server side.

Current symptoms: Almost any attempt to do any thing on an empty table gives unpredictable results. eg. A rs.MoveFirst may cause a message that "duplicate PKs cannot be entered"
OR NULL values are used improperly in the PK when there are no NULL values. I tried entering a record in the first row of the table and then do a rs.AddNew to enter a second record. The second record updated OK but some, not all, of the fields overlaid corresponding fields in the first record.

Any ideas?

TIA

Ralph D. Meredith

http://www.merecosoftware.com

 

[BobRodes]

Ouch.

I would first try creating a clean development machine, with SQL server, VB, and your code. Make sure it runs there. You can use the registry and task manager to check to see what is actually running on each machine. Hopefully, this will give you some idea of where the problems are.

Next, I would start checking to see if there are any corrupt dlls.

That's the best I can do on this one. Best of luck.

Bob

 

[Ralph8]

I have created a "clean" dev machine. Scrubbed the hard drive, loaded Windows, SQL Server 7(MSDE), VS/VB 6, SQL Server 7 SP4, VS SP5, and MDAC 2.5. I then added my code and began testing with basically the same results.

I have moved back and forth between having the cursors on the client and having them on the server. That just seems to change the order in which I get the same set of symptoms.

I am not sure how to go about checking for corrupted DLLs.

I am wondering if my actual DB files (.mdf & .ldf) could have been corrupted, and if so, could that cause this kind of situation?

At the moment I feel mentally numb and quite frustrated.

Any help out there?


TIA

Ralph D. Meredith

http://www.merecosoftware.com

 

[Ralph8]

P.S. When I do a full compile, it goes clean.

TIA

Ralph D. Meredith

http://www.merecosoftware.com

 

[Ralph8]

Here is a consistent example of what is happening:

Data entry form contains five data combos and five text boxes.

The Table to be updated (tblDistrictAgency) has ten corresponding data fields. The table is empty.

There is a set of ten pstrValues that contain the data that was input thru the DE Form.

the code lines are:
rs.AddNew
rs![data field name] = pstrValue
(nine more such lines to update the remaining data fields)
rs.update

The results are:

The program "hangs" on the rs.update line.

RTE '-2147217873(80040e2f)': Violation of PRIMARY KEY constraint 'PK_tblDistrictAgency'. Cannot insert duplicate key in object 'tblDistrictAgency'.

All ten pstrValues and all ten rs![data field]s contain the correct information.

The first row in the table has seven fields updated correctly and three that contain NULLs. The PK is one of those that updated correctly.

Properties for adoDistAgency are:

BOFAction 0-adMoveFirst
CacheSize 3
CommandType 2-adCmdTable
ConnectionString Provider=SQLOLEDB.1;PersistSecurityInfo=False;UserID=sa;
InitialCatalog=SEM4;DataSource=(local)
CursoeLocation 2-adUseServer
CursorType 1-adOpenKeyset
EOFAction 2-adDoAddNew
Locktype 2-adLockPessimistic
Mode 3-adModeReadWrite
Password blank
RecordSource tblDistrictAgency
UserName sa



TIA

Ralph D. Meredith

http://www.merecosoftware.com

 

[BobRodes]

How are you creating your primary key?

 

[Ralph8]

I believe all of my tables were designed/created in Enterprise Manager.

We also use the Data View/Conn1 for a variety of things, such as building views, etc.


TIA

Ralph D. Meredith

http://www.merecosoftware.com

 

[Ralph8]

I tried replacing the adodc, but that didn't help either.

TIA

Ralph D. Meredith

http://www.merecosoftware.com

 

[Ralph8]

FYI Guys

The most basic cause of the problem turned out to be a conflict between some "bound" data fields and some "line" statements trying to update fields prior to the rs.update line, which caused a double attempt to update the recordset.

P.S. - I finally got thru to a MS Engineer and he spotted it. Thanks for all the help. The cursors and "locking". etc. were all confusing the issue. Thanks again.



TIA

Ralph D. Meredith

http://www.merecosoftware.com