Intesting Problem Using Temporary Tables

  • Thread starter Thread starter Shane Brodie
  • Start date Start date
S

Shane Brodie

I'm developing a project using VB.Net and MSDE as the data source. In one
scenario I need to return a set of data which also requires some static data
to be included with it. I figured a simple way to do this is to generate a
simple stored procedure which uses a temporary table such as follows:

BEGIN

CREATE TABLE #MYTEMP ([ID] [Int], [Name][varchar](50));




INSERT INTO #MYTEMP ([ID], [Name]) VALUES(-1,'*Root Folder*')




INSERT INTO #MYTEMP SELECT * FROM tblFolder




SELECT * FROM #MYTEMP ORDER BY [Name]



END

The stored proc works fine until ...

The output of this particular stored proc is required to populate a
combobox. I figured the easiest way to do this is to simlpy bind the
dataset result of the stored proc to a combobox.

Problem: When you try to create a data adapter and subsequent dataset using
this method, VB.Net pukes, saying that #MYTEMP is invalid. NOT!

Any suggestions to work-around this.

Regards

Shane Brodie

I realize there are probably many better ways to accomplish this,
unfortunately my VB.Net knoweldge is a work in progress ... in this case I
could have accomplished this in VB 6.0 without any hassle at all (but I
wouldn't have bothered with data binding)
 
Two ways to complete this.

1) don't worry about warning in vb.net. manually setup all the requirements
in dataadapter and it should work

2) select id, name from tblfolder union select -1 as id, '*Root Folder*'. no
need to use temporary table.

Regards,

Rajesh Patel
 
Hi Shane,

This is why I don't use temp tables - they fall out of scope too fast.

Instead, create a real table - but first run a simple sp that searches
information_schema.tables and deletes the table if it already exists. Thus,
the table is real and you continually recreate it each time.

HTH,

Bernie Yaeger
 
Back
Top