How to check if connection is OK? 1

[ReportingAnalyst]

Hi,

Since I am getting this error:

Error Type:
Microsoft VBScript runtime (0x800A01FB)
An exception occurred: 'open'

At the line:

<%

Dim rs
Set rs = Server.CreateObject("ADODB.Recordset")
sSQL = "SELECT CategoryID, CategoryName FROM Categories Where CategoryID = 1 order by CategoryName"

rs.open sSQL, Conn
Response.Write sSQL

%>

I would like to check whether my database connection is working fine or not. How can I do that?

When I comment out the above lines and just leave the connection code, then the ASP page does not err.

THanks.

 

[Sheco]

You can do something like this:
[tt]
If Conn.State <> 1 Then
Response.Write "The ADO Connection is not ready."
Response.End
End If
[/tt]

 

[ReportingAnalyst]

Thanks for the tip.

When I do:

If Conn.State = 0 Then
  Response.Write "The ADO Connection is closed."
  Response.End
End If

If Conn.State = 1 Then
  Response.Write "The ADO Connection is open."
  Response.End
End If

I see the message "ADO Connection is open"

So I know that my connection string is fine.

However when I do this:

<%

Dim rs
Set rs = Server.CreateObject("ADODB.Recordset")
sSQL = "SELECT CategoryID, CategoryName FROM Categories Where CategoryID = 1 order by CategoryName"

[b]rs.open sSQL, Conn[\b]
Response.Write sSQL
%>

Why do I get an error at the above line where the error is:

Error Type:
Microsoft VBScript runtime (0x800A01FB)
An exception occurred: 'open'
/scripts/testing2/categories.asp, line 61

 

[ReportingAnalyst]

You know something, I am getting that error in all my ASP pages at the line similar to the above, though they refer to different tables of another database.

What could it mean? What should I be checking. I went back to the database server and I can see all the tables and data within them.

 

[Sheco]

What if it was flipped to get the open recordset as the return value of the connection object's Execute property:
[tt]
on error resume next
Set rs = Conn.Execute("SELECT * FROM Categories")
for each booboo in Conn.Errors
response.write "ADO Error Alert <BR>"
response.write booboo.Number & "<br>"
response.write booboo.Description & "<br>"
response.write booboo.Source & "<br><br>"
next

if err.Number <> 0 then
response.write "VBScript runtime Error Alert <BR>"
response.write "Number = " & err.Number & "<br>"
response.write "Description = " & err.Description & "<br>"
response.write "Source = " & err.Source & "<br>"
response.End
end if
[/tt]

 

[ReportingAnalyst]

This is the result I get on the browser:

The ADO Connection is open.
VBScript runtime Error Alert
Number = 507
Description = An exception occurred
Source = Microsoft VBScript runtime error

Now what should I do?

Thanks.

 

[Sheco]

Ah I was hoping there would be more info in the connection object's Errors collection but I guess that was empty.

If it is happening all over your site I'd guess that either the database is denying the authentication attempts or somehow the MDAC got hosed up on the web server.

 

[Sheco]

I'm assuming that things that used to work are now failing with this message even though they have not themselves been altered.

 

[ReportingAnalyst]

So what is the solution for this...

Thanks.

 

[ReportingAnalyst]

I restarted the IIS on the webserver and it fixed the issue.But what can I do at the code level to show to prevent this error? Or does it happen at times and we are supposed to start the IIS?

Thanks.

 

[Sheco]

Check to see if all the scripts with this failure are using the same ADO Connection string.

If so check to see if ANY scripts using that Connection string are working.

If the connection string never works then it is probably on the database side... but if no connection ever works anywhere on the server try installing the latest MDAC from microsoft.

 

[Sheco]

Hmmm, if restarting IIS fixed it then perhaps the ADO connection string was being stored in an Application variable and somehow it got erased or changed?

 

[ReportingAnalyst]

Does this script mean that I am storing it in an application variable?

'Dim conn

Set conn = Server.CreateObject("ADODB.Connection")

function GetConnectionString()
	GetConnectionString = "DSN=DSNName;uid=Username;pwd=password;"
end function

conn.open GetConnectionString()

I do not think so. Because application variables are more like:

application("variable")

THanks.

 

[ReportingAnalyst]

All of the scripts had failed and all pointed to the same database.asp file where I have declared and set the connection.

But they all work.

Now the code within the dbconnect.asp is like this:

'Dim conn

Set conn = Server.CreateObject("ADODB.Connection")

function GetConnectionString()
	GetConnectionString = "DSN=DSNName;uid=UserID;pwd=password;"
end function

conn.open GetConnectionString()

So you meant that GetConnectionString is an application variable here?

 

[Sheco]

If it was an application variable it would look like this:[tt]
conn.open application("variable")
[/tt]

So thats not it.

This is not one of the common problems. Has it happened to you before? Did you restart the IIS service or reboot the machine?

 

[ReportingAnalyst]

Nope it has happened to me the first time in the past 4 years! However when I re-start the IIS Service, all is gone.

So I have to make a note that next time I see this kind of error, I need to re-start the IIS Server.

Thanks for all the ideas and inputs.