Using LINQ to Entities. NOT SQLConnections or any other method.
I have a webpage where I need to display the 'ID' from one DataTable and the corresponding 'Name' from another DataTable, both in the same database. The ID and Name should be displayed as lblOrderId.Text = OrderId.ToString(); and lblName.Text = Name.ToString(); respectively.
Does not have to be a string, I just want the values from the Database in the labels.
As it stand now I am not getting any of the label text, but if I use the code right at the bottom of this all I get is the order and delivery dates.
Datatable containing OrderId has two columns; OrderId being autogenerated and sitting in column 0, and ProductId.
Datatable containing Name has 6 columns, Name being in column 1 as column 0 holds autogenerated customer ID
All I need is the last added Name and ID to be displayed as labels.
I have been researching this on this site and others for the last 5 hours and cannot seem to find a solution which suits my needs. It's all SQL Connections and a sparse sprinkling of Database values to text boxes.
Please see code below with what I have achieved, and if you require any further coding or information please do not hesitate to ask.
Thank you in advance for any suggestions or assistance you can provide.
ASPX.CS (back end)
protected void Page_Load(object sender, EventArgs e)
{
using (Entities myEntities = new Entities())
{
int OrderId = // Not sure what to put here, but if I don't have it I get the error *The name OrderId does not exist in the current context*
var orderId = (from ord in myEntities.Orders
where ord.OrderId.Equals(OrderId) // Error here
select ord).Last();
lblOrderID.ForeColor = Color.Red;
lblOrderID.Text = orderId.ToString();
Facing the exact same error with the method below concerning the Name. Both methods are contained within the same Using Entities {} curly brackets.
var name = (from ord in myEntities.Customer
where ord.Name.Equals(Name)
select ord).Last();
lblName.ForeColor = Color.Red;
lblName.Text = name.ToString();
Closing the Using Entities {} curly brackets, but still inside the Page_Load {} curly brackets I have the following code to add the order date and delivery date as seen below
DateTime date = DateTime.Now;
DateTime DeliveryDate = date.AddDays(5);
lblDate.ForeColor = Color.Red;
lblDeliveryDate.ForeColor = Color.Red;
lblDate.Text = date.ToLongDateString();
lblDeliveryDate.Text = DeliveryDate.ToLongDateString();
Full block of Page_Load
protected void Page_Load(object sender, EventArgs e)
{
using (Entities myEntities = new Entities())
{
//int OrderId = Convert.ToInt32(); // Not sure what to put here, but if I don't have it I get the error *The name OrderId does not exist in the current context*
var orderId = (from ord in myEntities.Orders
where ord.OrderId.Equals(OrderId)// Error here
select ord).Last();
lblOrderID.ForeColor = Color.Red;
lblOrderID.Text = orderId.ToString();
var name = (from ord in myEntities.Customer
where ord.Name.Equals(Name)
select ord).Last();
lblName.ForeColor = Color.Red;
lblName.Text = name.ToString();
}
DateTime date = DateTime.Now;
DateTime DeliveryDate = date.AddDays(5);
lblDate.ForeColor = Color.Red;
lblDeliveryDate.ForeColor = Color.Red;
lblDate.Text = date.ToLongDateString();
lblDeliveryDate.Text = DeliveryDate.ToLongDateString();
}
ASPX (front end)
<p>
Hi
<asp:Label ID="lblName" runat="server" Text=""></asp:Label>
Thank you for shopping at Cineplex.<br />
<br />
Your Order ID number is:
<asp:Label ID="lblOrderID" runat="server" Text=""></asp:Label><br />
<br />
Your order was placed on:
<asp:Label ID="lblDate" runat="server" Text=""></asp:Label><br />
<br />
Delivery date:
<asp:Label ID="lblDeliveryDate" runat="server" Text=""></asp:Label>
</p>
Hardcoding the Name and Order ID and removing the entities and querys as can be seen using the code below at the backend works a treat, but that's not acceptable. The customer needs to view their Order ID upon successful purchase.
The only consolation here is that I get a value in all four labels the client can read.
protected void Page_Load(object sender, EventArgs e)
{
lblName.ForeColor = Color.Red;
lblName.Text = "User";
lblOrderID.ForeColor = Color.Red;
lblOrderID.Text = "1";
DateTime date = DateTime.Now;
DateTime DeliveryDate = date.AddDays(5);
lblDate.ForeColor = Color.Red;
lblDeliveryDate.ForeColor = Color.Red;
lblDate.Text = date.ToLongDateString();
lblDeliveryDate.Text = DeliveryDate.ToLongDateString();
}
Your question is all about querying something from the database. You could remove all ASP stuff from the query, as it only distracts from what your problem is.
Instead you should show us the entity classes (Order, Customer). As I understand, you want the Order.OrderId and the Customer.Name (of the customer who's order it is) for a specific order, of which you don't know how it is identified.
Assuming you go with the usual Customer/Order sample model, your entities must look about like this:
public class Customer
{
public int CustomerId; // identifies a customer
public string Name; // the customer's name
public ICollection<Order> Orders; // collection of customer's orders
}
public class Order
{
public int OrderId; // identifies an order
public int CustomerId { get; } // ID of the customer who's order it is.
public Customer Customer { get; } // The Customer entity of that customer
}
Inside the Page_Load (leaving away what is not relevant)
// 1st find out the ID of the one and only order the page is about
int orderId = 4711; // can't help here - where do you intend to get it from?
// 2nd retrieve from DB whatever we need later
string customerName;
using (var myEntities = new Entities())
{
var orderInfo = (
from order in myEntities.Orders
where order.OrderId == orderId
select new { CustomerName = order.Customer.Name }
).SingleOrDefault();
if (orderInfo == null) // error handling if orderId is bad
throw new Exception("No such order found.");
customerName = orderInfo.CustomerName;
}
// 3rd use what we know to update controls
lblName.Text = customerName;