I have two cascading dropdown lists I'm attempting to bind to two separate SqlDataSources each.
These dropdownlists exist in a FormView's EditItemTemplate. Inside the EditItemTemplate two sqldatasource controls exist that populate the department and the jobname. The DeptID and the JobID are the primary keys in those tables. This creates the "cascading effect" between departments and jobs. When a department is selected, only the jobs associated with that department appear.
This piece is working properly.
<asp:FormView ID="frmProfile" runat="server" DataSourceID="sqlDSProfile"
DataKeyNames="EUID" style="margin-top: 0px">
<EditItemTemplate>
<asp:DropDownList ID="ddlDepartments" runat="server" Width="135px"
DataSourceID="sqlDSDepartments"
DataTextField="Department"
DataValueField="DeptID" AutoPostBack="True"
SelectedValue='<%# Bind("CurrentDeptID") %>'
AppendDataBoundItems="true" >
<asp:ListItem></asp:ListItem>
</asp:DropDownList>
<asp:DropDownList ID="ddlJobNames" runat="server" Width="185px"
DataSourceID="sqlDSJobs" DataTextField="JobName" DataValueField="JobID"
SelectedValue='<%# Bind("CurrentJobID") %>'
AppendDataBoundItems="true" >
<asp:ListItem></asp:ListItem>
</asp:DropDownList>
<asp:SqlDataSource ID="sqlDSDepartments" runat="server"
ConnectionString="<%$ ConnectionStrings:JobsDB %>"
SelectCommand="SELECT tblDepartments.DeptID,
tblDepartments.Department
FROM tblDepartments" />
<asp:SqlDataSource ID="sqlDSJobs" runat="server"
ConnectionString="<%$ ConnectionStrings:JobsDB %>"
SelectCommand="SELECT tblJobs.JobID, tblJobs.JobName FROM tblJobs
INNER JOIN tblDeptsJobs ON tblDeptsJobs.JobID = tblJobs.JobID
WHERE tblDeptsJobs.DeptID = @DeptID" >
<SelectParameters>
<asp:ControlParameter ControlID="ddlDepartments" Name="DeptID"
PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>
</EditItemTemplate>
</asp:FormView>
Outside the formview the SqlDataSource exists that binds all of the information to the Employee table in an update statement. I'm leaving all of the other information in this SqlDataSource even though it's been omitted from the FormView above.
<asp:SqlDataSource ID="sqlDSProfile" runat="server"
ConnectionString="<%$ ConnectionStrings:JobsDB %>"
SelectCommand="SELECT tblEmployee.EUID,
tblEmployee.DateHired,
tblEmployee.LastName,
tblEmployee.HiredLastName,
tblEmployee.FirstName,
tblEmployee.Role,
tblEmployee.JobGrade,
tblEmployee.CurrentDeptID,
tblDepartments.Department,
tblDepartments.DeptID,
tblEmployee.CurrentJobID,
tblJobs.JobName,
tblJobs.JobID,
tblEmployee.CurrentShift,
tblEmployee.JobDate,
tblEmployee.IsDisplaced,
tblEmployee.EligibilityDate
FROM tblEmployee
LEFT OUTER JOIN tblDepartments ON tblEmployee.CurrentDeptID = tblDepartments.DeptID
EFT OUTER JOIN tblJobs ON tblEmployee.CurrentJobID = tblJobs.JobID
WHERE (tblEmployee.EUID = @EUID)"
UpdateCommand="UPDATE [tblEmployee]
SET [tblEmployee].[DateHired] = @DateHired,
[tblEmployee].[LastName] = @LastName,
[tblEmployee].[HiredLastName] = @HiredLastName,
[tblEmployee].[FirstName] = @FirstName,
[tblEmployee].[Role] = @Role,
[tblEmployee].[JobGrade] = @JobGrade,
[tblEmployee].[CurrentDeptID] = @CurrentDeptID,
[tblEmployee].[CurrentJobID] = @CurrentJobID,
[tblEmployee].[CurrentShift] = @CurrentShift,
[tblEmployee].[JobDate] = @JobDate,
[tblEmployee].[IsDisplaced] = @IsDisplaced,
[tblEmployee].[EligibilityDate] = @EligibilityDate
WHERE [tblEmployee].[EUID] = @EUID"
ProviderName="System.Data.SqlClient">
<SelectParameters>
<asp:SessionParameter Name="EUID" SessionField="sProfileEUID" DbType="String" />
</SelectParameters>
<UpdateParameters>
<asp:Parameter Name="DateHired" DbType="Date" />
<asp:Parameter Name="LastName" DbType="String" />
<asp:Parameter Name="HiredLastName" DbType="String" />
<asp:Parameter Name="FirstName" DbType="String" />
<asp:Parameter Name="Role" DbType="String" />
<asp:Parameter Name="JobGrade" DbType="Byte" />
<asp:Parameter Name="CurrentDeptID" DbType="Int32" />
<asp:Parameter Name="CurrentJobID" DbType="Int32" />
<asp:Parameter Name="CurrentShift" DbType="Int32" />
<asp:Parameter Name="JobDate" DbType="Date" />
<asp:Parameter Name="IsDisplaced" DbType="Boolean"/>
<asp:Parameter Name="EligibilityDate" DbType="Date"/>
<asp:SessionParameter Name="EUID" SessionField="sProfileEUID" DbType="String" />
</UpdateParameters>
</asp:SqlDataSource>
The only pieces I can't figure out how to bind are the Departments and the Jobs. Everything else is working. I've tried using the following code in the DropDownList controls...
SelectedValue='<%# Bind("CurrentDeptID") %>'
SelectedValue='<%# Bind("CurrentJobID") %>'
...but these result in errors.
When the user clicks edit, I need the values in the two dropdownboxes to pull their selectedvalue from the main sqlDSProfile data source, but I need them to be updatable. I've gotten it to the point where I can update and bind the job that an associate belongs to, but because the dropdownlists cascade, when I attempt to change the department the AutoPostBack breaks the binding between sqlDSProfile - CurrentJobID and ddlJobs.
I added tblEmployee.CurrentDeptID and tblEmployee.CurrentJobID to the select statement, and added Bind() statements to the DropDownList controls.
SelectedValue='<%# Bind("CurrentDeptID") %>'
SelectedValue='<%# Bind("CurrentJobID") %>'
The two DropDownLists are now populated with accurate information pulled from the Employee table, showing the department and job that the employee belongs to.
The two DropDownLists are also populated by the two SqlDataSources inside the FormView, giving me options for changing the department and changing the job.
When I change the Job, it works and the employees job is updated.
When I change the Department, it breaks saying DataBinding methods such as Eval(), XPath(), and Bind() can only be used in the context of a databound control.
I removed the data binding from ddlJobs and coded that in the background.
Protected Sub frmProfile_ItemUpdating(sender As Object, e As System.Web.UI.WebControls.FormViewUpdateEventArgs) Handles frmProfile.ItemUpdating
If frmProfile.CurrentMode = FormViewMode.Edit Then
e.NewValues("CurrentJobID") = DirectCast(DirectCast(sender, FormView).FindControl("ddlJobs"), DropDownList).SelectedValue
End If
End Sub
The only piece that's left is building the code for when the ddlDepartments changes.
pseudocode...
' If Item exists in ddlJobs Then
' select item (CurrentJobID)
' else
' select index 0 and make them pick something new
' end if
So Close!
This is the code I've developed to loosely bind this. In the page_load I'm trying to pull the contents of CurrentJobID from sqlDSProfile and check to see if that value exists in ddlJobs. If it does I want to set ddlJobs.SelectedValue = to that CurrentJobID. If it doesn't I want to set the selectedindex to 0 which is a message saying "pick one" or something.
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If frmProfile.CurrentMode = FormViewMode.Edit Then
' Need to determine if the CurrentJobID returned in the select statement
' exists in the ddlJobs dropdownlist. If it does, set that to the
' selectedvalue, if not set it to 0 so the user can select a new job.
Dim ddlJobs As DropDownList = frmProfile.FindControl("ddlJobs")
Dim dvProfile As DataView = sqlDSProfile.Select(DataSourceSelectArguments.Empty)
Dim drvProfile As DataRowView = dvProfile(0)
If ddlJobs.Items.FindByValue(drvProfile("CurrentJobID")) Is DBNull.Value Then
ddlJobs.SelectedIndex = 0
Else
ddlJobs.SelectedValue = drvProfile("CurrentJobID")
End If
End If
End Sub
Its returning a null reference exception on the line where I'm checking for dbnull.value
I have a working solution now, thanks in part to Nuux and a bunch of online research. The tip about the join statement wasn't relevant, but the tip about including "CurrentJobID" and "CurrentDeptID" in my select query was spot on.
In addition to that I had to rework the controls a little. The two cascading dropdownlists are below. The ddlJobs dropdown list behaves like a normal databound control, but it doesn't have the Bind("CurrentJobID") statement I was trying in my original post.
<asp:DropDownList ID="ddlDepartments" runat="server" Width="185px"
DataSourceID="sqlDSDepartments"
DataTextField="Department"
DataValueField="DeptID"
SelectedValue='<%# Bind("CurrentDeptID") %>'
AppendDataBoundItems="true"
AutoPostBack="True" >
<asp:ListItem Text="--Select One--" Value="" />
</asp:DropDownList>
<asp:DropDownList ID="ddlJobs" runat="server" Width="185px"
DataSourceID="sqlDSJobs"
DataTextField="JobName"
DataValueField="JobID"
AppendDataBoundItems="true"
OnDataBinding="ddlJobs_DataBinding" />
The only thing the custom routine "ddlJobs_DataBinding" is doing is adding "--Select One--" as index 0 in the ddlJobs dropdown. I tried this in several places, like page_load, and the databound event of the formview with no success.
Protected Sub ddlJobs_DataBinding(sender As Object, e As System.EventArgs)
Dim ddlJobs As DropDownList = frmProfile.FindControl("ddlJobs")
Dim liSelectOne As New ListItem("--Select One--", 0)
ddlJobs.Items.Clear()
ddlJobs.Items.Insert(0, liSelectOne)
End Sub
The databound event of the formview frmProfile_DataBound event does do some work though. When the user clicks "edit" on the formview to enter editing mode this ensures that the dropdownlist ddlJobs has the correct job selected by default for the profile in question. If the user hasn't been assigned to a job then it defaults to selectedindex 0 which is "--Select One--" set in custom databinding event just above.
Protected Sub frmProfile_DataBound(sender As Object, e As System.EventArgs) Handles frmProfile.DataBound
If frmProfile.CurrentMode = FormViewMode.Edit Then
Dim ddlJobs As DropDownList = frmProfile.FindControl("ddlJobs")
Dim dvProfile As DataView = sqlDSProfile.Select(DataSourceSelectArguments.Empty)
Dim drProfile As DataRow = dvProfile.Table.Rows(0)
If drProfile("CurrentJobID").ToString() = "" Then
ddlJobs.SelectedIndex = 0
Else
ddlJobs.SelectedValue = drProfile("CurrentJobID").ToString()
End If
End If
End Sub
Finally, if the user selects a new job from ddlJobs, that value has to be fed to the database, which the ItemUpdating event of the formview handles.
Protected Sub frmProfile_ItemUpdating(sender As Object, e As System.Web.UI.WebControls.FormViewUpdateEventArgs) Handles frmProfile.ItemUpdating
If frmProfile.CurrentMode = FormViewMode.Edit Then
Dim ddlJobs As DropDownList = frmProfile.FindControl("ddlJobs")
e.NewValues("CurrentJobID") = ddlJobs.SelectedValue
End If
End Sub
Done!