ADODB.Connection error "800a0bb9" -- Help!!!!!

Hi, folks:

Thank you in advance for you kind input. My ASP program is supposed to
retrieve some information from a remote database and list the retrieved
information in a browser. It was working fine the day before
yesterday, but the exactly same code suddenly stopped working yesterday
- I did not change anything of my code. This is the weirdest thing I
have ever encounter!

The environment I'm on is Windows2000, IIS 5.0. The remote database
is on MS SQL server 2000.

Here is a summary of my code, the error message and unsuccessfully
efforts I have undertaken.

1. My code:
------------------------------------------------------------ -------
<%@LANGUAGE = VBScript%>

....

Dim adoCon
Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.Open "DSN=MyServer;UID=lum;PWD=c4yx!!99;"

Dim Output
Set Output = Server.CreateObject("ADODB.Recordset")

Dim sID
sID = Request.Form("id")

Dim spSQL
Set spSQL = adoCon.Execute("Execute SP_Search '" & sID &" '")

Output.Open spSQL 'HERE IS THE ERROR OCCURED!!
....
------------------------------------------------------------ -------

2. The error message is:

------------------------------------------------------------ -------
ADODB.Connection error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.
------------------------------------------------------------ -------


3. My un-successful debugging efforts

I searched the web. Many helps states that there are two possible
errors.

(1) the ADO constants that I used are not including the adovbs.inc
file.
My efforts: I double checked my adovbs.inc file. It contains all the
constants that I used.
I also tried to change the commend for the ADO Recordset's Open method
as follow, but none of them works:

Output.Open spSQL,adoCon,adOpenForwardOnly,adLockReadOnly,adCmdText


Output.Open
spSQL,adoCon,adOpenForwardOnly,adLockReadOnly,adCmdStoredPro c

Output.Open spSQL, adoCon, 0, 1, &H0001

Output.Open spSQL, adoCon, 0, 1, &H0004

(2) the global.asa is not firing
My efforts: I followed the steps suggested by the Microsoft support
page at http://support.microsoft.com/kb/q197323/
to set up the virtual directory which contains the global.asa file as
an 'Application'. I restart the IIS after I made the change. Still
not working.


(3) During my troubleshooting, it's worth to mention that the data is
retrieved and displayed in the asp page successfully when I changed the
stored proc to a Select statement, with nothing else changed:

When I changed the following line :
Set spSQL = adoCon.Execute("Execute SP_Search '" & sID &" '")
Output.Open spSQL

To:
Set spSQL = adoCon.Execute("Select * From T_INFO Where id = '" & sID &"
'")
Output.Open spSQL

It seems the recordset can returns the result of a select statement but
not the stored proc at this point. However, as I said, it used to work
for my stored proc the day before yesterday and my stored proc,
SP_Search, has been working fine all along when executed on the Query
Analyzer.

I'm going nuts...............................

Any suggestion would be appreciated!

Re: ADODB.Connection error "800a0bb9" -- Help!!!!!

lluum@yahoo.com wrote:
> Hi, folks:
> Dim adoCon
> Set adoCon = Server.CreateObject("ADODB.Connection")
> adoCon.Open "DSN=MyServer;UID=lum;PWD=c4yx!!99;"
>
> Dim Output
> Set Output = Server.CreateObject("ADODB.Recordset")
>
> Dim sID
> sID = Request.Form("id")
>
> Dim spSQL
> Set spSQL = adoCon.Execute("Execute SP_Search '" & sID &" '")

OK, spSQL now contains the recordset returned by the Execute method. What
did you think spSQL contained at this point?


>
> Output.Open spSQL 'HERE IS THE ERROR OCCURED!!

Now you attempt to open another recordset on the recordset contained in
spSQL??? Why?
> ...
> ------------------------------------------------------------ -------
>
> 2. The error message is:
>
> ------------------------------------------------------------ -------
> ADODB.Connection error '800a0bb9'
>
> Arguments are of the wrong type, are out of acceptable range, or are
> in conflict with one another.
> ------------------------------------------------------------ -------
>

Well, yeah! The first argument of the Open method is supposed to be a string
containing the "source", usually a sql statement. You're giving it a
variable containing a recordset object! No wonder the "Arguments are of the
wrong type ... "

If you don't have MSDN or Visual Studio, the ADO documentation can be found
at msdn.microsoft.com/library. Here are links to the documentation for the
Connection.Execute and Recordset.Open methods:
http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthcnne xecute.asp
http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthrsto pen.asp


The bottom line is: you already have an open recordset. There is no need to
use another Open statement. Assuming that you don't take the advice I will
provide later on, here is how the revised code should look:


Dim adoCon
Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.Open "DSN=MyServer;UID=lum;PWD=c4yx!!99;"

Dim Output

Dim sID
sID = Request.Form("id")

'you realy should validate this variable at this point -
'at least make sure it contains something

Set Output= adoCon.Execute("Execute SP_Search '" & sID &" '",,1)
'1 = adCmdText You should always tell ADO what you are executing



Suggestion:
by using dynamic sql instead of parameters to execute your stored
procedure you are:
1. Undoing the performance benefits you gained from using the stored
procedure
2. Leaving yourself open to sql injection attack:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

It is much more efficient, as well as more secure, to use parameters to pass
values to a stored procedure. See:
http://tinyurl.com/jyy0


HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: ADODB.Connection error "800a0bb9" -- Help!!!!!

Hi Bob, thank you so much for your kind help. I really aprrecite it.
I read through the links you gave me. Now I have some straw to grab
on...

I did what you suggested (as shown below)

> Dim adoCon
> Set adoCon = Server.CreateObject("ADODB.Connection")
> adoCon.Open "DSN=MyServer;UID=lum;PWD=c4yx!!99;"
>
> Dim Output
>
> Dim sID
> sID = Request.Form("id")
>
> Set Output= adoCon.Execute("Execute SP_Search '" & sID &" '",,1)

After the Execute statement when I try to access the recordset Output
by using the following statement:

Do While not Output.EOF

I am getting the following error:

Error Type:
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.

It appeared that the Recordset is closed.

If I replace the statement
Set Output= adoCon.Execute("Execute SP_Search '" & sID &" '",,1)
by a simple select statement
Set Output= adoCon.Execute("SELECT * FROM T_info where ID = '" & sID &
"'",,1)

Things worked.

What did I miss here?

Thank you again for the help...

Alex

Re: ADODB.Connection error "800a0bb9" -- Help!!!!!

Just an FYI incase it is....... you shouldn't be posting your DB
username/password in public ;o)

Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

wrote in message
news:1121028835.475768.40630@f14g2000cwb.googlegroups.com...
> Hi Bob, thank you so much for your kind help. I really aprrecite it.
> I read through the links you gave me. Now I have some straw to grab
> on...
>
> I did what you suggested (as shown below)
>
> > Dim adoCon
> > Set adoCon = Server.CreateObject("ADODB.Connection")
> > adoCon.Open "DSN=MyServer;UID=lum;PWD=c4yx!!99;"
> >
> > Dim Output
> >
> > Dim sID
> > sID = Request.Form("id")
> >
> > Set Output= adoCon.Execute("Execute SP_Search '" & sID &" '",,1)
>
> After the Execute statement when I try to access the recordset Output
> by using the following statement:
>
> Do While not Output.EOF
>
> I am getting the following error:
>
> Error Type:
> ADODB.Recordset (0x800A0E78)
> Operation is not allowed when the object is closed.
>
> It appeared that the Recordset is closed.
>
> If I replace the statement
> Set Output= adoCon.Execute("Execute SP_Search '" & sID &" '",,1)
> by a simple select statement
> Set Output= adoCon.Execute("SELECT * FROM T_info where ID = '" & sID &
> "'",,1)
>
> Things worked.
>
> What did I miss here?
>
> Thank you again for the help...
>
> Alex
>

Re: ADODB.Connection error "800a0bb9" -- Help!!!!!

