CODEWITHSUNDEEP
c#asp.netweb-services
Precious Okwu
Precious Okwu

Reputation: 450

Return sum of a column using ASP.NET WEB Services

I have been tring to return the sum of a column using this web service but it does not work .Please Help.Thank You. I am using Visual Studio 2010.It Just Keeps on Returning an error.System.InvalidOperationException: There was an error generating the XML document. ---> System.InvalidOperationException: Cannot serialize the DataTable. 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data;
using MySql.Data.MySqlClient;

namespace Transcript_System
{
    /// <summary>
    /// Summary description for check
    /// </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 check : System.Web.Services.WebService
    {

        [WebMethod]
    public DataTable connectoToMySql()
    {
        string connString = "SERVER=localhost" + ";" +
            "DATABASE=transcriptdb;" +
            "UID=root;" +
            "PASSWORD=;";

        MySqlConnection cnMySQL = new MySqlConnection(connString);

        MySqlCommand cmdMySQL = cnMySQL.CreateCommand();

        MySqlDataReader reader;

        cmdMySQL.CommandText = "SELECT SUM( Score ) FROM faculty_table WHERE Mat_No='PSC0908888'";

        cnMySQL.Open();

        reader = cmdMySQL.ExecuteReader();

        DataTable dt = new DataTable();
        dt.Load(reader);


        cnMySQL.Close();

        return dt;
    } 
            }
        }

Upvotes: 0

Views: 910

Answers (3)

Jonesopolis
Jonesopolis

Reputation: 25370

You don't need a table, you are simply getting a single value from the database, use Execute Scalar instead.

cnMySQL.Open();
string sum = mySqlcommand.ExecuteScalar();
cnMySQL.Close();
return sum;

and change your method to return a string instead

Upvotes: 1

Kuldeep
Kuldeep

Reputation: 454

You can send the data as a xml string from a dataset by DataSet.GetXml()

And than the user can deserialize it with DataSet.ReadXml()

And get the datatable from the dataset by DataSet.Tables

Upvotes: 1

Code Monkey
Code Monkey

Reputation: 643

Your web method is returning an object of type DataTable. Perhaps you want to change that to return just an int/string with the value of the count, and not the fetched Datatable instance!

Upvotes: 0

Related Questions