Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Using parameters with sqldatasource and mySQL

Reply
Thread Tools

Using parameters with sqldatasource and mySQL

 
 
tyea tyea is offline
Junior Member
Join Date: Aug 2008
Posts: 2
 
      08-06-2008
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_SelectedI ndexChanged" 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
 
Reply With Quote
 
 
 
 
tyea tyea is offline
Junior Member
Join Date: Aug 2008
Posts: 2
 
      08-07-2008
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."
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
sqldatasource using a stored procedure with OUTPUT parameters only Andy in South Jersey ASP .Net 3 02-11-2009 02:51 PM
sqldatasource using a stored procedure with OUTPUT parameters only Andy in South Jersey ASP .Net 0 02-11-2009 01:51 PM
Using Parameters within SqlDataSource Object--Need Help~ Brad Isaacs ASP .Net 0 01-30-2007 01:04 AM
Using SqlDataSource with MySQL msch-prv@bluewin.ch ASP .Net 5 09-17-2006 08:08 PM
MySQL and Oracle using SqlDataSource control Mr Seth T ASP .Net 0 08-30-2006 03:07 PM



Advertisments