How read data from the another table in c#

Question

I have created a project in ASP.NET MVC 5 with two tables.

This is the table's structure:

CREATE TABLE [dbo].[User] 
(
    [UserID]            INT              IDENTITY (1, 1) NOT NULL,
    [FirstName]         VARCHAR (50)     NOT NULL,
    [LastName]          VARCHAR (50)     NOT NULL,
    [Email]             VARCHAR (256)    NOT NULL,
    [DateOfBirth]       DATE             NULL,
    [Password]          NVARCHAR (MAX)   NOT NULL,
    [IsEmailVerified]   BIT              NOT NULL,
    [ActivationCode]    UNIQUEIDENTIFIER NOT NULL,
    [ResetPasswordCode] NVARCHAR (100)   NULL,

    PRIMARY KEY CLUSTERED ([UserID] ASC)
);

CREATE TABLE [dbo].[OrderDetails] 
(
    [OrderID]          INT  NOT NULL IDENTITY,
    [User_ID]          INT  NOT NULL,
    [OrderDate]        DATE NULL,
    [A_ChickenChop_BP] INT  NOT NULL,
    [A_ChickenChop_M]  INT  NOT NULL,
    [A_Spaghetti_AH]   INT  NOT NULL,
    [A_Spaghetti_P]    INT  NOT NULL,
    [A_Spaghetti_S]    INT  NOT NULL,
    [A_ChickenRice_CB] INT  NOT NULL,
    [A_ChickenRice_CW] INT  NOT NULL,
    [A_ChickenRice_D]  INT  NOT NULL,
    [A_WantanMee_IS]   INT  NOT NULL,
    [A_WantanMee_NS]   INT  NOT NULL,

    CONSTRAINT [PK_OrderDetails] PRIMARY KEY CLUSTERED ([OrderID] ASC),

    CONSTRAINT [FK_OrderDetails_User] 
        FOREIGN KEY ([User_ID]) REFERENCES [dbo].[User] ([UserID])
);

As the project is login needed so the UserID has been set up like 1,2,3,.... After login that, user can order the food from the program and I want to have a view that can display the UserID from the User table. So the code I write is like this:

public class OrderController : Controller
{
    // GET: Order
    [HttpGet]
    public ActionResult orderUser()
    {
        return View();
    }

    [HttpPost]
    public ActionResult orderUser(User orderUser)
    {
        using (myDatabaseEntities myDatabase = new myDatabaseEntities())
        {
            var userID = myDatabase.OrderDetails.Where(a => a.User_ID == orderUser.UserID).FirstOrDefault();
        }
        return View(orderUser);
    }
}

The User_ID is the ID I want it same like the UserID from the user table. So the userid in the controller I make that orderDetails's User_ID is same to the User's UserID but it doesn't work! And the view I display in program is empty like it is read a null data.

I want make the User_ID from OrderDetails table will read the UserID table here.

Where is the mistake I make?

@model Food_Founder.Models.OrderDetail

@{
    ViewBag.Title = "orderUser";
}

<h2>orderUser</h2>
@HttpContext.Current.User.Identity.Name


@using (Html.BeginForm())
{
    @Html.AntiForgeryToken()

    <div class="form-horizontal">
        <h4>OrderDetail</h4>
        <hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })
        <div class="form-group">
            @Html.LabelFor(model => model.OrderID, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.OrderID, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.OrderID, "", new { @class = "text-danger" })
            </div>
        </div>

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

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

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

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

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

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

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

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

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

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

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

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

        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Create" class="btn btn-default" />
            </div>
        </div>
    </div>
}

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

Above the code is the view code and it is like this.

As you can see there is a userid that I circle it already. I want make that it will display the data of the User's userid

var userID in your action is assigned but never used. Currently, you are just returning the same data posted. — Jasen
– Jasen, Commented Sep 7, 2019 at 20:18
Return the data inside. I make the orderUser is read from User table and after that I make orderDetails's User_ID is same with User's UserID — Steven Yu
– Steven Yu, Commented Sep 7, 2019 at 20:25
I'm having a difficult time understanding what you are trying to accomplish and what exactly is the problem you are experiencing. If you have a problem with the displayed page, please also show us your view code. And if you haven't already done so, please debug your action and indicate if the returned item contains the values you are expecting. — Jasen
– Jasen, Commented Sep 7, 2019 at 20:37

