Databinding issue with DDL and checkbox

[dot_net_rookie]

Hi ,

I am trying to bind a drop down and checkbox with values from two tables from database. The 1st table has categories and is binded to the DDL and the 2nd table has sub categories and is binded to checkbox and inter related by using the category ID (foreign key). I am facing two issues :

1. I am unable to list the subcategories except for the 1st category. Even if i try to select any other category the dropdown automatically displays the first category and does not lists the subcategories of other categories

2. When I try to update the value selected in table I am getting the following error: Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index at line no : 100

I am banging my head in gettis this fixed pls help me out

Aspx page:

 

 <form id="form1" runat="server">
         <div>
            <table>
                <tr>
                    <td>
                        <asp:Label ID="lbl_project" runat="server">Project</asp:Label>  
                    </td>
                    <td>
                        <asp:DropDownList ID="proj_dd" DataSourceID="ds_proj" runat="server" DataTextField="m_PROJ_NAME" DataValueField="M_PROJ_ID" AutoPostBack="true"></asp:DropDownList>
                        <asp:SqlDataSource ID="ds_proj" runat ="server" ConnectionString="Driver={MySQL ODBC 3.51 Driver};DATABASE=laqualite;option=0;pwd=cadt12;port=0;server=10.20.18.50;uid=root" ProviderName="System.Data.Odbc"  SelectCommand="select M_PROJ_ID,m_PROJ_NAME from mast_project_tbl where M_PROJ_STATUS='Active'" DataSourceMode="DataReader"  ></asp:SqlDataSource>  
                    </td>
                    <td>
                        <asp:Label ID="lbl_title" runat="server">Title</asp:Label>
                    </td>
                    <td>
                        <asp:DropDownList ID="title_dd" runat="server" DataSourceID="title_ds" DataTextField="M_TITLE_TITLE" DataValueField="M_TITLE_ID_PK" AutoPostBack="true"></asp:DropDownList>
                        <asp:SqlDataSource ID="title_ds" runat="server" ConnectionString ="Driver={MySQL ODBC 3.51 Driver};DATABASE=laqualite;option=0;pwd=cadt12;port=0;server=10.20.18.50;uid=root" ProviderName="System.Data.Odbc" DataSourceMode="DataReader" SelectCommand="Select M_TITLE_ID_PK,M_TITLE_TITLE from mast_title_tbl where M_TITLE_STATUS='Active' and M_TITLE_ID_PK>1"></asp:SqlDataSource>
                    </td>
                    <td>
                        <asp:Label ID="emp_lbl" runat="server">Employee</asp:Label>
                    </td>
                    <td>
                        <asp:DropDownList ID="emp_dd" runat="server"  AutoPostBack="true" DataTextField="M_Q_EMP_NAME" DataValueField="M_Q_EMP_ID_PK"></asp:DropDownList>
                   </td>
                </tr>  
            </table>
            <table>
                <tr>
                    <td>       
                        <%--<asp:CheckBoxList ID="cblCategory" runat="server" ></asp:CheckBoxList>--%>
                        <asp:DropDownList ID="ddlCategory" runat ="server" OnSelectedIndexChanged = "ddlCategory_SelectedIndexChanged" AutoPostBack ="true"></asp:DropDownList>
                    </td>
                </tr>
                <%--<tr>
                    <td>   
                        <asp:CheckBoxList ID="ckhSubCategory" runat="server"></asp:CheckBoxList>
                    </td>   
                </tr>--%>
                <tr>
                    <td>
                        <asp:CheckBoxList id="CheckBoxList2" runat="server"></asp:CheckBoxList>                           
                    </td>
                </tr>                          
                <tr>
                    <asp:Button ID="set_rights_btn" runat="server"  Text="Set Rights"    ></asp:Button>
                    <asp:SqlDataSource ID="title_dss" runat="server" ConnectionString="Driver={MySQL ODBC 3.51 Driver};DATABASE=laqualite;option=0;pwd=cadt12;port=0;server=10.20.18.50;uid=root"
                        DataSourceMode="DataReader" ProviderName="System.Data.Odbc" SelectCommand="Select M_PAGES_ID_PK,M_PAGES_PAGES,M_CATPAGES_ID, M_CATPAGES_CAT From mast_pages_tbl,mast_catpages_tbl Where M_CATPAGES_ID = M_PAGES_CATID and M_PAGES_ID_PK <> '27' Order By M_CATPAGES_CAT">
                    </asp:SqlDataSource>
                </tr>              
          </table>
        </div>
    </form>

 

