[RESOLVED]What am i doing wrong with the following code in trying to get multiple values from a record in an SQL Database

AJAX, Jquery, Post, Web Forms, Webservice, , , ,

Here is my JavaScript /JQuery code:  I need help in several areas, but especially in the data: "{}", I know it isn’t right.

function contentPageLoad() {
                getData();
            }



function getData() { 
     $.ajax({ type: "POST", 
     url: "index.aspx/getData", 
     data: "{}", 
     dataType: "json", 
     contentType: "application/json; charset=utf-8", 
     success: OnSuccess, 
     error: OnError }); 
} 

function OnSuccess(data) { 
     alert('success'); 
     alert(data.d[i].DataID); 
     alert(data.d[i].DataDescription); 
} 

function OnError(data) { 
     alert('fail'); 
}

Codebehind:

[WebMethod()]
        public static string getData()
        {
            string returnValue = string.Empty;

            try
            {
                string consString = ConfigurationManager.ConnectionStrings["strconnection"].ConnectionString;
                //Dim consString As String = System.Web.HttpContext.Current.Session("strConnection")
                SqlConnection conn = new SqlConnection(consString);
                SqlCommand cmd = new SqlCommand("spx_getData", conn);

                cmd.CommandType = CommandType.StoredProcedure;

                conn.Open();

                returnValue = cmd.ExecuteScalar().ToString();

                conn.Close();
            }
            catch
            {
                returnValue = "error";
            }

            return returnValue;
        }

Stored Procedure

ALTER PROCEDURE [dbo].[spx_getData]

AS

BEGIN

      SET NOCOUNT ON;
	SELECT * FROM SampleDB

END

I am not getting any data back.

Just leave out the data param if your method has no parameters.  Does the method get called?  Are you looking at the correct url?

http://forums.asp.net/t/1982579.aspx?Using+the+browser+s+dev+tools+to+diagnose+ajax+problems+and+other+things+

Thanks for the quick reply. The method does get called (used debug to check). . I don’t get an error, I just don’t get any data and there is data in the database.

How do I get access to the returned data from the database if I leave the "data" parameter out?

Thanks again for the prompt reply!

What does "spx_getData" return?  Unless it returns json text itself your code isn’t going to work as it’ll just return one cell of data as a string, so to see that string you’d do something like

function OnSuccess(data) { 
     alert(data.d); 
}

If your data coming back from the db is in the form of rows and columns, you’ll need to create c# object that has the properties you want to send back (a property for each column), then add a collection of those objects to a List or array, one object for
each row, then return that list of objects from your function, and it will serialise into json that you can then read from your js code.

That is probably my problem I am trying to get values out of the columns in the record. What would be the syntax for doing that?

Use a SqlDataReader

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader(v=vs.90).aspx

http://www.dotnetperls.com/sqldatareader

What is the syntax for getting the values back from the SqlDataReader  into the JavaScript function?

Look at the third post on this thread

http://forums.asp.net/t/1934215.aspx?Using+jQuery+ajax+to+call+asmx+webservice+methods

I’m creating the collection manually, but you will make one by looping through each row in the sqldatareader and adding to it the object whose properties you’ve populated from the fields of each row.

Thanks.

That worked!

Leave a Reply