I keep getting the following error whenever i try to access a temp table that was created using the ExecuteNonQuery method:
Invalid object name '#tList'.
I have the code within a data retrieval method of a BO.
this.FillDataTable(cmd);
if (this.MoveFirst())
{
this.ExecuteNonQuery("CREATE TABLE #tList (poitemsextended_pk int, po_no varchar(10), part_no varchar(30))");
SqlCommand tcmd = new SqlCommand();
do
{
tcmd.CommandText = "INSERT INTO #tList (poitemsextened_pk, po_no, part_no) VALUES (@poitemsextended_pk, @po_no, @part_no)";
int poiepk = this.CurrentRow["PoIEpk"] == DBNull.Value ? 0 : (int)this.CurrentRow["PoIEpk"];
tcmd.Parameters.AddWithValue("@poitemsextended_pk", poiepk).SqlDbType = SqlDbType.Int;
tcmd.Parameters.AddWithValue("@po_no", this.po_no).SqlDbType = SqlDbType.VarChar;
tcmd.Parameters.AddWithValue("@part_no", this.part_no).SqlDbType = SqlDbType.VarChar;
this.ExecuteNonQuery(tcmd);
} while (this.MoveNext());
The error is returned on this.ExecuteNonQuery(tcmd) at the end.
I've tried passing the insert as a string instead of an SqlCommad but i get the same error. I've also tried creating a global table prefixing tList with ## with same results.
Any help would be greatly appreciated.