How to retrieve a sub string from from a column in SQL database into a ListBox

Question

Kindly help me with the following :

I am using SQL database server 2008, in which I have a table with columns as shown in the image attached. I want to retrieve the ToolTip column and extract string after **Controls:** and separate type=value strings and list in a <code>ListBox</code> separately

I want to retrieve the ToolTip column and extract string after Controls: and separate type=value strings and list in a ListBox separately.

Then I want to loop through each row get the list and compare this list with another set of similar list and check if they have same string lists. I haven't tried any code, since I don't know how to start in the first place. Any help will be appreciated. Thank you.

when you say "I haven't tried any code...", did you inherit this problem or you created it for yourself. Is there any reason why Tool_Tip cannot be a MULTILINE text instead of separating them with semicolon — codingbiz
– codingbiz, Commented Sep 8, 2012 at 5:10
Yea this this ToolTip table I am using for other purpose also too. I also wonder when I am saving this tooltip table, its was MULTILINE text. But in database it is stored as single line. — Archana B.R
– Archana B.R, Commented Sep 8, 2012 at 5:24
Have you tried to read it back and display it? It should preserve the newlines — codingbiz
– codingbiz, Commented Sep 8, 2012 at 5:27
Yes, I have used this to display as tooltip for GridView headers, it preserves the newlines. — Archana B.R
– Archana B.R, Commented Sep 8, 2012 at 5:31

Community · Accepted Answer · 2017-05-23 10:30:26Z

1

It would be better if you had

Profile_Controls Table

Profile_Name       Controls_Key     Controls_Value
-----------------------------------------------------
Lamp_Profile1      ON_OFF           ON
Lamp_Profile1      BRIGHTNESS       NONE
Fan_Profile1       ON_OFF           ON
Fan_Profile1       SPEED            NONE
.....

Then you can select

Select Controls_Key+'='+Controls_Value AS Settings From Profile_Controls Where Profile_Name = 'Fan_Profile1'

Results
-----------------
Settings
---------------------
ON_OFF=ON
SPEED=ON

This give you more flexibility as you can filter, JOIN, COMPARE, and use other built-in SQL functionality

If you still want to maintain your table structure like you have it

string[] mysplit = Tool_Tip.Split(s.Split(new string[]{"Controls:"}, StringSplitOptions.RemoveEmptyEntries);

string controls = mysplit[1].Substring(0); //ON_OFF=On;BRIGHTNESS=NONE

string[] eachSettings = controls.Split(';');

//eachSettings[0] = ON_OFF=ON
//eachSettings[1] = BRIGHTNESS=NONE

UPDATE

public System.Data.DataTable GetProfileSettings(string profilename)
{
   string sql = "Select Controls_Key+'='+Controls_Value AS Settings From Profile_Controls Where Profile_Name = '"+profilename+"'";
   //write ADO.Net code here to get settings into DataTable
   //DataTable dt = blah blah blah;

   return dt;
}

in your page

protected void SomeEvent_Handler(object sender, EventArgs e)
{
   myListBox2.DataSource = GetProfileSettings("FanProfile1");
   myListBox2.DataTextField = "Settings";
   myListBox2.DataBind();
}

You can use this link as reference for adding item to your listBox How to retrieve commas delimited values in SQL to a ListBox separately

edited May 23, 2017 at 10:30

CommunityBot

11 silver badge

answered Sep 8, 2012 at 5:57

codingbiz

26.4k9 gold badges62 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Archana B.R Over a year ago

You have given me a brilliant idea..Indeed I will follow your first idea. Just tell me how to use that Settings and put in a ListBox and loop to each row do the same and compare with my own ListBox with similar set of value.

aiodintsov · Accepted Answer · 2012-09-08 05:23:44Z

0

String.Split and more reading on SqlConnection and SqlCommand

answered Sep 8, 2012 at 5:23

aiodintsov

2,61517 silver badges17 bronze badges

1 Comment

Himanshu Over a year ago

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes.

Nikhil Agrawal · Accepted Answer · 2012-09-08 06:11:29Z

0

How about we fire a query like this

select tooltip from FooTable where Profile_Type='Foo_Profile_Type' and Profile_Name = 'Foo_Profile_Name'

and get

"Type = LAMP;Profile = Lamp_Profile1;Controls:ON_OFF = ON;BRIGHTNESS = NONE;"

Then

string str = returnedstring.Split(new string[]{"Controls:"})[2];

will give you

"Controls:ON_OFF = ON"

edited Sep 8, 2012 at 6:11

answered Sep 8, 2012 at 5:47

Nikhil Agrawal

48.8k25 gold badges134 silver badges212 bronze badges

1 Comment

Archana B.R Over a year ago

I would try this, but I want ON_OFF = ON and BRIGHTNESS = NONE in the ListBox and compare with another list and jump to the next row and again list similarly and compare with the same list.

Collectives™ on Stack Overflow

How to retrieve a sub string from from a column in SQL database into a ListBox

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related