How to populate CheckBoxList values from a database

I don't understand what you mean by "check the values which are in DB".  Can you show us the data structure of the table and give a example show what you want to see if the checkboxlist?

I meant depending on the the values in the DB the checkbox would be checked or not.

My Clients tbl has a Client ID, and other columns with client info. The Equipment tbl has a EquipmentID and EquipmentCode. Finally the Client_Equipment tbl has the following structure:

Client_EquipmentID   ClientID   EquipmentID
1                                     5                      2
2                                     5                      12
3                                     5                      23

So basically I need to match the Client_Equipment tbl with the Equipment tbl and match the EquipmentID column and then if theres a match, put a check on the box.

In my previous comment I had the code showing the wrong table...here is the correct one:

objCmd = New SqlCommand("SELECT * FROM Equipment", objConn)
objRdr = objCmd.ExecuteReader()
chklstEquipment.DataSource = objRdr
chklstEquipment.DataValueField = "EquipmentID"
chklstEquipment.DataTextField = "EquipmentCode"
chklstEquipment.DataBind()
objRdr.Close()

What DB are you using? SQL or Access or something else?

SQL server 2000

You can add a DataBound event handler to your CheckBoxList and then in the handler run a query to see if there is a record in table Client_Equipment corresponding to each equipment checkbox.  Your code may look like

    protected void CheckBoxList1_DataBound(object sender, EventArgs e)
    {
        ConnectionStringSettings settings =
            ConfigurationManager.ConnectionStrings["TESTDBConnectionString"];
        SqlCommand cmd = new SqlCommand("SELECT equipmentId FROM Client_Equipment WHERE clientId=5 AND equipmentId=@equipmentId",
            new SqlConnection(settings.ConnectionString));  //here the sql hardcoded the clientId.  Make it as parameter as needed
        cmd.Parameters.Add("@equipmentId", SqlDbType.Int);
        cmd.Connection.Open();
        foreach (ListItem item in CheckBoxList1.Items)
        {
            cmd.Parameters["@equipmentId"].Value = int.Parse(item.Value);
            item.Selected = (cmd.ExecuteScalar() != null);
        }
        cmd.Connection.Close();
    }

Please note that this solution will be inefficient when you get a long equipment list because it calls the DB for each equipment.  A better approach is to use a DataList instead of a CheckBoxList.

aspx:
<asp:DataList ID="DataList1" runat="server" DataKeyField="equipmentId">
            <ItemTemplate>
                <asp:CheckBox ID="CheckBox1" runat="server" Checked='<%# (int)Eval("owned") > -1 %>' Text='<%# Eval("equipmentCode") %>' />
            </ItemTemplate>
        </asp:DataList>

Code behind:
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            ConnectionStringSettings settings =
                ConfigurationManager.ConnectionStrings["TESTDBConnectionString"];
            SqlCommand cmd = new SqlCommand("SELECT Equipment.equipmentId,Equipment.equipmentCode, ISNULL(Client_Equipment.clientId,-1) AS owned
FROM         Equipment LEFT JOIN
                      Client_Equipment ON Equipment.equipmentId = Client_Equipment.equipmentId
AND Client_Equipment.clientId=5",
                new SqlConnection(settings.ConnectionString));
            cmd.Connection.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            DataList1.DataSource = reader;
            DataList1.DataBind();
            cmd.Connection.Close();
        }
    }

Edwin

Hi Edwin_C,
I got a complitaion error - Overload resolution failed because no accessible 'Int' accepts this number of arguments.
Line 296:      <asp:CheckBox ID="CheckBox1" runat="server" Checked='<%# (int)Eval("owned") > -1 %>' Text='<%# Eval("equipmentCode") %>' />


Also what does this line do - ISNULL(Client_Equipment.clientId,-1) ?

Are you using VB?  If so, try
<asp:CheckBox ID="CheckBox1" runat="server" Checked='<%# CType(Eval("owned"),Integer) > -1 %>' Text='<%# Eval("equipmentCode") %>' />

Because I used LEFT JOIN, Client_Equipment.clientId returns the clientID if the client owns the equipment but NULL when he doesn't.  ISNULL(x, -1) is a SQL function that return -1 when x is NULL.

Edwin

Now i am getting a compile error of  - BC30451: Name 'Eval' is not declared, in the same line

