Need help with ADO Connection
-
Yes and as wilth all exception you put code to handle. Also each time you need to perform a process you should check the connection object exists and that the state is open, if not then you need to resolve that before you go forward on executing anything across the connection. Otherwise you have these random blow-ups which are just poor coding habits biting your butt.
-
Exactly!
Takes a little work but it's a solution. See this article on connection pooling and the .NET framework: http://www.ondotnet.com/pub/a/dotnet/2004/02/09/connpool.html
All the best,
DaleAuthor: An Introduction to SQL Server 2005 Management Studio
-
I hate to throw a wrench at the machine but this is VB6 that he has graciously inherited...
I have seen both cases of connection pooling and just open the connection/command and put them to bed when no longer needed. The 2nd choice works better most of the time (in my experience).. If you are worried about connections being left open for (n) you can also write a timer hat will fire the form terminate if nothing has occurred in (n)....
.NET does do a MUCH better job with ADO connections and really is the way to go in the future alas poor VB6 we knew him well....
Good Hunting!AJ Ahrens
webmaster@kritter.net -
I have to say I have had no issues with connection pooling in VB6 and I have to say I don't like sloppy coding and dangling connections that are not needed. Now for what Frank says it really doesn't matter, few connections, sparsely used and used fairly quickly. But not building it right just because of those facts isn't a good practice. Besides you can funtionalize many of the checks and streamline your code base against these anyway. One of my biggest pet peeves is find an app that has hung because someone decide that instead of checking the state of the connection to see if open they decide to use
On Error Resume Next
You should only resume or goto when it is a situation that cannot be avoided thru proper coding because of issues outside your knowledge or scope. And even then you should create a log file for every error so you can go back and deteremine what you need to study up on so you can be a better programmer.
Personally I was sorry to see the way they gutted VB for .NET, if I wanted to write in C# I would have written that way to begin with. And don't feed me the line about how it had to change so drastically so it could become more powerfull. Blah...Blah...Blah
End Rant
-
Antares I agree 100% with what you say about sloppy code. I just find it easier and more efficient for smaller apps to use the open/hold/close for the connection. I use a class module and call it when I need the connection and clean up the command and parameters etc... I do love checking the state of the connections I just have a harder time with connection pooling because then I lose sight of who is logged in and from where. Probably just me though.
i once had to "lab coat" a developer for opening a connection for EVERYTHING he did. Found over 8k connections on the test server. They mysteriously disappeared when he closed VB6....
He spent 2 weeks converting to 1 app = 1 connection = 1 command object and KILL the da#@$&$ recordsets when done with them 
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Yeah, you're right. I went off on a .NET tagent.
Back to Frank's original question, "Is there any advantage of closing and reopening an ADO connection in almost any event?", it sounded as if the previous programmer had open/close on every event. This is definitely not a good practice. Connection pooling and opening/closing the connection at program start/form load are both viable options. Connection pooling would work great for Frank if the application makes frequent calls to the data store for connections. If Frank's application will be running in a Web-based or multi-tier environment, pooling is probably his best option 'cause, for one, you wouldn't want a new connection established every time a new Web page is opened. Otherwise, if the number of new connections to open is minimal, just do the open/close routine on form load/close. It didn't sound like this application was going to have too many users so scalability isn't really an issue here.
All the best,
DaleAuthor: An Introduction to SQL Server 2005 Management Studio
-
Hehe, I enjoyed this discussion. Now, if I were to develop this from the start, I wouldn't use VB6 anymore. Not sure, if I would use VB.Net. I think I would use either C# or MVC++ (yuk, yes). But as AJ said, this is VB6 and it is a relikt. I don't want to put more effort in it than it is worth. It is running, it is in production and someday it will rest in peace hopefully. Till then I'll keep it alive. But after thinking over it for a night, I think I'll show best practise pattern, even if it just for myself. Point taken, Antares! And thanks to all who replied
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
...but one question remains to be answered.
What has "Carpal tunnel" to do with 4,000 posts?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Who knows, Frank? The site owners will have to divulge that one. Maybe this joke will give you some clues...
You're addicted to the Internet if... ...your service provider calls *you* for tech support.
...someone at school/work tells you a joke and you say "LOL!" (Lots Of Laughs)
...three words: carpal tunnel syndrome.
...you come home from somewhere and wait for your friends to say "re."
...you get a second phone line, just so you can call to order pizza.
...you raise your hand in school and say "BRB." (Be Right Back)
...you begin to say hehehe instead of laughing.
...you know and regularly use more than 10 different ways to smile in ascii text.
...when someone says "What did you say?", you automatically say "scroll up!"
...you marry your cybergirl/boyfriend and the two of you sit across the room typing love messages to each other.
All the best,
DaleAuthor: An Introduction to SQL Server 2005 Management Studio
-
Brilliant!
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hello
I think it is better to open the connection and close only at the end of the application rather than opening connection and closing and again opening
anand
-
Frank,
Just remember with the connection pooling thing that you need to make sure that the creation of connections is under your control. Use COM+ object pooling over MS SQL Server connection pooling. You'll get better performance that way.
Good luck!
All the best,
DaleAuthor: An Introduction to SQL Server 2005 Management Studio
-
Hm, at first I thought I'd only keep this app alive and running. Hm, now I think I use it as a playground to study best practises. Once again, thanks to all and I'll surely be back with more questions.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank,
Here's some good reading from MSDN on best practices with VB6/MDAC. They may not all apply but are good to keep in your Bookmarks:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvs600/html/cfr.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html/pooling2.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnovba01/html/EnterpriseApps.asp
All the best,
DaleAuthor: An Introduction to SQL Server 2005 Management Studio
-
Oh Dale, where would we be here, without your knowledge of MSDN. I'll have them printed tomorrow.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply