3 Replies - 1077 Views - Last Post: 02 October 2017 - 03:42 PM Rate Topic: -----

#1 Janine_A  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 22-July 17

Using stored procedure in asp.net MVC

Posted 22 July 2017 - 08:35 AM

I am quite new to using stored procedures in my mvc application so will appreciate any guidance regarding the question below.

The Purpose: I have a stored procedure that returns the balance amount of a gift card. Basically when the user inputs a card number, it returns all relevant details for that card. Thing is I dont know if I used the correct approach as it keeps breaking with this error : The data reader is incompatible with the specified 'GiftworxModel.Card'. A member of the type, 'CardID', does not have a corresponding column in the data reader with the same name.

What I tried so far:
The context class:
public virtual ObjectResult<string> GetBalance(string cardNumber)
    {
        var cardNumberParameter = cardNumber != null ?
            new ObjectParameter("CardNumber", cardNumber) :
            new ObjectParameter("CardNumber", typeof(string));

        return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<string>("GetBalance", cardNumberParameter);
    }



The controller:
  [HttpPost]
    public ActionResult GetBalance(string cardNumber)
    {

        var balance = db.GetBalance(cardNumber).ToList();

        return View("GetBalance");

    }


The View:
@using (Html.BeginForm("GetBalance", "Cards", FormMethod.Post))
                        {
                            @Html.AntiForgeryToken()
                            <div class="panel-body">
                                <div class="row">
                                    <div class="col-lg-12">
                                        <form role="form">
                                            @Html.ValidationSummary(true, "", new { @class = "text-danger" })
                                           
                                               
                                           
                                            <div class="form-group">
                                                Number of Cards: <input type="text" name="cardNumber"/>
                                            </div>
                                            

                                            <input type="submit" value="Create" class="btn btn-default" style="background-color: #0a9dbd; color: white;" />
                                            <button type="reset" class="btn btn-default" style="background-color: #0a9dbd; color: white;">Reset</button>

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

                                </div>
                            </div>
                        }


Stored Procedure:
     GO

ALTER PROCEDURE  [dbo].[GetBalance]
-- Add the parameters for the stored procedure here
@CardNumber nvarchar(20)
AS
BEGIN

SET NOCOUNT ON;
If Not Exists(Select CardID From Cards With (NOLOCK) Where CardNumber = @CardNumber OR (Cards.CardNumber2 = @CardNumber))
Begin
Select 'Invalid Card Number' as Error
END

