If remote sql server is not available?

[les]

I have data on a website homepage from a remote sql server. When the
remote server is unavailable, the page will fail.

How can I replace the data that should be displayed with an error
message, rather than the whole page failing?

THanks
Leslie

[Mads Holm]

The following code should do the trick

Dim objConn
on error resume next
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString="your connection string"
objConn.Open
if err.Number<>0 or objConn.Errors.Count>0 then
Response.Write "This page is temporarily out of order"
Response.end
end if

Regards
Mads Holm





"les" <les@asasdad.com> skrev i en meddelelse
news:3f0154f9.2506244812@msnews.microsoft.com...

> I have data on a website homepage from a remote sql server. When the
> remote server is unavailable, the page will fail.
>
> How can I replace the data that should be displayed with an error
> message, rather than the whole page failing?
>
> THanks
> Leslie

[Mads Holm]

Do you mean that you are'nt able to adopt the code that I showed?

If so, please post the code that you're using.

Regards
Mads Holm

[les]

Well I tried that code, but if the database server is not available,
the whole page still fails (with the standard '80004005' error),
rather than the error message in the appropriate part of the page.

Curiously, if this code is included, the error message is displayed
when there are no errors - as well as the data from the recordset.

Leslie

The code I am using is:
===================
Dim ConnVac
Set ConnVac=Server.CreateObject("ADODB.Connection")
ConnVac.Open "PROVIDER=MSDASQL;" & _
"DRIVER={SQL Server};" & _
"SERVER=192.168.175.18;DATABASE=DBname;" & _
"UID=iuser;PWD=password;"

Set rsVac = Server.CreateObject("ADODB.RecordSet")
sqlVac = "SELECT Count(table1.id) AS recno FROM table1 WHERE
ClosingDate +1>= GETDATE() AND IsClosed = 0 ;"
rsVac.Open sqlVac, connVac, 3, 3

on error resume next
if err.Number<>0 or ConnVac.Errors.Count>0 then
Response.Write "This page is temporarily out of order"
Response.end
end if
======================

On Wed, 2 Jul 2003 11:43:31 +0200, "Mads Holm"
<mho@lrsoftware.dontusethispart.dk> wrote:

>Do you mean that you are'nt able to adopt the code that I showed?
>
>If so, please post the code that you're using.
>
>Regards
>Mads Holm
>
>

[Mads Holm]

Leslie,

you have to put

on error resume next

before you try to open the connection as it is this sentence that instructs
the ASP interpreter to ignore errors so that you are able to check for
errors yourself rather than letting the page fail on error.

Regards
Mads Holm

[les]

OK got it this time.

Thanks again Mads
Leslie

On Wed, 2 Jul 2003 13:46:53 +0200, "Mads Holm"
<mho@lrsoftware.dontusethispart.dk> wrote:

>Leslie,
>
>you have to put
>
>on error resume next
>
>before you try to open the connection as it is this sentence that instructs
>the ASP interpreter to ignore errors so that you are able to check for
>errors yourself rather than letting the page fail on error.
>
>Regards
>Mads Holm
>
>

[Mads Holm]

What error is it (since it seems not to be on opening the conn)?

Try:

if err.Number<>0 then
Response.Write "This page is temporarily out of order<BR>"
Response.Write "Error number:" & Err.Number & " - " & Err.Description
Response.end
else
if ConnVac.Errors.Count>0 then
Response.Write "This page is temporarily out of order<BR>"
For Each objError In ConnVac.Errors
strError = strError & "Error #" & objError.Number & _
" - " & objError.Description & "<BR>" & _
"NativeError: " & objError.NativeError & "<BR>" & _
"SQLState: " & objError.SQLState & "<BR>" & _
"Reported by: " & objError.Source & "<BR>"
Next
Response.Write strError
Response.end
end if
end if

"les" <les@asasdad.com> skrev i en meddelelse
news:3f02d72e.94012781@msnews.microsoft.com...

> I spoke too soon. The error is displayed whether:
>
> if err.Number<>0 or ConnVac.Errors.Count>0
>
> applies or not
>
>
> On Wed, 02 Jul 2003 12:45:25 GMT, [email]les@asasdad.com[/email] (les) wrote:
>

> >OK got it this time.
> >
> >Thanks again Mads
> >Leslie
> >
> >On Wed, 2 Jul 2003 13:46:53 +0200, "Mads Holm"
> ><mho@lrsoftware.dontusethispart.dk> wrote:
> >

> >>Leslie,
> >>
> >>you have to put
> >>
> >>on error resume next
> >>
> >>before you try to open the connection as it is this sentence that

instructs

> >>the ASP interpreter to ignore errors so that you are able to check for
> >>errors yourself rather than letting the page fail on error.
> >>
> >>Regards
> >>Mads Holm
> >>
> >>

> >

>

[les]

Now I get this if the database is ok:

This page is temporarily out of order
Error #0 - [Microsoft][ODBC SQL Server Driver]Cursor type changed
NativeError: 0
SQLState: 01S02
Reported by: Microsoft OLE DB Provider for ODBC Drivers
Error #0 - [Microsoft][ODBC SQL Server Driver]Cursor concurrency
changed
NativeError: 0
SQLState: 01S02
Reported by: Microsoft OLE DB Provider for ODBC Drivers

and if the db is not ok:

This page is temporarily out of order
Error number:3709 - The application requested an operation on an
object with a reference to a closed or invalid Connection object.

(i changed the connection string to simulate the db not being found)

Leslie




On Wed, 2 Jul 2003 15:41:13 +0200, "Mads Holm"
<mho@lrsoftware.dontusethispart.dk> wrote:

