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  SQL Server 2005 Forums  Transact-SQL (2005)  How to read from temp table using C#

Author  Topic  tcheung Starting Member 1 Post Posted - 2007-08-14 : 01:00:55 Hello, I have written a Windows Forms app in C# to query from some dB table for a DataGridView control. The queries make use of a temp table and the query strings were combined as a single query string variable like this: private string queryOrdIDsbyFidOrdID = @" select distinct md2.FieldValue as ClOrdID into #ClOrdIdTable from MessageDetails md join MessageSummary ms on md.msgid = ms.msgid join MessageDetails md2 on md.msgid = md2.msgid where ms.MsgDate between '{0}' and '{1}' and md.FieldValue = '{2}' and md.FieldId in ('11','37') and md2.FieldId = '11' select er.* from Reference er join #ClOrdIdTable ot on er.Reference = ot.ClOrdID drop table #ClOrdIdTable"; This is then passed into the following the queryDb method using the following way of calling it: string queryString = string.Format(queryOrdIDsbyFidOrdID, beginString, endString, orderID); return queryDb(queryString); public DataTable queryDb(String queryString) { using (SqlConnection connection = new SqlConnection(this.connection)) { try { SqlDataAdapter adapter = new SqlDataAdapter(); SqlCommand command = new SqlCommand(queryString, connection); command.CommandType = CommandType.Text; adapter.SelectCommand = command; DataTable dataTable = new DataTable(); adapter.Fill(dataTable); return dataTable; } catch (Exception ex) { throw new ApplicationException("Cannot perform query with " + queryString); } } } This runs fine for some parameters but not for others. If I break the select statements down and run them inside SQL Server Management Studio, it's okay even with the parameters that caused the exception to be thrown. The point of failure comes from the second select statement where it's trying to join a table with the temp table created from the first table. I think it's unable to find the temp table by the time it reaches the second statement. Any ideas? Thanks.