How to use json to retrieve data from sql server ASP.net

Question

I am trying to populate a list from database using webservice in asp.net 3.5 with C# and jquery mobile.

It works fine when I run the site but when I publish it to my server it doesn't populate the list and the ajax error function catches an error. Here is my code.

My webservice:

public class product
{
    //[product] ,[img1] ,[descr]
    public string name;
    public string img1;
    public string descr;

}

[WebService(Namespace = "http://tempuri.org/")]
[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 CarService : System.Web.Services.WebService {


[WebMethod]
    public List<product> GetAllProducts()
    {
        List<product> productt = new List<product> {};

        string query = "SELECT [product] ,[img1] ,[descr] FROM [ELQ].[dbo].[products]";
        SqlCommand cmd = new SqlCommand(query);
        DataSet ds = GetData(cmd);
        DataTable dt = ds.Tables[0];

        // Process each employee record in table...
        //foreach (DataRow dr in dt.Rows)
        foreach(DataRow item in ds.Tables[0].Rows)
        {
            //productt.Add((DataRow) row);
            product pro = new product();
            pro.name = item["product"].ToString();
            pro.img1 = item["img1"].ToString();
            pro.descr = item["descr"].ToString();
            productt.Add(pro);
        }

        return productt; 
    }
    public static string GetShipDetails()
    {

        string query = "SELECT [product] ,[img1] ,[descr] FROM [ELQ].[dbo].[products]";
        SqlCommand cmd = new SqlCommand(query);
        return GetData(cmd).GetXml();
    }
    private static DataSet GetData(SqlCommand cmd)
    {
        string connString = "Data Source=GHOST-PC\\STC;Initial Catalog=ELQ;Integrated Security=True";
        using (SqlConnection con = new SqlConnection(connString))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataSet ds = new DataSet())
                {
                    sda.Fill(ds);
                    return ds;
                }
            }
        }
    }


}

The script:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<%@ Register TagPrefix="asp" Namespace="AjaxControlToolkit" Assembly="AjaxControlToolkit" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html>
<head runat="server">
    <title>jQuery Mobile Market</title>
    <link rel="stylesheet" href="http://code.jquery.com/mobile/1.3.0/jquery.mobile-1.3.0.min.css" />
    <script src="http://code.jquery.com/jquery-1.8.2.min.js"></script>
    <script src="http://code.jquery.com/mobile/1.3.0/jquery.mobile-1.3.0.min.js"></script>
    <script type="text/javascript">

            $(document).on('pageinit', function () {
                Greating();

            });

            function Greating() {

                $.ajax({

                    type: "POST",
                    url: "CarService.asmx/GetAllProducts",
                    data: "{}",
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: function (response) {

                        var productt = response.d;
                        $.each(productt, function (index, product) {

                            $("#theList").append("<li><img src='pic/" + product.img1 + "'> <br /> " + product.name + " <br />" + product.descr + "</li>");
                        });

                        $("#theList").listview("refresh");
                    },

                    error: function (response) {
                        $("#theList").append("<li>error<li>");
                    }
                });
            }
        </script>

It seams like json response is wrong and generates error but it works fine when I execute the code from my laptop. I don't know what I am missing so please help me.

The error:

POST http://192.168.1.7:7777/CarService.asmx/GetAllProducts 500 (Internal Server Error) jquery-1.8.2.min.js:2
send jquery-1.8.2.min.js:2
p.extend.ajax jquery-1.8.2.min.js:2
Greating 192.168.1.7:18
(anonymous function) 192.168.1.7:12
p.event.dispatch jquery-1.8.2.min.js:2
g.handle.h jquery-1.8.2.min.js:2
p.event.trigger jquery-1.8.2.min.js:2
(anonymous function) jquery-1.8.2.min.js:2
p.extend.each jquery-1.8.2.min.js:2
p.fn.p.each jquery-1.8.2.min.js:2
p.fn.extend.trigger jquery-1.8.2.min.js:2
a.Widget._trigger jquery.mobile-1.3.0.min.js:2
a.widget._createWidget jquery.mobile-1.3.0.min.js:2
(anonymous function) jquery.mobile-1.3.0.min.js:2
a.(anonymous function).(anonymous function) jquery.mobile-1.3.0.min.js:2
(anonymous function) jquery.mobile-1.3.0.min.js:2
p.extend.each jquery-1.8.2.min.js:2
p.fn.p.each jquery-1.8.2.min.js:2
a.fn.(anonymous function) jquery.mobile-1.3.0.min.js:2
C jquery.mobile-1.3.0.min.js:2
a.mobile.loadPage jquery.mobile-1.3.0.min.js:2
a.mobile.changePage jquery.mobile-1.3.0.min.js:2
a.mobile._handleHashChange jquery.mobile-1.3.0.min.js:2
(anonymous function) jquery.mobile-1.3.0.min.js:2
p.event.dispatch jquery-1.8.2.min.js:2
g.handle.h jquery-1.8.2.min.js:2
p.event.trigger jquery-1.8.2.min.js:2
(anonymous function) jquery-1.8.2.min.js:2
p.extend.each jquery-1.8.2.min.js:2
p.fn.p.each jquery-1.8.2.min.js:2
p.fn.extend.trigger jquery-1.8.2.min.js:2
(anonymous function) jquery.mobile-1.3.0.min.js:2

