How to insert more than one id to a row in sql using c# code

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

Hi,
I have a problem in adding more than one id to a column. My project is that I need to add details of person. In this I use 1 person can add more than one country name. For example a person name: abc can add his country names as India and USA. The country name is getting by drop down list. In my project all are using a tab control in first tab I can add name and in next tab there is a drop down list for country selection on selecting the country name there is a add button so I can add more than one country name to a particular name. So there is a main submit button which is for all the tabs all the data inserted should be inserted to database. example: name: abc country:1,2 . Country is saved as id.

Posted 23-Apr-14 23:18pm

My Doubt

Updated 23-Apr-14 23:20pm

phil.o

v2

Add a Solution

Comments

phil.o 24-Apr-14 5:22am

What have you done so far? Please descrive your tables and the code you have to update them.
Per se, it is not possible to insert several values in a single column of a single row. You have to adapt your database schema to handle that.

My Doubt 24-Apr-14 6:03am

I have done so far is
objCountryBO.CountryID= Convert.ToString(ddlCountryName.SelectedIndex);
In this code CountryID is the SQL column name and ddlCountryName is my drop down list. But here I have faced a problem that the value in the drop down list is stored in sql .

phil.o 24-Apr-14 6:14am

Please see Original Griff's solution1: it has everything you need to know about handling integers as strings and one-to-many database relationships.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2014-04-23T23:40:00

Solution 1

Don't.
Unless you start playing with strings (and that makes life very much more complicated later) you can only store one value per column, per row.
Instead, create another table which contains the cross references:
Your original table:

UserID
UseName
...

New table:

UserId
Country

You would then have several entries in the "Countries" table for each entry in the "Users" table.
I woudl actually go further, and have three tables:
CountryNames:

CountryID CountryName
   1      Wales
   2      France
   3      India
...

Users:

UserId    UserName
   1      Paul
   2      Pierre
   3      Sangit
...

UserCountries:

ID        CountryID
   1      1
   1      2
   2      2
   3      1
   3      2
   3      3
...

You can then get a list of countries for each user without any problems.

Posted 23-Apr-14 23:40pm

OriginalGriff

Comments

My Doubt 24-Apr-14 7:33am

Hey Original Griff,
In your example the id is repeating, but I need it in the below form
ID CountryID

1 1,2
2 2,3
3 1,2,3...

OriginalGriff 24-Apr-14 7:45am

No, you don't - read it again.

My Doubt 24-Apr-14 7:53am

Hey why should I create a new table I have seen that there is a comma seperated string is used for storing that id in sql server. Its data type is varchar.

OriginalGriff 24-Apr-14 8:06am

Because processing it later when you want to use the individual IDs is a PITA. SQL is not good with string manipulation, and cannot work directly with CSV data - so if you want to show which countries the user is associated with, you have to manually disassemble the CVS string, convert it to valid Int ID values, and then use them. While you can do that, it's a damn nuisance involving temporary tables - and you will do that more often than you will insert data!

Keeping it as separate columns means you can use them directly, which is a lot, lot easier. For example, this user is associated with 12 countries:
666 1,5,2,6,23,124,17,33,42,3,321,13,99
But today, his association with country 3 is no longer valid. How do you remove that from a string?
With tables, it's simple:
DELETE FROM UserCountries WHERE ID=666 AND CountryID=3

My Doubt 25-Apr-14 0:51am

Dear OriginalGriff,
Yea you are right , because I need to delete and update the country names so it should be a difficult task for me. Thanks for the reply.

OriginalGriff 25-Apr-14 2:06am

You're welcome!