private void autoCompleteStockCode() { con = new MySqlConnection(); dbcon = new Conf.dbs(); con.ConnectionString = dbcon.getConnectionString(); String sql = "SELECT stock_code FROM inventory_stocks WHERE supplier_code = ?supplier_code ORDER BY stock_code ASC"; try { con.Open(); MySqlCommand cmd = new MySqlCommand(sql, con); cmd.Parameters.AddWithValue("?supplier_code", txtBoxSupplierCode.Text); cmd.CommandType = CommandType.Text; MySqlDataReader rdr = cmd.ExecuteReader(); if (rdr.HasRows == true) { while (rdr.Read()) { collect2.Add(rdr["stock_code"].ToString()); } } rdr.Close(); txtBoxStockCode.AutoCompleteMode = AutoCompleteMode.Suggest; txtBoxStockCode.AutoCompleteSource = AutoCompleteSource.CustomSource; txtBoxStockCode.AutoCompleteCustomSource = collect2; con.Close(); } catch (Exception) { MessageBox.Show("Please Check your Database Server Connection", "Database Server Error", MessageBoxButtons.OK, MessageBoxIcon.Error); Application.ExitThread(); } }
private void getDataTable() { dbcon = new Conf.dbs(); dataGridView1.DataSource = null; dataGridView1.Rows.Clear(); dataGridView1.Refresh(); String connectionString = dbcon.getConnectionString(); String query = "SELECT order_quantity AS a, order_suppliers_itemno AS b, order_uom AS c, order_description AS d, "; query += "order_unitcost AS e, order_amount AS f FROM po_order_list "; query += "WHERE po_no = ?po_no"; using (MySqlConnection con = new MySqlConnection(connectionString)) { using (MySqlDataAdapter adapter = new MySqlDataAdapter(query, con)) { try { DataTable dataTable = new DataTable(); adapter.SelectCommand.Parameters.AddWithValue("?po_no", rdPOno.Text); adapter.Fill(dataTable); for (int i = 0; i < dataTable.Rows.Count; i++) { dataGridView1.Rows.Add(dataTable.Rows[i][0], dataTable.Rows[i][1], dataTable.Rows[i][2], dataTable.Rows[i][3], dataTable.Rows[i][4], dataTable.Rows[i][5]); } } catch (MySqlException ex) { MessageBox.Show("Error" + ex); } } } // end using }
private void btnProceed_Click(object sender, EventArgs e) { Convert.ToInt32(lblON.Text); String name = txtBoxName.Text; String address = txtBoxAdd.Text; con = new MySqlConnection(); dbcon = new Conf.dbs(); con.ConnectionString = dbcon.getConnectionString(); String query = "UPDATE order_store SET order_customer = ?customer_name, order_address = ?customer_address "; query += "WHERE order_no ='" + lblON.Text + "'"; try { con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); cmd.Parameters.AddWithValue("?customer_name", name); cmd.Parameters.AddWithValue("?customer_address", address); cmd.ExecuteNonQuery(); cmd.Dispose(); } finally { con.Close(); } }
public void toAddress() { con = new MySqlConnection(); dbcon = new Conf.dbs(); con.ConnectionString = dbcon.getConnectionString(); String query = "SELECT supplier_address FROM inventory_supplier "; query += "WHERE supplier_code = ?supplier_code"; try { con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); cmd.Parameters.AddWithValue("?supplier_code", txtBoxSupplierCode.Text); cmd.ExecuteScalar(); MySqlDataReader rdr = cmd.ExecuteReader(); if (rdr.Read()) { lAddress.Text = rdr["supplier_address"].ToString(); } else { lAddress.Text = ""; } } catch (Exception) { lAddress.Text = ""; } }
private void getDataTableItems() { dbcon = new Conf.dbs(); dataGridView2.DataSource = null; dataGridView2.Rows.Clear(); dataGridView2.Refresh(); String connectionString = dbcon.getConnectionString(); String query = "SELECT inventory_stocks.stock_code AS a, inventory_items.item_ean AS b, inventory_stocks.stock_name AS c, "; query += "inventory_items.item_retail_price AS d, inventory_category.cat_description AS e "; query += "FROM inventory_items "; query += "INNER JOIN inventory_stocks ON inventory_items.stock_code = inventory_stocks.stock_code "; query += "INNER JOIN inventory_category ON inventory_stocks.stock_cat_code = inventory_category.cat_code "; query += "WHERE (inventory_items.is_kit = 0)"; using (MySqlConnection con = new MySqlConnection(connectionString)) { using (MySqlDataAdapter adapter = new MySqlDataAdapter(query, con)) { try { DataTable dataTable = new DataTable(); adapter.Fill(dataTable); for (int i = 0; i < dataTable.Rows.Count; i++) { dataGridView2.Rows.Add(dataTable.Rows[i][0], dataTable.Rows[i][1], dataTable.Rows[i][2], dataTable.Rows[i][3], dataTable.Rows[i][4]); } } catch (MySqlException ex) { MessageBox.Show("Error" + ex); } } } // end using }
private void supplier() { con.ConnectionString = dbcon.getConnectionString(); String query = "SELECT supplier_code AS a FROM inventory_supplier "; query += "WHERE supplier_name = ?supplier_name"; try { con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); cmd.Parameters.AddWithValue("?supplier_name", cBoxSupplier.Text); cmd.ExecuteScalar(); MySqlDataReader rdr = cmd.ExecuteReader(); if (rdr.Read()) { sup = rdr["a"].ToString(); } } catch (Exception) { Application.ExitThread(); } finally { con.Close(); } }
private void fetchUnitAndSelling() { con.ConnectionString = dbcon.getConnectionString(); String query = "SELECT stock_uom AS a, stock_name AS b FROM inventory_stocks "; query += "WHERE stock_code = ?stock_code"; try { con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); cmd.Parameters.AddWithValue("?stock_code", dataGridView2.SelectedRows[0].Cells[0].Value.ToString()); cmd.ExecuteScalar(); MySqlDataReader rdr = cmd.ExecuteReader(); if (rdr.Read()) { uom = rdr["a"].ToString(); description = rdr["b"].ToString(); } con.Close(); } catch (Exception) { MessageBox.Show("Check Database Server", "Database Server Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
private void getDataTableKits() { dbcon = new Conf.dbs(); dataGridView1.DataSource = null; dataGridView1.Rows.Clear(); dataGridView1.Refresh(); String connectionString = dbcon.getConnectionString(); String query = "SELECT inventory_items_kit.kit_qty AS aa, inventory_stocks.stock_code AS a, inventory_stocks.stock_name AS b, inventory_stocks.stock_uom AS c, inventory_stocks.stock_selling_price AS d "; query += "FROM inventory_items_kit INNER JOIN inventory_stocks ON inventory_items_kit.stock_code = inventory_stocks.stock_code INNER JOIN inventory_items ON inventory_items_kit.item_ean = inventory_items.item_ean "; query += "WHERE (inventory_items_kit.item_ean = ?ean)"; using (MySqlConnection con = new MySqlConnection(connectionString)) { using (MySqlDataAdapter adapter = new MySqlDataAdapter(query, con)) { try { DataTable dataTable = new DataTable(); adapter.SelectCommand.Parameters.AddWithValue("?ean", Ean); adapter.Fill(dataTable); for (int i = 0; i < dataTable.Rows.Count; i++) { dataGridView1.Rows.Add(dataTable.Rows[i][0], dataTable.Rows[i][1], dataTable.Rows[i][2], dataTable.Rows[i][3], dataTable.Rows[i][4]); } } catch (MySqlException ex) { MessageBox.Show("Error" + ex); } } } // end using }
private void getDataTable() { dbcon = new Conf.dbs(); dataGridView1.DataSource = null; dataGridView1.Rows.Clear(); dataGridView1.Refresh(); String connectionString = dbcon.getConnectionString(); String query = "SELECT user_account.user_id AS a, user_account.user_name AS b, user_information.first_name AS c, user_information.middle_name AS d, user_information.last_name AS e, user_account.last_login AS f, user_account.date_created AS g "; query += "FROM user_account "; query += "INNER JOIN user_information ON user_account.user_id = user_information.user_id"; using (MySqlConnection con = new MySqlConnection(connectionString)) { using (MySqlDataAdapter adapter = new MySqlDataAdapter(query, con)) { try { DataTable dataTable = new DataTable(); adapter.Fill(dataTable); for (int i = 0; i < dataTable.Rows.Count; i++) { dataGridView1.Rows.Add(dataTable.Rows[i][0], dataTable.Rows[i][1], dataTable.Rows[i][2], dataTable.Rows[i][3], dataTable.Rows[i][4], dataTable.Rows[i][5], dataTable.Rows[i][6]); } } catch (Exception ex) { MessageBox.Show("Error" + ex); } } } // end using }
private void loadUsername() { con = new MySqlConnection(); dbcon = new Conf.dbs(); con.ConnectionString = dbcon.getConnectionString(); String query = "SELECT DISTINCT user_name FROM user_account ORDER BY user_name ASC"; try { cBoxUserName.Items.Clear(); con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); cmd.ExecuteScalar(); MySqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { cBoxUserName.BeginUpdate(); cBoxUserName.Items.Add(rdr["user_name"].ToString()); cBoxUserName.EndUpdate(); } con.Close(); } catch (Exception) { MessageBox.Show("Please Check your Database Server Connection", "Database Server Error", MessageBoxButtons.OK, MessageBoxIcon.Error); Application.ExitThread(); } }
private void autoComplete() { con.ConnectionString = dbcon.getConnectionString(); String sql = "SELECT DISTINCT user_name FROM user_account ORDER BY user_name ASC"; try { con.Open(); MySqlCommand cmd = new MySqlCommand(sql, con); cmd.CommandType = CommandType.Text; MySqlDataReader rdr = cmd.ExecuteReader(); if (rdr.HasRows == true) { while (rdr.Read()) { collect.Add(rdr["user_name"].ToString()); } } rdr.Close(); txtBoxUsername.AutoCompleteMode = AutoCompleteMode.Suggest; txtBoxUsername.AutoCompleteSource = AutoCompleteSource.CustomSource; txtBoxUsername.AutoCompleteCustomSource = collect; con.Close(); } catch (Exception) { MessageBox.Show("Please Check your Database Server Connection", "Database Server Error", MessageBoxButtons.OK, MessageBoxIcon.Error); Application.ExitThread(); } }
private void CheckIfStockCodePrevent() { con = new MySqlConnection(); dbcon = new Conf.dbs(); con.ConnectionString = dbcon.getConnectionString(); String query = "SELECT DISTINCT order_suppliers_itemno FROM po_order_list WHERE order_suppliers_itemno = ?a AND po_no = ?b"; try { con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); cmd.Parameters.AddWithValue("?a", txtBoxStockCode.Text); cmd.Parameters.AddWithValue("?b", rdPOno.Text); cmd.ExecuteScalar(); MySqlDataReader rdr = cmd.ExecuteReader(); if (rdr.Read()) { supplier_code = rdr["order_suppliers_itemno"].ToString(); } else { supplier_code = ""; } con.Close(); } catch (Exception) { MessageBox.Show("Please Check your Database Server Connection", "Database Server Error", MessageBoxButtons.OK, MessageBoxIcon.Error); Application.ExitThread(); } }
public void checkifTheSame() { con = new MySqlConnection(); dbcon = new Conf.dbs(); con.ConnectionString = dbcon.getConnectionString(); String query = "SELECT * FROM inventory_supplier "; query += "WHERE supplier_code = ?supplier_code AND supplier_name = ?supplier_name"; try { con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); cmd.Parameters.AddWithValue("?supplier_name", txtBoxSupplierName.Text); cmd.Parameters.AddWithValue("?supplier_code", txtBoxSupplierCode.Text); cmd.ExecuteScalar(); MySqlDataReader rdr = cmd.ExecuteReader(); if (rdr.Read()) { btnProceed.Enabled = true; } else { btnProceed.Enabled = false; txtBoxQty.Clear(); } } catch (Exception) { btnProceed.Enabled = false; txtBoxQty.Clear(); } }
private void ConfigCheck() { frmLogin fl = new frmLogin(); con.ConnectionString = dbcon.getConnectionString(); String query = "SELECT * FROM system_config"; try { con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); cmd.ExecuteScalar(); MySqlDataReader rdr = cmd.ExecuteReader(); if (rdr.Read()) { if (rdr["tax_type"].ToString() == "V") { taxP = Convert.ToDouble("." + rdr["vat_rate"]); taxDisplay = rdr["vat_rate"].ToString() + "%"; compName = rdr["company_name"].ToString(); address1 = rdr["company_address"].ToString(); address2 = rdr["company_address2"].ToString(); contact = rdr["company_contact"].ToString(); store_op = rdr["company_operator"].ToString(); permit_no = rdr["permit_no"].ToString(); TIN = rdr["tin_number"].ToString(); TaxT = rdr["tax_type"].ToString(); machine_no = rdr["machine_no"].ToString() + fl.tN; all_items_tax = Convert.ToInt16(rdr["all_items_tax"]); } else { taxP = 0; taxDisplay = "0%"; compName = rdr["company_name"].ToString(); address1 = rdr["company_address"].ToString(); address2 = rdr["company_address2"].ToString(); contact = rdr["company_contact"].ToString(); store_op = rdr["company_operator"].ToString(); permit_no = rdr["permit_no"].ToString(); TIN = rdr["tin_number"].ToString(); TaxT = rdr["tax_type"].ToString(); machine_no = rdr["machine_no"].ToString() + fl.tN; all_items_tax = Convert.ToInt16(rdr["all_items_tax"]); } } con.Close(); } catch (Exception) { } }
private void getDataTable() { frmLogin fs = new frmLogin(); dbcon = new Conf.dbs(); dataGridView1.DataSource = null; dataGridView1.Rows.Clear(); dataGridView1.Refresh(); String connectionString = dbcon.getConnectionString(); String query = "SELECT pos_orno AS a, pos_tax_perc AS b, pos_tax_amt AS c, "; query += "pos_total_amt AS d, pos_date AS e, pos_time AS f, pos_user AS g, pos_iswholesale AS h "; query += "FROM pos_store "; query += "WHERE pos_terminal = ?pos_terminal AND (pos_park = 1) AND (is_cancel = 0)"; using (MySqlConnection con = new MySqlConnection(connectionString)) { using (MySqlDataAdapter adapter = new MySqlDataAdapter(query, con)) { try { DataTable dataTable = new DataTable(); adapter.SelectCommand.Parameters.AddWithValue("?pos_terminal", fs.tN); adapter.Fill(dataTable); for (int i = 0; i < dataTable.Rows.Count; i++) { if (dataTable.Rows[i][7].ToString() == "1") { types = "Wholesale"; } else { types = "Retail"; } DateTime dates = DateTime.Parse(dataTable.Rows[i][5].ToString()); dataGridView1.Rows.Add(dataTable.Rows[i][0], dataTable.Rows[i][1], dataTable.Rows[i][2], dataTable.Rows[i][3], dataTable.Rows[i][4], dates.ToString("hh:mm:ss tt"), dataTable.Rows[i][6], types); } if (dataGridView1.Rows.Count != 0) { dataGridView1.FirstDisplayedScrollingRowIndex = dataGridView1.Rows.Count - 1; } } catch (MySqlException ex) { MessageBox.Show("Error" + ex); } } } // end using }
public void checkifTheSameStockToQty() { Double stock_cost_price; String UOM; con = new MySqlConnection(); dbcon = new Conf.dbs(); con.ConnectionString = dbcon.getConnectionString(); String query = "SELECT * FROM inventory_stocks "; query += "WHERE stock_code = ?stock_code AND stock_name = ?stock_name"; try { con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); cmd.Parameters.AddWithValue("?stock_code", txtBoxStockCode.Text); cmd.Parameters.AddWithValue("?stock_name", txtBoxParticulars.Text); cmd.ExecuteScalar(); MySqlDataReader rdr = cmd.ExecuteReader(); if (rdr.Read()) { stock_cost_price = Convert.ToDouble(rdr["stock_cost_price"]); UOM = rdr["stock_uom"].ToString(); txtBoxUnitPrice.Text = stock_cost_price.ToString("#,###,##0.00"); txtBoxUOM.Text = UOM; txtBoxQty.ReadOnly = false; } else { txtBoxQty.ReadOnly = true; txtBoxQty.Text = "0"; txtBoxUnitPrice.Text = "0.00"; txtBoxUOM.Clear(); } con.Close(); } catch (Exception) { txtBoxQty.ReadOnly = true; txtBoxQty.Text = "0"; txtBoxUnitPrice.Text = "0.00"; txtBoxUOM.Clear(); } }
private void ConfigCheck() { con.ConnectionString = dbcon.getConnectionString(); String query = "SELECT * FROM system_config"; try { con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); cmd.ExecuteScalar(); MySqlDataReader rdr = cmd.ExecuteReader(); if (rdr.Read()) { machine_no = rdr["machine_no"].ToString(); } con.Close(); } catch (Exception) { label11.Text = "Error!"; } }
private void getSecurityFlags() { con = new MySqlConnection(); dbcon = new Conf.dbs(); con.ConnectionString = dbcon.getConnectionString(); try { con.Open(); String query = "SELECT can_access AS a, has_sales AS b, has_customers AS c, has_inventory AS d, has_reports AS e, has_gc AS f, has_user_accounts AS g, has_conf AS h "; query += "FROM user_access_restrictions "; query += "WHERE user_id = ?grab"; MySqlCommand cmd = new MySqlCommand(query, con); cmd.Parameters.AddWithValue("?grab", grabID); cmd.ExecuteScalar(); MySqlDataReader rdr = cmd.ExecuteReader(); rdr.Read(); if (rdr["a"].ToString() == "1") { chkSystemAccess.Checked = true; } else { chkSystemAccess.Checked = false; } if (rdr["b"].ToString() == "1") { chkSales.Checked = true; } else { chkSales.Checked = false; } if (rdr["c"].ToString() == "1") { chkCustomers.Checked = true; } else { chkCustomers.Checked = false; } if (rdr["d"].ToString() == "1") { chkInventory.Checked = true; } else { chkInventory.Checked = false; } if (rdr["e"].ToString() == "1") { chkReports.Checked = true; } else { chkReports.Checked = false; } if (rdr["f"].ToString() == "1") { chkGiftCards.Checked = true; } else { chkGiftCards.Checked = false; } if (rdr["g"].ToString() == "1") { chkUserAccounts.Checked = true; } else { chkUserAccounts.Checked = false; } if (rdr["h"].ToString() == "1") { chkConfiguration.Checked = true; } else { chkConfiguration.Checked = false; } con.Close(); } catch (Exception) { MessageBox.Show("Please Check your Database Server Connection", "Database Server Error", MessageBoxButtons.OK, MessageBoxIcon.Error); Application.ExitThread(); } }