Select Sum(TotalAmount) as TotalAmount, StoreCustomerName, StoreCustomerISurname, StoreCustomerEmail, StoreCustomerDOB, StoreCustomerAddress, StoreCustomerCity, 
                         StoreCustomerRegion, StoreCustomerCountry, StoreCustomerCel, StoreCustomerDate, StoreCustomerToken, '' as Error, StoreCustomerID, CardNumber, CardStatus,
                         LoyaltyLevelName, LoyaltyLevel, DiscountLevelAmount, DiscountLevelName, GiftCardEnabled, LoyaltyEnabled, StoreCustomerNumber From (
SELECT        0 AS TotalAmount, StoreCustomers.StoreCustomerName, StoreCustomers.StoreCustomerISurname, StoreCustomers.StoreCustomerEmail, StoreCustomers.StoreCustomerDOB, StoreCustomers.StoreCustomerAddress, StoreCustomers.StoreCustomerCity, 
                         StoreCustomers.StoreCustomerRegion, StoreCustomers.StoreCustomerCountry, StoreCustomers.StoreCustomerCel, StoreCustomers.StoreCustomerDate, StoreCustomers.StoreCustomerToken, StoreCustomers.StoreCustomerID, 
                         Cards.CardNumber, Cards.CardStatus, LoyaltyLevels.LoyaltyLevelName, LoyaltyLevels.LoyaltyLevel, DiscountLevels.DiscountLevelAmount, DiscountLevels.DiscountLevelName, Cards.GiftCardEnabled, Cards.LoyaltyEnabled, StoreCustomerNumber
FROM            StoreCustomers WITH (NOLOCK) INNER JOIN
                         Cards WITH (NOLOCK) ON StoreCustomers.StoreCustomerID = Cards.StoreCustomerID INNER JOIN
                         DiscountLevels WITH (NOLOCK) ON Cards.DiscountLevelID = DiscountLevels.DiscountLevelID AND Cards.CustomerID = DiscountLevels.CustomerID  INNER JOIN 
                         LoyaltyLevels WITH (NOLOCK) ON Cards.LoyaltyLevelID = LoyaltyLevels.LoyaltyLevelID AND Cards.CustomerID = LoyaltyLevels.CustomerID
WHERE         (Cards.CardNumber = @CardNumber) OR (Cards.CardNumber2 = @CardNumber) --WHERE        (Cards.CardID = @CardID)
GROUP BY StoreCustomers.StoreCustomerName, StoreCustomers.StoreCustomerISurname, StoreCustomers.StoreCustomerEmail, StoreCustomers.StoreCustomerDOB, StoreCustomers.StoreCustomerAddress, StoreCustomers.StoreCustomerCity, 
                         StoreCustomers.StoreCustomerRegion, StoreCustomers.StoreCustomerCountry, StoreCustomers.StoreCustomerCel, StoreCustomers.StoreCustomerDate, StoreCustomers.StoreCustomerToken, StoreCustomers.StoreCustomerID, 
                         Cards.CardNumber, Cards.CardStatus, LoyaltyLevels.LoyaltyLevelName, LoyaltyLevels.LoyaltyLevel, DiscountLevels.DiscountLevelAmount, DiscountLevels.DiscountLevelName,Cards.GiftCardEnabled, Cards.LoyaltyEnabled, StoreCustomerNumber
Union ALL
SELECT        SUM(Sales.SaleTotalAmount) AS TotalAmount, StoreCustomers.StoreCustomerName, StoreCustomers.StoreCustomerISurname, StoreCustomers.StoreCustomerEmail, StoreCustomers.StoreCustomerDOB, StoreCustomers.StoreCustomerAddress,
                         StoreCustomers.StoreCustomerCity, StoreCustomers.StoreCustomerRegion, StoreCustomers.StoreCustomerCountry, StoreCustomers.StoreCustomerCel, StoreCustomers.StoreCustomerDate, 
                         StoreCustomers.StoreCustomerToken, StoreCustomers.StoreCustomerID, Cards.CardNumber, Cards.CardStatus, LoyaltyLevels.LoyaltyLevelName, LoyaltyLevels.LoyaltyLevel, DiscountLevels.DiscountLevelAmount, 
                         DiscountLevels.DiscountLevelName, Cards.GiftCardEnabled, Cards.LoyaltyEnabled, StoreCustomerNumber
FROM            StoreCustomers WITH (NOLOCK) INNER JOIN
                         Cards WITH (NOLOCK) ON StoreCustomers.StoreCustomerID = Cards.StoreCustomerID INNER JOIN
                         Sales WITH (NOLOCK) ON Cards.CardID = Sales.CardID INNER JOIN
                         LoyaltyLevels WITH (NOLOCK) ON Cards.LoyaltyLevelID = LoyaltyLevels.LoyaltyLevelID AND Cards.CustomerID = LoyaltyLevels.CustomerID  INNER JOIN
                         DiscountLevels WITH (NOLOCK) ON Cards.DiscountLevelID = DiscountLevels.DiscountLevelID AND Cards.CustomerID = DiscountLevels.CustomerID
WHERE         (Cards.CardNumber = @CardNumber) OR (Cards.CardNumber2 = @CardNumber)--WHERE        (Cards.CardID = @CardID)
GROUP BY StoreCustomers.StoreCustomerName, StoreCustomers.StoreCustomerISurname, StoreCustomers.StoreCustomerEmail, StoreCustomers.StoreCustomerDOB, StoreCustomers.StoreCustomerAddress, StoreCustomers.StoreCustomerCity, 
                         StoreCustomers.StoreCustomerRegion, StoreCustomers.StoreCustomerCountry, StoreCustomers.StoreCustomerCel, StoreCustomers.StoreCustomerDate, StoreCustomers.StoreCustomerToken, StoreCustomers.StoreCustomerID, 
                         Cards.CardNumber, Cards.CardStatus, LoyaltyLevels.LoyaltyLevelName, LoyaltyLevels.LoyaltyLevel, DiscountLevels.DiscountLevelAmount, DiscountLevels.DiscountLevelName, Cards.GiftCardEnabled, Cards.LoyaltyEnabled, StoreCustomerNumber

) as x
GROUP BY StoreCustomerName, StoreCustomerISurname, StoreCustomerEmail, StoreCustomerDOB, StoreCustomerAddress, StoreCustomerCity, StoreCustomerRegion, StoreCustomerCountry, StoreCustomerCel, StoreCustomerDate, StoreCustomerToken, StoreCustomerID, 
CardNumber, CardStatus, LoyaltyLevelName, LoyaltyLevel, DiscountLevelAmount, DiscountLevelName, GiftCardEnabled, LoyaltyEnabled, StoreCustomerNumber

END

This post has been edited by andrewsw: 22 July 2017 - 02:25 PM
Reason for edit:: added missing [code][/code] tags


Is This A Good Question/Topic? 0
  • +

Replies To: Using stored procedure in asp.net MVC

#2 andrewsw  Icon User is online

  • the case is sol-ved
  • member icon

Reputation: 6374
  • View blog
  • Posts: 25,755
  • Joined: 12-December 12

Re: Using stored procedure in asp.net MVC

Posted 22 July 2017 - 02:26 PM

Please use code tags when posting, it demonstrates how to use them directly in the area where you type your post. I've added them for you.
Was This Post Helpful? 0
  • +
  • -

#3 andrewsw  Icon User is online

  • the case is sol-ved
  • member icon

Reputation: 6374
  • View blog
  • Posts: 25,755
  • Joined: 12-December 12

Re: Using stored procedure in asp.net MVC

Posted 22 July 2017 - 02:38 PM

First thing I notice is that you have nested forms. Forms should not be nested.

Briefly searching the error message I get the overall impression that it relates to an SP that returns different values/content for different input. Is the SP fully tested, and working, independent of the code?

I notice the procedure has a variable @CardID that doesn't seem to be defined anywhere?

It might help also if you describe how CardNumber and CardID are connected.
Was This Post Helpful? 0
  • +
  • -

#4 WabiSabi  Icon User is offline

  • D.I.C Head

Reputation: 52
  • View blog
  • Posts: 224
  • Joined: 31-December 10

Re: Using stored procedure in asp.net MVC

Posted 02 October 2017 - 03:42 PM

Sorry. I had an answer originally, but your error message was not what I thought it was, so my answer was not that great.

But, to still attempt to be helpful, here is a similar question, with an answer that may help you.

https://forums.asp.n...+the+same+name+

This post has been edited by WabiSabi: 02 October 2017 - 03:51 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1