3

I am creating a webpage in which I have a series of pairs ListBoxes. In each pair there is a source (containing many options) in which you can then select an option (or multiple) and add it to the second listbox.

I have some permanent tables created which contain the static members for the source listboxes; however, I would like to use a "SELECT * INTO #tempsrc1 FROM src1" and then bind the #tempsrc1 to ListBox. From there a #tempdst1 would be created for the destination ListBox binding. Then any selections would just be moved back and forth from there accordingly.

While binding to permanent databases is easy enough, I am having trouble (if its even possible) binding to the #tempdb setup for the rest of this, I am quite new to SQL and do not have a solid understanding of the architecture.

John Saunders
  • 160,644
  • 26
  • 247
  • 397
Hub3rt
  • 113
  • 1
  • 10
  • thanks for all the insight, im pretty new to asp and sql in general. figure the best route is to re-tool this setup. – Hub3rt Jul 27 '11 at 19:11
  • If you think that an answer was relevant and helpful, please upvote it by clicking the upwards pointing triangle. The answer that was the best should be marked as accepted by clicking the check mark next to it. – Anders Abel Jul 28 '11 at 09:42
  • @Anders i dont have the 15 points to do so yet... otherwise i would – Hub3rt Jul 28 '11 at 16:34
  • Sorry, I didn't know that the voting limit applied to own questions as well. – Anders Abel Jul 28 '11 at 17:40

4 Answers4

1

I think that your design is wrong.

Temporary data should not be kept in the database, but in the application layer. In the case with two listboxes you are probably able to use the listboxes themselves to keep track of the items. If you need to keep data that is related to the text displayed in the listboxes I'd recommend putting a Dictionary<> in the Session object.

Keeping a #temptable alive between different requests which will probably be utilizing different database connections might be tricky too. Unless you store the database connection in Session I think that the #temptables will be lost when the connection pool resets the database connection.

Anders Abel
  • 67,989
  • 17
  • 150
  • 217
  • I was afraid of that ha. The reason I was attempting to use databases was that the certain listbox control I am using clears data on post unless it is databound, and binding to something like an arraylist doenst work, might have to switch listboxes. the listboxes have to be populated from a database, the website is large database for materials engineering research at my university and there are many many options for the researchers to select from as techniques ect.. – Hub3rt Jul 27 '11 at 17:31
1

I would suggest not to use the temp db. As the value might get lost with the new connection. Use the permanent tables and put the listbox data in cache if needed.

Ankit
  • 6,388
  • 8
  • 54
  • 79
1

Source Listbox has a selectedindexchanged event.

Use that event to:
1) create a listbox item
2) set listbox item = the selected item of the source
3) items.add or items.insert the listbox item to your destination listbox.

This is pretty standard. But do not use a temp table in the database. As @Andres says, keep track in your application layer. The only exception In can think of is if you need to keep track beyond the scope of the session.

Chains
  • 12,541
  • 8
  • 45
  • 62
  • for some reason the Obout ListBoxes dont keep data on post without being databound. cant removeAt(i) without an error since on post the listbox clears, and THEN the remove executes, causing index out of bounds problem. guess i have to re-think functionality vs. good looks on the listboxes – Hub3rt Jul 27 '11 at 17:34
0

I ran into this same issue with working with a temp table for a winforms application. Your application needs to be bound to a permanent table, you cannot bind to a temp table that is stored in SQL server.

You can create a stored procedure that will return the data that you want.

Taryn
  • 242,637
  • 56
  • 362
  • 405