asked on

Trying to populate an Ajax CascadingDropDown using VB.Net

I have a dropdown on a webpage that I am trying to populate using a CascadingDropDown using Ajax. I am using VS 2005 2.0. Below is my aspx code, as well as my VB.Net code. The dropdown is not populating.

<tr>
                        <td style="width: 110px; height: 21px;" align="right">
                            <asp:Label ID="SRNameLabel" runat="server" Text="SR_Name"></asp:Label>
                        </td>
                        <td style="width: 300px; height: 21px;">
                            <asp:DropDownList ID="SRNameDDL" runat="server" Width="150px">
                            </asp:DropDownList>
                            <ajaxToolkit:CascadingDropDown Category="SalesRep" ID="CascadingDropDown1" runat="server"
                                ServiceMethod="GetSalesRep" TargetControlID="SRNameDDL" UseContextKey="True">
                            </ajaxToolkit:CascadingDropDown>
                        </td>
                    </tr>
 
 
 
 
 
    <System.Web.Services.WebMethodAttribute()> <System.Web.Script.Services.ScriptMethodAttribute()> Public Shared Function GetSalesRep(ByVal knownCategoryValues As String, ByVal category As String) As CascadingDropDownNameValue()
        Dim strConnection As String = ConfigurationManager.ConnectionStrings("StikeData").ConnectionString
        Dim sqlConn As SqlConnection = New SqlConnection(strConnection)
        Dim strSalesRepQuery As String = "SELECT * FROM tbl_SalesRep WHERE [End] IS NULL OR [End] > GetDate(); "
        Dim cmdFetchSalesRep As SqlCommand = New SqlCommand(strSalesRepQuery, sqlConn)
 
        Dim dtrSR As SqlDataReader
        Dim mySR As New List(Of CascadingDropDownNameValue)
 
        sqlConn.Open()
        dtrSR = cmdFetchSalesRep.ExecuteReader
 
        While dtrSR.Read()
            Dim strSRName As String = dtrSR("SR_Name").ToString
            Dim strSRID As Integer = CInt(dtrSR("SRID").ToString)
 
            mySR.Add(New CascadingDropDownNameValue(strSRName, strSRID))
        End While
 
        Return mySR.ToArray
    End Function

Open in new window

shahprabal

Get a dataset, bind the datatable of the ds to the ddl, set the DataTextField and DataValueField properties and you should be all set.

Dim ds As DataSet 
ds = myobject.GetDataSet() 
droplist.DataSource = ds.Tables(0).DefaultView 
droplist.DataTextField = ds.Tables(0).Columns("DataColumn").ColumnName
droplist.DataValueField = ds.Tables(0).Columns("ValueColumn").ColumnName
droplist.DataBind()

Open in new window

utlonghornjulie

ASKER

I thought the whole point of using a CascadingDropDown was to not have to populate the asp:dropdownlist using a dataset.

shahprabal

The whole point of CascadingDropDown is that if you have multiple ddl and when you select one the second one needs to be filtered and so on... you can use cascadingdropdown to make things easy.
Check out :http://www.asp.net/ajax/ajaxcontroltoolkit/samples/CascadingDropDown/CascadingDropDown.aspx
and look at the properties to give you an idea on how to make it work.

utlonghornjulie

ASKER

I have looked at that page, and I have replicated that code, but it still does not work. Is there something wrong with my code???

shahprabal

I can't understand why you would need to use this control. This control is useful only when you need to filter values that are available in one dropdownlist's based on the selected value in another dropdownlist. In your code I only see one ddl.

utlonghornjulie

ASKER

I have two CascadingDropDown controls on my page. The first one, the parent one, is not populating when the page first loads.

