getting info from temp table

[Savas Ates]

My Stored
************************************************** *******
CREATE PROCEDURE st_seconddegree
@fromwhom numeric(18), @towhom numeric(18) AS
if not exists (select 1 from crosstable where (fromwhom=@fromwhom and
towhom=@towhom ) or (fromwhom=@towhom and towhom=@fromwhom))
Begin
create table #pele1
(xuserid numeric(18),xarkid numeric (18)
)
insert into #pele1 SELECT
xuserid = CASE WHEN fromwhom <> @fromwhom then @fromwhom ELSE fromwhom
END,
xarkid = CASE WHEN fromwhom = @fromwhom then towhom ELSE fromwhom END
FROM crosstable
where fromwhom=@fromwhom or towhom=@fromwhom
create table #pele2
(xuserid numeric(18),xarkid numeric (18)
)
insert into #pele2
SELECT
xuserid = CASE WHEN fromwhom <> @towhom then @towhom ELSE fromwhom END,
xarkid = CASE WHEN fromwhom = @towhom then towhom ELSE fromwhom END
FROM crosstable
where fromwhom=@towhom or towhom=@towhom

select #pele1.xarkid as xarkid from #pele1 INNER JOIN #pele2 ON
#pele1.xarkid=#pele2.xarkid;
drop table #pele1
drop table #pele2
End
GO
************************************************** *******
in asp

set rs=baglantim.execute ("st_seconddegree @fromwhom='10',@towhom='7'")
response.write rs("xarkid")

error is
ADODB.Recordset (0x800A0CC1) Item cannot
be found in the collection corresponding to the requested name or ordinal

I TRIED
exec st_seconddegree @fromwhom='10',@towhom='7' in Sql query analyser
it returns me xarkid=2
it works..

but in asp it doesnt works.. i thimk it is about temp table field name or
Drop temp table..
how can it works in asp?

[Tibor Karaszi]

You could try adding SET NOCOUNT ON in the beginning of your proc code...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Savas Ates" <sa***@indexinteractive.com> wrote in message
news:%2****************@TK2MSFTNGP14.phx.gbl...

My Stored
************************************************** *******
CREATE PROCEDURE st_seconddegree
@fromwhom numeric(18), @towhom numeric(18) AS
if not exists (select 1 from crosstable where (fromwhom=@fromwhom and
towhom=@towhom ) or (fromwhom=@towhom and towhom=@fromwhom))
Begin
create table #pele1
(xuserid numeric(18),xarkid numeric (18)
)
insert into #pele1 SELECT
xuserid = CASE WHEN fromwhom <> @fromwhom then @fromwhom ELSE fromwhom
END,
xarkid = CASE WHEN fromwhom = @fromwhom then towhom ELSE fromwhom END
FROM crosstable
where fromwhom=@fromwhom or towhom=@fromwhom
create table #pele2
(xuserid numeric(18),xarkid numeric (18)
)
insert into #pele2
SELECT
xuserid = CASE WHEN fromwhom <> @towhom then @towhom ELSE fromwhom END,
xarkid = CASE WHEN fromwhom = @towhom then towhom ELSE fromwhom END
FROM crosstable
where fromwhom=@towhom or towhom=@towhom

select #pele1.xarkid as xarkid from #pele1 INNER JOIN #pele2 ON
#pele1.xarkid=#pele2.xarkid;
drop table #pele1
drop table #pele2
End
GO
************************************************** *******
in asp

set rs=baglantim.execute ("st_seconddegree @fromwhom='10',@towhom='7'")
response.write rs("xarkid")

error is
ADODB.Recordset (0x800A0CC1) Item cannot
be found in the collection corresponding to the requested name or ordinal

I TRIED
exec st_seconddegree @fromwhom='10',@towhom='7' in Sql query analyser
it returns me xarkid=2
it works..

but in asp it doesnt works.. i thimk it is about temp table field name or
Drop temp table..
how can it works in asp?

[Savas Ates]

thanks
what is the function of this command
SET NOCOUNT ON

[Tibor Karaszi]

It makes SQL Server not return the "rows affected" message after each DML operation. ADO treats this
as a closed recordset...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Savas Ates" <sa***@indexinteractive.com> wrote in message
news:eh*************@TK2MSFTNGP11.phx.gbl...

thanks
what is the function of this command
SET NOCOUNT ON