CODEWITHSUNDEEP
javascriptc#htmlsql-server
Alexis Villar
Alexis Villar

Reputation: 370

How to access values returned by a Stored Procedure in Code Behind

I am beginner in Stored Procedure. I want to get the Sum of 2 Columns and display it in different label in HTML. I want to know what is the main source of the problem of my code.

The C# debugger says 'Make sure that the column exist in the table'.

Stored Procedure:

CREATE PROCEDURE [dbo].[spTotalOverTime]
    @tsdate DATE = '2018/1/1'
AS
BEGIN
    SELECT SUM(Overtime), SUM(PlanOt)
    FROM tblTimesheet           
    WHERE YEAR(tsDate) =  YEAR(@tsDate)  AND MONTH(tsDate) = MONTH(@tsdate)
END

HTML:

<div class="row cells12">
    <div class="cell colspan4 offset1">
        <h6 class="mif-hour-glass"> Total Planned Overtime:</h6>
        <div>
            <label id="lblPlan"></label>
        </div>
        <br />
    </div>
    <div class="cell colspan4 offset1">
        <h6 class="mif-alarm"> Total Actual Overtime:</h6>
        <div>
            <label id="lblActual"></label>
        </div>
    </div>
</div>

C#

[WebMethod]
public string LoadOT(DateTime date)
{
    List<OTTotal> mylist = new List<OTTotal>();
    using (SqlConnection connection = new SqlConnection(connectionString()))
    {
        connection.Open();
        SqlCommand cmd = new SqlCommand("spTotalOverTime", connection);
        cmd.Parameters.Add("@tsdate", SqlDbType.Date).Value = date;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandTimeout = 0;

        SqlDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            mylist.Add(new OTTotal
            {
                 OTAct = Convert.ToDouble(dr["Overtime"].ToString()),
                 OTPlan = Convert.ToDouble(dr["PlanOt"].ToString())
            });
        }
        dr.Close();
        connection.Close();
    }
    JavaScriptSerializer jss = new JavaScriptSerializer();
    string jsn = jss.Serialize(mylist);
    return jsn;
}

JavaScript

function getOTVal(year, month) {
    var asd = "{'date':'" + year + "/" + month + "/1" + "'}";
    $.ajax({
        type: 'POST',
        url: '../WebService/wsSummary.asmx/LoadOT',
        dataType: 'json',
        data: asd,
        contentType: 'application/json; charset=utf-8',
        success: function (response) {
            var cells = eval("(" + response.d + ")");
            for (var i = 0; i < cells.length; i++) {
                $('#lblActual').text(cells[i].OTAct);
                $('#lblPlan').text(cells[i].OTPlan);
            }
        },
        error: function (error) {
            alert(JSON.stringify(error))
            console.log(error);
        }
    });
}

Upvotes: 2

Views: 360

Answers (1)

Alexandru Popa
Alexandru Popa

Reputation: 166

The issue is your stored procedure does not return columns named Overtime and PlanOt. When you apply functions over a column the resulting column is unnamed, so you should give it an alias, like this:

CREATE PROCEDURE [dbo].[spTotalOverTime]
    @tsdate DATE = '2018/1/1'
AS
BEGIN
    SELECT SUM(Overtime) AS Overtime, SUM(PlanOt) AS PlanOt
    FROM tblTimesheet         
    WHERE YEAR(tsDate) = YEAR(@tsDate) AND MONTH(tsDate) = MONTH(@tsdate)
END

Upvotes: 2

Related Questions