Los2000 · Accepted Answer · 2019-09-08 05:46:10Z

1

If I understand your question correctly, what you want to do is return a view using the OrderDetails that matches the UserId passed in through the User argument. The first problem I see with your code is you're using variable name "orderUser" both as your Action method name, and as parameter that is passed in. While your compiler won't complain about it, other programmers will give you a look like you just wrote 1000 lines of spaghetti code and didn't indent your loops and if statements. Using duplicate names for different items in the same method makes your code less readable, and less maintainable. If it helps, try being more verbose in your naming conventions such as, "UserAction," or "userWithOrder."

Second problem, while your entity framework statement is correct, (you're pulling the entire data record, not just the UserId; the variable should be named, UserDetails) you have a scope problem with the using statement. The "UserId" record will drop out of scope outside of the braces that define the using statement, so you must use it before execution leaves the using scope. Alternatively, you can assign the record to a local variable defined outside of the using's scope.

Finally, the way this is written, all you are doing is calling your method and passing in a user parameter, and then returning that same user parameter to the view. What what are your views bound to, a User model or a Details model? You'll probably need a view for both, or even better consider setting up a partial views.

This is how your code should look if I understand what you're trying to do:

[HttpPost]
public ActionResult orderUser(User userWithOrder)
{
    using (myDatabaseEntities myDatabase = new myDatabaseEntities())
    {
        var orderDetails = myDatabase.OrderDetails.Where(a => a.User_ID == userWithOrder.UserID).FirstOrDefault();
        return View(orderDetails)    //It is OK to return from within a using statement; the using will close and dispose as needed.
    }
}

You should include the code for your views, otherwise, I'm just guessing on what you're attempting to display.

edited Sep 8, 2019 at 5:46

answered Sep 7, 2019 at 21:14

Los2000

668 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Steven Yu Over a year ago

First, Thanks for your suggestion so I can write the code like public action result orderFood() for different food I put? Lastly, I'm required to create two view page for user model and details model? As I already create the user model for the registration and now I need to create another ADO model for it?

Los2000 Over a year ago

There are many approaches you can take; however, these are not questions I can answer for you. It's part of the learning process; its why programmers get paid the big bucks; not everyone has the patience and perseverance to learn how to write efficient and maintainable code. For now, go with what works and what makes sense to you, but the more you program, you'll learn new and better ways to architect your code.

Los2000 Over a year ago

So, looking at your view, the Model is an orderDetail object; which means the orderDetail record will be available the Model, not the orderUser record. There are a few approaches you can take here. You can either create a class that defines a "has a" relationship between the orderDetail and orderUser (i.e., orderDetail has an orderUser), then use that class as you're view's model; then the orderUser object will be returned along with the orderDetail object. Or, you can create a partial view that uses orderDetail as the model, then use the partial view where you have the User_Id displayed.

Steven Yu Over a year ago

Owhh Thanks! You're giving me a direction is better than answer! Thanks for your patience reply!

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

You are passing an User Object as parameter and i believe this User object is currently logged user of the system. If you are passing logged user object correctly, your code should works fine if there is an order placed by that user. And if that user doesn't have an order then it will return null.

So you have to check,

Are you are passing right object to that Function.

Is there any order is in the OrderDetails table associated with that user.

Check your PlaceOrder method where you put entry to OrderDetails table and make sure you are inserting correct USER_ID to OrderDetails table

Its a problem of debugging i guess

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Sep 8, 2019 at 6:43

Shahjahan

5525 silver badges12 bronze badges

3 Comments

Steven Yu Over a year ago

Ya, I think I have passing the right object to the function but am I needed to create another ADO model for it?

Shahjahan Over a year ago

How could you access OrderDetails without creating ADO model?

Steven Yu Over a year ago

I think I go wrong way for accessing it. Before now I trying creating another model for order table, I'm using relationship linking two table. And the account model has two class which are user table and order table.

Collectives™ on Stack Overflow

How read data from the another table in c#

2 Answers 2

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related