>What error is it (since it seems not to be on opening the conn)?
>
>Try:
>
>if err.Number<>0 then
> Response.Write "This page is temporarily out of order<BR>"
> Response.Write "Error number:" & Err.Number & " - " & Err.Description
> Response.end
> else
> if ConnVac.Errors.Count>0 then
> Response.Write "This page is temporarily out of order<BR>"
> For Each objError In ConnVac.Errors
> strError = strError & "Error #" & objError.Number & _
> " - " & objError.Description & "<BR>" & _
> "NativeError: " & objError.NativeError & "<BR>" & _
> "SQLState: " & objError.SQLState & "<BR>" & _
> "Reported by: " & objError.Source & "<BR>"
> Next
> Response.Write strError
> Response.end
> end if
>end if
>
>"les" <les@asasdad.com> skrev i en meddelelse
>news:3f02d72e.94012781@msnews.microsoft.com...

>> I spoke too soon. The error is displayed whether:
>>
>> if err.Number<>0 or ConnVac.Errors.Count>0
>>
>> applies or not
>>
>>
>> On Wed, 02 Jul 2003 12:45:25 GMT, [email]les@asasdad.com[/email] (les) wrote:
>>

>> >OK got it this time.
>> >
>> >Thanks again Mads
>> >Leslie
>> >
>> >On Wed, 2 Jul 2003 13:46:53 +0200, "Mads Holm"
>> ><mho@lrsoftware.dontusethispart.dk> wrote:
>> >
>> >>Leslie,
>> >>
>> >>you have to put
>> >>
>> >>on error resume next
>> >>
>> >>before you try to open the connection as it is this sentence that

>instructs

>> >>the ASP interpreter to ignore errors so that you are able to check for
>> >>errors yourself rather than letting the page fail on error.
>> >>
>> >>Regards
>> >>Mads Holm
>> >>
>> >>
>> >

>>

>
>

[Mark Schupp]

You only want messages if there is an error. Try

if err.Number<>0 then
Response.Write "This page is temporarily out of order<BR>"
Response.Write "Error number:" & Err.Number & " - " & Err.Description
For Each objError In ConnVac.Errors
strError = strError & "Error #" & objError.Number & _
" - " & objError.Description & "<BR>" & _
"NativeError: " & objError.NativeError & "<BR>" & _
"SQLState: " & objError.SQLState & "<BR>" & _
"Reported by: " & objError.Source & "<BR>"
Next
Response.Write strError
Response.end
end if

Note: You will get the error from the err object twice this way (it will be
duplicated in the connection errors collection). You can filter out the
duplicate message by comparing objError.Number with err.Number.

--
Mark Schupp
--
Head of Development
Integrity eLearning
Online Learning Solutions Provider
[email]mschupp@ielearning.com[/email]
[url]http://www.ielearning.com[/url]
714.637.9480 x17


"les" <les@asasdad.com> wrote in message
news:3f02f47c.101514750@msnews.microsoft.com...

> Now I get this if the database is ok:
>
> This page is temporarily out of order
> Error #0 - [Microsoft][ODBC SQL Server Driver]Cursor type changed
> NativeError: 0
> SQLState: 01S02
> Reported by: Microsoft OLE DB Provider for ODBC Drivers
> Error #0 - [Microsoft][ODBC SQL Server Driver]Cursor concurrency
> changed
> NativeError: 0
> SQLState: 01S02
> Reported by: Microsoft OLE DB Provider for ODBC Drivers
>
> and if the db is not ok:
>
> This page is temporarily out of order
> Error number:3709 - The application requested an operation on an
> object with a reference to a closed or invalid Connection object.
>
> (i changed the connection string to simulate the db not being found)
>
> Leslie
>
>

[les]

Thanks. That's what I needed (apart from the response.end as I want to
continue showing the rest of the page)

Leslie


On Wed, 2 Jul 2003 08:52:39 -0700, "Mark Schupp"
<mschupp@ielearning.com> wrote:

>You only want messages if there is an error. Try
>
>if err.Number<>0 then
> Response.Write "This page is temporarily out of order<BR>"
> Response.Write "Error number:" & Err.Number & " - " & Err.Description
> For Each objError In ConnVac.Errors
> strError = strError & "Error #" & objError.Number & _
> " - " & objError.Description & "<BR>" & _
> "NativeError: " & objError.NativeError & "<BR>" & _
> "SQLState: " & objError.SQLState & "<BR>" & _
> "Reported by: " & objError.Source & "<BR>"
> Next
> Response.Write strError
> Response.end
>end if
>
>Note: You will get the error from the err object twice this way (it will be
>duplicated in the connection errors collection). You can filter out the
>duplicate message by comparing objError.Number with err.Number.
>
>--
>Mark Schupp
>--
>Head of Development
>Integrity eLearning
>Online Learning Solutions Provider
>mschupp@ielearning.com
>[url]http://www.ielearning.com[/url]
>714.637.9480 x17
>
>
>"les" <les@asasdad.com> wrote in message
>news:3f02f47c.101514750@msnews.microsoft.com...

>> Now I get this if the database is ok:
>>
>> This page is temporarily out of order
>> Error #0 - [Microsoft][ODBC SQL Server Driver]Cursor type changed
>> NativeError: 0
>> SQLState: 01S02
>> Reported by: Microsoft OLE DB Provider for ODBC Drivers
>> Error #0 - [Microsoft][ODBC SQL Server Driver]Cursor concurrency
>> changed
>> NativeError: 0
>> SQLState: 01S02
>> Reported by: Microsoft OLE DB Provider for ODBC Drivers
>>
>> and if the db is not ok:
>>
>> This page is temporarily out of order
>> Error number:3709 - The application requested an operation on an
>> object with a reference to a closed or invalid Connection object.
>>
>> (i changed the connection string to simulate the db not being found)
>>
>> Leslie
>>
>>

>
>