<tr>
                        <td style="width: 110px; height: 21px;" align="right">
                            <asp:Label ID="SRNameLabel" runat="server" Text="SR_Name"></asp:Label>
                        </td>
                        <td style="width: 300px; height: 21px;">
                            <asp:DropDownList ID="SRNameDDL" runat="server" Width="150px">
                            </asp:DropDownList>
                            <ajaxToolkit:CascadingDropDown Category="SalesRep" ID="CascadingDropDown1" runat="server"
                                ServiceMethod="GetSalesRep" TargetControlID="SRNameDDL" UseContextKey="True">
                            </ajaxToolkit:CascadingDropDown>
                        </td>
                    </tr>
                    <tr>
                        <td style="width: 110px; height: 29px;" align="right">
                            <asp:Label ID="SRSubNameLabel" runat="server" Text="SR_Sub_Name"></asp:Label>
                        </td>
                        <td style="width: 300px; height: 29px;">
                            <aspSample:KeySortDropDownList ID="SRSubNameDDL" runat="server" Width="150px">
                            </aspSample:KeySortDropDownList>
                            <ajaxToolkit:CascadingDropDown ID="CascadingDropDown2" runat="server" TargetControlID="SRSubNameDDL"
                                Category="SubSalesRep" ParentControlID="SRNameDDL" ServiceMethod="GetSubSalesRep"
                                UseContextKey="True">
                            </ajaxToolkit:CascadingDropDown>
                        </td>
                    </tr>
 
 
 
 
 
 
 
 
 
 
 
    <System.Web.Services.WebMethodAttribute()> <System.Web.Script.Services.ScriptMethodAttribute()> Public Shared Function GetSalesRep(ByVal knownCategoryValues As String, ByVal category As String) As CascadingDropDownNameValue()
        Dim strConnection As String = ConfigurationManager.ConnectionStrings("StikeData").ConnectionString
        Dim sqlConn As SqlConnection = New SqlConnection(strConnection)
        Dim strSalesRepQuery As String = "SELECT * FROM tbl_SalesRep WHERE [End] IS NULL OR [End] > GetDate(); "
        Dim cmdFetchSalesRep As SqlCommand = New SqlCommand(strSalesRepQuery, sqlConn)
 
        Dim dtrSR As SqlDataReader
        Dim mySR As New List(Of CascadingDropDownNameValue)
 
        sqlConn.Open()
        dtrSR = cmdFetchSalesRep.ExecuteReader
 
        While dtrSR.Read()
            Dim strSRName As String = dtrSR("SR_Name").ToString
            Dim strSRID As Integer = CInt(dtrSR("SRID").ToString)
 
            mySR.Add(New CascadingDropDownNameValue(strSRName, strSRID))
        End While
 
        Return mySR.ToArray
    End Function
 
    <System.Web.Services.WebMethodAttribute()> <System.Web.Script.Services.ScriptMethodAttribute()> Public Shared Function GetSubSalesRep(ByVal knownCategoryValues As String, ByVal category As String) As CascadingDropDownNameValue()
        Dim strConnection As String = ConfigurationManager.ConnectionStrings("StikeData").ConnectionString
        Dim sqlConn As SqlConnection = New SqlConnection(strConnection)
        Dim strSubSRQuery As String = "SELECT * FROM tbl_salesrep_sub WHERE SRID = @pSRID"
        Dim cmdFetchSubSR As SqlCommand = New SqlCommand(strSubSRQuery, sqlConn)
 
        Dim dtrSubSR As SqlDataReader
        Dim kvSubSR As StringDictionary = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)
 
        Dim intSRID As Integer
 
        If Not kvSubSR.ContainsKey("SalesRep") Or Not Int32.TryParse(kvSubSR("SalesRep"), intSRID) Then
            Return Nothing
        End If
 
        cmdFetchSubSR.Parameters.AddWithValue("@pSRID", intSRID)
        Dim myTeams As New List(Of CascadingDropDownNameValue)
 
        sqlConn.Open()
        dtrSubSR = cmdFetchSubSR.ExecuteReader
 
        While dtrSubSR.Read()
            Dim strSubSR As String = dtrSubSR("SRSub_Name").ToString
            Dim strSubSRID As Integer = CInt(dtrSubSR("SRID_SubID").ToString)
 
            myTeams.Add(New CascadingDropDownNameValue(strSubSR, strSubSRID))
        End While
 
        Return myTeams.ToArray
    End Function

Open in new window

shahprabal

