Help with ADO erro and Stored Proc

[warren2600]

i've written a simple stored proc in SQL SERVER and
am running it with ASP.
The proc runs fine in Query Analyzer, returning the correct number depending if the company exists or not...1 = not exists, 0 = exists.
However I'm getting the following error ONLY when the result is 1..???

ADODB error Item cannot be found in the collection corresponding to the requested name or ordinal

see code below


Any input would be greatly appreciated.
Thanks,
warren



HERE IS THE ASP CODE:
-------------------------------------------------
Dim adoCon
dim rs
dim scompany_name
dim strsql
Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.Open "Provider=SQLOLEDB; Data Source = (local); Initial Catalog = Customer_Directory; User Id = user; Password = password"

Set rs = Server.CreateObject("ADODB.Recordset")


'get company name from form
scompany_name = Request.form("company_name")


'run stored proc
strSQL = "sp_insert_new_company '" & scompany_name & "'"

rs.open strsql,adocon,adOpenStatic,adLockReadOnly,adcmdtext

'resultset returns 0 when false but errors if not 0
Response.Write rs("resultset")

rs.Close
set rs = nothing
-----------------------------------------------

/********************************************/
/** SQL STORED PROC **/
/********************************************/
CREATE PROCEDURE [dbo].[sp_insert_new_company]
@company_name varchar(250)
AS
declare @company_id int
if (not exists(select * from company where company_name = @company_name))
BEGIN
insert into company (company_name)
select @company_name

set @company_id = (select company_id from company where company_name = @company_name)

insert into technology(company_id)
select @company_id
insert into business(company_id)
select @company_id
insert into lessons(company_id)
select @company_id
insert into knowledge(company_id)
select @company_id

/*if company is new then return 1*/
select 1 as resultset
END

/* if the company exists then return 1*/
select 0 as resultset

 

[rac2]

Maybe there is an error in the BEGIN block. Maybe the SELECT 1 AS resultset is never executed. In that case there would not be an item named resultset.

I would code this a bit differently.

/* Instead of this, */
set @company_id = (select company_id from company where company_name = @company_name

/* I would code this.*/
select @company_id = company_id from company where company_name = @company_name

 

[warren2600]

the stored proc works fine.
i've tested it with query analyzer.
I will worry about the structure later.
Right now i need to return whether or not the proc was successfull to my asp page with either a 1 or 0.
thanks for the input.
i'll keep searching.

 

[Veep]

Get rid of the recordset object. Code-fix below:

Dim adoCon
dim rs    
dim scompany_name
dim strsql
dim myReturnValue        
Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.Open "Provider=SQLOLEDB; Data Source = (local); Initial Catalog = Customer_Directory; User Id = user; Password = password"

 'get company name from form
scompany_name = Request.form("company_name")


 'run stored proc
strSQL = "EXEC sp_insert_new_company @company_name='" & scompany_name & "'" 

set rs = AdoCon.execute(strSql)
myReturnValue = rs(0)
Response.Write("Return Value = " & myReturnValue)
set rs = nothing
AdoConn.close
set AdoCon = nothing

 

[warren2600]

Thanks for the example.
I still am not getting a result of 1(successfull) from my stored proc.
I get the 0 , but not 1.
Yes i did response.write my sql statement
yes i did test the sp in query analyzer.

Here's the interesting part...
the SP runs successfully everytime.
I've response.write the recordcount and...
when i do get a value it's -1, but i get nothing else.?

i'm using adovbs.inc too with adOpenStatic.



thanks

 

[Veep]

You need to terminate the sp (I didn't notice before). The way it is now it's just going to keep falling through.

/*if company is new then return 1*/
select 1 as resultset
[b]return 1[/b]
END

/* if the company exists then return 1*/
select 0 as resultset
return 0