lluum@yahoo.com wrote:
> Hi Bob, thank you so much for your kind help. I really aprrecite it.
> I read through the links you gave me. Now I have some straw to grab
> on...
>
> I did what you suggested (as shown below)
>
>> Dim adoCon
>> Set adoCon = Server.CreateObject("ADODB.Connection")

Nothing to do with your error, but, with IIS5+ you should not use the
Server.Createobject method. Instead use the vbscript CreateObject method:

Set adoCon = CreateObject("ADODB.Connection")

>> adoCon.Open "DSN=MyServer;UID=lum;PWD=c4yx!!99;"
>>
>> Dim Output
>>
>> Dim sID
>> sID = Request.Form("id")

Again, you should validate sID at this point ...

>>
>> Set Output= adoCon.Execute("Execute SP_Search '" & sID &" '",,1)
>
> After the Execute statement when I try to access the recordset Output
> by using the following statement:
>
> Do While not Output.EOF
>
> I am getting the following error:
>
> Error Type:
> ADODB.Recordset (0x800A0E78)
> Operation is not allowed when the object is closed.
>
> It appeared that the Recordset is closed.
>
> If I replace the statement
> Set Output= adoCon.Execute("Execute SP_Search '" & sID &" '",,1)
> by a simple select statement
> Set Output= adoCon.Execute("SELECT * FROM T_info where ID = '" & sID &
> "'",,1)
>
> Things worked.
>
> What did I miss here?
>
I can't be sure. If you do this:

dim sSQL
sSQL="Execute SP_Search '" & sID &" '"
Response.Write sSQL
Response.End

After you run the page, do you have a statement that can be copied from the
browser window, pasted into QA and run without modification? If so, does it
give you the expected results?

If you do not have "SET NOCOUNT ON" as the first line in the body of your
stored procedure, and you are doing any DML (data modification language)
queries in your stored procedure, this error can result, because SQL Server
sends the informational messages you see in QA ("x rows were affected ... ")
to the client in the form of extra resultsets. It's always a good practice
to start your procedures by setting nocount on.


My preference (which you would have seen if you read the last link I posted)
would be to run this procedure like this:

dim Output
set output=create0bject("adodb.recordset")

adoCon.SP_Search sID, output

Lastly, it is a bad idea to prefix your stored procedure with "sp_". You pay
a performance penalty when you do so, since SQL Server expects procedures
with that prefix to be system procedures, looking for them first in Master,
and only looking for the one in the current database when it does not find
it in Master. Worse, if you give your procedure the same name as a system
procedure and execute it, the system procedure will be run, not your custom
procedure.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: ADODB.Connection error "800a0bb9" -- Help!!!!!

Hi Bob,

> I can't be sure. If you do this:
>
> dim sSQL
> sSQL="Execute SP_Search '" & sID &" '"
> Response.Write sSQL
> Response.End
>
> After you run the page, do you have a statement that can be copied from the
> browser window, pasted into QA and run without modification? If so, does it
> give you the expected results?

YES. I add this piece code in and when I enter 331 and run the page, I
got:
SP_Search 331, which I copied into QA and the stored proc run well. I
also verified the sID right after the sID = Request.Form("id") by using
Response.Write sID and the value, 331 showed up in my browser. Based on
this test, the argument seems passed correctly to the stored proc.


> If you do not have "SET NOCOUNT ON" as the first line in the body of your
> stored procedure, and you are doing any DML (data modification language)
> queries in your stored procedure, this error can result, because SQL Server
> sends the informational messages you see in QA ("x rows were affected ... ")
> to the client in the form of extra resultsets. It's always a good practice
> to start your procedures by setting nocount on.

Yes. I always put it in the first line of my procedure, including this
one.

> My preference (which you would have seen if you read the last link I posted)
> would be to run this procedure like this:
>
> dim Output
> set output=create0bject("adodb.recordset")
>
> adoCon.SP_Search sID, output

No luck for me - I tried to do it this way but I got:

