Need help with ADO Connection

Question

Post reply

Need help with ADO Connection

Viewing 6 posts - 31 through 35 (of 35 total)

You must be logged in to reply to this topic. Login to reply

Dale Corey SSCrazy Eights Points: 9019 More actions · Answer 1

That is the cutest emoticon. I'm blushing!

Hope there's some good stuff in there for you!

All the best,

Dale

Author: An Introduction to SQL Server 2005 Management Studio

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 2

I must have missed the part where he said his VB6 app ran under IIS. A standalone VB6 app won't use connection pooling.

You should minimize the number of times a connection is created, and always destroy it (Set = Nothing) after closing it (same for recordsets). On the other hand, you shouldn't leave a connection open for hours when there is no activity in the program.

One connection for the entire program is usually sufficient for a low-usage VB app, or at most one per form. Certainly not one per event, unless you expect events to happen very infrequently (some kind of low-interactivity display application maybe). But they shouldn't stay open for hours of inactivity, maybe put timers on the forms to force closure x minutes after the last event.

You could create a connection class that returns an open connection either by returning the existing open one or opening a new one, with an explicit close method and a timer to force a close. It would need a reset method to reset the timer that would be called from all UI events. The approach will not work with bound controls, and may be too much work for a doomed VB6 app.

The numerous connection openings are bad programming, but how bad? Do they make the program a little less responsive, or are they dragging down a busy server? Leaving 8000 connections open would provoke me to rewrite the application, but saving someone a few seconds per week might not. How much time do you want to invest in learning how to optimize VB6 code? (I've got a coupla decades of Fortran 2 and Fortran IV expertise I'd like to sell someone, so I can make room in my brain for .NET, Java, and a few other things. I'll even pay you to take it if you can remove all memory of COBOL while you're at it.)

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Scott,

thanks for your thoughts.

If I read you right, I suspect a lot of mainframers here would love to get your knowledge

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 4

To help ease Frank's pain..

I am tasked with "enhancing" several of our VB6 applications. the one I am currently playing with has the following:

written by 3rd party contract company
ALL variables created as OBJECT
for EVERY request from the server a NEW connection defined as SET oADOCONN = CREATEOBJECT("ADODB.Connection")
for EVERY recordset a NEW recordset defined as SET oADORS = CREATEOBJECT("ADODB.Recordset")

Now the application has been breaking app. for a VERY long time (prior to me starting at company).. I have looked at this for the 1st time today and DO NOT see the following

Error handling, Destruction of Recordsets, Destruction of Connections. Has anyone here seen 100x connections for the same user/application just because they have hit "Search" 80x times.....

I have....

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Let me guess, your developers were also first-time VB programmers with a mainframe background?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 6

Believe it or not they are MS papered in VB, SQL, ASP, etc... MCSE, MCAD, ...

Another GREAT reason why PAPER alone is not a good reason to hire...

Good Hunting!

AJ Ahrens

webmaster@kritter.net