| Author |
Topic |
|
MikeB
Constraint Violating Yak Guru
387 Posts |
 Posted - 2003-03-13 : 08:22:15
|
| I need to know what I should do with the ADO connection to my database from my application. I currently have my application connecting to the database at startup and disconnection at application's end. Is this a bad idea? Should I only connect to the database when I need it? EG:- Connect to database- Query (SELECT, INSERT INTO, DELETE etc....)- Disconnect databaseAdivice please?Mike B |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2003-03-13 : 09:30:19
|
| It's better practice to connect when you need to rather than keep a persistant connection open, but it also depends on the overheads you have and are likely to face.I tend to define the ADO connectiojn object globally and then connect to it when needed. Make sure you close all your objects each time if you do it this way else you will get memory leeks... :) |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-03-13 : 09:36:20
|
| It depends on how much activity you have. And, opening a connection, returning the data, and having the user browse it is NOT activity. User browsing is not an activity even though they may be navigating constantly. If they make an update, add a row, or delete a row, THAT is activity. As much as possible, you should open the connection, get whatever you need or perform whatever action needed, and get out as quickly as possible. You don't necessarily have to close the connection, you can set a recordset's ActiveConnection property to Nothing. That will leave you with a disconnected recordset that you can browse at your leisure, make whatever changes needed, and then reconnect and submit them all using the UpdateBatch method. This is what a lot of ASP code does and what ADO was designed to do well. You can also just close the connection when you're done and open a new one when you need to do something. |
|
|
|
MikeB
Constraint Violating Yak Guru
387 Posts |
Posted - 2003-03-13 : 10:43:36
|
| If I was to close the connection after each use, when can I close it?Lets say I do the following:- Open Connection- Query Database (SELECT *, INSERT INTO, etc...)- Set Recordset connection to nothing- Close Connection- View Recordset, change recordset?- Open Connection- Set Recordset connection- Update Batch- close Connection- close recordsetWould this be correct?After a recordset is obtained, is the connection still required to view the data?Mike B |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-03-13 : 11:06:31
|
| No. When you do this:Set rsObj.ActiveConnection=NothingYou can close the connection or not, it's up to you, but the recordset is now populated and can be manipulated any way you wish. When you want to post the changes and such back to the database, you reattach, reopen, or create a new connection object and set the ActiveConnection property to point to it. The sequence you have now is fine, but don't close the recordset or set it to Nothing until you are completely done with it. |
|
|
|
MikeB
Constraint Violating Yak Guru
387 Posts |
Posted - 2003-03-13 : 11:38:31
|
| Can I add a new record to a disconnected recordset?Mike B |
|
|
|
MikeB
Constraint Violating Yak Guru
387 Posts |
Posted - 2003-03-13 : 11:41:33
|
quote:
Can I add a new record to a disconnected recordset?Mike B
This brings up other thoughts, if the recordset is disconnected, and a user on one machine adds a new record, a user on another machine adds a new record as well on the same table. This can present a problem due to primary key conflicts when they try and UpdateBatch.Mike B |
|
|
|
sherrer
64 Posts |
Posted - 2003-03-13 : 13:03:20
|
| Yes you can add or modify a disconnected recordset set. You must choose the lock type of batchoptimistic to get your changes back to the database. To change the database, you must set your active connecton for the recordset back to a connection object's connection. Then you post your changes using recordset method batchupdate, you can either update all changes in the recordset at once, or row by row. Take note, that when you update the entire recordset, it still sends a seperate update statement to the database for each changed row.Kevin |
|
|
|
sherrer
64 Posts |
Posted - 2003-03-13 : 13:07:38
|
| Sorry I didn't completey read your second question, as it seemed you figured out how to update the database. You will get an error if the row has already been modified by another user, you can check the documentation to learn about error handling. It is really no different that any other database conflict, however the longer you hold the data disconnected the more likely you are to cause a conflict on an active server... Common sense. :)Kevin |
|
|
|
|
ADO / MS Access, easy question