ADO Connection

[WOTRAC]

Hi Folks

I am a novice programmer who needs a bit of direction.
I am developing an application which will query a SQL
database and retrieve a list of stock codes, which will be used to populate a list box.
This routine will be performed several times, while the application is opened, but may return different result sets based on the users requests.

Question?

Should I open a recordset every time I need to query the database and close it when finished, or are there any other
alternatives.

eg What is connection pooling etc?


Thanks

 

[onpnt]

Connection Pooling

it depends on the structure of the application that you are writing on how you use the recordset's. Seeing as you stated recordset, I would take it you are on a VB(.NET) platform??? That will most likely be important information for us to determine what is best for optimization. then your best bet will be to check in the forums that match your platform and programming usage

Forum796 VB.NET
Forum222 VB5+6

In general programming terms, you open a connection/use it to populate a object/use the object/release ALL resources after done. That is for each time it is needed. You don't want to be left out there with opened connections and active objects that can cause conflicts with other applications and events.


___________________________________________________________________
[sub]

http://www.onpnt.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page faq333-3811[/sub]

 

[WOTRAC]

Hi

I am on a VB 6 platform.

I only mentioned the word recordset, as I assumed this would be the most obvious way to return the result set to the list box.

On that note, if I populate a recordset with a result set from a database. Can I still use the values in that recordset, if I close it and close the connection.
Or do I have to make use of the recordset before closing it.


Thanks

 

[samshiell]

The answer is yes..... When you define your connection object make the cursor type property = "adUseClient".. (e.g. adoConnection.CursorType = adUserClient" I think).

Then you can populate the recordset. Once you've done that set the recordset's connection object to "nothing"

(set adoCOnnection = Nothing)

Then you end up with a populated recordset that you can use anywhere in your application (that it's scoped obviously) without having to maintain a connection to your database. Be aware though that if you are wanted to update the SQL table directly from this recordset that's another complication.

Hope that helps,

Sam

 

[WOTRAC]

Thanks

Thats the bit I wasn't sure of.


Paul