I tested the code in C# but don't understand why it said Eval is not declared.  I am now busy in something else.  Let me come back to you later.

sounds good ....BTW i am actually using VB , dont know much about the difference between the two..so maybe?

Forget to ask you which version of ASP.NET are you using? 1.1 or 2.0?

Hi Edwin, I am using 1.1. Your solution works fine now, not getting any more errors. However i am getting just the partial output. I need to show the entire list of checkboxes, but its coming up with just the checked ones. So the complete list should be returned and whenever there's a hit with Client_equipment,  the box would be checked. This was how I was originally pulling all the boxes (doesnt do anything else)

objCmd = New SqlCommand("SELECT * FROM Equipment ORDER BY EquipmentCodeASC ", objConn)
objRdr = objCmd.ExecuteReader()
chklstEquipment.DataSource = objRdr
chklstEquipment.DataValueField = "EquipmentID"
chklstEquipment.DataTextField = "EquipmentCode"
chklstEquipment.DataBind()
objRdr.Close()

Did you use the modifed SQL query?

SELECT tblEquipment.equipmentId,tblEquipment.equipmentCode, CASE WHEN tblClient_Equipment.clientId IS NULL THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT) END AS owned FROM tblEquipment LEFT JOIN tblClient_Equipment ON tblEquipment.equipmentId = tblClient_Equipment.equipmentId AND tblClient_Equipment.clientId=2

I tested it with tables with the following structure.
tblEquipment has fields (equipmentId, equipmentCode)
tblClient has fields (clientId, clientCode)
tblClient_Equipment has fields (equipmentid, clientId)

Try running it in a query window in your SQL Enterprise Manager.  LEFT JOIN is the trick.

You should see the whole list of equipments with an additional field "owned" which is either 1 or 0.

Edwin, I just tried your query in enterprise mgr. I dont get the whole list, just the records which are checked or exist. So basically its the same list as when i tried it with your other query - ISNULL(Client_Equipment.clientId,-1) AS owned

I ran this script and get what I expected.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblEquipment](
      [equipmentId] [int] NOT NULL,
      [equipmentCode] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_tblEquipment] PRIMARY KEY CLUSTERED
