Inserting multiple rows of data into one table II

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)

Inserting multiple rows of data into one table II

Author

Topic

jiri.pekhart
Starting Member

3 Posts

Posted - 2007-08-02 : 14:07:13

(MS SQL 2005, ASP, Dreamweaver)

There is something that should be a common task, but I have been working on it for 3 days now and can't get it work ....

On one page there is a form with 5 checkboxes and one hidden field.
Each checkbox represents a unique color. (its value is the color_ID)
Inserting multiple rows of data into one table.

User picks the colors he likes and submits to a page "process.asp"
A unique user_ID (from a hidden field) is submitted with the checked checkboxes as well.

The checkboxes are generated dynamically from a table tbl_colors that stores the color_ID, color_Name .. etc)

Now the page "process.asp"
<%= request.form("color_ID") %> writes values of all checked checkboxes like: 1,2,3, (in case the user has checked the first 3 boxes)
and
<%= request.form("user_ID")%> writes 5,5,5,5,5 (in case the user_ID = 5 and it doesn't matter how many colors the user has picked)

I need to store the data into a table called tbl_Users_Colors.
It is a linking table with no primary key.
It just records the user_ID and color_ID from our form, and handels them as a foreign key linking to tables: tbl_Users and tbl_Colors with one to many relationship.

This way I can retrieve what colors have been picked by one user and what users are associated with particular color...

THE PROBLEM is that the data can't be stored in multiple rows in the table tbl_Users_Colors.
As soon as more than one checkbox is selected, I get the error message that the number of values doesn't fit the table.
If the user checks just one, it works fine.

I need to store the data into multiple rows like this:

tbl_Users_Colors:

column ........ column
UserID ........ ColorID
... 1 ............... 2
... 1 ............... 4
... 1 ............... 5
(When eg. user with user_ID=1 has checked colors 2,4,5)

I've been trying to loop the INSERT statement, but it doesn't seem to work.

Could someone offer a solution?
I have been searching the web and found many people asking the same Q. But there was no straight forward answer or tutorial how to do it.

I use ASP, MS SQL 2005 and Dreamweaver 8

I believe that more people than just me would be interested in a solution of a problem like this.

Thanks for checking out and possibly helping with this problem.

Jiri.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-08-03 : 12:13:29

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

stephe40
Posting Yak Master

218 Posts

Posted - 2007-08-03 : 15:34:09

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=87285

Posting the same message twice is frowned upon.

- Eric

jiri.pekhart
Starting Member

3 Posts

Posted - 2007-08-03 : 15:52:13

quote:
Originally posted by stephe40

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=87285

Posting the same message twice is frowned upon.

- Eric

I didn't mean to ...
Accidentally I haven't copied all the text into the first message, so the first is incomplete.
This is why I posted this one. Unfortunately I'm not able to delete the fist one. Would appreciate if someone could do it.
J.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources