Need Help: Temp Table - 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

General SQL Server Forums

New to SQL Server Programming

Need Help: Temp Table

Author

Topic

JasonAnt
Starting Member

23 Posts

Posted - 2007-08-27 : 09:33:02

How can I get data from temp table into vb recordset ?
I created a sp called sp_test, and hope it will return recordset

create proc sp_test
as
create table #list( code char(30), name char(200))
insert into #list ( code, name )
select Item, [Item Name] from tbItem
select * from #list
return

It works fine in Query Analyzer, now I try in VB 6.0

Dim cn as new ADODB.connection
Dim rs as new ADODB.recordset
cn.open ....
rs.open "select * from sp_test",cn
tdbGrid1.datasource = rs

vb cannot return recordset as I expected,
whats wrong with my vb program?
thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-27 : 09:38:21

rs.open "exec sp_test",cn
or
set rs = cn.Execute "sp_test"

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

JasonAnt
Starting Member

23 Posts

Posted - 2007-08-27 : 10:29:00

Thanks spirit1,
I've tried your code, now I get no error, but still cant get any data

if I put debug.print rs(0) bellow then rs.open, I get error message
"Item cannot be found in the collection corresponding to the requested name or ordinal"

and when I tried the other option,
set rs = cn.exec "sp_test"
I got error message syntax error, "expected end of statement"

do I use the wrong references or what ? I am using Microsoft Activex Data Object 2.6

The point of my question is, I need to do some complex calculation, store it in somewhere (temporary), display it in grid and maybe print it. Is there other solution ?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-27 : 10:40:03

put
set nocount on
at the beginning of your sproc.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

JasonAnt
Starting Member

23 Posts

Posted - 2007-08-27 : 10:52:03

Now it works, Thanks a lot Spirit1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources