SQL Server 2000 stored procedure not returning rows
John, see the programming FAQ on my site which talks about something similar
( http://vyaskn.tripod.com/programming_faq.htm#q10 )
--
HTH,
Vyas
Visit my site for Replication FAQ, Code samples, Database Interview
questions,
Database naming conventions, Database programming guidelines,
Interesting articles, Links and much more at:
http://vyaskn.tripod.com/
Hi all,
I am trying to return results from a stored procedure in SQL Server 2000.
No problem there, I've done it bunches. But for some reason this procedure
will not return the rows. I get a "Operation not allowed when object is
closed" error. Here is the sp I am trying to run:
as
create table #compare(
compname nvarchar(15),
compqtyA int,
compqtyB int,
pricediff smallmoney)
declare modelA cursor local for
select
g3.compname,
g3.compqty,
c.complist * g3.compqty
from
grp3 as g3
inner join
compnt as c
on
c.ctrt = g3.ctrt
and c.compname = g3.compname
where
order by
g3.compname
open modelA
begin
insert
#compare(compname,
compqtyA,
pricediff)
values(
end
close modelA
deallocate modelA
declare modelB cursor scroll local for
select
g3.compname,
g3.compqty,
c.complist * g3.compqty
from
grp3 as g3
inner join
compnt as c
on
c.ctrt = g3.ctrt
and c.compname = g3.compname
where
order by
g3.compname
open modelB
begin
from
#compare
where
begin
insert
#compare(compname,
compqtyA,
compqtyB,
pricediff)
values(
0,
end
else
begin
update
#compare
set
where
end
end
close modelB
deallocate modelB
update
#compare
set
compqtyB = -compqtyA
where
compqtyB is null
select
c.compname,
c.compqtyA,
c.compqtyB,
c.pricediff
from
#compare as c
where
c.compqtyB <> 0
order by
c.compname
This owrks fine if I call it from the query analyzer and I get the rows I
expect, but for some reason this doesn't return anything when I try to call
it from VB with this code:
Dim conCompare As ADODB.Connection
Dim cmdCompare As ADODB.Command
Dim rsCompare As ADODB.Recordset
Dim ContractID as String
Dim ModelA as String
Dim ModelB as String
Set conCompare = New ADODB.Connection
Set cmdCompare = New ADODB.Command
Set rsCompare = New ADODB.Recordset
conCompare.ConnectionString = ConnString
conCompare.Open
With cmdCompare
.CommandType = adCmdStoredProc
.CommandText = "sbs_pcscompare"
Set .ActiveConnection = conCompare
adParamInput, 15, ContractID)
adParamInput, 50, ModelA)
adParamInput, 50, ModelB)
Set rsCompare = .Execute
arrResults = rsCompare.GetRows
End With
conCompare.Close
Any ideas?
Thanks in advance,
John Palmer
