locked
Web Service not returning data to AJAX call RRS feed

  • Question

  • User-1093413310 posted

    I have been tasked with building an HTML5 website.  The website will need to connect to an MS SQL database and pass data from it to the Website for the user to review.  I have to build a Web Service which connects to the SQL database.  The Methods I had intended to pass data to the Web page to populate a List box and to use the <g class="gr_ gr_13 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="13" data-gr-id="13">listbox</g> selection populate a graph with the data.  I can see the data from the IIS server and the WSDL website.  I can see the data using SOAPUI if I point it at the Web address.   However, when I run the AJAX script within Javascript it is not returning the data.  I am puzzled.  

    I have attached the Webservice Code as well as the code I am using in the Javascript to call the web service. 

    using System;
    using System.Collections.Generic;
    using System.Web;
    using System.Data;
    using System.Web.Services;
    using System.Data.SqlClient;
    using System.Web.Script.Serialization;

    [WebService(Namespace = "http://tempuri.org/",
    Description = "Database Connection Service",
    Name = "UnivariateWebService")]
    //[System.ComponentModel.ToolboxItem(false)]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
    [System.Web.Script.Services.ScriptService]

    public class Service : System.Web.Services.WebService
    {
    public Service () {

    //Uncomment the following line if using designed components
    //InitializeComponent();
    }

    [WebMethod]
    public DataSet GetBatchData()
    {

    //Modify this connection string to use your SQL Server and log on information.
    var con = new SqlConnection("server=DEVELOPMENT-SER;uid=parcview;pwd=parcview;database=UnivariateRuns");

    //Open the Customers table to serve as the parent table.
    var daRunSet = new SqlDataAdapter("Select * From dbo.Products Order By product desc",
    con);

    //Create a client-side DataSet to hold the Customers and Orders tables.
    var ds = new DataSet();

    //Explicitly open the connection to allow explicit closing.
    con.Open();

    //Fill the DataSet with the Customers table and the Orders table.
    daRunSet.Fill(ds, "Batches");
    //string myJsonString = (new JavaScriptSerializer()).Serialize(ds); // might need to phase the dataset as a string
    //Explicitly close the connection - do not wait for garbage collection.
    con.Close();

    //Return the DataSet to the client.
    return ds;
    }

    [WebMethod]
    public DataSet GetSampleData(string product)
    {

    //Modify this connection string to use your SQL Server and log on information.
    var con = new SqlConnection("server=DEVELOPMENT-SER;uid=parcview;pwd=parcview;database=UnivariateRuns");

    //Open the Customers table to serve as the parent table.
    var daRunSet = new SqlDataAdapter("Select * From dbo.RunSetData where product like @product Order by product desc", con);
    daRunSet.SelectCommand.Parameters.AddWithValue("@product", "%" + product + "%");

    //Create a client-side DataSet to hold the Customers and Orders tables.
    var ds=new DataSet();

    //Explicitly open the connection to allow explicit closing.
    con.Open();

    //Fill the DataSet with the Customers table and the Orders table.
    daRunSet.Fill(ds, "RunSet");
    //daOrders.Fill(ds, "Orders");

    //Explicitly close the connection - do not wait for garbage collection.
    con.Close();

    //Return the DataSet to the client.
    return ds;
    }

    }

    function DataConnect() {
       		$.ajax({
       			type: 'GET',
       			url: 'http://192.168.1.103/Web%20Service/Service.asmx/GetBatchData',
       			dataType: 'json',
       			contentType: 'application/json;charset=utf-8',
       			success: function(responce) {
       				var names = response.d;
                     alert(names);
                },
       			failure: function(error) {
       				 alert(response.d); 
       			}
       		});
       };
    
    // How do I get the data passed to the Plot Array from the AJAX call?

    Tuesday, June 12, 2018 7:10 PM

Answers

  • User-474980206 posted

    yep, you are trying to build too large of a JSON object. you can up the max memory size of the asp.net process, but then you will probably not be able to load on the client. you need to implement paging and limit the size of your json response. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 13, 2018 2:59 PM

