The Web Design Group

... Making the Web accessible to all.

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Deliberate DSN-Less Connection to SQL Server Database, Convert Error Message to Meaningful Text
JohnnySteel
post Nov 5 2018, 10:13 PM
Post #1


Newbie
*

Group: Members
Posts: 16
Joined: 8-June 16
Member No.: 24,307



Sorry, the heading should read "Deliberate DSN-Less Connection Failure to SQL Server Database". I couldn't change this as there does not appear to be means to do so.

I'm getting an (expected) error message with a DSN-Less connection to a SQL database on a SQL Server that says:

"Microsoft OLE DB Provider for SQL Server error '80040e4d'
Login failed for user '<username>'"

Now, i know why the connection failed as it was deliberate on my part to cause this error. I want to trap this error and convert it to text to say the database is not available, such as when it is shut down to do software maintenance on it, etc., rather than display the error message like the one given above.

How do i trap this error and convert it into some meaningful text on the web page to say the database is not available?

The code i am using is ASP Classic.

Thank you.

This post has been edited by JohnnySteel: Nov 5 2018, 10:41 PM
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post Nov 6 2018, 01:49 AM
Post #2


Programming Fanatic
********

Group: Members
Posts: 1,981
Joined: 27-April 13
From: Edinburg, Texas
Member No.: 19,088



Most likely you would have to dig through the Microsoft SDK to find the list of SQL Server error codes and their meaning. You might get more information by posting on the Microsoft forum.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
JohnnySteel
post Nov 6 2018, 07:39 PM
Post #3


Newbie
*

Group: Members
Posts: 16
Joined: 8-June 16
Member No.: 24,307



Thank you. I'm not so much concerned about the error codes, etc., as i already have these defined. What I'm trying to do is to catch the failed connection event and convert the connection failure into words like "Database Unavailable" on the web page rather than showing the default error message as noted previously.

Trying to find that in MSDN is like looking for a needle in a haystack.

This post has been edited by JohnnySteel: Nov 6 2018, 07:42 PM
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post Nov 7 2018, 01:30 AM
Post #4


Programming Fanatic
********

Group: Members
Posts: 1,981
Joined: 27-April 13
From: Edinburg, Texas
Member No.: 19,088



If I understand you correctly you could call an error handling function, with the error number as a parameter. That function could contain a switch statement that takes the parameter and returns any words you want, based on the error number (parameter).

Or are you asking for help trapping the errors? I guess I don't understand completely.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
JohnnySteel
post Nov 11 2018, 09:13 PM
Post #5


Newbie
*

Group: Members
Posts: 16
Joined: 8-June 16
Member No.: 24,307



Thank you. This is the answer I came up with, and it works:

ON ERROR RESUME NEXT
conntemp.open myDSN 'This activates the database connection, as given above this code.
IF ERR.NUMBER <>0 THEN 'There is far more than just one error code for this failure. Hence, "<> 0 ".
DBConnection="0"
ELSE
DBConnection="1"
END IF
ERR.CLEAR

Then in the web page, retrieve the value for DBConnection and evaluate it. Where DBConnection="0", this indicates a failure to connect. For connection failuire, display the text "Database Unavailable" on the web page, otherwise, continue with producing the asp web page normally.

Testing has shown it to work well. Now implementing it into the production system.

Does this all make sense?
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post Nov 11 2018, 11:08 PM
Post #6


Programming Fanatic
********

Group: Members
Posts: 1,981
Joined: 27-April 13
From: Edinburg, Texas
Member No.: 19,088



So, this is for ASP? I didn't understand that point, glad you got it worked out.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Christian J
post Nov 12 2018, 02:25 AM
Post #7


.
********

Group: WDG Moderators
Posts: 9,628
Joined: 10-August 06
Member No.: 7



QUOTE(JohnnySteel @ Nov 6 2018, 04:13 AM) *

Sorry, the heading should read "Deliberate DSN-Less Connection Failure to SQL Server Database". I couldn't change this as there does not appear to be means to do so.

A moderator can edit the heading if you still want it changed.

QUOTE(JohnnySteel @ Nov 7 2018, 01:39 AM) *

I'm not so much concerned about the error codes, etc., as i already have these defined. What I'm trying to do is to catch the failed connection event

I don't know ASP, but maybe a try/catch statement could work (depending on ASP scripting language, see also https://stackoverflow.com/questions/472558/...-in-asp-classic ).
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post Nov 12 2018, 12:49 PM
Post #8


Programming Fanatic
********

Group: Members
Posts: 1,981
Joined: 27-April 13
From: Edinburg, Texas
Member No.: 19,088



I haven't used vbscript in many years but I don't think it supports try/catch (unless it was added in recent years). In that case 'ON ERROR RESUME NEXT' is the way to go. I believe there should be a 'On Error GoTo 0' at the end, to turn on error checking again.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post

Reply to this topicStart new topic
2 User(s) are reading this topic (2 Guests and 0 Anonymous Users)
0 Members:

 



- Lo-Fi Version Time is now: 19th March 2024 - 12:19 AM