Ok... try this :

            Dim strConnection As String = ConfigurationManager.ConnectionStrings("StikeData").ConnectionString
            Dim sqlConn As SqlConnection = New SqlConnection(strConnection)
            Dim strSalesRepQuery As String = "SELECT * FROM tbl_SalesRep WHERE [End] IS NULL OR [End] > GetDate(); "
            Dim cmdFetchSalesRep As SqlCommand = New SqlCommand(strSalesRepQuery, sqlConn)
            Dim ds As New DataSet
            sqlConn.Open()
            Dim adapter As SqlDataAdapter = New SqlDataAdapter(strSalesRepQuery, sqlConn)
            adapter.Fill(ds, "tbl_SalesRep")

Open in new window

shahprabal

Remove this line :
sqlConn.Open()
from the above code.

utlonghornjulie

ASKER

Ok I know how to populate a normal asp:dropdownlist. I want to know why the function called "GetSalesRep" is not populating the CascadingDropDown.

shahprabal

You seem to be missing service path :

<ajaxToolkit:CascadingDropDown
ID="CascadingDropDown1"
runat="server"
TargetControlID="ddlMake"
Category="Make"
PromptText="Select a manufacturer"
ServicePath="CarsService.asmx"
ServiceMethod="GetCarMakes" />

Open in new window

utlonghornjulie

ASKER

Ok but if add the ServicePath property, how do I add the web service to my web site and have it point to that and then call the ServiceMethod?

shahprabal

Right click your website > Add New Item > Select WebService > Give it a name and you are done.
Copy the GetSalesRep and the other method in .vb file which will be in the app code folder.
I didn't realise that you have the methods in the codebehind... assumed that you copy/pasted from the webservice.
Anyways... set the ServicePath to the webservice name you picked : eg: GetDropDownListData.asmx and you should be all set.

Member_2_4913559

Wow this is a very interresting thread. I lost interest in the ajaxcontroltoolkit awhile back after I thought I had wound it through its paces. I never tried to do this before. I think I may setup a sample for myself just to see it spin.

Anyway I see both of your points here. I think you do need a servicepath, but your question is valid on how to call the path. I'm sure there is another way, but in the example I viewed it looked like they were using an updatepanel trigger to cause the servicemethod to be called.

http://programming.top54u.com/post/AJAX-Cascading-Dropdown-Example-using-XML-Data.aspx

