ADODB error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

ADODB error

Author

Topic

brianatajpi
Starting Member

12 Posts

Posted - 2004-07-14 : 05:09:31

ADODB.Fields error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.
Recently this has been the result of running a SPROC That worked fine. I don't know what's changed on the server but I need to fix my code.

My SPROC takes a set of input parameters and checks that there are no conflicts with 2 criteria.

If #1 is wrong the SPROC returns a single result row with 1 column (named "Result") with value -1.

If #2 is wrong the SPROC returns a single result row with 1 column (named "Result") with value -2.

If all is OK the SPROC inserts a row into a table and returns the @@Identitiy as a single result row with 1 column (named "Result") with value @@Identity.

It works great in QA. Every time I get a 1 row, 1 column result set with column name="Result" and an integer value of -1, -2 or the ID.

When I run the code via ADODB (using classic ASP and VBScript) I set a resultset using the return set from executing the SPROC. If the result is -1 or -2 then there is a usable field value, but if the SPROC has worked and created a row there is no result set.

I'm confused.

This was working for over a year. Now its broke.

Here's my code...

SPROC:
CREATE PROCEDURE spAddApplication
(
...
)
AS BEGIN
IF EXISTS(SELECT ...)
SELECT -1 AS Result -- subdomain already exists
ELSE IF EXISTS(...)
SELECT -2 AS Result -- loginname already exists
ELSE
BEGIN
INSERT INTO ... (...)
VALUES (...)

SELECT @@Identity AS Result
END
END
GO
ASP VBScript:
Set cmdApply = Server.CreateObject("ADODB.Command")
cmdApply.ActiveConnection = MM_sgc_STRING
cmdApply.CommandText = "dbo.spAddApplication"
cmdApply.CommandType = 4
cmdApply.CommandTimeout = 0
cmdApply.Prepared = true
cmdApply.Parameters.Append cmdApply.CreateParameter(...)
...
cmdApply.Parameters.Append cmdApply.CreateParameter(...)
Set cmdResult = cmdApply.Execute
commandResultValue = cmdResult.Fields.Item("Result").Value

Can anyone figure out what's wrong (and how I can fix it)?

Brian
----@

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-07-14 : 05:16:54

Are you sure that the field name being returned by the sproc is still called "Result"?

DOES commandResultValue = cmdResult.Fields.Item(0).Value work as opposed to
commandResultValue = cmdResult.Fields.Item("Result").Value ?

Duane.

brianatajpi
Starting Member

12 Posts

Posted - 2004-07-15 : 03:47:06

I also tried cmdResult.Fields.Item(0).Value and got the same error, so it looks like sql is not returning a resultset at all if the insert is successful.

I wondered if sql was returning the result of the first sql statement (INSERT INTO ...) rather than the second (SELECT @@IDentity AS Result) for the successful case, but don't know how to test for that - let alone fix it.

Brian Lowe
---------@

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-07-15 : 03:56:51

You should be able to navigate to the next result set.

With a .net datareader the syntax is rdr.Nextresult()

Perhaps something like that?

Duane.

brianatajpi
Starting Member

12 Posts

Posted - 2004-07-15 : 04:26:59

Wow that was a fast response!

Unfortunately this is a classic ASP site so a .Net reader is out of the question.

I'll have a look to see whether NextResult() is available to me in classic ASP though. Even then, I need to collect the result from the first resultset for cases 1 & 2, but only try to step on to the nextresult() if the first resultset is empty. I need some error handling and other stuff.

Thanks for your help.

Brian Lowe
---------@

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2004-07-15 : 08:10:45

Has your connection string/include file been modified "MM_sgc_STRING"
If you say your sproc works (returning correct recordset) in QA it is probably NOT down to SQL server but rather a connection issue
I see you are using UltraDev, can you do any query (SELECT Col1 FROM MyTable) via UD Databindings on the SQL server (local/remote)??

Andy

mr_mist
Grunnio

1870 Posts

Posted - 2004-07-15 : 08:17:49

You need to use SET NOCOUNT ON and SET NOCOUNT OFF around the rows that produce results (eg INSERTs) that you do not wish to return to ASP (or whatever.)

methinks.

-------
Moo. :)

brianatajpi
Starting Member

12 Posts

Posted - 2004-07-15 : 09:17:04

Yayy! NOCOUNT ON/OFF saves the day. Thank you!

So, I wonder how come it worked happily for a year without these commands and then started failing in the last month? Something must have changed, but what? I'm aware that my SQL DBA skills are, shall we say, minimal, so I particularly don't mess with settings or configs unless I have to - and I haven't had to.

Of course it's probably nothing to do with SQL. Probably more like an ADODB/ASP/.Net framework issue. Who knows? I'm just happy my site works again.

Thank you

Brian Lowe
---------@

Kristen
Test

22859 Posts

Posted - 2004-07-15 : 10:02:20

I think (but I'm certainly not sure), that the NOCOUNT resultset comes through looking like an ADO message, rather than necessarily looking like a recordset; if I've got that right an ADODB/ASP/.Net change could indeed expose it - in which case there will be a stack more needing attention all around the place real soon now!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources