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