(
      [equipmentId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblClient_Equipment](
      [clientId] [int] NOT NULL,
      [equipmentId] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[tblEquipment]
           ([equipmentId]
           ,[equipmentCode])
     VALUES
           (1 ,'E1')
GO
INSERT INTO [dbo].[tblEquipment]
           ([equipmentId]
           ,[equipmentCode])
     VALUES
           (2 ,'E2')
GO
INSERT INTO [dbo].[tblEquipment]
           ([equipmentId]
           ,[equipmentCode])
     VALUES
           (3 ,'E3')
GO
INSERT INTO [dbo].[tblEquipment]
           ([equipmentId]
           ,[equipmentCode])
     VALUES
           (4 ,'E4')
GO
INSERT INTO [dbo].[tblClient_Equipment]
           ([clientId]
           ,[equipmentId])
     VALUES
           (2 ,2)
GO
INSERT INTO [dbo].[tblClient_Equipment]
           ([clientId]
           ,[equipmentId])
     VALUES
           (2 ,3)
GO
SELECT tblEquipment.equipmentId,tblEquipment.equipmentCode, CASE WHEN tblClient_Equipment.clientId IS NULL THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT) END AS owned FROM tblEquipment LEFT JOIN tblClient_Equipment ON tblEquipment.equipmentId = tblClient_Equipment.equipmentId AND tblClient_Equipment.clientId=2
GO

You can try it and see if you get the same result.

Please show me your SQL query and your table structure.

Edwin

I am using the exact same sql query, (i am using slightly different tables/columns but its exactly the same as yours).
objCmd5 = New SqlCommand("SELECT ContentsEquipment.ContentsEquipmentID, ContentsEquipment.ContentsEquipmentCode, CASE WHEN CarrierEquipment.CarrierID IS NULL THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT) END AS owned FROM ContentsEquipment LEFT JOIN CarrierEquipment ON ContentsEquipment.ContentsEquipmentID = CarrierEquipment.ContentsEquipmentID WHERE CarrierEquipment.CarrierID = " + Request.QueryString("id"), objConn)
objRdr5 = objCmd5.ExecuteReader()
dlEquipment.DataSource = objRdr5
dlEquipment.DataBind()
objRdr5.Close()

Table Structure is as follows:
This is for ContentsEquipment:
ContentsEquipmentID       ContentsEquipment       ContentsEquipmentCode
1                                                Eq1                                             FRT
2                                                Eq2                                             GBB
3                                                Eq3                                             MGJ
4                                                Eq4                                             NHG
5                                                Eq5                                             RTET
6                                                Eq6                                             RXFF
7                                                Eq7                                             WERR
8                                                Eq8                                             WWWT
9                                                Eq9                                             ZSDF
10                                              Eq10                                           ZZFF

This is for CarrierEquipment: (CarrierEquipmentID is the primary key)
CarrierEquipmentID               CarrierID                       ContentsEquipmentID
30                                        10                                           1
31                                        10                                           2
32                                        10                                           3
33                                        11                                           2
34                                        11                                           5
35                                        11                                           6
36                                        11                                           4
37                                        11                                           7
38                                        11                                           8

For example if you choose CarrierID 10, then just the first 3 checkboxes show (1,2,3), instead of showing all the 10 equipment, with the first 3 checked and last 7 unchecked.

One thing worth noting here is if an item is unchecked then it just doesnt contain a record in CarrierEquipment, it doesnt show as NULL. I noticed you were checking for NULL in your query, would that make a difference?

Your query is NOT exactly as mine.  I used ... LEFT JOIN tblClient_Equipment ON tblEquipment.equipmentId = tblClient_Equipment.equipmentId >>AND<< tblClient_Equipment.clientId=2

 and your qery is ... LEFT JOIN CarrierEquipment ON ContentsEquipment.ContentsEquipmentID = CarrierEquipment.ContentsEquipmentID >>WHERE<< CarrierEquipment.CarrierID = " + Request.QueryString("id")

Change the WHERE to AND and it should return what you want.

Edwin

Edwin, Thats totally my bad. In fact I had it as AND earlier but was getting an error so switched it to WHERE, but now when i try it with AND it works perfectly. The errors must have been unrelated. Just one more question for you. When I update the page now i get an error, as i changed my code from just checkboxlist into one with a datalist. I get a - Specified cast is not valid on this first line here. my code-behind looks like this:

 For Each Item In dlEquipment.Items
                If Item.Selected Then
                    Dim insertCheckedSQL As String = "INSERT INTO CarrierEquipment (CarrierID, ContentsEquipmentID) VALUES (" + Request.QueryString("id") + ", " + Item.Value + ")"
                    SqlHelper.ExecuteNonQuery(objConn, CommandType.Text, insertCheckedSQL)
                End If
            Next

Is dlEquipment a datalist or checkboxlist?  If it is a datalist, then what is the variable type of Item.  Also it should not have "Selected" property.

Your code should look like this.

                Dim chkbox As CheckBox

        Dim insertCheckedSQL As String = String.Format("INSERT INTO CarrierEquipment (CarrierID, ContentsEquipmentID) VALUES ({0}, @equipmentId)", Request.QueryString("id"))
        Dim cmd As System.Data.SqlClient.SqlCommand

        cmd = New System.Data.SqlClient.SqlCommand(insertCheckedSQL, objConn)
        cmd.Parameters.Add("@equipmentId", Data.SqlDbType.Int)

        For Each item As DataListItem In Me.DataList1.Items
            chkbox = CType(item.FindControl("CheckBox1"), CheckBox)
            If (chkbox.Checked) Then
                'do your update
                cmd.Parameters("@equipmentId").Value = CType(DataList1.DataKeys(item.ItemIndex), Integer)
                cmd.ExecuteNonQuery()
            End If
        Next

Edwin, that worked perfectly. This I cant figure how you structured this part of the query, shouldnt the request.querystring(id) come first and what does the {0) do? .....ContentsEquipmentID) VALUES ({0}, @equipmentId)", Request.QueryString("id"))......

I would surely give you more points if i could! Thanks!

I prefer using String.Format to build complex string like query rather using string concatenation because it is easier to read the string and thus less chance to make error.  String.Format("I like {0} and '{1}'", "ASP.NET", "C#") will give you the string "I like ASP.NET and 'C#'".