Using a comma-delimited string to populate a checkboxlist!

In my C# ASP.Net application, I have a datagrid with templates.  One of the items
in the template is a checkboxlist.  I want to do my SELECT from SQL Server so that
it returns one row per row in the datagrid, so I want to get the value and text values
for the checkboxlist into one column in the Select statement.

In the code-behind, probably in the datagrid ItemDataBound event, I want to take the
comma-delimited values coming from the server and create a datasource for the
checkboxlist.  Can I do that?  :-)

Data in SQL Server
---------------------
Id          Text
----        -----------
1           Urban
2           Surburban
3           Countryside
4           City

In my Select statement, one column will look like this:
--------------------------------------------------------------
1,Urban,2,Surburban,3,Countryside,4,City

In the code-behind, I want to make it look like a table again so I can bind it to the checkboxlist.

Thanks!
JeffDrummondAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

existenz2Commented:
public struct Data {
  public int Id;
  public int Text;
}

string data = "1,Urban,2,Surburban,3,Countryside,4,City";
string[] results = data.Split(',');
Data[] myData = new Data[(int)Math.Round(results.Length/2)];
int j = 0;
for(int i = 0; i < data.Length; i++) {
    Data d = new Data();
    d.Id = int.Parse(data[i]);
    d.Text = data[i+1];
  myData[j] = d;
j++;
 i++;
}

checkboxlist cbl = new checkboxlist();
cbl.DataSource = myData;
cbl.DataKeyField = "Id";
cbl.DataMember = "Text";
cbl.DataBind();

This should work.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bob LearnedCommented:
Actually, if your data is in row fashion, why are you going to the extra work of packing the string, and then unpacking?  What is the advantage there?

Bob
JeffDrummondAuthor Commented:
I want all of the data related to my one datagrid item to be in one row from the select statement.
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Bob LearnedCommented:
That doesn't make sense, if you are still displaying the data in rows.  Is there something else there that I can't see that explains why you want all the data on 1 row?

Bob
JeffDrummondAuthor Commented:
Hi Bob:

I have a datagrid with a templated column.  One datagrid item contains a photo, some text, and a series of checkboxes.  
The checkbox values are all related to one datagrid item, so I want them to be returned in one row from the database.

On Datagrid_ItemDataBound, I will "unpack" the checkbox items, create a datasource from them and bind to a checkboxlist.

That's my idea anyway.  If there is a better way to do this please please let me know!
Bob LearnedCommented:
Let me see if I understand what you are talking about.

Are you saying that the data comes from the source in rows, and you want to display that one row in columns instead?

Bob
JeffDrummondAuthor Commented:
Bob, how can I send you a private link to the page, so you'll see what I mean?
Bob LearnedCommented:
My email address is in my profile.

Bob
Bob LearnedCommented:
How is your page laid out?  Where is the DataGrid?  

Bob
Bob LearnedCommented:
:)
JeffDrummondAuthor Commented:
Hi, I managed to do what I wanted to do using a user-defined function in the database to
created the comma-delimited string.  In the code-behind, I created a datatable and populated
it with the string values to create datasource for a datalist in my datagrid. The checkboxlist wasn't
the control I wanted because I really wanted an IMAGE of a checked checkbox, not an actual checkbox.
It worked out great.

function
---------
ALTER   FUNCTION dbo._udf_GetHomeSettingsByHomeId(@HomeID int)
RETURNS VARCHAR(1000) AS

BEGIN
         DECLARE @SETTINGLIST VARCHAR(1000)

         SELECT       @SETTINGLIST = COALESCE(@SETTINGLIST + ',', '') + CAST(T2.SETTINGSID AS VARCHAR(2)) + ',' + T2.SETTING
         FROM       _HOMESETTINGS T1
                  LEFT OUTER JOIN  _SETTINGS T2 ON T1.SETTINGID = T2.SETTINGSID
      WHERE      T1.HOMEID = @HomeID
      AND             T1.TYPEID = 1

         RETURN @SETTINGLIST
END

code-behind
--------------
//populate datalist with home type values
if (e.Item.Cells[3].Text != "0")
{
      System.Web.UI.WebControls.DataList dataList = (System.Web.UI.WebControls.DataList)e.Item.FindControl("dlHomeTypes");
      string data = e.Item.Cells[3].Text;
      string[] results = data.Split(',');
      DataTable dt = new DataTable();
      DataColumn dc1 = new DataColumn("ID");
      dt.Columns.Add(dc1);
      DataColumn dc2 = new DataColumn("Text");
      dt.Columns.Add(dc2);

      for(int i = 0; i < results.Length; i++)
      {
            DataRow dr = dt.NewRow();
            dr[0] = results[i].ToString();
            dr[1] = results[i+1].ToString();
            dt.Rows.Add(dr);
            i++;
      }
      dataList.DataSource = dt;
      dataList.DataBind();
}
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.