protected void btnAdd_Click(object sender, EventArgs e) { string _ErrorStr = "Connection not openned"; OrderTbl _OrderTbl = new OrderTbl(); OrderHeaderData _OrderHeaderData = Get_dvOrderHeaderData(false); OrderTblData _OrderData = new OrderTblData(); // first summary data _OrderData.CustomerId = _OrderHeaderData.CustomerID; _OrderData.OrderDate = _OrderHeaderData.OrderDate; _OrderData.RoastDate = _OrderHeaderData.RoastDate; _OrderData.RequiredByDate = _OrderHeaderData.RequiredByDate; _OrderData.ToBeDeliveredBy = Convert.ToInt32(_OrderHeaderData.ToBeDeliveredBy); _OrderData.PurchaseOrder = _OrderHeaderData.PurchaseOrder; _OrderData.Confirmed = _OrderHeaderData.Confirmed; _OrderData.InvoiceDone = _OrderHeaderData.InvoiceDone; _OrderData.Done = _OrderHeaderData.Done; _OrderData.PurchaseOrder = _OrderHeaderData.PurchaseOrder; _OrderData.Notes = _OrderHeaderData.Notes; // Now line data TrackerTools _TT = new TrackerTools(); _OrderData.ItemTypeID = Convert.ToInt32(ddlNewItemDesc.SelectedValue); _OrderData.ItemTypeID = _TT.ChangeItemIfGroupToNextItemInGroup(_OrderData.CustomerId, _OrderData.ItemTypeID, _OrderData.RequiredByDate); _OrderData.QuantityOrdered = Convert.ToDouble(tbxNewQuantityOrdered.Text); _OrderData.PackagingID = Convert.ToInt32(ddlNewPackaging.SelectedValue); _ErrorStr = _OrderTbl.InsertNewOrderLine(_OrderData); ltrlStatus.Text = (String.IsNullOrWhiteSpace(_ErrorStr) ? "Item Added" : "Error adding item: " + _ErrorStr); HideNewOrderItemPanel(); }
/* * try * { * _conn.Open(); * if (_cmd.ExecuteNonQuery() > 0) * * * OleDbConnection _conn = new OleDbConnection(_connectionString); * * // add parameters in the order they appear in the update command * OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn); * _cmd.Parameters.Add(new OleDbParameter { Value = ItemTypeID }); * _cmd.Parameters.Add(new OleDbParameter { Value = Math.Round(QuantityOrdered,2) }); * _cmd.Parameters.Add(new OleDbParameter { Value = PackagingID }); * _cmd.Parameters.Add(new OleDbParameter { Value = OrderID }); * * try * { * _conn.Open(); * if (_cmd.ExecuteNonQuery() > 0) * return false; * } * catch (OleDbException ex) * { * string _ErrStr = ex.Message; * return _ErrStr == ""; * } * finally * { * _conn.Close(); * } * * return true; * } */ public bool InsertOrderDetails(Int64 CustomerID, DateTime OrderDate, DateTime RoastDate, Int32 ToBeDeliveredBy, DateTime RequiredByDate, Boolean Confirmed, Boolean Done, String Notes, double QuantityOrdered, Int32 PackagingID, Int32 ItemTypeID) { string _sqlCmd = "INSERT INTO OrdersTbl (CustomerId, OrderDate, RoastDate, RequiredByDate, ToBeDeliveredBy, Confirmed, Done, Notes, " + " ItemTypeID, QuantityOrdered, PackagingID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; //1 2 3 4 5 6 7 8 9 10 11 bool _Success = false; // check if the item is a group item then get the next group. TrackerTools _TT = new TrackerTools(); ItemTypeID = _TT.ChangeItemIfGroupToNextItemInGroup(CustomerID, ItemTypeID, RequiredByDate); TrackerDb _TDB = new TrackerDb(); // first summary data _TDB.AddParams(CustomerID, DbType.Int64, "@CustomerID"); _TDB.AddParams(OrderDate, DbType.Date, "@OrderDate"); _TDB.AddParams(RoastDate, DbType.Date, "@RoastDate"); _TDB.AddParams(RequiredByDate, DbType.Date, "@RequiredByDate"); _TDB.AddParams(ToBeDeliveredBy, DbType.Int32, "@ToBeDeliveredBy"); _TDB.AddParams(Confirmed, DbType.Boolean, "@Confirmed"); _TDB.AddParams(Done, DbType.Boolean, "@Done"); _TDB.AddParams(Notes, DbType.String, "@Notes"); // Now line data _TDB.AddParams(ItemTypeID, DbType.Int32, "@ItemTypeID"); _TDB.AddParams(Math.Round(QuantityOrdered, 2), DbType.Double, "@QuantityOrdered"); _TDB.AddParams(PackagingID, DbType.Int32, "@PackagingID"); _Success = String.IsNullOrEmpty(_TDB.ExecuteNonQuerySQL(_sqlCmd)); _TDB.Close(); return(_Success); }
protected bool AddNewOrderLine(int pNewItemID, double pNewQuantityOrdered, int pNewPackagingID) { string _ErrorStr = ""; OrderTblData _OrderData = new OrderTblData(); OrderTbl _OrderTbl = new OrderTbl(); _OrderData.CustomerId = Convert.ToInt64(ddlContacts.SelectedValue); _OrderData.OrderDate = Convert.ToDateTime(tbxOrderDate.Text).Date; _OrderData.RoastDate = Convert.ToDateTime(tbxRoastDate.Text).Date; _OrderData.RequiredByDate = Convert.ToDateTime(tbxRequiredByDate.Text).Date; _OrderData.ToBeDeliveredBy = Convert.ToInt32(ddlToBeDeliveredBy.SelectedValue); _OrderData.PurchaseOrder = tbxPurchaseOrder.Text; _OrderData.Confirmed = Convert.ToBoolean(cbxConfirmed.Checked); _OrderData.InvoiceDone = cbxInvoiceDone.Checked; _OrderData.Done = Convert.ToBoolean(cbxDone.Checked); _OrderData.Notes = tbxNotes.Text; // Now line data Session[OrderHeaderData.CONST_BOUNDOLDDELIVERYDATE] = _OrderData.RequiredByDate.Date; TrackerTools _TT = new TrackerTools(); _OrderData.ItemTypeID = _TT.ChangeItemIfGroupToNextItemInGroup(_OrderData.CustomerId, pNewItemID, _OrderData.RequiredByDate); _OrderData.QuantityOrdered = pNewQuantityOrdered; _OrderData.PackagingID = pNewPackagingID; _ErrorStr = _OrderTbl.InsertNewOrderLine(_OrderData); BindRowQueryParameters(); return(String.IsNullOrEmpty(_ErrorStr)); }
/* * OleDbConnection _conn = new OleDbConnection(_connectionString); * // custoemr ZZname = 9 is a general customer name so condition must be to that * string _sqlCmd = "SELECT [ItemTypeID], [QuantityOrdered], [PackagingID], [OrderID] FROM [OrdersTbl] WHERE "; * if (CustomerId == 9) * _sqlCmd += "([CustomerId] = 9) AND ([RequiredByDate] = ?) AND ([Notes] = ?)"; * else * _sqlCmd += "([CustomerId] = ?) AND ([RequiredByDate] = ?)"; * // attach the command * OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn); * if (CustomerId == 9) * { * _cmd.Parameters.Add(new OleDbParameter { Value = DeliveryDate }); * _cmd.Parameters.Add(new OleDbParameter { Value = Notes }); * } * else * { * _cmd.Parameters.Add(new OleDbParameter { Value = CustomerId }); * _cmd.Parameters.Add(new OleDbParameter { Value = DeliveryDate }); * } * try * { * _conn.Open(); * OleDbDataReader _DataReader = _cmd.ExecuteReader(); * while (_DataReader.Read()) * { * OrderDetailData od = new OrderDetailData(); * * od.ItemTypeID = (_DataReader["ItemTypeID"] == DBNull.Value) ? 0 : (Int32)_DataReader["ItemTypeID"]; * od.PackagingID = (_DataReader["PackagingID"] == DBNull.Value) ? 0 : (Int32)_DataReader["PackagingID"]; * od.OrderID = (Int32)_DataReader["OrderId"]; // this is the PK cannot be null * od.QuantityOrdered = (_DataReader["QuantityOrdered"] == DBNull.Value) ? 1 : Math.Round(Convert.ToDouble(_DataReader["QuantityOrdered"]),2); * * oDetails.Add(od); * } * _DataReader.Close(); * } * catch (Exception _ex) * { * lastError = _ex.Message; * } * finally * { * _conn.Close(); * } * return oDetails; * } */ /// <summary> /// Update Order Details, using the orderID update the line info. /// </summary> /// <param name="ItemTypeID"></param> /// <param name="QuantityOrdered"></param> /// <param name="PackagingID"></param> /// <param name="OrderID"></param> /// <returns></returns> public bool UpdateOrderDetails(Int64 OrderID, Int64 CustomerID, Int32 ItemTypeID, DateTime DeliveryDate, double QuantityOrdered, Int32 PackagingID) { bool _Success = false; string _sqlCmd = "UPDATE OrdersTbl SET ItemTypeID = ?, QuantityOrdered = ?, PackagingID = ? WHERE (OrderId = ?)"; TrackerDb _TDB = new TrackerDb(); // check if the item is a group item then get the next group. TrackerTools _TT = new TrackerTools(); ItemTypeID = _TT.ChangeItemIfGroupToNextItemInGroup(CustomerID, ItemTypeID, DeliveryDate); _TDB.AddParams(ItemTypeID, DbType.Int32, "@ItemTypeID"); _TDB.AddParams(Math.Round(QuantityOrdered, 2), DbType.Double, "@QuantityOrdered"); _TDB.AddParams(PackagingID, DbType.Int32, "@PackagingID"); _TDB.AddWhereParams(OrderID, DbType.Int64, "@OrderID"); _Success = String.IsNullOrEmpty(_TDB.ExecuteNonQuerySQL(_sqlCmd)); _TDB.Close(); return(_Success); }