Click here to Skip to main content
14,241,604 members
Rate this:
Please Sign up or sign in to vote.
I am new to MVC and Entity Framework. I have the following stored procedure in SQL Server 2012:
-------------------------------------------------------------------------------
ALTER PROCEDURE [Elite].[AppInsertMrwdUpgrade] 
(
	 @UpgradeGrpID INT
	,@MrwdID VARCHAR(9)
	,@EID VARCHAR(10)
	,@ResultMsg VARCHAR(255) = NULL OUTPUT
)
AS

SET NOCOUNT ON;

	
SELECT DISTINCT TOP 1
	@ResultMsg = CONCAT('Marriott Rewards Number : ' ,@MrwdID, ' has already been succesfully upgraded to ', IIF(A.UpgradeLvlCd = 'P', 'Platinum' , 'Gold') , '.')
FROM Elite.MrwdUpgradeTranHist	AS A
	JOIN Elite.UpgradeGrpRef	AS B
		ON A.UpgradeGrpID = B.UpgradeGrpID
WHERE A.MrwdID = @MrwdID
	AND A.ResponseDt IS NOT NULL
	AND A.ResponseCd IN ('NO UPDATE LEVEL - LEV, LEV TYP & DT SAME','UPGRADE LEVEL LOWER THAN CURRENT LEVEL','NNIGN')
	AND A.UpgradeLvlCd >= B.MaxLvl
ORDER BY 1 DESC
;

IF @ResultMsg IS NOT NULL
	BEGIN
		RETURN
	END 
;

DECLARE @Lvl CHAR(1)

SELECT 
	@Lvl = R.MaxLvl
FROM Elite.UpgradeGrpRef				AS R
WHERE R.UpgradeGrpID = @UpgradeGrpID
;

INSERT INTO Elite.MrwdUpgradeTranHist
	(
		UpgradeGrpID
		, MrwdID
		, UpgradeLvlCd
		, InputDt
		, InputSource
		, UserID
	)
VALUES
	(	
		@UpgradeGrpID
		,@MrwdID
		,@Lvl
		,GETDATE()
		,'Manual_Assoc'
		,@EID
	)
;

SET @ResultMsg = CONCAT('Success! Marriott Rewards Number : ' ,@MrwdID, ' has been submitted for upgrade to ', IIF(@Lvl = 'P', 'Platinum' , 'Gold') , '.')
;

RETURN


-------------------------------------------------------------------------------
I want to be able to use MVC 5 and Entity Framework to call this stored procedure to insert a record if it is not a duplicate. I will use GroupID, MrwdID, and EID for textbox input with a submit button to activate the call of the stored procedure. I would expect to receive back the @ResultMsg and be able to either clear the form on success or keep the information in the text boxes for editing with a pop-up message to the user. (I realize some of this may be bad practices. We are using this on our Intranet only using IIS). I hope with what I have provided, a solution can be found.

What I have tried:

I have been able to create as Database first a Data Entity model which has identified both the stored procedure and function identified without a return collection. From there when I try to create a controller with view it errors saying there isn't an object.

I have tried with the associated table and it did create views. I used the Create View and modified it as follows:
-------------------------------------------------------------------------------
@model EliteMrwd.Models.MrwdUpgradeTranHist

@{
    ViewBag.Title = "Create";
}

<h2>Submit Upgrade</h2>

@using (Html.BeginForm()) 
{
    @Html.AntiForgeryToken()
    
    <div class="form-horizontal">
        
        <hr>
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })
        <div class="form-group">
            @Html.Label("Group ID: ", htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                <input type="text" id="UpgradeGrpID" name="UpgradeGrpID" value= @ViewBag.idName />
                @Html.ValidationMessageFor(model => model.UpgradeGrpID, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.Label("Marriott Rewards #: ", htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.MrwdID, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.MrwdID, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.Label("Submitting Person: ", htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => @User.Identity.Name.Split('\\')[1], new { htmlAttributes = new {@class = "form-control", @disabled="true" } })
                @Html.ValidationMessageFor(model => model.UserID, "", new { @class = "text-danger" })
            </div>
        </div>

         <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                
            </div>
        </div>
    </div>
}

@*<div>
    @Html.ActionLink("Back to List", "Index")
</div>*@

@section Scripts {
    @Scripts.Render("~/bundles/jqueryval")
}

------------------------------------------------------------------------------

My controller has the following sections of code:
------------------------------------------------------------------------------
public ActionResult Create(string id)
        {
            if (id == null)
            {
                ViewBag.idName = "";
            }
            else
            {
                ViewBag.idName = id;
            }
            return View();
        }
and 
       [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Create([Bind(Include = "TranID,UpgradeGrpID,MrwdID,UpgradeLvlCd,InputDt,InputSource,UserID,ExportDt,ExportCd,ResponseDt,ResponseCd")] MrwdUpgradeTranHist mrwdUpgradeTranHist)
        {
            //[Bind(Include = "TranID,UpgradeGrpID,MrwdID,UpgradeLvlCd,InputDt,InputSource,UserID,ExportDt,ExportCd,ResponseDt,ResponseCd"
            if (ModelState.IsValid)
            {
                db.MrwdUpgradeTranHists.Add(mrwdUpgradeTranHist);
                db.SaveChanges();
                return RedirectToAction("Index");
            }

            return View(mrwdUpgradeTranHist);
        }
Posted
Updated 9-Feb-17 15:35pm
v2
Rate this:
Please Sign up or sign in to vote.

Solution 1

I'm sorry I included a commented out duplicate bind statement.
   
Comments
Karthik_Mahalingam 7-Feb-17 22:22pm
   
use Imporove question to edit the question, dont use solution widget to post comments.
Rate this:
Please Sign up or sign in to vote.

Solution 2

My solution was to change the stored procedure to not have an output variable. Instead it now has a select statement to push the data to me. I then pass the data to a results.cshtml page. I am still open to learning being able to pass via an output parameter if there are any suggestions.
   

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

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100