I would really like to do this manually myself (don't like updatepanels). So I wonder if you cant attach the webmethod to the change event of the dropdown itself? If I get a working sample going before I lose interest I'll post some code.

shahprabal

You can have a webmethod... but the control needs to know where it can find the method i.e. which webservice it belongs to.

utlonghornjulie

ASKER

Ok I have added a web service to my website called PopulateDropDown.asmx. Then I copied and pasted the functions to the PopulateDropDown.vb class that was created by the asmx page. But the cascadingdropdown is still not populating. The first section of code is my PopulateDropDown.vb class. The second is my aspx page.

Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Collections
Imports System.Collections.Generic
Imports System.Collections.Specialized
Imports AjaxControlToolkit
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
<System.Web.Script.Services.ScriptService()> _
<WebService(Namespace:="http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Public Class PopulateDropDown
    Inherits System.Web.Services.WebService
 
    <WebMethod()> _
    Public Function GetSalesRep(ByVal knownCategoryValues As String, ByVal category As String) As CascadingDropDownNameValue()
        Dim strConnection As String = ConfigurationManager.ConnectionStrings("StikeData").ConnectionString
        Dim sqlConn As SqlConnection = New SqlConnection(strConnection)
        Dim strSalesRepQuery As String = "SELECT * FROM tbl_SalesRep WHERE [End] IS NULL OR [End] > GetDate(); "
        Dim cmdFetchSalesRep As SqlCommand = New SqlCommand(strSalesRepQuery, sqlConn)
 
        Dim dtrSR As SqlDataReader
        Dim mySR As New List(Of CascadingDropDownNameValue)
 
        sqlConn.Open()
        dtrSR = cmdFetchSalesRep.ExecuteReader
 
        While dtrSR.Read()
            Dim strSRName As String = dtrSR("SR_Name").ToString
            Dim strSRID As Integer = CInt(dtrSR("SRID").ToString)
 
            mySR.Add(New CascadingDropDownNameValue(strSRName, strSRID))
        End While
 
        sqlConn.Close()
 
        Return mySR.ToArray
    End Function
 
    <WebMethod()> _
    Public Function GetSubSalesRep(ByVal knownCategoryValues As String, ByVal category As String) As CascadingDropDownNameValue()
        Dim strConnection As String = ConfigurationManager.ConnectionStrings("StikeData").ConnectionString
        Dim sqlConn As SqlConnection = New SqlConnection(strConnection)
        Dim strSubSRQuery As String = "SELECT * FROM tbl_salesrep_sub WHERE SRID = @pSRID"
        Dim cmdFetchSubSR As SqlCommand = New SqlCommand(strSubSRQuery, sqlConn)
 
        Dim dtrSubSR As SqlDataReader
        Dim kvSubSR As StringDictionary = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)
 
        Dim intSRID As Integer
 
        If Not kvSubSR.ContainsKey("SalesRep") Or Not Int32.TryParse(kvSubSR("SalesRep"), intSRID) Then
            Return Nothing
        End If
 
        cmdFetchSubSR.Parameters.AddWithValue("@pSRID", intSRID)
        Dim mySubSR As New List(Of CascadingDropDownNameValue)
 
        sqlConn.Open()
        dtrSubSR = cmdFetchSubSR.ExecuteReader
 
        While dtrSubSR.Read()
            Dim strSubSR As String = dtrSubSR("SRSub_Name").ToString
            Dim strSubSRID As Integer = CInt(dtrSubSR("SRID_SubID").ToString)
 
            mySubSR.Add(New CascadingDropDownNameValue(strSubSR, strSubSRID))
        End While
 
        Return mySubSR.ToArray
    End Function
End Class
 
 
 
 
 
 
 
 
 
 
<tr>
                        <td style="width: 110px; height: 21px;" align="right">
                            <asp:Label ID="SRNameLabel" runat="server" Text="SR_Name"></asp:Label>
                        </td>
                        <td style="width: 300px; height: 21px;">
                            <asp:DropDownList ID="SRNameDDL" runat="server" Width="150px">
                            </asp:DropDownList>
                            <ajaxToolkit:CascadingDropDown ServicePath="PopulateDropDown.asmx" Category="SalesRep" ID="CascadingDropDown1" runat="server"
                                ServiceMethod="GetSalesRep" TargetControlID="SRNameDDL" UseContextKey="True">
                            </ajaxToolkit:CascadingDropDown>
                        </td>
                    </tr>
                    <tr>
                        <td style="width: 110px; height: 29px;" align="right">
                            <asp:Label ID="SRSubNameLabel" runat="server" Text="SR_Sub_Name"></asp:Label>
                        </td>
                        <td style="width: 300px; height: 29px;">
                            <aspSample:KeySortDropDownList ID="SRSubNameDDL" runat="server" Width="150px">
                            </aspSample:KeySortDropDownList>
                            <ajaxToolkit:CascadingDropDown ID="CascadingDropDown2" runat="server" TargetControlID="SRSubNameDDL"
                                Category="SubSalesRep" ServicePath="PopulateDropDown.asmx" ParentControlID="SRNameDDL" ServiceMethod="GetSubSalesRep"
                                UseContextKey="True">
                            </ajaxToolkit:CascadingDropDown>
                        </td>
                    </tr>

Open in new window

Member_2_4913559

Again, interesting exercise.

I setup a simple version of this just to see how it pulled together, and was surprised at the seemlessness. I'll post that below.

My observations of your code and potential issues with it not running are:
1) You haven't setup your webmethod correctly and it is returning no value, or never even being hit.
2) The custom control you are using "<aspSample:KeySortDropDownList ID="SRSubNameDDL" runat="server" Width="150px">" may not be compatible.

