protected void Button2_Click(object sender, EventArgs e)
{
int intCustIDOID = Convert.ToInt32(Request.QueryString["qsnOrderCustID"]);
DateTime DTNowOID = DateTime.Now;
string strEmpOID = "Default User"; //Request.QueryString["sessionpass"];
double dblSubTotOID = Convert.ToDouble(lblPGTotExVAT.Text);
int intStatus = 1;
double dblVATOID = Convert.ToDouble(lblPGVAT.Text);
double dblTotOID = Convert.ToDouble(lblPGTotIncVAT.Text);
int m = 0;
for (m = 1; m <= 11; m++)
{
if (((TextBox)Panel1.FindControl("txtID" + m.ToString())).Text != "")
{
if (((TextBox)Panel1.FindControl("txtQty" + m.ToString())).Text != "")
{
string sConnectionString = "Data Source=xxxx;Initial Catalog=xxxx;User ID=xxxx;Password=xxxx";
SqlConnection objConn = new SqlConnection(sConnectionString);
//this gets the order id at the moment its just static
int intOrderID = Convert.ToInt32(Session["sNewID"]);
//This gets the stock ID from the textbox.
string strstock_ID = ((TextBox)Panel1.FindControl("txtID" + m.ToString())).Text;
int stock_IDint = Convert.ToInt32(strstock_ID);
//this gets the qty from the textbox.
string strQtyUpDate = ((TextBox)Panel1.FindControl("txtQty" + m.ToString())).Text;
int intQtyUpDate = Convert.ToInt32(strQtyUpDate);
//this gets the description from the textbox.
string strDesc = ((Label)Panel1.FindControl("lblDesc" + m.ToString())).Text;
//this gets the price from the textbox.
double dblPrice = Convert.ToDouble(((Label)Panel1.FindControl("lblPrice" + m.ToString())).Text);
//this gets the total ex vat from the textbox.
double dblTotalExVAT = Convert.ToDouble(((Label)Panel1.FindControl("lblTotal" + m.ToString())).Text);
//this gets the vat from the textbox.
double dblVAT = Convert.ToDouble(((Label)Panel1.FindControl("lblVAT" + m.ToString())).Text);
//this gets the status e.g. taken, collecting, delivery from the textbox.
string strToDeliver = ((DropDownList)Panel1.FindControl("ddl" + m.ToString())).Text;
//this gets the total cost price from the textbox.
double dblCP1 = Convert.ToDouble(((Label)Panel1.FindControl("lblCP" + m.ToString())).Text);
double dblCP = dblCP1 * intQtyUpDate;
//this gets the condition new or s/h from the textbox.
string strNSH = ((Label)Panel1.FindControl("lblNSH" + m.ToString())).Text;
//This is the sql statement.
objConn.Open();
string sqlUpDate = "INSERT INTO tbl_Part_order(Order_ID, stock_qty, Stock_ID, Price, description, ToDeliever, TotalExVAT, VAT, cost_price, n_or_sh) VALUES" +
"(@orderID,@qty,@stockID,@price,@description,@ToDeliever,@TotalExVAT,@VAT,@Cost_price,@NSH)";
SqlCommand objCmd1 = new SqlCommand(sqlUpDate, objConn);
objCmd1.Parameters.AddWithValue("@orderID", intOrderID);
objCmd1.Parameters.AddWithValue("@qty", intQtyUpDate);
objCmd1.Parameters.AddWithValue("@StockID", stock_IDint);
objCmd1.Parameters.AddWithValue("@Price", dblPrice);
objCmd1.Parameters.AddWithValue("@description", strDesc);
objCmd1.Parameters.AddWithValue("@ToDeliever", strToDeliver);
objCmd1.Parameters.AddWithValue("@TotalExVAT", dblTotalExVAT);
objCmd1.Parameters.AddWithValue("@VAT", dblVAT);
objCmd1.Parameters.AddWithValue("@cost_price", dblCP);
objCmd1.Parameters.AddWithValue("@NSH", strNSH);
objCmd1.ExecuteNonQuery();
lblcomplete2.Text = "yes!!";
objConn.Close();
}
}
} // end for loop
// This loops through the order lines and takes off stock from tbl_stock
int n = 0;
for (n = 1; n <= 11; n++)
{
if (((TextBox)Panel1.FindControl("txtID" + n.ToString())).Text != "")
{
if (((TextBox)Panel1.FindControl("txtQty" + n.ToString())).Text != "")
{
string sConnectionString = "Data Source=xxxxx.com;Initial Catalog=xxxx;User ID=xxxx;Password=xxxx";
SqlConnection objConn = new SqlConnection(sConnectionString);
//This gets the stock ID from the textbox.
string strstock_ID = ((TextBox)Panel1.FindControl("txtID" + n.ToString())).Text;
int stock_IDint = Convert.ToInt32(strstock_ID);
//this gets the qty from the textbox.
string strQtyUpDate = ((TextBox)Panel1.FindControl("txtQty" + n.ToString())).Text;
int intQtyUpDate = Convert.ToInt32(strQtyUpDate);
//This is the sql statement.
objConn.Open();
string sqlUpDate = "UPDATE tbl_stock SET qty = qty -" + intQtyUpDate + " WHERE stock_ID = " + stock_IDint;
SqlCommand objCmd1 = new SqlCommand(sqlUpDate, objConn);
objCmd1.ExecuteNonQuery();
lblcomplete.Text = "yes!";
objConn.Close();
}
}
} // end for loop
// This loops through, see if anything is for delivery, if it is direct us to the delivery page
// if not take us to the payment page
int p = 0;
int intDel = 0;
for (p = 1; p <= 11; p++)
{
if (((TextBox)Panel1.FindControl("txtID" + p.ToString())).Text != "")
{
if (((DropDownList)Panel1.FindControl("ddl" + p.ToString())).Text == "Delivery")
{
intDel += intDel + p;
}
}
}
// if there are deliverys take use this method
if (intDel >= 1)
{
Response.Redirect("delivery_schedule.aspx?qsnOrderCustID=" + Request.QueryString["qsnOrderCustID"] +
"&orderID=" + Convert.ToString(Session["sNewID"]));
}
else
{
Response.Redirect("order_pay.aspx?qsnOrderCustID=" + Request.QueryString["qsnOrderCustID"] +
"&orderID=" + Convert.ToString(Session["sNewID"]));
}
////////////////////////////////////////////////////////beginning of code to read and write code for del /////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// This will hold all the values entered by the Staff Member
List myOrder = new List();
// Loop around to fill up all of your orderlines
myOrder.Add(new OrderLine((int)Convert.ToInt32(this.txtQty1.Text), Convert.ToInt32(txtID1.Text)));
// Call our method to adjust the stock (This would be in Business Layer)
myOrder = adjustStock(myOrder);
// At this point the tbl_stock_location has been updated
// Check to make sure we could fulfill the entire order
foreach (OrderLine orln in myOrder)
{
if (orln._QtySupplied < orln._Qty)
{
lblE.Text = lblE.Text + (string.Format("We could only supply {0} of productid {1} - we just don't have any more stock<br/><br/>", orln._QtySupplied, orln._ProductID));
}
}
// Now we can go and update the tbl_del_location
foreach (OrderLine orLn in myOrder)
{
foreach (stockLocationAmount stkAmnt in orLn.locationQtys)
{
// We can now use these figures to update the other tables.
// but here I am just dispalying the information to the screen.
updateDelLocation(stkAmnt._ProductID, stkAmnt._LocationID, stkAmnt._QtySupplied);
}
}
// if i move this below then it just dosnt want to compile says its missing a {
} // end of button2 click//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
public List adjustStock(List myOrder)
{
// You could wrap this all in a System.Transaction so that if anything went
// wrong all chnages to the database would roll back
foreach (OrderLine orln in myOrder)
{
// Loop through all of the stock locations
for (int stockloc = 1; stockloc <= 5; stockloc++)
{
// Get the stock level at the current stock location
// This is another method in our BLL that will get the result from the DAL
int stockQty = getStockForProductAtLocation(orln._ProductID, stockloc);
if (!(stockQty == 0))
{
int actualLocation = getLocationID(orln._ProductID, stockloc);
// Check to see if we have enough stock at this location to fulfill
// the staff memebers order
if (stockQty >= (orln._Qty - orln._QtySupplied))
{
// We have got enough stock
// Call another BLL method to update the stock - this in turn would call the DAL
adjustStockForProductAtLocation(orln._ProductID, stockQty - (orln._Qty - orln._QtySupplied), stockloc);
// Update the supplied stock in our OrderLine Object
orln._QtySupplied = orln._Qty;
// Update the logged on what stock we got and from where
orln.locationQtys.Add(new stockLocationAmount(orln._ProductID, actualLocation, stockQty, stockloc));
// No need to keep looping now
break; // TODO: might not be correct. Was : Exit For
}
else
{
// We don't have enough so minus what we can
// Call another BLL method to update the stock - this in turn would call the DAL
adjustStockForProductAtLocation(orln._ProductID, stockQty, stockloc);
// Update the supplied stock in our OrderLine Object
orln._QtySupplied += stockQty;
// Update the logged on what stock we got and from where
orln.locationQtys.Add(new stockLocationAmount(orln._ProductID, actualLocation, stockQty, stockloc));
} // end of else
}
}
}
return myOrder;
}
I have started to create a method for update tbl_del_location but i was getting a little confused with this
and i started to write my own public int, but i was getting confused by the stkAmnt_LlocationID, how do i write that in my public int UpdateDelLocation ()
do i just use those variables to build my sql? and would it be public void as im not going to be returning anything?
Jeremy Huson
Senior Network Engineer
Carden Computers - PC Repair Brighton
it wont let me change it to public list as it throws me an error (invalide expression term 'public' and then on list it says 'expected ;')
i dont have an error, what i have is that i cant put the other block of code inside the button press so nothing really happens e.g. the items get added to the list but nothing gets updated or written to etc
Jeremy Huson
Senior Network Engineer
Carden Computers - PC Repair Brighton
hi, yes i know its not correct, however this was the best solution for the customer, i only have to add in an extra loop and it will be fine
jezh
I know i could normalise this, but it means more tables and l lot more programming
It's 44 replies, a week later, and your stress levels are way high, and you're still trying to get this approach to work.
Everyone has to learn this lesson the hard way:
It is NOT faster to do the data model wrong.
If you take this lesson to heart, you can look back on it (like all of us look back on the time we learned this the hard way) and laugh about it afterwards. :)
If not, expect more of the same stress levels in the future. :(
Best of luck!
If this answered your question, be sure to mark it as the answer. That way, everybody after you will know it's the answer also!
hey there, dont want to keep buggin you, after all it is a saturday night! i ran the code but it wont work properly, it asks for the end } for button2 click to be written on line 338 which is above
List
<OrderLine> myOrder = new
List<OrderLine>();
this section of code, i dont understand, as in the editor it shows the matching end bracket where its supposed to be
Jeremy Huson
Senior Network Engineer
Carden Computers - PC Repair Brighton
jezh
Member
285 Points
510 Posts
Re: reading from one table, writing to another in loop
Nov 17, 2007 10:55 AM|LINK
here is the code
protected void Button2_Click(object sender, EventArgs e) { int intCustIDOID = Convert.ToInt32(Request.QueryString["qsnOrderCustID"]); DateTime DTNowOID = DateTime.Now; string strEmpOID = "Default User"; //Request.QueryString["sessionpass"]; double dblSubTotOID = Convert.ToDouble(lblPGTotExVAT.Text); int intStatus = 1; double dblVATOID = Convert.ToDouble(lblPGVAT.Text); double dblTotOID = Convert.ToDouble(lblPGTotIncVAT.Text); int m = 0; for (m = 1; m <= 11; m++) { if (((TextBox)Panel1.FindControl("txtID" + m.ToString())).Text != "") { if (((TextBox)Panel1.FindControl("txtQty" + m.ToString())).Text != "") { string sConnectionString = "Data Source=xxxx;Initial Catalog=xxxx;User ID=xxxx;Password=xxxx"; SqlConnection objConn = new SqlConnection(sConnectionString); //this gets the order id at the moment its just static int intOrderID = Convert.ToInt32(Session["sNewID"]); //This gets the stock ID from the textbox. string strstock_ID = ((TextBox)Panel1.FindControl("txtID" + m.ToString())).Text; int stock_IDint = Convert.ToInt32(strstock_ID); //this gets the qty from the textbox. string strQtyUpDate = ((TextBox)Panel1.FindControl("txtQty" + m.ToString())).Text; int intQtyUpDate = Convert.ToInt32(strQtyUpDate); //this gets the description from the textbox. string strDesc = ((Label)Panel1.FindControl("lblDesc" + m.ToString())).Text; //this gets the price from the textbox. double dblPrice = Convert.ToDouble(((Label)Panel1.FindControl("lblPrice" + m.ToString())).Text); //this gets the total ex vat from the textbox. double dblTotalExVAT = Convert.ToDouble(((Label)Panel1.FindControl("lblTotal" + m.ToString())).Text); //this gets the vat from the textbox. double dblVAT = Convert.ToDouble(((Label)Panel1.FindControl("lblVAT" + m.ToString())).Text); //this gets the status e.g. taken, collecting, delivery from the textbox. string strToDeliver = ((DropDownList)Panel1.FindControl("ddl" + m.ToString())).Text; //this gets the total cost price from the textbox. double dblCP1 = Convert.ToDouble(((Label)Panel1.FindControl("lblCP" + m.ToString())).Text); double dblCP = dblCP1 * intQtyUpDate; //this gets the condition new or s/h from the textbox. string strNSH = ((Label)Panel1.FindControl("lblNSH" + m.ToString())).Text; //This is the sql statement. objConn.Open(); string sqlUpDate = "INSERT INTO tbl_Part_order(Order_ID, stock_qty, Stock_ID, Price, description, ToDeliever, TotalExVAT, VAT, cost_price, n_or_sh) VALUES" + "(@orderID,@qty,@stockID,@price,@description,@ToDeliever,@TotalExVAT,@VAT,@Cost_price,@NSH)"; SqlCommand objCmd1 = new SqlCommand(sqlUpDate, objConn); objCmd1.Parameters.AddWithValue("@orderID", intOrderID); objCmd1.Parameters.AddWithValue("@qty", intQtyUpDate); objCmd1.Parameters.AddWithValue("@StockID", stock_IDint); objCmd1.Parameters.AddWithValue("@Price", dblPrice); objCmd1.Parameters.AddWithValue("@description", strDesc); objCmd1.Parameters.AddWithValue("@ToDeliever", strToDeliver); objCmd1.Parameters.AddWithValue("@TotalExVAT", dblTotalExVAT); objCmd1.Parameters.AddWithValue("@VAT", dblVAT); objCmd1.Parameters.AddWithValue("@cost_price", dblCP); objCmd1.Parameters.AddWithValue("@NSH", strNSH); objCmd1.ExecuteNonQuery(); lblcomplete2.Text = "yes!!"; objConn.Close(); } } } // end for loop // This loops through the order lines and takes off stock from tbl_stock int n = 0; for (n = 1; n <= 11; n++) { if (((TextBox)Panel1.FindControl("txtID" + n.ToString())).Text != "") { if (((TextBox)Panel1.FindControl("txtQty" + n.ToString())).Text != "") { string sConnectionString = "Data Source=xxxxx.com;Initial Catalog=xxxx;User ID=xxxx;Password=xxxx"; SqlConnection objConn = new SqlConnection(sConnectionString); //This gets the stock ID from the textbox. string strstock_ID = ((TextBox)Panel1.FindControl("txtID" + n.ToString())).Text; int stock_IDint = Convert.ToInt32(strstock_ID); //this gets the qty from the textbox. string strQtyUpDate = ((TextBox)Panel1.FindControl("txtQty" + n.ToString())).Text; int intQtyUpDate = Convert.ToInt32(strQtyUpDate); //This is the sql statement. objConn.Open(); string sqlUpDate = "UPDATE tbl_stock SET qty = qty -" + intQtyUpDate + " WHERE stock_ID = " + stock_IDint; SqlCommand objCmd1 = new SqlCommand(sqlUpDate, objConn); objCmd1.ExecuteNonQuery(); lblcomplete.Text = "yes!"; objConn.Close(); } } } // end for loop // This loops through, see if anything is for delivery, if it is direct us to the delivery page // if not take us to the payment page int p = 0; int intDel = 0; for (p = 1; p <= 11; p++) { if (((TextBox)Panel1.FindControl("txtID" + p.ToString())).Text != "") { if (((DropDownList)Panel1.FindControl("ddl" + p.ToString())).Text == "Delivery") { intDel += intDel + p; } } } // if there are deliverys take use this method if (intDel >= 1) { Response.Redirect("delivery_schedule.aspx?qsnOrderCustID=" + Request.QueryString["qsnOrderCustID"] + "&orderID=" + Convert.ToString(Session["sNewID"])); } else { Response.Redirect("order_pay.aspx?qsnOrderCustID=" + Request.QueryString["qsnOrderCustID"] + "&orderID=" + Convert.ToString(Session["sNewID"])); } ////////////////////////////////////////////////////////beginning of code to read and write code for del ///////////////////////////////////// ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// //////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// // This will hold all the values entered by the Staff Member List myOrder = new List(); // Loop around to fill up all of your orderlines myOrder.Add(new OrderLine((int)Convert.ToInt32(this.txtQty1.Text), Convert.ToInt32(txtID1.Text))); // Call our method to adjust the stock (This would be in Business Layer) myOrder = adjustStock(myOrder); // At this point the tbl_stock_location has been updated // Check to make sure we could fulfill the entire order foreach (OrderLine orln in myOrder) { if (orln._QtySupplied < orln._Qty) { lblE.Text = lblE.Text + (string.Format("We could only supply {0} of productid {1} - we just don't have any more stock<br/><br/>", orln._QtySupplied, orln._ProductID)); } } // Now we can go and update the tbl_del_location foreach (OrderLine orLn in myOrder) { foreach (stockLocationAmount stkAmnt in orLn.locationQtys) { // We can now use these figures to update the other tables. // but here I am just dispalying the information to the screen. updateDelLocation(stkAmnt._ProductID, stkAmnt._LocationID, stkAmnt._QtySupplied); } } // if i move this below then it just dosnt want to compile says its missing a { } // end of button2 click////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// public List adjustStock(List myOrder) { // You could wrap this all in a System.Transaction so that if anything went // wrong all chnages to the database would roll back foreach (OrderLine orln in myOrder) { // Loop through all of the stock locations for (int stockloc = 1; stockloc <= 5; stockloc++) { // Get the stock level at the current stock location // This is another method in our BLL that will get the result from the DAL int stockQty = getStockForProductAtLocation(orln._ProductID, stockloc); if (!(stockQty == 0)) { int actualLocation = getLocationID(orln._ProductID, stockloc); // Check to see if we have enough stock at this location to fulfill // the staff memebers order if (stockQty >= (orln._Qty - orln._QtySupplied)) { // We have got enough stock // Call another BLL method to update the stock - this in turn would call the DAL adjustStockForProductAtLocation(orln._ProductID, stockQty - (orln._Qty - orln._QtySupplied), stockloc); // Update the supplied stock in our OrderLine Object orln._QtySupplied = orln._Qty; // Update the logged on what stock we got and from where orln.locationQtys.Add(new stockLocationAmount(orln._ProductID, actualLocation, stockQty, stockloc)); // No need to keep looping now break; // TODO: might not be correct. Was : Exit For } else { // We don't have enough so minus what we can // Call another BLL method to update the stock - this in turn would call the DAL adjustStockForProductAtLocation(orln._ProductID, stockQty, stockloc); // Update the supplied stock in our OrderLine Object orln._QtySupplied += stockQty; // Update the logged on what stock we got and from where orln.locationQtys.Add(new stockLocationAmount(orln._ProductID, actualLocation, stockQty, stockloc)); } // end of else } } } return myOrder; }I have started to create a method for update tbl_del_location but i was getting a little confused with this
you gave me this line
updateDelLocation(stkAmnt._ProductID, stkAmnt._LocationID, stkAmnt._QtySupplied);
and i started to write my own public int, but i was getting confused by the stkAmnt_LlocationID, how do i write that in my public int UpdateDelLocation ()
do i just use those variables to build my sql? and would it be public void as im not going to be returning anything?
Senior Network Engineer
Carden Computers - PC Repair Brighton
scott@elband...
Star
11346 Points
1865 Posts
Re: reading from one table, writing to another in loop
Nov 17, 2007 11:45 AM|LINK
I am not a C# guy but I think this should be changed to:
public List<OrderLine> adjustStock(List<OrderLine> myOrder )
Also ensure you add in the:
using System.Collections.Generic;
namespace. Also you may need to change:
to
// This will hold all the values entered by the Staff Member
List<OrderLine> myOrder = new List <OrderLine>;
so whats the error you are getting?
jezh
Member
285 Points
510 Posts
Re: reading from one table, writing to another in loop
Nov 17, 2007 12:00 PM|LINK
it wont let me change it to public list as it throws me an error (invalide expression term 'public' and then on list it says 'expected ;')
i dont have an error, what i have is that i cant put the other block of code inside the button press so nothing really happens e.g. the items get added to the list but nothing gets updated or written to etc
Senior Network Engineer
Carden Computers - PC Repair Brighton
scott@elband...
Star
11346 Points
1865 Posts
Re: reading from one table, writing to another in loop
Nov 17, 2007 12:01 PM|LINK
Can you zip up your project and email it to me? It may be easier for me to see whats going wrong using visual studio scott@elbandit.co.uk
jezh
Member
285 Points
510 Posts
Re: reading from one table, writing to another in loop
Nov 17, 2007 02:08 PM|LINK
hi scott, i emailed you the page in question dont know if you have it by now?
Senior Network Engineer
Carden Computers - PC Repair Brighton
scott@elband...
Star
11346 Points
1865 Posts
Re: reading from one table, writing to another in loop
Nov 17, 2007 02:13 PM|LINK
got it , just having a look now
scott@elband...
Star
11346 Points
1865 Posts
Re: reading from one table, writing to another in loop
Nov 17, 2007 02:23 PM|LINK
I have just sent you the code, Iet me know if all is ok
david wendel...
All-Star
15865 Points
2243 Posts
Re: reading from one table, writing to another in loop
Nov 17, 2007 02:29 PM|LINK
It's 44 replies, a week later, and your stress levels are way high, and you're still trying to get this approach to work.
Everyone has to learn this lesson the hard way:
If you take this lesson to heart, you can look back on it (like all of us look back on the time we learned this the hard way) and laugh about it afterwards. :)
If not, expect more of the same stress levels in the future. :(
Best of luck!
jezh
Member
285 Points
510 Posts
Re: reading from one table, writing to another in loop
Nov 17, 2007 08:56 PM|LINK
List
<OrderLine> myOrder = new List<OrderLine>();this section of code, i dont understand, as in the editor it shows the matching end bracket where its supposed to be
Senior Network Engineer
Carden Computers - PC Repair Brighton
scott@elband...
Star
11346 Points
1865 Posts
Re: reading from one table, writing to another in loop
Nov 18, 2007 10:03 AM|LINK
Please email me the code again.