code behind: 

 Imports System.Data.Odbc
Imports MySql.Data.MySqlClient
Imports System.Web.UI.WebControls
Partial Public Class rights
    Inherits System.Web.UI.Page
    Dim MyConn As String
    Dim strSQL As String
    Dim objConnection As OdbcConnection
    Dim connString As String
    Dim conString As String = ConfigurationManager.ConnectionStrings("connection_string").ConnectionString.ToString()
    Dim MySQLCon, MySQLSubCon As New MySqlConnection(conString)
    Dim MySQLReader, MySqlSubReader As MySqlDataReader
    Dim SQLQuery, strQuery As String
    Dim myCommand As OdbcCommand
    Dim pgcount_SQL As String


    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        bindCategory()

    End Sub
    Protected Sub proj_dd_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles proj_dd.SelectedIndexChanged
        'To list employees according to the project selected
        Dim proj_name, emplist As String
        proj_name = proj_dd.SelectedValue.ToString
        emplist = emp_dd.SelectedValue
        connString = ConfigurationManager.ConnectionStrings("conn_proj").ToString
        objConnection = New OdbcConnection(connString)
        objConnection.Open()
        'strSQL = "select M_Q_EMP_ID_PK,M_Q_EMP_NAME from mast_emp_tbl as a left join mast_project_tbl as b on a.M_Q_EMP_PROJECT = b.M_PROJ_ID where b.M_PROJ_ID='" & proj_dd.SelectedValue & "'"
        strSQL = "select a.M_Q_EMP_ID_PK,a.M_Q_EMP_NAME from mast_emp_tbl as a left join mast_project_tbl as b on a.M_Q_EMP_PROJECT = b.M_PROJ_ID left join mast_title_tbl as c on a.M_Q_EMP_TITLE=c.M_TITLE_ID_PK where c.M_TITLE_ID_PK='" & title_dd.SelectedValue & "' and b.M_PROJ_ID='" & proj_dd.SelectedValue & "'"
        'Response.Write(strSQL)

        Dim da As OdbcDataAdapter = New OdbcDataAdapter(strSQL, objConnection)
        Dim ds As DataSet = New DataSet
        da.Fill(ds, "mast_emp_tbl")
        emp_dd.DataSource = ds
        emp_dd.DataBind()
    End Sub
    Protected Sub ddlCategory_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles ddlCategory.SelectedIndexChanged
        bindSubCategory()
    End Sub

    Protected Sub set_rights_btn_Click(ByVal sender As Object, ByVal e As EventArgs) Handles set_rights_btn.Click
        saverights()

    End Sub
    Protected Sub title_dd_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles title_dd.SelectedIndexChanged
        'To list employees in dropdown according to the TITLE and PROJECT selected
        Dim proj_name, emplist As String
        proj_name = proj_dd.SelectedValue.ToString
        emplist = emp_dd.SelectedValue
        connString = ConfigurationManager.ConnectionStrings("conn_proj").ToString
        objConnection = New OdbcConnection(connString)
        objConnection.Open()
        strSQL = "select a.M_Q_EMP_ID_PK,a.M_Q_EMP_NAME from mast_emp_tbl as a left join mast_project_tbl as b on a.M_Q_EMP_PROJECT = b.M_PROJ_ID left join mast_title_tbl as c on a.M_Q_EMP_TITLE=c.M_TITLE_ID_PK where c.M_TITLE_ID_PK='" & title_dd.SelectedValue & "' and b.M_PROJ_ID='" & proj_dd.SelectedValue & "'"
        'Response.Write(strSQL)
        Dim da As OdbcDataAdapter = New OdbcDataAdapter(strSQL, objConnection)
        Dim ds As DataSet = New DataSet
        da.Fill(ds, "mast_emp_tbl")
        emp_dd.DataSource = ds
        emp_dd.DataBind()
    End Sub

    Protected Sub bindCategory()
        MySQLCon.Open()
        SQLQuery = "SELECT distinct M_CATPAGES_ID,M_CATPAGES_CAT FROM mast_catpages_tbl WHERE (M_CATPAGES_ID <> '0') ORDER BY M_CATPAGES_ID"
        Dim dtSet As New DataSet()
        Dim DA As New MySqlDataAdapter(SQLQuery, MySQLCon)
        DA.Fill(dtSet)
        ddlCategory.DataSource = dtSet
        ddlCategory.DataTextField = "M_CATPAGES_CAT"
        ddlCategory.DataValueField = "M_CATPAGES_ID"
        ddlCategory.DataBind()
        MySQLCon.Close()
    End Sub

    Protected Sub bindSubCategory()
        MySQLSubCon.Open()
        'Dim stGet As String = "Select a.M_PAGES_PAGES,a.M_PAGES_ID_PK from mast_pages_tbl as a inner join mast_catpages_tbl as b on a.M_PAGES_CATID = b.M_CATPAGES_ID where b.M_CATPAGES_CAT= '" & ddlCategory.SelectedValue.ToString() & "'"
        'Dim stGet As String = "Select a.M_PAGES_PAGES,a.M_PAGES_ID_PK from mast_pages_tbl as a inner join mast_catpages_tbl as b on a.M_PAGES_CATID = b.M_CATPAGES_ID where b.M_CATPAGES_CAT=1"
        Dim stGet As String = "Select a.M_PAGES_PAGES,a.M_PAGES_ID_PK from mast_pages_tbl as a inner join mast_catpages_tbl as b on a.M_PAGES_CATID = b.M_CATPAGES_ID where b.M_CATPAGES_CAT= '" & ddlCategory.SelectedItem .ToString() & "' and a.M_PAGES_ID_PK <>'27' "
        Dim dsGet As New DataSet()
        Dim MySQLDA As New MySqlDataAdapter(stGet, MySQLSubCon)
        MySQLDA.Fill(dsGet)
        CheckBoxList2.DataSource = dsGet
        CheckBoxList2.DataTextField = "M_PAGES_PAGES"
        CheckBoxList2.DataValueField = "M_PAGES_ID_PK"
        CheckBoxList2.DataBind()
        MySQLSubCon.Close()
    End Sub

   
    Protected Sub saverights()
        Dim rights_uptd_time As String
        rights_uptd_time = System.DateTime.Now
        For i As Integer = 1 To CheckBoxList2.Items.Count
            Response.Write(CheckBoxList2.SelectedValue.ToString)
            'If CheckBoxList2.Items(i).Selected Then
            If CheckBoxList2.Items(i).Selected Then
                MySQLSubCon.Open()
                strSQL = "Insert into tran_titlerights_tbl(T_TITLERIGHTS_ID,T_TITLERIGHTS_PAGES,T_TITLERIGHTS_BY,T_TITLERIGHTS_UPDATE)Values(" & CheckBoxList2.Items(i).Value & ",Pg_Id,'" & Session("empid") & "','" & rights_uptd_time.ToString & "')"


                MySQLSubCon.Close()
            End If
        Next
    End Sub

End Class

[jbisono]

Make sure your dropdownlist does not have duplicate values, for example if you result set when you bind the sub category dropdownlist have a duplicate value, when postback is going to go back to first position because your value must be unique, verify that first and then tell us, what happened. to be more straightforward check this field M_PAGES_ID_PK when you bind the sub category dropdown.

Regards.

[dot_net_rookie]

Make sure your dropdownlist does not have duplicate values, for example if you result set when you bind the sub category dropdownlist have a duplicate value, when postback is going to go back to first position because your value must be unique, verify that first and then tell us, what happened. to be more straightforward check this field M_PAGES_ID_PK when you bind the sub category dropdown.

Regards.

Hi,
I am able to fix the first issue by using If Not IsPostBack in page load ...Working on the 2nd issue.....

[jbisono]

You right :).

[jbisono]

Just double check this, I am not sure. but when you say this
For i As Integer = 1 To CheckBoxList2.Items.Count
I think the checkboxlist index start in 0.

[dot_net_rookie]

You are right the index starts from 0 and that has fixed the issue