Using parameters with sqldatasource and mySQL

Discussion in '.NET' started by tyea, Aug 6, 2008.

  1. tyea

    tyea

    Joined:
    Aug 6, 2008
    Messages:
    2
    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"><asp:DropDownList ID="ddlPriorityID" DataSourceID="sdsPriorityList" DataValueField="priorityID"
    datatextfield="priorityName" AppendDataBoundItems="true" TabIndex="4" runat="server">
    <asp:ListItem Value="" Selected="True" />
    </asp:DropDownList></td></tr>
    <tr><td class="fieldHeading">Job Type:</td>
    <td class="fieldText"><asp:DropDownList ID="ddlTypeCode" DataSourceID="sdsTypeList" DataValueField="typeCode"
    datatextfield="typeName" AppendDataBoundItems="true" TabIndex="4" runat="server">
    <asp:ListItem Value="" Selected="True" />
    </asp:DropDownList></td></tr>
    <tr><td class="fieldHeading">Institute:</td>
    <td class="fieldText"><asp:DropDownList ID="ddlInstituteCode" DataSourceID="sdsInstituteList" DataValueField="instituteCode"
    datatextfield="instituteName" AppendDataBoundItems="true" TabIndex="5" AutoPostBack="true" OnSelectedIndexChanged="ddlInstituteCode_SelectedIndexChanged" runat="server">
    <asp:ListItem Selected="True" Value="" />
    </asp:DropDownList></td></tr>
    <tr><td class="fieldHeading">Contact Person:</td>
    <td class="fieldText"><asp:DropDownList ID="ddlContactUserName" DataSourceID="sdsContactUserList" DataValueField="userName"
    datatextfield="name" AppendDataBoundItems="true" TabIndex="6" runat="server">
    <asp:ListItem Value="" Selected="True" />
    </asp:DropDownList></td></tr>
    <tr><td class="fieldHeading">Assigned User:</td>
    <td class="fieldText"><asp:DropDownList ID="ddlAssignedUserName" DataSourceID="sdsAssignedUserList" DataValueField="userName"
    datatextfield="name" AppendDataBoundItems="true" TabIndex="7" runat="server">
    <asp:ListItem Value="" Selected="True" />
    </asp:DropDownList></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, Aug 6, 2008
    #1
    1. Advertising

  2. tyea

    tyea

    Joined:
    Aug 6, 2008
    Messages:
    2
    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."
    tyea, Aug 7, 2008
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Mr Seth T
    Replies:
    0
    Views:
    443
    Mr Seth T
    Aug 30, 2006
  2. msch-prv@bluewin.ch

    Using SqlDataSource with MySQL

    msch-prv@bluewin.ch, Sep 17, 2006, in forum: ASP .Net
    Replies:
    5
    Views:
    25,747
    Mark Rae
    Sep 17, 2006
  3. Brad Isaacs
    Replies:
    0
    Views:
    318
    Brad Isaacs
    Jan 30, 2007
  4. Andy in South Jersey
    Replies:
    3
    Views:
    2,463
    Andy in South Jersey
    Feb 11, 2009
  5. Andy in South Jersey
    Replies:
    0
    Views:
    277
    Andy in South Jersey
    Feb 11, 2009
Loading...

Share This Page