>I am getting an invalid object name when trying to select out of a Temporary table:
>Code Is:
> string sSQL4;
> sSQL4 =
> "SELECT FIELD12,AcNum,Delref into #Tempy1 FROM BI.dbo.BI01 WHERE SUBSTRING(STGROUP, 1, 2) = " +
> textGroup.Text +
> " AND (PERIOD = " + TextPeriodFrom.Text + ")" +
> "GROUP BY FIELD12,AcNum,Delref ";
> OleDbCommand cmdCount = new OleDbCommand(sSQL4, conn);
> OleDbDataReader rdrCount = cmdCount.ExecuteReader();
>
> string sSQL5;
> sSQL5 =
> "SELECT Field12,Count(FIELD12) as MyCount from #Tempy1 group by Field12 ";
> OleDbCommand cmdCount2 = new OleDbCommand(sSQL5, conn);
> OleDbDataReader rdrCount2 = cmdCount2.ExecuteReader();
>
>The rdrCount2 gives the error when executing. Also Tried ##Tempy1 ..same error
>
>TIA
>Gerard

Gerard,

Why not put both statements into one command and execute them as one. You can access both result sets using ExecuteNextResult method, if memory serves.

In addition, why you're not using parameters? You can also get both results combined into one select.

BTW, the first statement doesn't create an output, so why do you use ExecuteReader? You don't get a result when you select into ...

You can comment out first part of your code and re-write the second as

string sSQL5 = @"select  Field12, count(Field12) as myCount

from (select distinct FIELD12,AcNum,Delref FROM BI.dbo.BI01 WHERE STGROUP LIKE @Group + '%'
 AND PERIOD = @Period) X group by Field12"

cmdCount2.Parameters.Add(...); -- adding parameter "@Group"
cmdCount2.Parameters.Add(...); -- adding parameter "@Period"

OleDBDataReader rdrCount2 = cmdCount2.ExecuteReader();

Also, why do you use OleDB stuff instead of Sql related objects? Is there any specific reason?