Insertion of UserId into tblOrder fails

Emmmanuel FIADUFE

Hello Team,

The error message I'm getting says that UserId does not exist, and I tried to create a relationship between user table and tblOrder table but it also says foreign key conflict whenever I tried to insert data so i remove the relationship, the Schema for both tblUser and tblOrder is Order, and though the UserId is present in the tblUser, kindly assist.

public int AddOrder(OrderViewModel objOrderViewModel)
        {
    // Check if User Exists
            var userExists = objRestaurantDBEntities.tblUsers
                          .Any(u => u.UserId == objOrderViewModel.UserId);

            if (!userExists)
            {
                throw new Exception("? Error: UserId (" + objOrderViewModel.UserId + ") does not exist in tblUser.");
            }

    // Create Order Object
    tblOrder objOrder = new tblOrder
    {
        CustomerId = objOrderViewModel.CustomerId,
        FinalTotal = objOrderViewModel.FinalTotal,
        OrderDate = DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss"),
        OrderNumber = DateTime.Now.ToString("ddMMMyyyyHHmmss"),
        PaymentTypeId = objOrderViewModel.PaymentTypeId,
        UserId = objOrderViewModel.UserId
    };

objRestaurantDBEntities.tblOrders.Add(objOrder);
objRestaurantDBEntities.SaveChanges();

int OrderId = objOrder.OderId; // ? Ensure correct field name

// List to store Order Details
List orderDetailsList = new List();

    foreach (var item in objOrderViewModel.ListofOrderDetailViewModel)
    {
        tblOrderDetail objOrderDetails = new tblOrderDetail
        {
            OderId = OrderId,
            Discount = item.Discount,
            ItemId = item.ItemId,
            Total = item.Total,
            UnitPrice = item.UnitPrice,
            Quantity = Convert.ToInt32(item.Quantity),
            Vat = item.Vat
        };

orderDetailsList.Add(objOrderDetails);

        // Check if Item exists in stock before updating
        var tblQty = objRestaurantDBEntities.tblQuantities
                        .SingleOrDefault(x => x.ItemId == item.ItemId);

        if (tblQty != null)
        {
            int newQuantity = Convert.ToInt32(tblQty.Quantity) - Convert.ToInt32(item.Quantity);
            tblQty.Quantity = newQuantity >= 0 ? newQuantity : 0; // Prevent negative stock

            objRestaurantDBEntities.Entry(tblQty).State = EntityState.Modified;
        }
    }

    // Save all Order Details in one go
    objRestaurantDBEntities.tblOrderDetails.AddRange(orderDetailsList);
    objRestaurantDBEntities.SaveChanges(); // ? Single SaveChanges() for better performance

return OrderId;
}
}

}

10 Replies

Know the answer? Post it — somebody with the same question will find it here.

Emmmanuel FIADUFE1y ago
Hello team,

Any additional help on this question will be highly appreciated
Emmmanuel FIADUFE1y ago
But when I remove the user code from the class, I'm able to save the data into the database without any error.

Can anyone assist me with the user code, so that when I save order it will also save the userId at the same time.
Emmmanuel FIADUFE1y ago
This is my current update now in the controller, though it doesn't return the error again that the User is not found but the Order data do not save in the database.

public JsonResult SaveSales(OrderViewModel objOrderViewModel)
{
// Get logged-in user's ID from session
int? userId = Session["UserId"] as int?;

if (userId == null)
{
return Json(new { success = false, message = "User is not logged in." }, JsonRequestBehavior.AllowGet);
}

//Assign it to the ViewModel
objOrderViewModel.UserId = userId.Value;

// Proceed with saving the order
OrderRepositories objOrderRepositories = new OrderRepositories();
var OrderNumber = objOrderRepositories.AddOrder(objOrderViewModel);

return Json(new { success = true, orderNumber = OrderNumber }, JsonRequestBehavior.AllowGet);
}
Emmmanuel FIADUFE1y ago
Hello Team,

Please let me bring in some clarity here, may be that will help us find the solution

my earlier code is in a class and this is my controller code, and my login query is in the controller

public JsonResult SaveSales(OrderViewModel objOrderViewModel)
{
OrderRepositories objOrderRepositories = new OrderRepositories();
var OrderNumber = objOrderRepositories.AddOrder(objOrderViewModel);

return Json(OrderNumber, JsonRequestBehavior.AllowGet);

}
Emmmanuel FIADUFE1y ago
Hello team,

Please any further help will be highly appreciated.

Thanks
Emmmanuel FIADUFE1y ago
Hello Paul,

this is the error that popup after effecting the changes, User with ID {objOrderViewModel.UserId} not found.

Thank you for your response, as I run sql query this is the result as you can see.
Tuhin Paul1y ago
Before inserting an order, your code checks if the UserId exists. However, ensure that the UserId you're passing is the exact one stored in the database (case-sensitive or not, depending on your database's collation settings).

You can modify your check as follows to explicitly throw an exception if the user is not found:
```
var user = objRestaurantDBEntities.tblUsers
    .FirstOrDefault(u => u.UserId == objOrderViewModel.UserId);

if (user == null)
{
    throw new Exception($"User with ID {objOrderViewModel.UserId} not found.");
}
```
This will provide a clearer error message when the user does not exist in the tblUser table.
Tuhin Paul1y ago
This is related to the UserId being referenced in your tblOrder table, and the system not being able to find a corresponding user in the tblUser table.

Even though you've removed the relationship, there may still be foreign key constraints in the database itself. Run the following query to check for any remaining foreign key constraints:
```
SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
AND TABLE_NAME = 'tblOrder';
```
If any foreign key constraints are found, you might need to drop or adjust them in the database.
Emmmanuel FIADUFE1y ago
Hello Amira,

Thanks for your response, after removing the relationship there was no foreign key conflict again, but says UserId does not exist meanwhile it is the same username I used to login before trying to save the data into the database

Amira Bedhiafi1y ago

It is either the UserId doesn't exist (we can talk about foreign key constraint violation) or the relationship between tables is causing conflicts.

Even though you removed the relationship, the database might still have constraints:

SELECT * 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' 
AND TABLE_NAME = 'tblOrder';

Or simply add explicit validation:

// Before saving, verify the user exists with the exact ID
var user = objRestaurantDBEntities.tblUsers
    .FirstOrDefault(u => u.UserId == objOrderViewModel.UserId);
    
if (user == null)
{
    throw new Exception($"User with ID {objOrderViewModel.UserId} not found");
}