Problem with INSERT statement due to value being comma separated

[addy]

OK, I have a form which is used to create the values required to create a new record.

One of the fields is built up by the user selecting one or more values from a list box - if more than one value is selected then it becomes a comma separated string, i.e. value1, value2.

I then need to have a button which allows the user to save the new record and I was going to use the INSERT INTO statement to do this but because one of the values to insert may be comma separated, it doesn't work because it appears that there are more values to insert than there are fields to insert into. How can I have it treat value1, value2 as one Value only?

Thanks.

 

[addy]

Ignore this post - sorted it.

Apologies

 

[PHV]

Anyway, having comma separated values in a single field is (for me) strange.
Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

having comma separated values in a single field is (for me) strange

strange for me too!

Leslie

 

[addy]

PHV

I agree that normally you wouldn't but the field i am updating is a set of aspnet_roles which form an Access COntrol Level field to restrict or grant access to documents over a secure website.

Each document is given an Access Control Level (ACL) number and against that is a list of comma separated roles - each user of the system is assigned to one role.

If the document they wish to view as an ACL which contains their role as one of its values then they can view it.

The form I am working on allows users to select one or more roles and create a new ACL.

 

[lespaul]

Sounds like a many to many relationship and should be set up like any other database with a single piece of information in a field. Is the ACL number stored in a table with a path to the document? Are the users stored in a table that contains the role assigned? So:

User
ID
Name
Role

Document
ACL
Path

DocumentRole
ACL
Role

Now this would be my expected structure for the process you have described.

If there's additional information about the Role, then another table and change all the Roles above to the FK to this table:

Role
ID
RoleName

Have you read the fundamentals document linked below? It's an invaluable resource when designing databases.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi