How to retrieve values separately from column?

Question

0.00/5 (No votes)

See more:

C#

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 18-Jan-16 20:15pm

Member 11873864

Add a Solution

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

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)

Mycroft Holmes · Answer 1 · 2016-01-18T21:00:00

Solution 1

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

SQL

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

SQL

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,',')

Posted 18-Jan-16 21:00pm

Mycroft Holmes

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