success: function (response) { console.log(response); do this and see your browser console by pressing f12 — Ankush Jain
– Ankush Jain, Commented Mar 16, 2013 at 6:11
this is the error Failed to load resource: the server responded with a status of 500 (Internal Server Error) localhost:7777/CarService.asmx/GetAllProducts POST localhost:7777/CarService.asmx/GetAllProducts 500 (Internal Server Error) jquery-1.8.2.min.js:2 send jquery-1.8.2.min.js:2 p.extend.ajax jquery-1.8.2.min.js:2 Greating localhost:18 (anonymous function) localhost:12 p.event.dispatch jquery-1.8.2.min.js:2 g.handle.h jquery-1.8.2.min.js:2 p.event.trigger jquery-1.8.2.min.js:2 (anonymous function) jquery-1.8.2.min.js:2 p.extend.each jquery-1.8.2.min.js — Seif sammain
– Seif sammain, Commented Mar 16, 2013 at 7:34
didn't you say you get the error from the server? why do you have "localhost" in the error url? — roman m
– roman m, Commented Mar 16, 2013 at 7:38
now iam publishing in the same pc POST 192.168.1.7:7777/CarService.asmx/GetAllProducts 500 (Internal Server Error) jquery-1.8.2.min.js:2 send jquery-1.8.2.min.js:2 p.extend.ajax jquery-1.8.2.min.js:2 Greating 192.168.1.7:18 (anonymous function) 192.168.1.7:12 p.event.dispatch jquery-1.8.2.min.js:2 g.handle.h jquery-1.8.2.min.js:2 p.event.trigger jquery-1.8.2.min.js:2 (anonymous function) jquery-1.8.2.min.js:2 p.extend.each jquery-1.8.2.min.js:2 p.fn.p.each jquery-1.8.2.min.js:2 p.fn.extend.trigger jquery-1.8.2.min.js:2 a.Widget._trigger jquery.mobile- — Seif sammain
– Seif sammain, Commented Mar 16, 2013 at 7:39

Seif sammain · Accepted Answer · 2013-03-17 11:25:40Z

i fixed the error and actually it wasn't an error i had log in and authentication issues in my database so i decided to post the code for beginners like me so this is my code

first i built a service to deal with my database and i used a simple way to do that

this is my CarService class

using System;
using System.Collections.Generic;
//using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data.SqlClient;
//using System.Web.Script.Serialization;
using System.Web.Script.Services;
using System.IO;
//using Newtonsoft.Json;
using System.Data;
using System.Text;



/// <summary>
/// Summary description for CarService
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[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 CarService : System.Web.Services.WebService {

    public class product
    {
        public string name;
        public string img1;
        public string descr;

    }

    static List<product> productt = new List<product> { };

    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    [WebMethod]
    public List<product> GetAllProducts()
    {

        string query = "SELECT  [product] ,[img1] ,[descr] FROM [ELQ].[dbo].[products]";
        SqlCommand cmd = new SqlCommand(query);
        DataSet ds = GetData(cmd);
        DataTable dt = ds.Tables[0];
        foreach(DataRow item in ds.Tables[0].Rows)
        {
            product pro = new product();
            pro.name = item["product"].ToString();
            pro.img1 = item["img1"].ToString();
            pro.descr = item["descr"].ToString();
            productt.Add(pro);
        }

        return productt; 
    }

    private static DataSet GetData(SqlCommand cmd)
    {
        string connString = "Data Source=GHOST-PC\\STC;Initial Catalog=ELQ;Persist Security Info=True;User ID=sa;Password=seif@ghost";
        string str = "Data Source=GHOST-PC\\STC;Initial Catalog=ELQ;Persist Security Info=True;User ID=sa;Password=seif@ghost";
        using (SqlConnection con = new SqlConnection(str))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataSet ds = new DataSet())
                {
                    sda.Fill(ds);
                    return ds;
                }
            }
        }
    }


}

