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

Renjith V S

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.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

sudipta sanyal12y ago
Accepted answer
in that case as per normalization we have to break this into a sapareate table and the table like

the table name is 'tbl_person_country' and the column are 'person_id and country_id'

if you dont have Split a 'string to a table' then run this script

CREATE FUNCTION [dbo].[Split]
(
@String NVARCHAR(4000),
@Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
WITH Split(stpos,endpos)
AS(
SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
UNION ALL
SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
FROM Split
WHERE endpos > 0
)
SELECT 'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
FROM Split
)
GO

i give you an example for understand

DECLARE @current_person_id INT
DECLARE @DelimitedString NVARCHAR(128)
SET @DelimitedString = 'Usa,India,Uk'

insert into tbl_person values (name,address)
values ('testperson','test address')

set @current_person_id = @@IDENTITY

insert into tbl_person_country (person_id and country_id)
SELECT @current_person_id, data
FROM dbo.Split(@DelimitedString, ',')
0
Dileep Kumar Patel12y ago
Hi,
Greeting of the day.
You can use this code here you can add multiple id in same column.

Create proc [dbo].[BuySell_InsertCity]
(
@staId int,
@City varchar(500),
@message varchar(max) output
)
As
insert tb_StateCity (ctyNam,ctyStaId) values(@City,@staId)
set @message='Record Saved Successfully'

--See the table

select * from tb_StateCity
0