Suggestion:
Simplify your code, until it works. Then add functional back in until you find a problem. I would start with replacing that custom control with a regular drop down. 2nd be sure you are getting data.

I didn't go to any trouble with filtering data, or setting one dropdown value based on another. I simply focused on the fact that you were indicating that no data was being returned.

As far as whether you need this control or dont need it, or what it's used for well...

who cares, you wanna use it, you make the call...you have your reasons and I'm good with that.

What it's for? Populate one list based on the values of another without (and this is the key point) posting back the entire page...doesn't matter what datasource you use.

****WEB SERVICE/CLASS****
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[ScriptService]
public class WebService : System.Web.Services.WebService 
{
 
    public WebService () {}
 
    [WebMethod]
    public CascadingDropDownNameValue[] populateDDL(string knownCategoryValues, string category)
    {
        List<CascadingDropDownNameValue> vList = new List<CascadingDropDownNameValue>();
        vList.Add(new CascadingDropDownNameValue("Tom", "0"));
        vList.Add(new CascadingDropDownNameValue("Jim", "1"));
        vList.Add(new CascadingDropDownNameValue("Helen", "2"));
 
        return vList.ToArray();
    }
 
 
    [WebMethod]
    public CascadingDropDownNameValue[] populateSecondDDL(string knownCategoryValues, string category)
    {
        List<CascadingDropDownNameValue> vList = new List<CascadingDropDownNameValue>();
        vList.Add(new CascadingDropDownNameValue("Josh", "0"));
        vList.Add(new CascadingDropDownNameValue("Karen", "1"));
        vList.Add(new CascadingDropDownNameValue("Jill", "2"));
 
        return vList.ToArray();
    }
}
 
 
 
****ASPX PAGE****
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Cascading DDL</title>
</head>
<body>
    <form id="form1" runat="server">
 
	<asp:ScriptManager ID="ScriptManager1" runat="server">
		<Services>
			<asp:ServiceReference Path="~/svc/WebService.asmx" />
		</Services>
	</asp:ScriptManager>
 
 
    <asp:DropDownList ID="primaryDDL" runat="server" Width="150px"></asp:DropDownList>
    <ajaxToolkit:CascadingDropDown 
		ServicePath="~/svc/WebService.asmx"
		ServiceMethod="populateDDL" 
		Category="SalesRep" 
		ID="cdd1" 
		runat="server"
        TargetControlID="primaryDDL" 
        UseContextKey="True"
        PromptText="Select Rep">
    </ajaxToolkit:CascadingDropDown>
 
    <asp:DropDownList ID="secondaryDDL" runat="server" Width="150px"></asp:DropDownList>
    <ajaxToolkit:CascadingDropDown 
		ServicePath="~/svc/WebService.asmx"
		ServiceMethod="populateSecondDDL"
		Category="SubSalesRep"
		ID="cdd2" 
		runat="server" 
		TargetControlID="secondaryDDL"
		ParentControlID="primaryDDL" 
        UseContextKey="True"
        PromptText="Select Sub">
    </ajaxToolkit:CascadingDropDown>
 
    </form>
</body>
</html>

Open in new window

Member_2_4913559

NOTE: You don't need the scriptmanager service reference. I just had it in there when I was testing that I had my webservice setup correctly. It doesn't help or hurt...

shahprabal

Remove :
<System.Web.Script.Services.ScriptService()> _

Also set a breakpoint and see if the method gets called... follow through the method and see if the datareader has data, the list (eg mySR) has data, etc.

ASKER CERTIFIED SOLUTION

shahprabal

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Member_2_4913559

I guess I'd need to hear why this question is being deleted. Otherwise "I Object" <bangs gavel>!!!

My guess is frustration. Guess 2 would be that the custom control she was using is not gonna work in this application.

I did take the time to try to help with a thread that was struggling along. I think the user was pretty close to a solution. Just needed to feedback on what she had tested and the results she was getting (breakpoint in webmethod not being hit, custom control not working but normal dropdownlist fine...) and we could have focused in... don't give up utlonghornjulie, you can do it!

Member_2_4913559

I'm a little late I think....party's over :)