and then here is my bage

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<%@ Register TagPrefix="asp" Namespace="AjaxControlToolkit" Assembly="AjaxControlToolkit" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head runat="server">
    <title>jQuery Mobile Market</title>
    <link rel="stylesheet" href="http://code.jquery.com/mobile/1.3.0/jquery.mobile-1.3.0.min.css" />
    <script src="http://code.jquery.com/jquery-1.8.2.min.js"></script>
    <script src="http://code.jquery.com/mobile/1.3.0/jquery.mobile-1.3.0.min.js"></script>
    <script type="text/javascript" type="text/javascript">


        $(document).ready(function () {

            $("#theList").removeData();
            Greating();
        });

        function Greating() {

            $.ajax({

                type: "POST",
                url: "CarService.asmx/GetAllProducts",
                data: "{}",
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (response) {

                    var productt = response.d;
                    $.each(productt, function (index, product) {

                        $("#theList").append("<li><img src='pic/" + product.img1 + "'> <br /> " + product.name + " <br />" + product.descr + "</li>");
                    });

                    console.log(response);
                },

                error: function (response) {
                    $("#theList").append("<li>error<li>");

                    console.log(response);
                }
            });
        }
    </script>
</head>
<body>
    <div data-role="page" id="homePage">
        <div data-role="header">
            <div data-role="navbar">
                <ul>
                    <li><a href="#homePage" class="ui-btnactive">Home</a></li>
                    <li><a href="#aboutPage">7arag</a></li>
                    <li><a href="#market">Market</a></li>
                    <li><a href="#settings">Settings</a></li>
                </ul>
            </div>
        </div>
        <div data-role="content">
            <form action="echo.cfm" method="post">
            <div data-role="fieldcontain">
                <label for="name">
                    Name:</label>
                <input type="text" name="name" id="name" value="" />
            </div>
            <div data-role="fieldcontain">
                <label for="email">
                    Email:</label>
                <input type="text" name="email" id="email" value="" />
            </div>
            <div data-role="fieldcontain">
                <input type="submit" name="submit" value="Send" />
            </div>
            </form>
        </div>
        <div data-role="footer" data-position="fixed" dataid="footernav">
            <p>
                asd</p>
        </div>
    </div>
    <div data-role="page" id="market">
        <div data-role="navbar">
            <ul>
                <li><a href="#homePage" class="ui-btnactive">Home</a></li>
                <li><a href="#aboutPage">7arag</a></li>
                <li><a href="#market">Market</a></li>
                <li><a href="#settings">Settings</a></li>
            </ul>
        </div>
        <div data-role="content">
            <ul id="theList" data-role="listview" data-inset="true" data-filter="true">
            </ul>
        </div>
        <div data-role="footer" data-position="fixed" dataid="footernav">
        </div>
    </div>
    <div data-role="footer" data-position="fixed" dataid="footernav">
    </div>
    </div>
</body>
</html>

and finish .. enjoy ;)

PrishahCoder · Accepted Answer · 2014-10-30 17:16:21Z

You need to convert byte[] in to "Convert.ToBase64String" in webservice, then javascript understand image string in form of base64. Here I have done with below code in my webservice.

//MyJs Code:

function getMyImage(id_No){
    $.support.cors= true;
    $.ajax({`enter code here`
        url: "http://someipaddreess/phonegap.asmx/spKP_MyImage",
        contentType: 'application/json;charset=utf-8',
        type: "POST",
        data: JSON.stringify({CSID_NO:getCSIDNumberByFK(id_No)}),
        async:false,
        dataType: "json",
        success : function(msg)  {
            var myImageStr = msg.d;
            $("#testImg").attr('src','data:image/jpeg;base64,' + myImageStr);
        },
        error: function(xhr, errorThrown, textStatus){
            window.alert("error: Failed to call WebService for measuring image" +  " " + textStatus);
        }
    });
}
//MyJs Code Ends here:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using Newtonsoft.Json;

using System.Data;
using System.IO;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Drawing;

namespace phonegap_webservices
{
    /// <summary>
    /// Summary description for phonegap
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // 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 phonegap : System.Web.Services.WebService
    {                
        dalKPDataSQl tblKPDataSql = new dalKPDataSQl();
        dalBuilderPortal tblBuilderPortalSql = new dalBuilderPortal();

        [WebMethod]
        public string spKP_MyImage(long CSID_NO)
        {
            DataTable dt = new DataTable();                                       
            dt = tblKPDataSql.dtGetInfoByCSID("SP_ImageFunCall", CSID_NO);
            byte[] imageByteArray = (byte[])dt.Rows[0]["MyImage"];
            return Convert.ToBase64String(imageByteArray);
        }
    }
}

Collectives™ on Stack Overflow

How to use json to retrieve data from sql server ASP.net

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related