Hi Naomi.
This system is inherited from an other programmer and the least amont of change the better .......They are using OLEDB and the least amount of chnges the better......
I'll try out your changes but is there any reason why using the Temporary table would not work.
I think its easier to follow putting code into small chunks rather than 1 large piece of code

regards,
Gerard



>>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?