Using parameters with sqldatasource and mySQL

[tyea]

I have an ASP.net page which has a number of dropdownlists. Each of the dropdownlists has an sqldatasource as it's source. The values displayed in the "Contact Person" dropdownlist is dependent on the selection made from the "Institute" dropdownlist (this part seems to be working fine). However, when the page first loads or if the blank item is selected from the Institute dropdownlist, then I want the "Contact Person" dropdownlist to display all available contact people (i.e. pass a null value in the select parameter to the mysql stored procedure). Instead it displays an empty dropdownlist. If you select an Institute then it works fine and reloads the Contact Person list with the right data. So it is just when the page initially loads that the Contact Person list is empty. I'm not sure what is going wrong...

<table class="formTable">
<tr><td class="fieldHeading">Job ID:</td><td class="fieldText"><asp:TextBox id="intJobID" Columns="10" MaxLength="10" TabIndex="1" CssClass="textbox" runat="server"/></td></tr>
<tr><td class="fieldHeading">Priority Level:</td>
<td class="fieldText"><aspropDownList ID="ddlPriorityID" DataSourceID="sdsPriorityList" DataValueField="priorityID"
datatextfield="priorityName" AppendDataBoundItems="true" TabIndex="4" runat="server">
<asp:ListItem Value="" Selected="True" />
</aspropDownList></td></tr>
<tr><td class="fieldHeading">Job Type:</td>
<td class="fieldText"><aspropDownList ID="ddlTypeCode" DataSourceID="sdsTypeList" DataValueField="typeCode"
datatextfield="typeName" AppendDataBoundItems="true" TabIndex="4" runat="server">
<asp:ListItem Value="" Selected="True" />
</aspropDownList></td></tr>
<tr><td class="fieldHeading">Institute:</td>
<td class="fieldText"><aspropDownList ID="ddlInstituteCode" DataSourceID="sdsInstituteList" DataValueField="instituteCode"
datatextfield="instituteName" AppendDataBoundItems="true" TabIndex="5" AutoPostBack="true" OnSelectedIndexChanged="ddlInstituteCode_SelectedIndexChanged" runat="server">
<asp:ListItem Selected="True" Value="" />
</aspropDownList></td></tr>
<tr><td class="fieldHeading">Contact Person:</td>
<td class="fieldText"><aspropDownList ID="ddlContactUserName" DataSourceID="sdsContactUserList" DataValueField="userName"
datatextfield="name" AppendDataBoundItems="true" TabIndex="6" runat="server">
<asp:ListItem Value="" Selected="True" />
</aspropDownList></td></tr>
<tr><td class="fieldHeading">Assigned User:</td>
<td class="fieldText"><aspropDownList ID="ddlAssignedUserName" DataSourceID="sdsAssignedUserList" DataValueField="userName"
datatextfield="name" AppendDataBoundItems="true" TabIndex="7" runat="server">
<asp:ListItem Value="" Selected="True" />
</aspropDownList></td></tr>
<tr><td colspan="2" align="center"><asp:Button ID="btnSearch" Text="Search" CommandName="Search" ToolTip="Enter the search criteria and then click 'Search'." CssClass="button" runat="server" /></td></tr>
</table>
<asp:SqlDataSource ID="sdsPriorityList" ConnectionString="<%$ ConnectionStrings:mainDBConnection %>"
ProviderName="<%$ ConnectionStrings:mainDBConnection.ProviderName %>"
SelectCommand="CALL HELP.lstJobPriorities" runat="server" />
<asp:SqlDataSource ID="sdsTypeList" ConnectionString="<%$ ConnectionStrings:mainDBConnection %>"
ProviderName="<%$ ConnectionStrings:mainDBConnection.ProviderName %>"
SelectCommand="CALL HELP.lstJobTypes" runat="server" />
<asp:SqlDataSource ID="sdsInstituteList" ConnectionString="<%$ ConnectionStrings:mainDBConnection %>"
ProviderName="<%$ ConnectionStrings:mainDBConnection.ProviderName %>"
SelectCommand="CALL HELP.lstJobInstitutes" runat="server" />
<asp:SqlDataSource ID="sdsContactUserList" ConnectionString="<%$ ConnectionStrings:mainDBConnection %>"
ProviderName="<%$ ConnectionStrings:mainDBConnection.ProviderName %>"
SelectCommand="CALL HELP.lstJobContacts(?)" runat="server">
<SelectParameters>
<asp:ControlParameter Name="instituteCode" Type="String" ControlID="ddlInstituteCode" ConvertEmptyStringToNull="true" DefaultValue="" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="sdsAssignedUserList" ConnectionString="<%$ ConnectionStrings:mainDBConnection %>"
ProviderName="<%$ ConnectionStrings:mainDBConnection.ProviderName %>"
SelectCommand="CALL HELP.lstUserAtLevel(3)" runat="server" />




Protected Sub ddlInstituteCode_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
'if an institute has been selected
If ddlInstituteCode.SelectedValue <> "" Then
'update the select command and parameter for the contact list to the selected Institute code
sdsContactUserList.SelectParameters("instituteCode").DefaultValue = ddlInstituteCode.SelectedValue
Else
'update the select command and parameter for the contact list to retrieve a full list
sdsContactUserList.SelectParameters("instituteCode").DefaultValue = ""
End If

'rebind the contact dropdownlist based on the institute selection
ddlContactUserName.Items.Clear()
ddlContactUserName.DataBind()

'insert a blank item at the start of the list and select it
ddlContactUserName.Items.Insert(0, "")
ddlContactUserName.SelectedIndex = 0
End Sub

 

[tyea]

Solution -

It looks like if the fields were empty, ConvertEmptyStringToNull was working but actually cancelling the query.

"To make your SELECT query to be executed when the value is set to NULL, you must set the CancelSelectOnNullParameter attribute of the SqlDataSource to False. By default this value is True, so if any of your paramters is set to NULL, the SelectCommand will not be executed. If you set it to Fasle, the SelectCommand will execute the query. If you use set the parameter's ConvertEmtpyStringToNull attribute to True, you must set the CancelSelectOnNullParameter to false."