Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP 3 Classic ASP Active Server Pages 3.0 > Classic ASP Professional
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Classic ASP Professional For advanced coder questions in ASP 3. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Professional section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old February 15th, 2010, 11:03 AM
Friend of Wrox
Points: 1,561, Level: 16
Points: 1,561, Level: 16 Points: 1,561, Level: 16 Points: 1,561, Level: 16
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2005
Location: , , .
Posts: 317
Thanks: 0
Thanked 0 Times in 0 Posts
Question How to deal with SQL connection error in ASP code

We have an application that pulls data from several servers, and one of the external servers that we don't have control of went down recently. When our code attempted to open the connection string to that server, it pulled our entire application down.

So I need to know how to check that a connection is working first *before* attempting to open it, so that it won't bring everything else down with it. here is what the connection string looks like:

Code:
Dim strConn As String
strConn = "Provider=SQLOLEDB; Data Source=SERVERNAME; Initial Catalog=DATABASENAME; User ID=USERID; Password=PASSWORD"
And here is the line of code that had the error:

Code:
objConn = Server.CreateObject("ADODB.Connection")
        objConn.Open (strConn) '<<<----- ERROR OCCURRED HERE!!!
And and all help is appreciated.
__________________
KWilliams
Reply With Quote
  #2 (permalink)  
Old February 15th, 2010, 02:54 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,652
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Code:
<%
...
objConn = Server.CreateObject("ADODB.Connection")
On Error Resume Next
    objConn.Open (strConn)
On Error GoTo 0
If objConn.State <> 1 Then
    ... unable to connect to that db ...
    ... so you get to figure out how to handle that ...
    ... note that objConn is now useless, so don't attempt to use it ...
 
Else
    ... should be okay 
 
End If
...
Reply With Quote
  #3 (permalink)  
Old February 15th, 2010, 05:02 PM
Friend of Wrox
Points: 1,561, Level: 16
Points: 1,561, Level: 16 Points: 1,561, Level: 16 Points: 1,561, Level: 16
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2005
Location: , , .
Posts: 317
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the quick response. I'll take your suggested code and give it a try. thanks.
__________________
KWilliams
Reply With Quote
  #4 (permalink)  
Old February 15th, 2010, 05:18 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,652
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Fair warning: The attempt to open *could* take some time if you are trying to open across a network and the network is down. Because the driver will need to wait for the timeout on the network connection. You can control that by specifying a shorter time for the connectiontimeout:
Code:
Set conn = ...
conn.ConnectionTimeout = 10 
On Error Resume Next
    conn.Open ...
Just don't set the time too short so that even normal connection opens fail.
Reply With Quote
  #5 (permalink)  
Old February 15th, 2010, 05:27 PM
Friend of Wrox
Points: 6,468, Level: 34
Points: 6,468, Level: 34 Points: 6,468, Level: 34 Points: 6,468, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,835
Thanks: 12
Thanked 16 Times in 16 Posts
Send a message via AIM to mat41
Default

Nice! This has happened once in a blue moon for me....
__________________
Wind is your friend
Matt
Reply With Quote
  #6 (permalink)  
Old February 15th, 2010, 05:59 PM
Friend of Wrox
Points: 1,561, Level: 16
Points: 1,561, Level: 16 Points: 1,561, Level: 16 Points: 1,561, Level: 16
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2005
Location: , , .
Posts: 317
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for that information. Everything seems to be working. Hopefully it won't happen anytime soon though:)
__________________
KWilliams
Reply With Quote
  #7 (permalink)  
Old February 16th, 2010, 04:13 PM
Friend of Wrox
Points: 1,561, Level: 16
Points: 1,561, Level: 16 Points: 1,561, Level: 16 Points: 1,561, Level: 16
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2005
Location: , , .
Posts: 317
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Old Pendant,

I've added the code without a problem, but I'm not sure how to check that what I've added works because that connection is now working. Do you have any suggestions on how I could test it?
__________________
KWilliams
Reply With Quote
  #8 (permalink)  
Old February 16th, 2010, 04:22 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,652
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Sure. Turn off the SQL Server engine that you are trying to connect to.

Hopefully, you can do this at some point in time then the sytem isn't in use.

Alternative: Install SQL Server (could be Express version, so free) on some other machine and connect to it only for this purpose. Then shut it off.
Reply With Quote
  #9 (permalink)  
Old February 16th, 2010, 04:25 PM
Friend of Wrox
Points: 1,561, Level: 16
Points: 1,561, Level: 16 Points: 1,561, Level: 16 Points: 1,561, Level: 16
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2005
Location: , , .
Posts: 317
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for that information. I wouldn't be able to do the first, but I already have SQL Server Express installed on my machine, so the second option should work. Thanks again for your help!
__________________
KWilliams
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
sql connection error navbingo20 ASP.NET 2.0 Basics 0 June 23rd, 2008 06:07 PM
connection error with SQL sh.rajkumar ASP.NET 2.0 Basics 4 June 6th, 2006 12:30 AM
connection error with SQL sh.rajkumar ASP.NET 2.0 Professional 0 June 1st, 2006 09:48 PM
how to deal with the run time error="6" zouky VB How-To 1 September 25th, 2004 06:37 AM
How to deal with String has ' in SQL Statement chiyahu Classic ASP Databases 1 August 28th, 2003 05:00 PM



All times are GMT -4. The time now is 01:43 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.