Cascading DropDownList with Stored Procedure

Question

I have this aspx code and I'm so confused for the asmx.cs code.

ASPX:

  <div class="col-md-6">

 <asp:DropDownList ID="ddlMake" runat="server" CssClass="form-control"></asp:DropDownList>
<ajaxToolkit:CascadingDropDown ID="cdlMake" TargetControlID="ddlMake" PromptText="Select Make" PromptValue="" ServicePath="CascadingDropdown1.cs.asmx" ServiceMethod="GetMakes" runat="server" Category="MakeId" LoadingText="Loading Make..." />
    </div>
 <div class="col-md-6">
<asp:DropDownList ID="ddlModel" runat="server"  CssClass="form-control"></asp:DropDownList>
<ajaxToolkit:CascadingDropDown ID="cdlModel" TargetControlID="ddlModel" PromptText="Select Model" PromptValue="" ServicePath="CascadingDropdown1.cs.asmx" ServiceMethod="GetModels" runat="server" Category="ModelId" ParentControlID="ddlMake" LoadingText="Loading Model..." />
</div>

and I have a Stored Procedure called Search_MAKE and Search_MODEL and the model SP needs parameter.

Can someone help me construct the asmx.cs code.

UPDATE:

[ScriptService]
public class CascadingDropdown1 : System.Web.Services.WebService
{
    [WebMethod]
    public CascadingDropDownNameValue[] GetMakes(string knownCategoryValues)
    {
        string query = "exec spIBCInventorySearch_MAKE @IDCustomer = 253433";
        List<CascadingDropDownNameValue> Makes = GetData(query);
        return Makes.ToArray();
    }

[WebMethod]
public CascadingDropDownNameValue[] GetModels(string knownCategoryValues)
{
    string make = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)["MakeId"];
    string query = string.Format("exec spIBCInventorySearch_MODEL @VehicleMake = {0}, @IDCustomer = 253433 ", make);
    List<CascadingDropDownNameValue> Models = GetData(query);
    return Models.ToArray();
}

private List<CascadingDropDownNameValue> GetData(string query)
{
    string conString = ConfigurationManager.ConnectionStrings["UID=pal;Password=123;DATABASE=ATDBSQL;"].ConnectionString;
    SqlCommand cmd = new SqlCommand(query);
    List<CascadingDropDownNameValue> values = new List<CascadingDropDownNameValue>();
    using (SqlConnection con = new SqlConnection(conString))
    {
        con.Open();
        cmd.Connection = con;
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                values.Add(new CascadingDropDownNameValue
                {
                    name = reader[0].ToString(),
                    value = reader[1].ToString()
                });
            }
            reader.Close();
            con.Close();
            return values;
        }
    }
}

}

i just finished making the asmx code but it will break in the conString part. can someone help me debug this problem?

Answer 1

You are trying to get connection string from config file in a weird manner. Generally its a key in connectionStrings section. For example:

<connectionStrings>
<add name="YourConnectionString" connectionString="Server=WIN-9A0EHHSEC8L\SQLEXPRESS2014;Database=SenseAPIClient;Integrated Security = true;" providerName="System.Data.SqlClient" />

To retrieve database connection string I need to use "YourConnectionString". Like following:

var conString = ConfigurationManager.ConnectionStrings["YourConnectionString"].ConnectionString;

Answer 2

[ScriptService]
public class CascadingDropdown1 : System.Web.Services.WebService
{
    [WebMethod]
    public CascadingDropDownNameValue[] GetMakes(string knownCategoryValues)
    {
        string query = "exec spIBCInventorySearch_MAKE @IDCustomer = 253433";
        List<CascadingDropDownNameValue> Makes = GetData(query);
        return Makes.ToArray();
    }

[WebMethod]
public CascadingDropDownNameValue[] GetModels(string knownCategoryValues)
{
    string make = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)["MakeId"];
    string query = string.Format("exec spIBCInventorySearch_MODEL @VehicleMake = {0}, @IDCustomer = 253433 ", make);
    List<CascadingDropDownNameValue> Models = GetData(query);
    return Models.ToArray();
}

private List<CascadingDropDownNameValue> GetData(string query)
{
    string conString = ConfigurationManager.ConnectionStrings["UID=pal;Password=123;DATABASE=ATDBSQL;"].ConnectionString;
    SqlCommand cmd = new SqlCommand(query);
    List<CascadingDropDownNameValue> values = new List<CascadingDropDownNameValue>();
    using (SqlConnection con = new SqlConnection(conString))
    {
        con.Open();
        cmd.Connection = con;
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                values.Add(new CascadingDropDownNameValue
                {
                    name = reader[0].ToString(),
                    value = reader[0].ToString()
                });
            }
            reader.Close();
            con.Close();
            return values;
        }
    }
}

i just change the index in thevalue=reader[0].ToString() from 1 to 0 :)

because i have only 1 column in the table.