public void reader(object obj) { DBConnection db = new DBConnection(); try { string query = "SELECT * FROM items WHERE Item_Code='"+barCode+"'"; if (db.OpenConnection() == true) { //populate data gridview from result MySqlCommand cmd = new MySqlCommand(query, db.connection); MySqlDataAdapter adapter = new MySqlDataAdapter(cmd); DataTable table = new DataTable(); adapter.Fill(table); if(table.Rows.Count==0) { MessageBox.Show("Invalid BarCode", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } else { Billingform bf=(Billingform)obj; bf.txtBoxDescription.Text=table.Rows[0].ItemArray[1].ToString(); bf.txtBoxDiscount.Text=table.Rows[0].ItemArray[4].ToString(); bf.textBox8.Text=table.Rows[0].ItemArray[2].ToString(); bf.txtBoxCode.ReadOnly = true; bf.ActiveControl = bf.textBox2; } } else { MessageBox.Show("DB Connection Error, Please Try Again", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } } catch (Exception exc) { MessageBox.Show("Error Occured, Please Try Again, " + exc.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { bool a = db.CloseConnection(); } }
private void btnSearch_Click(object sender, EventArgs e) { DBConnection db = new DBConnection(); //Admin item = new Admin(); try { string query; if (db.OpenConnection() == true) { query = "SELECT * From users"; MySqlCommand cmd = new MySqlCommand(query, db.connection); MySqlDataAdapter adapter = new MySqlDataAdapter(); adapter.SelectCommand = cmd; DataTable table = new DataTable(); adapter.Fill(table); BindingSource bsource = new BindingSource(); bsource.DataSource = table; dataGridView2.DataSource = table; adapter.Update(table); } } catch (Exception ex) { MessageBox.Show("Error Occured," + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { bool a = db.CloseConnection(); } }
//--------------startOfBillNoGen Function--------------------------------------------------------------------------------------------------------------------------- //function is for generate billno for BillingForm public int BillNoGen(object obj) { DBConnection db = new DBConnection(); int billno = 0; //initialize billno variable try { string query = "SELECT COUNT(Quantity) FROM bills"; int count = 0; //initialize count variable if (db.OpenConnection() == true) { MySqlCommand cmd = new MySqlCommand(query, db.connection); count = int.Parse(cmd.ExecuteScalar().ToString()); //check for existing bills if (count < 1) billno = 000001; //first billno else { string queryOne = "SELECT Invoice_No FROM bills ORDER BY Invoice_No DESC LIMIT 1"; MySqlCommand cmdOne = new MySqlCommand(queryOne, db.connection); int result = int.Parse(cmdOne.ExecuteScalar().ToString()); //retrieve billno of last bill billno = result + 1; //next billno } } else { throw new Exception("DB Connection Error"); } } catch (Exception ex) { Billingform bf = (Billingform)obj; DialogResult result = MessageBox.Show("Error " + ex.Message + " Do you need to Retry?", "Oops!", System.Windows.Forms.MessageBoxButtons.RetryCancel, System.Windows.Forms.MessageBoxIcon.Question); if (result == DialogResult.Retry) { bf.reload(); } else { bf.Close(); } } finally { bool a = db.CloseConnection(); } return billno; }
//--------------endOftotal Function--------------------------------------------------------------------------------------------------------------------- //--------------startOfbillToDB Function--------------------------------------------------------------------------------------------------------------------- public void billToDB(object obj) { Billingform bf = (Billingform)obj; DBConnection db = new DBConnection(); try { string inv_no = bf.textBox1.Text; string user = bf.label16.Text; string code = null; string qty = null; string discount = null; string total = null; DateTime date = Convert.ToDateTime(bf.label9.Text.Substring(0, bf.label9.Text.Length - 12)); string time = bf.label9.Text.Substring(10, 11); string pay_Type = "ca"; if (db.OpenConnection() == true) { for (int i = 0; i < bf.dataGridView1.RowCount; i++) { code = bf.dataGridView1.Rows[i].Cells[1].Value.ToString(); qty = bf.dataGridView1.Rows[i].Cells[3].Value.ToString(); discount = bf.dataGridView1.Rows[i].Cells[4].Value.ToString(); total = bf.dataGridView1.Rows[i].Cells[6].Value.ToString(); string query = "INSERT INTO bills VALUES('" + inv_no + "','" + code + "','" + qty + "','" + total + "','" + discount + "')"; MySqlCommand cmd = new MySqlCommand(query, db.connection); cmd.ExecuteNonQuery(); } string query2 = "INSERT INTO bills_info VALUES('" + inv_no + "','" + user + "','" + date.Year + "-" + date.Month + "-" + date.Day + "','" + time + "','" + pay_Type + "')"; MySqlCommand cmdOne = new MySqlCommand(query2, db.connection); cmdOne.ExecuteNonQuery(); bf.reload(); } else { throw new Exception("DB Connection Error"); } } catch (Exception exc) { MessageBox.Show("Errror Occured, Please Try Again, " + exc.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
//--------------startOfBillNoGen Function--------------------------------------------------------------------------------------------------------------------------- //function is for generate billno for BillingForm public int BillNoGen(object obj) { DBConnection db = new DBConnection(); int billno = 0; //initialize billno variable try { string query = "SELECT COUNT(Quantity) FROM bills"; int count = 0; //initialize count variable if (db.OpenConnection() == true) { MySqlCommand cmd = new MySqlCommand(query, db.connection); count = int.Parse(cmd.ExecuteScalar().ToString()); //check for existing bills if (count < 1) { billno = 000001; //first billno } else { string queryOne = "SELECT Invoice_No FROM bills ORDER BY Invoice_No DESC LIMIT 1"; MySqlCommand cmdOne = new MySqlCommand(queryOne, db.connection); int result = int.Parse(cmdOne.ExecuteScalar().ToString()); //retrieve billno of last bill billno = result + 1; //next billno } } else { throw new Exception("DB Connection Error"); } } catch (Exception ex) { Billingform bf = (Billingform)obj; DialogResult result = MessageBox.Show("Error " + ex.Message + " Do you need to Retry?", "Oops!", System.Windows.Forms.MessageBoxButtons.RetryCancel, System.Windows.Forms.MessageBoxIcon.Question); if (result == DialogResult.Retry) { bf.reload(); } else { bf.Close(); } } finally { bool a = db.CloseConnection(); } return(billno); }
//--------------endOfEnter Function--------------------------------------------------------------------------------------------------------------------------- //--------------startOfManualBilling Function------------------------------------------------------------------------------------------------------------------- public void manualBilling(string form, string searchKey, object obj = null) { DBConnection db = new DBConnection(); TextBox textbox = null; DataGridView datagridview = null; string querystring = null; //for ManualBilling form if (form == "mb") { ManualBilling mb = (ManualBilling)obj; textbox = mb.txtBoxDescription; datagridview = mb.dataGridView1; } if (form == "admin") { Admin frm = (Admin)obj; textbox = frm.textBox6; datagridview = frm.dataGridView1; } try { string query = null; textbox.ReadOnly = false; textbox.Text = textbox.Text + searchKey; //assign character of the pressed key into the end of the textbox textbox.Select(textbox.Text.Length, 0); //move cursor into the end of text in the textbox textbox.ReadOnly = true; if (form == "mb")//for ManualBilling form { querystring = "SELECT * From items WHERE Description LIKE CONCAT('" + textbox.Text + "','%')"; } if (form == "admin") { if (textbox.Text == "") { querystring = "SELECT * From items"; } else { querystring = "SELECT * From items WHERE Description LIKE CONCAT('" + textbox.Text + "','%')"; } } query = querystring; if (db.OpenConnection() == true) { //populate data gridview from result MySqlCommand cmd = new MySqlCommand(query, db.connection); MySqlDataAdapter adapter = new MySqlDataAdapter(cmd); DataTable table = new DataTable(); adapter.Fill(table); BindingSource bs = new BindingSource(); bs.DataSource = table; datagridview.DataSource = bs; datagridview.AutoResizeRows(DataGridViewAutoSizeRowsMode.AllCellsExceptHeaders); if (datagridview.RowCount > 0) { datagridview.Rows[0].Selected = true; //auto select first record of datagridview if there is any record } } else { MessageBox.Show("DB Connection Error, Please Try Again", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } } catch (Exception exc) { MessageBox.Show("Error Occured, Please Try Again" + exc.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { bool a = db.CloseConnection(); } }