Invalid object -- temporary table issue.

[vbhooligan]

Hi All,

I need your expertise.
The following code calls two SP, and returns datasets. First SP creates a temp table, which will be used by second SP. When code runs, at line 750 have an error says invalid object ##rtbl, however if I make a break point at line 749, and do a select * from ##rtbl in query analyzer, it will return correctly in the query analyzer. Any help will be appreciated.


Thank you

733 iSqlCmd.CommandText = "sp_rpt_eff_test_summary " & vEntityId & ", '" & vRunDt & "'"
734 iSqlDataAdapter = New SqlDataAdapter(iSqlCmd.CommandText, vStrConnectionString)
735 iSqlDataAdapter.Fill(iDataSet, "ds_EffTestListSummary")
736
737 Dim dtEffTestSummary As New cfcReportData.dsReportDataTable
738 dtEffTestSummary.Copy(iDataSet.Tables("ds_EffTestListSummary"))
739 dtEffTestSummary.iTableName = "ds_EffTestListSummary"
740 dtEffTestSummary.SetFormatCodes() : dtEffTestSummary.SetNumberOfColumns()
741 gDataSet.Add(dtEffTestSummary)
742
743 'az 9/6/05 add raise progress bar %
744 RaiseEvent proBarPercent(10)
745
746 'get the entire list of effectiveness tests for the sponsor and the date. after we build this list, we'll
747 'parse through each test and get the data for each test.
748 iSqlCmd.CommandText = "sp_rpt_eff_test_list " & vEntityId & ",'" & vRunDt & "' "
749 iSqlDataAdapter = New SqlDataAdapter(iSqlCmd.CommandText, vStrConnectionString)
750 iSqlDataAdapter.Fill(iDataSet, "ds_EffTestList")

 

[TechGnome]

It needs to be on the same connection... your code will open two connections (one for each dataAdaptor).

Best bet would be to create the connection yourself and pass it to the DataAdaptor constructors. You may also want to think about using parameters rather than concatenating the string together. Less likely for errors.

-tg