public bool insertWithDataID(historyBLL u) { bool isSuccess = false; SqlConnection conn = new SqlConnection(myconnstrng); try { String sql = @"INSERT INTO tbl_history (" + HistoryDate + "," + HistoryBy + "," + HistoryAction + "," + HistoryDetail + "," + HistoryPageName + "," + HistoryDataID + ") VALUES " + "(@history_date," + "@history_by," + "@history_action," + "@history_detail," + "@page_name," + "@data_id)"; SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddWithValue("@history_date", u.history_date); cmd.Parameters.AddWithValue("@history_by", u.history_by); cmd.Parameters.AddWithValue("@history_action", u.history_action); cmd.Parameters.AddWithValue("@history_detail", u.history_detail); cmd.Parameters.AddWithValue("@page_name", u.page_name); cmd.Parameters.AddWithValue("@data_id", u.data_id); conn.Open(); int rows = cmd.ExecuteNonQuery(); //if the query is executed successfully then the rows' value = 0 if (rows > 0) { //query successful isSuccess = true; } else { //Query falled isSuccess = false; } } catch (Exception ex) { Module.Tool tool = new Module.Tool(); tool.saveToText(ex); } finally { conn.Close(); } return(isSuccess); }
public bool ChangeDataID(historyBLL u, string oldDataID) { bool isSuccess = false; SqlConnection conn = new SqlConnection(myconnstrng); try { String sql = @"UPDATE tbl_history SET " + HistoryDataID + "=@data_id" + " WHERE data_id=@oldDataID AND page_name =@page_name"; SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddWithValue("@oldDataID", oldDataID); cmd.Parameters.AddWithValue("@data_id", u.data_id); cmd.Parameters.AddWithValue("@page_name", u.page_name); conn.Open(); int rows = cmd.ExecuteNonQuery(); //if the query is executed successfully then the rows' value = 0 if (rows > 0) { //query successful isSuccess = true; } else { //Query falled isSuccess = false; } } catch (Exception ex) { Module.Tool tool = new Module.Tool(); tool.saveToText(ex); } finally { conn.Close(); } return(isSuccess); }
private void btnAdd_Click(object sender, EventArgs e) { if (Validation()) { //get customer table code int customer_tbl_code = 0; string customerFullName = cmbCustomer.Text; string shortName = ""; string PONo = txtPONo.Text; int po_code = 0; DateTime updatedDate = DateTime.Now; int userID = MainDashboard.USER_ID; foreach (DataRow row in dt_CustomerList.Rows) { if (row[dalData.FullName].ToString() == customerFullName) { customer_tbl_code = Convert.ToInt32(row[dalData.TableCode].ToString()); shortName = row[dalData.ShortName].ToString(); break; } } uData.PO_date = dtpPODate.Value; uData.Customer_tbl_code = customer_tbl_code; uData.PO_no = txtPONo.Text; uData.Address_1 = txtAddress1.Text.ToUpper(); uData.Address_2 = txtAddress2.Text.ToUpper(); uData.Address_City = txtCity.Text.ToUpper(); uData.Address_State = txtState.Text.ToUpper(); uData.Address_Country = txtCountry.Text.ToUpper(); uData.Address_Postal_Code = txtPostalCode.Text.ToUpper(); uData.DefaultShippingAddress = false; if (cbUseDefaultAddress.Checked) { uData.DefaultShippingAddress = true; } uData.IsRemoved = false; uData.Updated_Date = updatedDate; uData.Updated_By = userID; if (poEditing) { uData.PO_code = Convert.ToInt32(EditingPOCode); po_code = Convert.ToInt32(EditingPOCode); //dalData.PODelete(uData); //remove old one } else { //get po code DataTable dt = dalData.POSelect(); if (dt.Rows.Count <= 0) { po_code = -1; } else { DataView dv = dt.DefaultView; dv.Sort = dalData.POCode + " desc"; DataTable sortedDT = dv.ToTable(); po_code = Convert.ToInt32(sortedDT.Rows[0][dalData.POCode].ToString()) + 1; } uData.PO_code = po_code; } if (po_code != -1) { bool failedToInsert = false; foreach (DataRow row in dt_OrderList.Rows) { string itemCode = row[header_Code].ToString(); uData.IsRemoved = false; if (!string.IsNullOrEmpty(itemCode)) { int po_qty = Convert.ToInt32(row[header_OrderQty]); string note = row[header_Note].ToString(); string dataMode = row[header_DataMode].ToString(); string tblCode = row[header_TblCode].ToString(); uData.Item_code = itemCode; uData.PO_qty = po_qty; uData.PO_note = note; if (dataMode == text_New) { if (!dalData.InsertPO(uData)) { MessageBox.Show("Unable to insert/update " + itemCode + " PO data!"); failedToInsert = true; } } else if (dataMode == text_ToRemove) { uData.IsRemoved = true; uData.Table_Code = Convert.ToInt32(tblCode); if (!dalData.PORemove(uData)) { MessageBox.Show("Unable to remove " + itemCode + " PO data!"); failedToInsert = true; } } else if (dataMode == text_ToEdit) { uData.Table_Code = Convert.ToInt32(tblCode); if (!dalData.POUpdate(uData)) { MessageBox.Show("Unable to update " + itemCode + " PO data!"); failedToInsert = true; } } } } if (!failedToInsert) { MessageBox.Show("PO inserted/updated!"); if (poEditing) { poEdited = true; tool.historyRecord(text.PO_Edited, text.GetPONumberAndCustomer(PONo, shortName), updatedDate, userID, dalData.POTableName, po_code); } else { historyDAL dalHistory = new historyDAL(); historyBLL uHistory = new historyBLL(); uHistory.page_name = dalData.POTableName; uHistory.data_id = -1; dalHistory.ChangeDataID(uHistory, po_code.ToString()); tool.historyRecord(text.PO_Added, text.GetPONumberAndCustomer(PONo, shortName), updatedDate, userID, dalData.POTableName, po_code); } Close(); } } else { MessageBox.Show("P/O code error(-1), please try again."); } } }