ADO Connection Objects

[TEG]

Ok, I would like your opinions; after many hours testing; I can't figure out how the connection object works... Nor what the most efficient way of using one is...

When I create a connection object in code and then set it to a recordset object and a command object (for a stored procedure using SQL Server 7.0; VB6) then disconnect the recordset by setting its activeconnection property to Nothing and same for the command object; close the connection; SQL Server performance monitor is not showing the connection to be closed. WHY????
On top of this, when I run the same routine again; then I have two connections open! After a certain period of time the connection apparently times out and does actually close. Is this a bug??? Or am I doing something incorrectly? Has anyone else seen this?

Also, what is the best way to use connection object; create and destroy each time or just open one connection object at form load and destroy it at form unload; what are the pros and cons of each way... Seems to me that by creating the connection at form load and destroying at form unload, may put a little more overhead on each client; but reduces the server traffic by not having to recreate this connection every time... But, I don't know; will this cause problems with multiple users when everyone always has one connection open???

Boy, this was a book wasn't it? Thanks for reading, and for the help...

[TEG]

Closing the connection like cn.close should close the connection object. Give me your code and i'll take a look at it. Also tell me what you are trying to do; when you say it won't unload from memory what do you mean? you close your application and then do a Vulcan-Death-Grip (CTL-ALT-DEL) and the app is still in memory? If that's the case then you are not unloading all your forms...

[ruggerid]

TEG,

Please check out my post (6/7/99 12:45) about an ADO/OLEDB error(well at least at the time I thought it was an error concerning both entities). After reading your post it sounds as if I am having the same error, just in Access - so it might just be an ADO error. I currently have a case opened with Microsoft (well actually People Unlimited?!?)and when we get it solved I'll keep you posted.

Try stepping through your code to see if you get similar results - that is the only way I can can get consistent, correct results. Oh btw, I checked for the conn.state after the conn.close (prior to set conn = nothing of course) in my code and it did say 0 (closed). But who knows...

As for what I think is the best way to deal with connections...my rule is one connection per function/subroutine. But then again...maybe that's my problem :)

I need this problem solved soon...deadline is approaching and this is the only thing holding me back!


Dennis

[mjmst74]

Man, i wish i could figure out how to close a connection object. I wasn't looking with SQL Server though-- my app won't free from memory when it closes... IF i open a connection object.

the thing is, i close the connection and i set it equal to Nothing, which i thought would get rid of it.

Have you figured anything out yet about closing a connection?

[mjmst74]

Sorry i didn't give any specifics--

I run my app, then close it. then i do a ctl-alt-del, and it's still there in the list of processes. However, if i run the app but never open the connection, when i close the app it leaves the list of processes.

Here's the small project i'm testing with- just a form and a button- you click the button to open and close a connection, then close the form. If you don't open the connection, it leaves memory... otherwise it stays.

---------
Private Sub Command1_Click()

Dim cnn1 As New ADODB.Connection

cnn1.ConnectionString = "dsn=data_source; uid=user_id; pwd=pass_word; database=data_base"
cnn1.Open

cnn1.Close

Set cnn1 = Nothing

End Sub 'command1_click

Private Sub Form_Load()

Show

End Sub 'form_load

Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)

Unload Form1

Set Form1 = Nothing

End Sub 'form_queryunload
-----

I've run this and closed it, and just let it sit in memory. On its own, it goes from about 3932K to nearly 0K, but it stays around 1000K of virtual memory.

[TEG]

Very interesting! I have never tried that before; does this still do the same thing when you single step the open and close? I wonder if its puking on the open and close being to close together; maybe single step would work? I'm curious about this, but it's Friday and after 5:00 and I need to get home; maybe i'll try it this weekend... thanks for the reply; glad to see that i'm not the only one having trouble with closing connections! Just another MS headache huh?

[mjmst74]

Okay, what do you mean by "single-step"? I'm not sure what you're talking about.

Also, i don't think it's freaking out because the open and close are too close together- i've used the same code to open and close a connection and done a lot of things in between... and it still hangs out in memory.

[TEG]

I'm talking about using debug and stepping through your code by setting a toggle breakpoint...