Click here to Skip to main content
14,733,542 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
In database i saved multiple values in one column.
For example:-Column name-Shopping
             Values-jabong,amazon,snapdeal



Now i want to retrieve these values separately(Jabong separate in grid view column or check box,amazon separate,snapdeal separate) and display it as links.How can i do this?</pre>
Posted
Comments
User-11630313 19-Jan-16 2:25am
   
to solve your problem you need to select the values seperatly from the database...
Member 11873864 19-Jan-16 2:27am
   
how i can select the values separately from one column?
User-11630313 19-Jan-16 2:35am
   
it simple select the value into a variable..and return the variable using stored procedure...
Member 11873864 19-Jan-16 2:46am
   
When i am selecting the values and displaying in checkbox list then all three are coming in one checkbox,not in three seperate checkboxes.
Member 11873864 19-Jan-16 2:48am
   
how to retrieve these values separately?in 3 checkboxes
User-11630313 19-Jan-16 2:48am
   
where you want to display the values row? or column?
Member 11873864 19-Jan-16 3:26am
   
Firstly i wrote this code to save the multiple values in one column:-
protected void Button1_Click(object sender, EventArgs e)
{
for (int i = 0; i < cblShopping.Items.Count - 1; i++)
{
if (cblShopping.Items[i].Selected)
{
string interests = string.Empty;
foreach (ListItem items in this.cblShopping.Items)
if (items.Selected)
interests += items + ",";
string str = "update tbl_value set shopping='" + interests + "' where id='" + Session["s"].ToString() + "'";
str = str.TrimStart(',');
obj.exec(str);
break;
}
}


Problem:-At the time of registration i am giving one option to user to select some interests.Suppose user selects checkbox jabong from shopping interest,yatra from travel interest.I am saving this in database (jabong,yatra) in one column only.After login,user should be able to view and open these links.How to do this

1 solution

You are paying the cost of bad database design. The values should be in separate records and not jammed into one field.

You could extract the record and then create a child collection in your business logic layer using a split function.

You could create a sub query in the database that does the same thing, I suggest you use 2 fields, the primary key and ONE of the values per record using a split function.

An old split function
CREATE   FUNCTION [dbo].[fn_Split]
 (@List  VARCHAR(8000), @Delimiter CHAR(1))

RETURNS @Results TABLE
 (Item VARCHAR(8000),ID INT IDENTITY(1,1))

AS

BEGIN
 DECLARE @IndexStart INT
 DECLARE @IndexEnd INT
 DECLARE @Length  INT
 DECLARE @Word  VARCHAR(8000)

 SET @IndexStart = 1
 SET @IndexEnd = 0

 SET @Length = LEN(@List)
IF @Delimiter = '' SET @Delimiter = ','

--Get rid of any tabs or returns
SET @List = REPLACE(@List,CHAR(9),'')
SET @List = REPLACE(@List,CHAR(10),'')
SET @List = REPLACE(@List,CHAR(13),'')

WHILE @IndexStart <= @Length
BEGIN
	SET @IndexEnd = CHARINDEX(@Delimiter, @List, @IndexStart)
		
	IF @Delimiter = CHAR(32) 
		SET @IndexEnd = CHARINDEX(SPACE(1), @List, @IndexStart)

	IF @IndexEnd = 0
		SET @IndexEnd = @Length + 1

	SET @Word = SUBSTRING(@List, @IndexStart, @IndexEnd - @IndexStart)
	SET @IndexStart = @IndexEnd + 1

	INSERT INTO @Results(Item)
	SELECT @Word
END

RETURN
END 

GO


Usage
DECLARE @VALUES VARCHAR(1000)

SET @Values = 'Shop1,Shop2,Shop3,Shop4,Shop5,Shop6,Shop7 ' 


SELECT 1001 AS PrimaryKey, item AS Vendor
from dbo.fn_Split(@VALUES,',')
   
Comments
Member 11873864 19-Jan-16 3:26am
   
Firstly i wrote this code to save the multiple values in one column:-
protected void Button1_Click(object sender, EventArgs e)
{
for (int i = 0; i < cblShopping.Items.Count - 1; i++)
{
if (cblShopping.Items[i].Selected)
{
string interests = string.Empty;
foreach (ListItem items in this.cblShopping.Items)
if (items.Selected)
interests += items + ",";
string str = "update tbl_value set shopping='" + interests + "' where id='" + Session["s"].ToString() + "'";
str = str.TrimStart(',');
obj.exec(str);
break;
}
}


Problem:-At the time of registration i am giving one option to user to select some interests.Suppose user selects checkbox jabong from shopping interest,yatra from travel interest.I am saving this in database (jabong,yatra) in one column only.After login,user should be able to view and open these links.How to do this
Member 11873864 19-Jan-16 3:29am
   
I am having two columns:-
Userid-U1001
Shopping-Jabong,Amazon
Travel- Yatra,makemytrip


In one column i saved multiple values
Member 11873864 19-Jan-16 3:32am
   
Now i want that at the time of retrieval, two checkbox or two separate grid view columns should display,in which one checkbox will display jabong,second checkbox will display amazon.
Mycroft Holmes 19-Jan-16 3:45am
   
It is still a dodgy design and you will still need to split the text into discreet values. I would suggest you supply a hyperlink instead of a checkox. If I had a choice I would change my data structure to have a child table (Interest) with an InterestType and a value.
Member 11873864 19-Jan-16 3:53am
   
According to you i should make two tables in database.one for interests(shopping,travel) and one for values(jabong,amazon).
Then what about jabong and amazon.Then these values i should save in separate columns?
F-ES Sitecore 19-Jan-16 4:56am
   
Have a "user table"

UserID, UserName
1 John

An Interests table
InterestID, InterestName
1, Shopping
2, Travel

An InterestValue table
InterestValueID, InterestValueName, InterestID (maps to Interest table)
1, Amazon, 1
2, Jabong, 1
3, Expedia, 2
4, Trivago, 2

Finally a UserInterest table

UserID, InterestValueID
1, 1
1, 2
1, 4

The above means John likes Amazon and Jabong for shopping and Trivago for travel. You can use JOIN statements to link these tables together to get a single result that has interest name and interest value, or you can select per interest type by joining and filtering on InterestID. Or some variation on the above idea.
Member 11873864 19-Jan-16 5:30am
   
Thanks for your reply.This explanation is very helpful to solve my problem.
User-11630313 20-Jan-16 5:34am
   
excellent..great explanation..#F-ES Sitecore

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900