I want to get SQL Server records in to asp.net using ajax jquery but i didn't get

Question

If I select the year 2011, the value of aData[0]=2011, it appears, but the java script function with the alert does not get called.

I get no results.

HTML:

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
    <script src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.11.3.min.js"></script>
    <script src="Scripts/jquery.min.js"></script>
    <script src="Scripts/jquery.jqplot.min.js"></script>
    <script src="Scripts/jqplot.pieRenderer.min.js"></script>
    <script type="text/javascript">
        $(function () {
                $("#ddl1").change(function () {
                    var selectedText = $(this).find("option:selected").text();
                    var selectedValue = $(this).val();
                    var aData = [];
                    aData[0] = selectedText;
                    var jsonData = JSON.stringify({ aData: aData});
                    $.ajax({
                        type: 'POST',
                        url: 'Default.aspx/getCityPopulation',
                        data:jsonData,
                        async: true,
                        cache:false,
                        contentType: 'application/json; charset=utf-8',
                        dataType: 'json',
                        success: OnSuccess,
                        error: OnErrorCall
                    });
                    function OnSuccess(response) {
                          var aData = response.d;
                    var arr = []; 
                    $.map(aData, function (item, index) {
                        var i = [item.cityname, item.population];
                       arr.push(i);
                   });
                    var plot1 = jQuery.jqplot('chartdiv', [arr],
                        {
                            seriesDefaults: {
                                renderer: jQuery.jqplot.PieRenderer,
                                rendererOptions: {
                                    showDataLabels: true
                                }
                            },
                            legend: { show: true, location: 'e' }
                        }
                    )
                    }
                  function OnErrorCall_(response) {
                    alert("Whoops something went wrong!");
                }
                e.preventDefault();
            });
    </script>
</head>
<body>
    <form id="form1" runat="server">
        <div id="chartdiv"></div>
             <select id="ddl1" runat="server" name="ddl1">
                <option value="" selected="selected">--Select--</option>
                <option value="0">2010</option>
                <option value="1">2011</option>
                <option value="2">2012</option>
                <option value="3">2013</option>
                <option value="4">2014</option>
            </select>
        </form>
</body>
</html>

In code behind of the .aspx page, I have code like this:

public class cityPopulation
{
    public string cityname { get; set; }
    public int population { get; set; }
}

[WebMethod]
public List<cityPopulation> getCityPopulation(List<string> aData)
{
    List<cityPopulation> p = new List<cityPopulation>();

    using (SqlConnection cn = new SqlConnection("server=.....;user id=sa;password=...;database=Example"))
    {
        string myQuery = "SELECT cityname, population FROM  tblcitypopulation WHERE  year = @year";

        SqlCommand cmd = new SqlCommand(myQuery, cn);
        //cmd.CommandText = myQuery;
        cmd.CommandType = CommandType.Text;

        SqlParameter parameter = new SqlParameter();
        parameter.ParameterName = "@year";
        parameter.Value = aData[0];
        //cmd.Parameters.AddWithValue("@year", aData[0]);
        // cmd.Connection = cn;

        cn.Open();

        SqlDataReader dr = cmd.ExecuteReader();

        while (dr.Read())
        {
            cityPopulation cpData = new cityPopulation();
            cpData.cityname = dr["cityname"].ToString();
            cpData.population = Convert.ToInt32(dr["population"].ToString());
            //cpData.year = Convert.ToInt32(dr["year"].ToString());

            p.Add(cpData);
        }

        return p;
    }
}

The javascript function with the alert is OnErrorCall_. Your error callback is OnErrorCall. They're different. — Nick.Mc
– Nick.Mc, Commented Jul 12, 2016 at 9:39

marc_s · Accepted Answer · 2016-07-12 09:05:45Z

You're creating the SqlParameter - but never adding it to the SqlCommand .... also: use the using() construct for SqlCommand and SqlDataReader, too - not just for SqlConnection!

Try this code:

[WebMethod]
public List<cityPopulation> getCityPopulation(List<string> aData)
{
    List<cityPopulation> p = new List<cityPopulation>();

    string myQuery = "SELECT cityname, population FROM  tblcitypopulation WHERE  year = @year";

    using (SqlConnection cn = new SqlConnection("server=.....;user id=sa;password=...;database=Example"))
    using (SqlCommand cmd = new SqlCommand(myQuery, cn))
    {
        cmd.Parameters.Add("@year", SqlDbType.Int).Value = aData[0];

        cn.Open();

        using (SqlDataReader dr = cmd.ExecuteReader())
        {
            while (dr.Read())
            {
                 cityPopulation cpData = new cityPopulation();
                 cpData.cityname = dr["cityname"].ToString();
                 cpData.population = Convert.ToInt32(dr["population"].ToString());

                 p.Add(cpData);
            }
        } 

        return p;
    }
}

Collectives™ on Stack Overflow

I want to get SQL Server records in to asp.net using ajax jquery but i didn't get

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related