private void GetProductInfo() { try { SQLConn.sqL = "SELECT ProductCode, Description, UnitPrice, StocksOnHand FROM Product WHERE ProductNo =" + productID + ""; SQLConn.ConnDB(); SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn); SQLConn.dr = SQLConn.cmd.ExecuteReader(); if (SQLConn.dr.Read() == true) { lblProductCode.Text = SQLConn.dr[0].ToString(); lblDescription.Text = SQLConn.dr[1].ToString(); lblPrice.Text = Strings.FormatNumber(SQLConn.dr[2]).ToString(); lblCurrentStocks.Text = SQLConn.dr[3].ToString(); } } catch (Exception ex) { Interaction.MsgBox(ex.ToString()); } finally { SQLConn.cmd.Dispose(); SQLConn.conn.Close(); } }
private bool IsAdding() { bool ret = false; try { SQLConn.sqL = "SELECT * FROM Company"; SQLConn.ConnDB(); SQLConn.cmd = new SqlCommand(SQLConn.sqL, SQLConn.conn); SQLConn.dr = SQLConn.cmd.ExecuteReader(); if (SQLConn.dr.Read() == true) { ret = false; } else { ret = true; } } catch (Exception ex) { Interaction.MsgBox(ex.ToString()); } finally { SQLConn.cmd.Dispose(); SQLConn.conn.Close(); } return(ret); }
private void GetProductNo() { try { SQLConn.sqL = "SELECT ProductNo FROM Product ORDER BY ProductNo DESC"; SQLConn.ConnDB(); SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn); SQLConn.dr = SQLConn.cmd.ExecuteReader(); if (SQLConn.dr.Read() == true) { lblProductNo.Text = (Convert.ToInt32(SQLConn.dr["ProductNo"]) + 1).ToString(); } else { lblProductNo.Text = "1"; } } catch (Exception ex) { Interaction.MsgBox(ex.ToString()); } finally { SQLConn.cmd.Dispose(); SQLConn.conn.Close(); } }
private void LoadInvoices() { dgwInvoice.Visible = true; try { SQLConn.sqL = "SELECT TDate, InvoiceNo FROM Transactions WHERE TDate = '" + DateTime.Now.ToString("MM/dd/yyyy") + "' ORDER BY InvoiceNo Desc"; SQLConn.ConnDB(); SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn); SQLConn.dr = SQLConn.cmd.ExecuteReader(); dgwInvoice.Rows.Clear(); while (SQLConn.dr.Read() == true) { dgwInvoice.Rows.Add(SQLConn.dr[0].ToString(), SQLConn.dr[1].ToString()); } } catch (Exception ex) { Interaction.MsgBox(ex.Message); } finally { SQLConn.cmd.Dispose(); SQLConn.conn.Close(); } }
private void LoadReport() { try { SQLConn.sqL = "SELECT ReturnDate as DateReturn, SR.InvoiceNo, TotalAmount as AmountRefund, SUM(SRI.Quantity) as ItemQuantity, P.UnitPrice as ItemPrice, (SUM(SRI.Quantity) * P.UnitPrice) as ExtendedPrice, Description as Product, QtyTotal.TotQuantity as TotalItemReturn FROM SalesReturn as SR INNER JOIN SalesReturnItem SRI ON SR.InvoiceNo =SRI.InvoiceNo INNER JOIN Product P ON P.ProductNo = SRI.ProductID INNER JOIN (SELECT SUM(Quantity) as TotQuantity, InvoiceNo FROM SalesReturnItem GROUP BY InvoiceNo ) QtyTotal ON QtyTotal.InvoiceNo = SR.InvoiceNo WHERE ReturnDate BETWEEN '" + StartDate.ToString("yyyy-MM-dd") + "' AND '" + EndDate.ToString("yyyy-MM-dd") + "' GROUP BY P.ProductNo, SR.InvoiceNo ORDER By ReturnDate, SR.InvoiceNo"; SQLConn.ConnDB(); SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn); SQLConn.da = new MySqlDataAdapter(SQLConn.cmd); this.dsReportC.Return.Clear(); SQLConn.da.Fill(this.dsReportC.Return); ReportParameter startDate = new ReportParameter("StartDate", StartDate.ToString()); ReportParameter endDate = new ReportParameter("EndDate", EndDate.ToString()); this.reportViewer1.LocalReport.SetParameters(new ReportParameter[] { startDate, endDate }); this.reportViewer1.SetDisplayMode(DisplayMode.PrintLayout); this.reportViewer1.ZoomPercent = 90; this.reportViewer1.ZoomMode = Microsoft.Reporting.WinForms.ZoomMode.Percent; this.reportViewer1.RefreshReport(); } catch (Exception ex) { Interaction.MsgBox(ex.ToString()); } }
private void getStaffInfo() { try { SQLConn.sqL = "SELECT CONCAT(lastname, ', ', Firstname, ' ', MI) as StaffName, Role FROM Staff WHERE StaffID = '" + lblStaffID.Text + "'"; SQLConn.ConnDB(); SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn); SQLConn.dr = SQLConn.cmd.ExecuteReader(); if (SQLConn.dr.Read() == true) { lblPosition.Text = SQLConn.dr["Role"].ToString(); lblCustName.Text = SQLConn.dr["Staffname"].ToString(); } } catch (Exception ex) { Interaction.MsgBox(ex.Message); } finally { SQLConn.cmd.Dispose(); SQLConn.conn.Close(); } }
public void GetInvoiceNo() { try { SQLConn.sqL = "SELECT InvoiceNo FROM Transactions ORDER BY InvoiceNO DESC"; SQLConn.ConnDB(); SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn); SQLConn.dr = SQLConn.cmd.ExecuteReader(); if (SQLConn.dr.Read() == true) { lblInvoice.Text = (Convert.ToInt32(SQLConn.dr["InvoiceNo"]) + 1).ToString(); } else { lblInvoice.Text = "100100000"; } } catch (Exception ex) { Interaction.MsgBox(ex.ToString()); } finally { SQLConn.cmd.Dispose(); SQLConn.conn.Close(); } }
private void LoadUpdateCategory() { try { SQLConn.sqL = "SELECT * FROM Category WHERE CategoryNo = '" + categoryID + "'"; SQLConn.ConnDB(); SQLConn.cmd = new SqlCommand(SQLConn.sqL, SQLConn.conn); SQLConn.dr = SQLConn.cmd.ExecuteReader(); if (SQLConn.dr.Read() == true) { lblCategoryNo.Text = SQLConn.dr["CategoryNo"].ToString(); txtCatName.Text = SQLConn.dr["CategoryName"].ToString(); txtDescription.Text = SQLConn.dr["Description"].ToString(); } } catch (Exception ex) { Interaction.MsgBox(ex.ToString()); } finally { SQLConn.cmd.Dispose(); SQLConn.conn.Close(); } }
private void AddCustomer() { try { if (txtDue.Text == "" || txtComments.Text == "" || txtRemaining.Text == "" || txtShop.Text == "") { Interaction.MsgBox("Please Enter All Required Business Fields", MsgBoxStyle.Information, "Incomplete Form Submission"); } else { SQLConn.sqL = "INSERT INTO customer(Lastname, Firstname, CNIC, Street, City, Province, ContactNo, DuePayment, CommentOnDuePayment, RemainingBalance, shopname, mobileNo) VALUES('" + txtLastname.Text + "', '" + txtFirstname.Text + "', '" + txtCNIC.Text + "', '" + txtStreet.Text + "', '" + txtCity.Text + "', '" + txtProvince.Text + "', '" + txtContractNo.Text + "', '" + txtDue.Text + "', '" + txtComments.Text + "', '" + txtRemaining.Text + "', '" + txtShop.Text + "', '" + textBox1.Text + "')"; SQLConn.ConnDB(); SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn); SQLConn.cmd.ExecuteNonQuery(); Interaction.MsgBox("New Customer successfully added.", MsgBoxStyle.Information, "Add Customer"); this.Close(); } } catch (Exception ex) { Interaction.MsgBox(ex.ToString()); } finally { SQLConn.cmd.Dispose(); SQLConn.conn.Close(); } }
public void LoadInvoiceDetail(string search) { try { SQLConn.sqL = "SELECT description_product,ProductNo,ItemPrice,Quantity,ProductName,InvoiceNo FROM transactiondetails WHERE InvoiceNo LIKE '" + search.Trim() + "%' ORDER By InvoiceNo"; SQLConn.ConnDB(); SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn); SQLConn.dr = SQLConn.cmd.ExecuteReader(); ListViewItem x = null; ListView1.Items.Clear(); while (SQLConn.dr.Read() == true) { x = new ListViewItem(SQLConn.dr["ProductNo"].ToString()); x.SubItems.Add(SQLConn.dr["ProductName"].ToString()); x.SubItems.Add(SQLConn.dr["ItemPrice"].ToString()); x.SubItems.Add(SQLConn.dr["Quantity"].ToString()); x.SubItems.Add(SQLConn.dr["InvoiceNo"].ToString()); x.SubItems.Add(SQLConn.dr["description_product"].ToString()); ListView1.Items.Add(x); } } catch (Exception ex) { Interaction.MsgBox(ex.Message); } finally { SQLConn.cmd.Dispose(); SQLConn.conn.Close(); } }
private bool IsPasswordCorrect() { bool ret = false; try { SQLConn.sqL = "SELECT * FROM Staff WHERE Role ='Admin' AND UPassword = '******'"; SQLConn.ConnDB(); SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn); SQLConn.dr = SQLConn.cmd.ExecuteReader(); if (SQLConn.dr.Read() == true) { ret = true; } } catch (Exception ex) { Interaction.MsgBox(ex.Message); } finally { SQLConn.cmd.Dispose(); SQLConn.conn.Close(); } return(ret); }
public void LoadStaffs(string search) { try { SQLConn.sqL = "SELECT StaffID, CONCAT(Lastname, ', ', Firstname, ' ', MI) as ClientName, CONCAT(Street, ', ', Barangay, ', ', City , ', ', Province) as Address, ContactNo, username, role FROM Staff WHERE LASTNAME LIKE '" + search.Trim() + "%' ORDER By Lastname"; SQLConn.ConnDB(); SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn); SQLConn.dr = SQLConn.cmd.ExecuteReader(); ListViewItem x = null; ListView1.Items.Clear(); while (SQLConn.dr.Read() == true) { x = new ListViewItem(SQLConn.dr["StaffId"].ToString()); x.SubItems.Add(SQLConn.dr["ClientName"].ToString()); x.SubItems.Add(SQLConn.dr["ContactNo"].ToString()); x.SubItems.Add(SQLConn.dr["Address"].ToString()); x.SubItems.Add(SQLConn.dr["username"].ToString()); x.SubItems.Add(SQLConn.dr["Role"].ToString()); ListView1.Items.Add(x); } } catch (Exception ex) { Interaction.MsgBox(ex.Message); } finally { SQLConn.cmd.Dispose(); SQLConn.conn.Close(); } }
public string GetInvoiceNo() { string ret = ""; try { SQLConn.sqL = "SELECT Id FROM Invoice ORDER BY Id DESC"; SQLConn.ConnDB(); SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn); SQLConn.dr = SQLConn.cmd.ExecuteReader(); if (SQLConn.dr.Read() == true) { ret = "INV-000-" + (Convert.ToInt32(SQLConn.dr["Id"]) + 1).ToString(); } } catch (Exception ex) { Interaction.MsgBox(ex.ToString()); } finally { SQLConn.cmd.Dispose(); SQLConn.conn.Close(); } return(ret); }
public void SalesReturns(DateTime startDate, DateTime endDate, string searchString) { try { SQLConn.sqL = "SELECT InvoiceDate, InvoiceNo, Customer, SalesPerson, TotalAmount FROM Invoice WHERE InvoiceDate BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.ToString("yyyy-MM-dd") + "' AND (InvoiceNo LIKE '%" + txtSearch.Text + "%' OR SalesPerson LIKE '%" + txtSearch.Text + "%' OR Customer LIKE '%" + txtSearch.Text + "%') GROUP BY InvoiceNo ORDER BY InvoiceDate, InvoiceNo DESC"; SQLConn.ConnDB(); SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn); SQLConn.dr = SQLConn.cmd.ExecuteReader(); dgw.Rows.Clear(); while (SQLConn.dr.Read() == true) { dgw.Rows.Add(Convert.ToDateTime(SQLConn.dr[0]).ToString("MM/dd/yyyy"), SQLConn.dr[1].ToString(), SQLConn.dr[2].ToString(), SQLConn.dr[3].ToString(), Strings.FormatNumber(SQLConn.dr[4]).ToString(), Strings.FormatNumber(SQLConn.dr[4]).ToString()); } } catch (Exception ex) { Interaction.MsgBox(ex.ToString()); } finally { SQLConn.cmd.Dispose(); SQLConn.conn.Close(); } }
public void AddTransactionDetails() { try { SQLConn.sqL = "INSERT INTO TransactionDetails(InvoiceNo, ProductNo, ItemPrice, Quantity, Discount) VALUES(@InvoiceNo, @ProductNo, @ItemPrice, @Quantity, @Discount)"; SQLConn.ConnDB(); SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn); SQLConn.cmd.Parameters.Add("InvoiceNo", MySql.Data.MySqlClient.MySqlDbType.VarChar); SQLConn.cmd.Parameters.Add("ProductNo", MySql.Data.MySqlClient.MySqlDbType.Int32); SQLConn.cmd.Parameters.Add("ItemPrice", MySql.Data.MySqlClient.MySqlDbType.Double); SQLConn.cmd.Parameters.Add("Quantity", MySql.Data.MySqlClient.MySqlDbType.Double); SQLConn.cmd.Parameters.Add("Discount", MySql.Data.MySqlClient.MySqlDbType.Double); for (int i = 0; i <= dgw.Rows.Count - 1; i++) { SQLConn.cmd.Parameters["InvoiceNo"].Value = lblInvoice.Text; SQLConn.cmd.Parameters["ProductNo"].Value = dgw.Rows[i].Cells[0].Value; SQLConn.cmd.Parameters["ItemPrice"].Value = Convert.ToDouble(dgw.Rows[i].Cells[3].Value.ToString().Replace(",", "")); SQLConn.cmd.Parameters["Quantity"].Value = Convert.ToDouble(dgw.Rows[i].Cells[4].Value); SQLConn.cmd.Parameters["Discount"].Value = Convert.ToDouble(dgw.Rows[i].Cells[6].Value.ToString().Replace(",", "")); SQLConn.cmd.ExecuteNonQuery(); } } catch (Exception ex) { Interaction.MsgBox(ex.ToString()); } finally { SQLConn.cmd.Dispose(); SQLConn.conn.Close(); } }
private void LoadCategory() { try { SQLConn.sqL = "SELECT * FROM Product WHERE ProductCode LIKE '" + txtCatName.Text + "%' ORDER BY ProductCode "; SQLConn.ConnDB(); SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn); SQLConn.dr = SQLConn.cmd.ExecuteReader(); ListViewItem x = null; ListView1.Items.Clear(); while (SQLConn.dr.Read() == true) { x = new ListViewItem(SQLConn.dr["ProductNo"].ToString()); x.SubItems.Add(SQLConn.dr["ProductCode"].ToString()); x.SubItems.Add(SQLConn.dr["UnitPrice"].ToString()); x.SubItems.Add(SQLConn.dr["Description"].ToString()); ListView1.Items.Add(x); } } catch (Exception ex) { Interaction.MsgBox(ex.ToString()); } finally { SQLConn.cmd.Dispose(); SQLConn.conn.Close(); } }
//Decrease product's stocks on hand public void UpdateProductQuantity() { try { SQLConn.sqL = "UPDATE Product SET StocksOnHand = StocksOnHand - @Quantity WHERE ProductNo = @ProductNo"; SQLConn.ConnDB(); SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn); SQLConn.cmd.Parameters.Add("ProductNo", MySql.Data.MySqlClient.MySqlDbType.Int32); SQLConn.cmd.Parameters.Add("Quantity", MySql.Data.MySqlClient.MySqlDbType.Double); for (int i = 0; i <= dgw.Rows.Count - 1; i++) { SQLConn.cmd.Parameters["ProductNo"].Value = dgw.Rows[i].Cells[0].Value; SQLConn.cmd.Parameters["Quantity"].Value = Convert.ToDouble(dgw.Rows[i].Cells[4].Value); SQLConn.cmd.ExecuteNonQuery(); } } catch (Exception ex) { Interaction.MsgBox(ex.ToString()); } finally { SQLConn.cmd.Dispose(); SQLConn.conn.Close(); } }
private void LoadCustomer() { try { SQLConn.sqL = "SELECT * FROM customer WHERE Firstname LIKE '" + textBox1.Text + "%' ORDER BY Firstname "; SQLConn.ConnDB(); SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn); SQLConn.dr = SQLConn.cmd.ExecuteReader(); ListViewItem x = null; listView3.Items.Clear(); while (SQLConn.dr.Read() == true) { x = new ListViewItem(SQLConn.dr["CusID"].ToString()); x.SubItems.Add(SQLConn.dr["Firstname"].ToString()); x.SubItems.Add(SQLConn.dr["DuePayment"].ToString()); x.SubItems.Add(SQLConn.dr["RemainingBalance"].ToString()); listView3.Items.Add(x); } } catch (Exception ex) { Interaction.MsgBox(ex.ToString()); } finally { SQLConn.cmd.Dispose(); SQLConn.conn.Close(); } }
public void GetBarcodeForAutoComplete() { try { SQLConn.sqL = "SELECT CONCAT(Barcode,' - ', TRIM(Description),' => P', FORMAT(UnitPrice,2)) as Barcode FROM Product Order By Barcode"; SQLConn.ConnDB(); SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn); DataSet ds = new DataSet(); MySqlDataAdapter da = new MySqlDataAdapter(SQLConn.cmd); da.Fill(ds, "list"); var source = new List <string>(); for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++) { source.Add(ds.Tables[0].Rows[i]["Barcode"].ToString()); } this.txtBarcode.AutoCompleteList = source; } catch (Exception ex) { Interaction.MsgBox(ex.Message); } finally { SQLConn.cmd.Dispose(); SQLConn.conn.Close(); } }
private void LoadInvoices(DateTime startDate, DateTime endDate, string searchString) { try { SQLConn.sqL = "SELECT STR_TO_DATE(REPLACE(TDate, '-', '/'), '%m/%d/%Y') as TDate, InvoiceNo, CONCAT(lastname, ', ', firstname, ' ', MI) as StaffName FROM Transactions T INNER JOIN staff S ON T.StaffID = S.StaffID WHERE STR_TO_DATE(REPLACE(TDATE, '-', '/'), '%m/%d/%Y') BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.ToString("yyyy-MM-dd") + "' AND InvoiceNo LIKE '%" + txtName.Text + "%' ORDER BY TDATE, InvoiceNo Desc"; SQLConn.ConnDB(); SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn); SQLConn.dr = SQLConn.cmd.ExecuteReader(); ListViewItem x = null; ListView1.Items.Clear(); while (SQLConn.dr.Read() == true) { x = new ListViewItem(Strings.Format(SQLConn.dr["TDate"], "MM/dd/yyyy")); x.SubItems.Add(SQLConn.dr["InvoiceNo"].ToString()); x.SubItems.Add(SQLConn.dr["StaffName"].ToString()); ListView1.Items.Add(x); } } catch (Exception ex) { Interaction.MsgBox(ex.ToString()); } finally { SQLConn.cmd.Dispose(); SQLConn.conn.Close(); } }
private int GetProductIDByBarcode(string barcode) { int ret = 0; try { SQLConn.sqL = "SELECT ProductNo FROM Product WHERE barcode = '" + barcode + "'"; SQLConn.ConnDB(); SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn); SQLConn.dr = SQLConn.cmd.ExecuteReader(); if (SQLConn.dr.Read() == true) { ret = Convert.ToInt32(SQLConn.dr[0]); } } catch (Exception ex) { Interaction.MsgBox(ex.Message); } finally { SQLConn.cmd.Dispose(); SQLConn.conn.Close(); } return(ret); }
private bool checkQuantity(int quantity) { try { SQLConn.sqL = "SELECT StocksOnHand FROM product WHERE ProductCode = '" + Productname + "'"; SQLConn.ConnDB(); SQLConn.cmd = new SqlCommand(SQLConn.sqL, SQLConn.conn); SQLConn.dr = SQLConn.cmd.ExecuteReader(); if (SQLConn.dr.Read() == true) { int dbquantity = int.Parse(SQLConn.dr["StocksOnHand"].ToString()); if (dbquantity - quantity > 0) { return(true); } return(false); } else { //MessageBox.Show("Invalid Password. Please try again."); } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { SQLConn.cmd.Dispose(); SQLConn.conn.Close(); } return(false); }
private void GetCustomerInformation() { try { SQLConn.sqL = "SELECT Id, Lastname, Firstname, ContactNo, Address FROM customer WHERE id = '" + customerID + "'"; SQLConn.ConnDB(); SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn); SQLConn.dr = SQLConn.cmd.ExecuteReader(); if (SQLConn.dr.Read() == true) { txtLastname.Tag = SQLConn.dr[0].ToString(); txtLastname.Text = SQLConn.dr[1].ToString(); txtFirstname.Text = SQLConn.dr[2].ToString(); txtContactno.Text = SQLConn.dr[3].ToString(); txtAddress.Text = SQLConn.dr[4].ToString(); } } catch (Exception ex) { Interaction.MsgBox(ex.ToString()); } finally { SQLConn.cmd.Dispose(); SQLConn.conn.Close(); } }
public void UpdateProductQuantity() { foreach (DataGridViewRow row in dataGridView1.Rows) { try { SQLConn.sqL = "select StocksOnHand from product where Barcode = '" + row.Cells["Code"].Value.ToString() + "'"; SQLConn.ConnDB(); SQLConn.cmd = new SqlCommand(SQLConn.sqL, SQLConn.conn); SQLConn.dr = SQLConn.cmd.ExecuteReader(); if (SQLConn.dr.Read() == true) { int stocks = int.Parse(SQLConn.dr["StocksOnHand"].ToString()); SQLConn.sqL = "update product SET StocksOnHand = @stocks where Barcode = '" + row.Cells["Code"].Value.ToString() + "'"; SQLConn.ConnDB(); SQLConn.cmd = new SqlCommand(SQLConn.sqL, SQLConn.conn); SQLConn.cmd.Parameters.AddWithValue("@stocks", (stocks - int.Parse(row.Cells["Quantity"].Value.ToString()))); SQLConn.cmd.ExecuteNonQuery(); } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { SQLConn.cmd.Dispose(); SQLConn.conn.Close(); } } dataGridView1.Rows.Clear(); dataGridView1.Refresh(); }
public void GetCompanyInfo() { try { SQLConn.sqL = "SELECT CompanyID, Name, Address, PhoneNo, Email, Website, TINNumber FROM Company"; SQLConn.ConnDB(); SQLConn.cmd = new SqlCommand(SQLConn.sqL, SQLConn.conn); SQLConn.dr = SQLConn.cmd.ExecuteReader(); if (SQLConn.dr.Read()) { txtName.Tag = SQLConn.dr[0].ToString(); txtName.Text = SQLConn.dr[1].ToString(); txtAddress.Text = SQLConn.dr[2].ToString(); txtPhoneNo.Text = SQLConn.dr[3].ToString(); txtEmail.Text = SQLConn.dr[4].ToString(); txtWebsite.Text = SQLConn.dr[5].ToString(); txtTINNumber.Text = SQLConn.dr[6].ToString(); } } catch (Exception ex) { Interaction.MsgBox(ex.ToString()); } finally { SQLConn.cmd.Dispose(); SQLConn.conn.Close(); } }
private void getInvoiceNo() { try { SQLConn.sqL = "SELECT TOP 1 InvoiceNo FROM transactions ORDER BY InvoiceNo DESC"; SQLConn.ConnDB(); SQLConn.cmd = new SqlCommand(SQLConn.sqL, SQLConn.conn); SQLConn.dr = SQLConn.cmd.ExecuteReader(); if (SQLConn.dr.Read() == true) { InvoiceNo = int.Parse(SQLConn.dr["InvoiceNo"].ToString()) + 1; invoiceno.Text = "Invoice No: " + InvoiceNo.ToString(); } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { SQLConn.cmd.Dispose(); SQLConn.conn.Close(); } }
public void GetVATInfo() { try { SQLConn.sqL = "SELECT VATID, VatPercent FROM VatSetting"; SQLConn.ConnDB(); SQLConn.cmd = new SqlCommand(SQLConn.sqL, SQLConn.conn); SQLConn.dr = SQLConn.cmd.ExecuteReader(); if (SQLConn.dr.Read()) { txtPercent.Tag = SQLConn.dr[0]; txtPercent.Text = SQLConn.dr[1].ToString(); isAddingVat = false; } else { isAddingVat = true; } } catch (Exception ex) { Interaction.MsgBox(ex.ToString()); } finally { SQLConn.cmd.Dispose(); SQLConn.conn.Close(); } }
private bool IsValidQuantity(double EnteredQuantity) { bool ret = false; try { SQLConn.sqL = "SELECT * FROM PRODUCT WHERE StocksOnHand >= " + EnteredQuantity + ""; SQLConn.ConnDB(); SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn); SQLConn.dr = SQLConn.cmd.ExecuteReader(); if (SQLConn.dr.Read() == true) { ret = true; } else { ret = false; } } catch (Exception ex) { Interaction.MsgBox(ex.ToString()); } finally { SQLConn.cmd.Dispose(); SQLConn.conn.Close(); } return(ret); }
private void AddProducts() { string barcode = ""; if (txtBarcode.Text.Trim() == "") { barcode = "NO BARCODE"; } else { barcode = txtBarcode.Text; } try { SQLConn.sqL = "INSERT INTO Product(ProductCode, Description, Barcode, UnitPrice, StocksOnHand, ReorderLevel, CategoryNo) VALUES('" + txtProductCode.Text + "', '" + txtDescription.Text + "', '" + barcode + "', '" + txtUnitPrice.Text.Replace(",", "") + "', '" + txtStocksOnHand.Text.Replace(",", "") + "', '" + txtReorderLevel.Text + "', '" + categoryID + "')"; SQLConn.ConnDB(); SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn); SQLConn.cmd.ExecuteNonQuery(); Interaction.MsgBox("Product successfully added.", MsgBoxStyle.Information, "Add Product"); AddStockIn(); } catch (Exception ex) { Interaction.MsgBox(ex.Message); } finally { SQLConn.cmd.Dispose(); SQLConn.conn.Close(); } }
private void AddStaff() { try { if (txtUsername.Text == "" || txtPassword.Text == "" || txtRole.Text == "" || txtConfirmPWD.Text == "") { Interaction.MsgBox("Please Enter All Required User Fields", MsgBoxStyle.Information, "Incomplete Form Submission"); } else { SQLConn.sqL = "INSERT INTO STAFF(Lastname, Firstname, Street, City, Province, ContactNo, Username, Role, UPassword,mobileNo) VALUES('" + txtLastname.Text + "', '" + txtFirstname.Text + "', '" + txtStreet.Text + "', '" + txtCity.Text + "', '" + txtProvince.Text + "', '" + txtContractNo.Text + "', '" + txtUsername.Text + "', '" + txtRole.Text + "', '" + txtPassword.Text + "', '" + textBox1.Text + "')"; SQLConn.ConnDB(); SQLConn.cmd = new MySqlCommand(SQLConn.sqL, SQLConn.conn); SQLConn.cmd.ExecuteNonQuery(); Interaction.MsgBox("New staff successfully added.", MsgBoxStyle.Information, "Add Staff"); this.Close(); } } catch (Exception ex) { Interaction.MsgBox(ex.ToString()); } finally { SQLConn.cmd.Dispose(); SQLConn.conn.Close(); } }