All replies

  • User36583972 posted


    Hi Cam Evenson,

    I can see the data from the IIS server and the WSDL website.  I can see the data using SOAPUI if I point it at the Web address.   However, when I run the AJAX script within Javascript it is not returning the data.  I am puzzled. 

    Do you uncomment the following line?

     // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
        [System.Web.Script.Services.ScriptService]
    

    Be default, you need use the the POST request. If you want to invoke it using GET, you need to add:

    [WebMethod]
    [ScriptMethod(UseHttpGet=true)]
    


    If you issue still exist, please use F12 Nework develope tool. I'm trying to suggest that rather than stopping at seeing that "it doesn't work", it should be easier to fix the issue if you look at what actually happens (F12 console for possible error messages, F12 network to inspect the http query and the response).

    You can show us the specific error messages.

    Besides, the following samples for your reference.

    Calling ASP.Net WebMethod using jQuery AJAX:
    https://www.aspsnippets.com/Articles/Calling-ASPNet-WebMethod-using-jQuery-AJAX.aspx

    Best Regards,

    Yong Lu

    Wednesday, June 13, 2018 5:59 AM
  • User-1093413310 posted

    This was really helpful.  Also, someone had suggested on CodeProject to use the Newtonsoft <g class="gr_ gr_59 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="59" data-gr-id="59">Json</g> package to convert the recordset data to <g class="gr_ gr_99 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="99" data-gr-id="99">Json</g>.  I have tried that and the data appears to reformat for one of the database calls but not the one with the Parameter. I just get a memory overflow error when the web service tries to convert the recordset to <g class="gr_ gr_380 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="380" data-gr-id="380">Json</g>.  I can only surmise this is because of the size of data being converted. 

    The new web service code is: 

    using System.Data;
    using System.Web.Services;
    using System.Data.SqlClient;
    using Newtonsoft.Json;
    using System.Web.Script.Services;
    
    [WebService(Namespace = "http://tempuri.org/",
        Description = "Database Connection Service",
        Name = "UnivariateWebService")]
    //[System.ComponentModel.ToolboxItem(false)]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
     [System.Web.Script.Services.ScriptService]
    
    public class Service : System.Web.Services.WebService
    {
        public Service () {
    
            //Uncomment the following line if using designed components 
            //InitializeComponent(); 
        }
    
        [WebMethod]
        [ScriptMethod(UseHttpGet = true)]
        public string GetBatchData()
        {
    
            //Modify this connection string to use your SQL Server and log on information.
            var con = new SqlConnection("server=DEVELOPMENT-SER;uid=******;pwd=******;database=UnivariateRuns");
    
            //Open the Customers table to serve as the parent table.
            var daRunSet = new SqlDataAdapter("Select * From dbo.Products Order By product desc",
                con);
    
            //Create a client-side DataSet to hold the Customers and Orders tables.
            var ds = new DataSet();
    
            //Explicitly open the connection to allow explicit closing.
            con.Open();
    
            //Fill the DataSet with the Customers table and the Orders table.
            daRunSet.Fill(ds, "Batches");
            DataTable table = new DataTable();
            DataColumn idColumn = new DataColumn("id", typeof(int));
            idColumn.AutoIncrement = true;
    
            DataColumn itemColumn = new DataColumn("item");
            table.Columns.Add(idColumn);
            table.Columns.Add(itemColumn);
            ds.Tables.Add(table);
    
            for (int i = 0; i < 2; i++)
            {
                DataRow newRow = table.NewRow();
                newRow["item"] = "item " + i;
                table.Rows.Add(newRow);
            }
    
            ds.AcceptChanges();
            string json = JsonConvert.SerializeObject(ds, Formatting.Indented);
            //Explicitly close the connection - do not wait for garbage collection.
            con.Close();
            
            //Return the DataSet to the client.
            return json;
        }
    
        [WebMethod]
        [ScriptMethod(UseHttpGet = true)]
        public string GetSampleData(string product)
        {
           
            //Modify this connection string to use your SQL Server and log on information.
            var con = new SqlConnection("server=DEVELOPMENT-SER;uid=*****;pwd=*****;database=UnivariateRuns");
    
            //Open the Customers table to serve as the parent table.
            var daRunSet = new SqlDataAdapter("Select * From dbo.RunSetData where product like @product Order by product desc", con);
            daRunSet.SelectCommand.Parameters.AddWithValue("@product", "%" + product + "%");
    
            //Create a client-side DataSet to hold the Customers and Orders tables.
            var ds=new DataSet();
    
            
    
            //Explicitly open the connection to allow explicit closing.
            con.Open();
    
            //Fill the DataSet with the Customers table and the Orders table.
            daRunSet.Fill(ds, "RunSet");
            DataTable table = new DataTable();
            DataColumn idColumn = new DataColumn("id", typeof(int));
            idColumn.AutoIncrement = true;
    
            DataColumn itemColumn = new DataColumn("item");
            table.Columns.Add(idColumn);
            table.Columns.Add(itemColumn);
            ds.Tables.Add(table);
    
            for (int i = 0; i < 2; i++)
            {
                DataRow newRow = table.NewRow();
                newRow["item"] = "item " + i;
                table.Rows.Add(newRow);
            }
    
            ds.AcceptChanges();
            string json = JsonConvert.SerializeObject(ds, Formatting.Indented);
            //daOrders.Fill(ds, "Orders");
    
            //Explicitly close the connection - do not wait for garbage collection.
            con.Close();
            
            //Return the DataSet to the client.
            return json;
        }
    
        
    
    }

    The error I am getting when I run the Web Service interactively from Visual Studio and then select the get sample data method is this:

    System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown.
       at System.String.GetStringForStringBuilder(String value, Int32 startIndex, Int32 length, Int32 capacity)
       at System.Text.StringBuilder.GetNewString(String currentString, Int32 requiredLength)
       at System.Text.StringBuilder.Append(String value)
       at System.IO.StringWriter.Write(String value)
       at Newtonsoft.Json.JsonTextWriter.WriteNull()
       at Newtonsoft.Json.JsonWriter.AutoCompleteClose(JsonContainerType type)
       at Newtonsoft.Json.JsonWriter.WriteEndObject()
       at Newtonsoft.Json.JsonWriter.WriteEnd(JsonContainerType type)
       at Newtonsoft.Json.JsonWriter.WriteEnd()
       at Newtonsoft.Json.JsonWriter.AutoCompleteAll()
       at Newtonsoft.Json.JsonTextWriter.Close()
       at Newtonsoft.Json.JsonWriter.Dispose(Boolean disposing)
       at Newtonsoft.Json.JsonWriter.System.IDisposable.Dispose()
       at Newtonsoft.Json.JsonConvert.SerializeObjectInternal(Object value, Type type, JsonSerializer jsonSerializer)
       at Newtonsoft.Json.JsonConvert.SerializeObject(Object value, Type type, Formatting formatting, JsonSerializerSettings settings)
       at Newtonsoft.Json.JsonConvert.SerializeObject(Object value, Formatting formatting)
       at Service.GetSampleData(String product) in c:\inetpub\wwwroot\Demo\Web Service\App_Code\Service.cs:line 105


    Wednesday, June 13, 2018 1:46 PM
  • User-474980206 posted

    yep, you are trying to build too large of a JSON object. you can up the max memory size of the asp.net process, but then you will probably not be able to load on the client. you need to implement paging and limit the size of your json response. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 13, 2018 2:59 PM
  • User-1093413310 posted

    Yes, this was exactly the issue.  I scaled back my query to only return the top 1000 records and no more memory error.  Now I am on to getting the connected data into the HTML5 web page.  smile

    Wednesday, June 13, 2018 3:21 PM