Wrox Programmer Forums
| 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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old February 15th, 2010, 11:03 AM
Banned
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.
  #2 (permalink)  
Old February 15th, 2010, 02:54 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
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
...
  #3 (permalink)  
Old February 15th, 2010, 05:02 PM
Banned
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.
  #4 (permalink)  
Old February 15th, 2010, 05:18 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
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.
  #5 (permalink)  
Old February 15th, 2010, 05:27 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 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
  #6 (permalink)  
Old February 15th, 2010, 05:59 PM
Banned
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:)
  #7 (permalink)  
Old February 16th, 2010, 04:13 PM
Banned
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?
  #8 (permalink)  
Old February 16th, 2010, 04:22 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
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.
  #9 (permalink)  
Old February 16th, 2010, 04:25 PM
Banned
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!


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





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.