adding values from listbox to database

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

Development Tools

ASP.NET

adding values from listbox to database

Author

Topic

alxtech
Yak Posting Veteran

66 Posts

Posted - 2007-04-20 : 15:27:30

In my asp form i need to add values to database table from a list box selection with comma delimeter, then i have to retrieve those values from the table and select them back in the selection box whatever values where selected before and saved to the data base, example:

this is the form list box:
---------------
|Big House |*
|Red cat |
|Parks |*
|Television |*
|Computer |
|Picture |
|--------------
now lets say three values were selected *
Big house
Parks
Television
What I need is save all three values to the same field in the database table with a commma between each one.
Big house,Parks,Television

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-20 : 16:09:52

No, you don't. that is a really, really bad database design. You need to normalize your schema and store one list item per ROW in a related table.

see: http://www.datamodel.org/NormalizationRules.html

Trying to stuff multiple values into 1 column is a huge database no-no; it completely eliminates every single advantage that using a relational database provides.

Once you have your design sorted out, all you need to do is simply insert a row into your table for each item selected in your list.

- Jeff
http://weblogs.sqlteam.com/JeffS

alxtech
Yak Posting Veteran

66 Posts

Posted - 2007-04-22 : 09:09:24

unfornulately, that is what my client wants, he wants to add more that one category to one enter event ex. if adding a new event, eventName: tennis tournament he wants to have as many categories as he wants for that, such as:
Categories: sports,recreation,outdoors

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-22 : 09:12:13

That's what adding a related table does. Did you read the link I posted?

If you have a table of Events:

EventID
EventName

and then a table of Categories:

CategoryID
CategoryName

and you want for an Event to have many categories, you create a table that relates the two, called, say, "EventCategories":

EventID
CategoryID (with a composite primary key of BOTH columns)

This is a standard, proper, normalized, database design. Now you can have as many categories per event as you want, and you just add/delete them from the EventCategories table to maintain the list.

- Jeff
http://weblogs.sqlteam.com/JeffS

alxtech
Yak Posting Veteran

66 Posts

Posted - 2007-04-22 : 09:43:38

right now my code takes the value of the list box form selected and add it to the database, only one value but i need multiple selection added in the same step, how i am going to relate all those selected values to that one event.
currently i have one main table called events:
eventID *
categoryNum
another table called:
categories:
categoryNum *
categoryName

* Primary key

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-22 : 09:57:20

alxtech -- As I said in my previuos post, you need to change or alter your database design to accommodate multiple categories per event. This is a key, fundamental relational database concept -- it is very important that you understand how to create related tables and how a normalized schema works when trying to design a database application. Again, did you read and understand the link I showed you?

Once you have a proper database design, as I already mentioned, when a user wants to add or remove categories for an event, you simply add/remove rows from the EventCategories table. It is very easy and simple.

So, if they are checking off items in a listbox, you simply go through the list and add or remove rows from the EventsCategories table as appropriate.

- Jeff
http://weblogs.sqlteam.com/JeffS

alxtech
Yak Posting Veteran

66 Posts

Posted - 2007-04-23 : 12:20:26

lets say a user selects two categories from the list box, when clicking add the eventname, and eventid will be saved how i am going to save it in the eventcategories table if the eventid will be different, event id is autocreated by database.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-23 : 12:35:58

Are you asking how to get an identity value when adding a row to a table? You use the Scope_Identity() function.


declare @EventId int
insert into Events (...) values (....)
set @EventID= scope_identity()

And now you use @EventID to add related rows to the EventsCategories table.

You should probably post these questions in the Beginning SQL 2000 forum to get help with the basics; I strongly recommend a decent book on SQL fundamentals since these are key concepts to understand when working with a relational database.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources