Three ADO Mysteries

[MelissaEvans]

Given: SQL 7.0, using an ODBC connection, ADO 2.5, MDAC 2.51, server side cursor, keyset cursor type, optimistic locks

1. I have a trigger that is altering a field in the background upon update. Actually, it makes several changes. I am using a keyset cursor type (which means that I should have the most current version of the data reguardless), but not all of the changes that the trigger makes show up. When I look at the record in the query analyzer, the change is there. In my app, if I move from the record and come back to it, I still don't see it. I have to re-get the recordset. Like I said, this is only for certain fields. Does anyone know of something that will affect how a trigger makes its changes?

2. My app uses several different tables. I've been testing it on a large database (100,000 records) and sometimes it's zippy, other times it's slower than snot. These tests have been when no one else is in the office, so it's not network traffic. My coworker pointed out that when the Enterprise Manager is open and something has been done/looked at in a specific table, then it's fast; but when the Enterprise Manager is closed, or has most recently looked at a different table, then it's slow. Any ideas what on earth is going on there? An explination would be nice, a solution would be even better =)

3. The user has a scroll bar that s/he used to navigate through the records. I had to manually impliment the scroll bar and the movement through the recordset due to many constraints; and in general, it works fine. But... (you knew I was going to say that...) when the user deletes a record, the scroll bar's max is decrimented by one and the deleted record (call it D) is indeed gone. However, when the user scrolls through the records, the record immediatly after the deleted one (call it D+) is correct, but the one following that (call it D++) is the same as D+. The very last record in the recordset is not visible to the user. It seems that there has been a bug noticed for this in MDAC 2.1, but I'm using 2.51 which is supposed to fix it.

That's the end of my oddities. Thanks for your time. *sigh*
~Melissa

 

[Terpsfan]

First of all I wouldn't use ODBC as a database access method. I use ADO and the OLEDB Provider for SQL Server (SQLOLEDB). It's a faster data access method than ODBC. Secondly, when you use a Keyset Cursor, you won't be able to see all additions and deletions made by other users until you requery or refresh the recordset. I use the Dynamic Cursor. Thirdly, I would reduce the number of records you are pulling up in your form. Because a user should only have to access one record at a time, I only allow the user to bring up that one record. Fourthly, I'd use a stored procedure instead of a trigger to make the updates to your records. Stored procedures are faster because they are compiled. Triggers are an older, less efficient way of doing things. I hope this has helped you.

Chuck

 

[MelissaEvans]

First, I'm using ODBC because other customers may not be using SQL Server. I have to keep other options available; my manager requires that. Second, I understand the concept of a keyset cursor and I'm not particularly worried about changes made by other users; just the ones made by the current user. You were very correct about the Requery though; thank you - it fixed whatever it was that was wrong with both the delete problem and the trigger problem (still don't understand why it was wrong, but now I'm just happy it works =) Third, the admin user has control over how many records a user will see at a time; by default it's 500. The user will not get all 100,000 records at one time. Fourth, the triggers already exsist are are used by several other programs that I cannot change.

I'm sorry I didn't explain these things to begin with (it's hard giving you all the information you need to help and not giving you so much that you get lost).

For the record, my second question has been answered. We found that SQL has a "Auto Close" option on the database that had to be turned off. Then it works fine.

Thanks for your time