columns not accessible from store procedure in asp.net

Question

1.00/5 (1 vote)

See more:

, +

i am using a store procedure to access field in asp.net View, i am putting db columns in HTML table, it's workign except for one field, 'NetSalary'. It can't be accessed, it says that model doesn't contain it.

Store Procedure:

SQL

ALTER PROCEDURE [dbo].[GetMonthlyReport] @emplID INT = NULL,
	@month VARCHAR(50) = NULL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	IF (@emplID IS NOT NULL AND @month IS NOT NULL) --If Block begins
	BEGIN
		SELECT *
		FROM MonthlyRecord
		WHERE Month = @month AND EmplID = @emplID

		---------------------------------------------------------------
		Declare @StartDate Date,  @EndDate Date, @mydate date, @TotalDays int, @BasicSalary int, @TotalHours int
		,@BSalaryHour varchar(50)
		Set @mydate = GETUTCDATE()
		Set @StartDate = (SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101))
		Set @EndDate = (SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101))
		Set @TotalDays =((DATEDIFF(dd, @StartDate, @EndDate) + 1)
		  -(DATEDIFF(wk, @StartDate, @EndDate) * 1)
		  -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)) 
		Set @TotalHours  = (@TotalDays * 8)
		Set @BasicSalary = (Select BasicSalary from HrEmployee where EmplID=@emplID)
		Set @BSalaryHour = @BasicSalary / @TotalHours
		Select @BasicSalary as NetSalary, @BSalaryHour as SalaryPerHour
		---------------------------------------------------------------

		Declare @No_Rows int
	    Select No_Rows= count(*) FROM MonthlyRecord
		WHERE Month = @month AND EmplID = @emplID
	    Return @No_Rows
	END --If block ends
	ELSE --Else block begins
	BEGIN
		RETURN 0
	END --Else block ends
END

VIEW:

@using EmployeeAttendance_app.Models
@model IEnumerable<getmonthlyreportresult>
           
@{
    
    var Item = Model.FirstOrDefault();
}

<td>
                        	Name
                        </td>
                        <td>
                        	@Item.EmplName
                        </td>
                        <td>
                        	Designation
                        </td>
                        <td>
                        	@Item.DeptName
                        </td>
                    
                    <tr style="border-removedsolid 3px black">
                    	<td>Total Working Time</td>
                        <td>@Item.OverallTime</td>
                        <td>Net Salary PKR</td>
                        <td>@Item.NetSalary</td>
                    </tr>
                    <table><tbody><tr>

so this throws error : why ?

@Item.NetSalary

Posted 19-Feb-14 0:19am

Hunain Hafeez

Updated 19-Feb-14 0:24am

CHill60

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ArunRajendra · Answer 1 · 2014-02-19T01:27:00

Solution 1

This is just a guess as I am not expert with MVC and Entity Framework which I guess its been used. The SP is returning multiple result set. SELECT * FROM MonthlyRecord and Select @BasicSalary as NetSalary, @BSalaryHour as SalaryPerHour. My guess is the modal is pointing to first result set. NetSalary is in 2nd result set. Hope I am correct :) and gives you hint to proceed.

Posted 19-Feb-14 1:27am

ArunRajendra

Comments

Hunain Hafeez 19-Feb-14 7:34am

so how to get 2nd sir ?

ArunRajendra 19-Feb-14 7:41am

Sorry as I told you I am not expert. One solution could be merge it into single query SELECT *, @BasicSalary as NetSalary, @BSalaryHour as SalaryPerHour FROM MonthlyRecord WHERE Month = @month AND EmplID = @emplID

Hunain Hafeez · Answer 2 · 2014-02-19T02:00:00

C#

 public ActionResult Generated_PaySlip(int? emplID, String month)
        {
            if (!String.IsNullOrEmpty(Session["Admin"] as string))
            {
                int? basicSalary= 0;
                int? BSalaryPerHour = 0;
                IEnumerable<getmonthlyreportresult> PaySlip = DataContext.GetMonthlyReport(emplID, month, ref basicSalary, ref BSalaryPerHour).ToList();
                ViewBag.Month = Request.QueryString["Month"];
                //String NetSalary = DataContext.GetMonthlyReport(emplID, month).First()
                ViewBag.Salary = basicSalary;
                return View(PaySlip);
            }
            else
            {
                return RedirectToAction("IsAuth_Page", "Home");
            }
</getmonthlyreportresult>

SQL

USE [a1]
GO
/****** Object:  StoredProcedure [dbo].[GetMonthlyReport]    Script Date: 2014-02-19 5:30:57 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetMonthlyReport] @emplID INT = NULL,
	@month VARCHAR(50) = NULL, @BasicSalary int = null out ,@BSalaryHour int= Null out
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	IF (@emplID IS NOT NULL AND @month IS NOT NULL) --If Block begins
	BEGIN
		SELECT * 
		FROM MonthlyRecord
		WHERE Month = @month AND EmplID = @emplID

		---------------------------------------------------------------
		Declare @StartDate Date,  @EndDate Date, @mydate date, @TotalDays int, @TotalHours int
		
		Set @mydate = GETUTCDATE()
		Set @StartDate = (SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101))
		Set @EndDate = (SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101))
		Set @TotalDays =((DATEDIFF(dd, @StartDate, @EndDate) + 1)
		  -(DATEDIFF(wk, @StartDate, @EndDate) * 1)
		  -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)) 
		Set @TotalHours  = (@TotalDays * 8)
		Set @BasicSalary = (Select BasicSalary from HrEmployee where EmplID=@emplID)
		Set @BSalaryHour = @BasicSalary / @TotalHours
		Select @BasicSalary as NetSalary, @BSalaryHour as SalaryPerHour
		---------------------------------------------------------------

		Declare @No_Rows int
	    Select No_Rows= count(*) FROM MonthlyRecord
		WHERE Month = @month AND EmplID = @emplID
	    Return @No_Rows
	END --If block ends
	ELSE --Else block begins
	BEGIN
		RETURN 0
	END --Else block ends
END