Microsoft VBScript runtime (0x800A000D)
Type mismatch: 'Create0bject'

I will try to play the code a little bit more and see what else I can
find.

> Lastly, it is a bad idea to prefix your stored procedure with "sp_". You pay
> a performance penalty when you do so, since SQL Server expects procedures
> with that prefix to be system procedures, looking for them first in Master,
> and only looking for the one in the current database when it does not find
> it in Master. Worse, if you give your procedure the same name as a system
> procedure and execute it, the system procedure will be run, not your custom
> procedure.

Thank you very much for the advice. Yup, usually I use "LP_" or "UP_".
But it's good to know that I should avoid using "SP_" by all means.

Thank you again for your valuable inputs. I'll update when I have
more clue.

Alex

Re: ADODB.Connection error "800a0bb9" -- Help!!!!!

lluum@yahoo.com wrote:
>>
>> dim Output
>> set output=create0bject("adodb.recordset")
>>
>> adoCon.SP_Search sID, output
>
> No luck for me - I tried to do it this way but I got:
>
> Microsoft VBScript runtime (0x800A000D)
> Type mismatch: 'Create0bject'
>

Let's isolate this. It sounds as if you have a problem with your MDAC
installation. Create a page containing only:

<%
dim Output
set output=create0bject("adodb.recordset")
%>

If running this test page results in the mismatch error, then you have a
problem with MDAC (ADO) which needs to be corrected. If you don't get an
error, then add this:

<%
dim adoCon
Set adoCon = CreateObject("ADODB.Connection")

dim Output
set output=create0bject("adodb.recordset")
%>

Still no error? Then add this (switching to an OLE DB connection string
rather than the outmoded ODBC connection you are using):


<%
dim adoCon
Set adoCon = CreateObject("ADODB.Connection")
adoCon.Open "Provider=SQLOLEDB;" & _
"Data Source=MyServer;UID=lum;PWD=c4yx!!99;"
dim Output
set output=create0bject("adodb.recordset")
cn.close
%>

Still no error?

<%
dim adoCon
Set adoCon = CreateObject("ADODB.Connection")
adoCon.Open "Provider=SQLOLEDB;" & _
"Data Source=MyServer;UID=lum;PWD=c4yx!!99;"
dim Output
set output=create0bject("adodb.recordset")
adoCon.SP_Search "133", output
if not output.eof then
response.write output.getstring(,,"; ","
")
else
response.write "Empty recordset")
end if
output.close:set output=nothing
cn.close:set output = nothing
%>

If you still have problems, show us the CREATE PROCEDURE statement -
actually, all we need to see is the parameter declaration section so we know
what datatype the parameter should be (I guessed it was a string parameter,
but you tested wiuth a numeric input so I have doubts)

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: ADODB.Connection error "800a0bb9" -- Help!!!!!

> Let's isolate this. It sounds as if you have a problem with your MDAC
> installation. Create a page containing only:
>
> <%
> dim Output
> set output=create0bject("adodb.recordset")
> %>
>
> If running this test page results in the mismatch error, then you have a
> problem with MDAC (ADO) which needs to be corrected.

Rock on... I got the mismatch error just by running this piece of code.
I will look up the MSDN. Any specific direction that you recommend?

Re: ADODB.Connection error "800a0bb9" -- Help!!!!!

lluum@yahoo.com wrote:
>> Let's isolate this. It sounds as if you have a problem with your MDAC
>> installation. Create a page containing only:
>>
>> <%
>> dim Output
>> set output=create0bject("adodb.recordset")
>> %>
>>
>> If running this test page results in the mismatch error, then you
>> have a problem with MDAC (ADO) which needs to be corrected.
>
> Rock on... I got the mismatch error just by running this piece of
> code. I will look up the MSDN. Any specific direction that you
> recommend?

Usually, installing the latest version of MDAC will solve these types of
problems. Sometimes running Windows Setup using the Repair option is
required (deending on your OS). Start by going to www.microsoft.com and
searching for MDAC.

If worse comes to worst, give MS Product Support a call.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"