Multiple commands in ASP query

[gyeadon]

I'm trying to do the following using an ADODB.Connection object in ASP:

1) declare a temporary table,
2) select years into the table from another table,
3) use the temporary table to join with an existing table to get my results.

I've tried doing this in separate queries, which results in the second step complaing about not recognising the table.
I've also tried doing the whole lot in one query, which results in an empty record set.
The query I've got works in Query analyzer, so it's not that.
Help,

Graham.

Code:
RS1=DBConn.Execute(
&quot;declare @yrs table(yr integer); insert into @yrs select distinct yr=datepart(year, reviewdate) from mediareview; select yr, num=count(*), av=avg(cast(stars as decimal(6,2))) from mediareview, @yrs where datepart(year,reviewdate)=yr and datepart(year, getdate())-yr<5 group by yr;&quot;

 

[Spellman]

The problem when you split the queries up into individual queries was one I had before in that the first query was not finshing executing before the second query started thus there was no table to access it. As for returning no records I don't think you can have count(*). It must be a field name. In this case I think any one will do. Also just check to make sure that the @yr table is filling and that the third query is the problem. Mark

The key to immortality is to make a big impression in this life!!