无法从asp.net中的存储过程访问列 [英] columns not accessible from store procedure in asp.net

问题描述

我正在使用商店程序访问asp.net View中的字段，我将db列放在HTML表中，除了一个字段'NetSalary'之外它是workign。它无法访问，它表示模型不包含它。



商店程序：

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:

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>

所以这会引发错误：为什么？

@ Item.NetSalary

so this throws error : why ?

@Item.NetSalary

推荐答案

这只是一个猜测，因为我不是MVC和实体框架的专家，我猜它已被使用。 SP返回多个结果集。 SELECT * FROM MonthlyRecord并选择@BasicSalary作为NetSalary，@ BSalaryHour作为SalaryPerHour。我的猜测是模态指向第一个结果集。 NetSalary是第二个结果集。希望我是正确的:)并给你提示继续。

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.

 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>

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

这篇关于无法从asp.net中的存储过程访问列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！