//********************************************************************************Function to update company info in the company table //this function is good for second round modifications private void updateButton_Click(object sender, EventArgs e) { //string connectionString = "Data Source=localhost" + "; Database=commissionrepo" + "; User ID=root" + "; Password=141210;"; string connectionString = ConfigurationManager.ConnectionStrings["CommDB"].ConnectionString; MySqlConnection MySqlConn = new MySqlConnection(connectionString); try { MySqlConn.Open(); string commandString = ("UPDATE company SET company_name = '" + txtcname.Text.Trim() + "', " + "address = '" + txtAddr.Text.Trim() + "', state = '" + stateComboBox.SelectedItem.ToString() + "', phone_number = '" + mskedtxtphone.Text.Trim() + "', contact_person = '" + txtcontact.Text.Trim() + "', city = '" + txtcompcity.Text.Trim() + "', zip = '" + txtcompzip.Text.Trim() + "' WHERE company_name = '" + txtcname.Text.Trim() + "'"); textBox1.Text = commandString; MySqlCommand mysqlcommand = new MySqlCommand(commandString, MySqlConn); companyTableAdapter getcomp = new companyDSTableAdapters.companyTableAdapter(); //DataTable table = new companyDS.companyDataTable(); DataTable table = GetDataTable( // Pass open database connection to function ref MySqlConn, // Pass SQL statement to create SqlDataReader commandString); txtcname.Text = " "; txtAddr.Text = " "; txtcompcity.Text = " "; txtcompzip.Text = " "; stateComboBox.Text = " "; mskedtxtphone.Text = " "; txtcontact.Text = " "; } catch (MySqlException ex) { MessageBox.Show(ex.Message); } }
//**************************************************This function SELECTS job given the jid private void txtjobsrch_Click(object sender, EventArgs e) { //string connectionString = "Data Source=localhost" + "; Database=commission" + "; User ID=root" + "; Password=141210;"; string connectionString = ConfigurationManager.ConnectionStrings["CommDB"].ConnectionString; MySqlConnection MySqlConn = new MySqlConnection(connectionString); try { MySqlConn.Open(); //string commandString25 = ("SELECT company_name, address, state, phone_number, contact_person FROM company WHERE company_name LIKE '%" + txtcid.Text + "%'"); // string commandString26 = ("SELECT cid FROM job WHERE jid = '" + txtjobsrch.Text + "'"); //("SELECT company_name, address, state, phone_number, contact_person FROM company WHERE company_name = jc"); // MySqlCommand mysqlcommand26 = new MySqlCommand(commandString26, MySqlConn); // companyTableAdapter getcomp26 = new companyDSTableAdapters.companyTableAdapter(); // //DataTable table = new companyDS.companyDataTable(); // DataTable table26 = GetDataTable( // // Pass open database connection to function //ref MySqlConn, // // Pass SQL statement to create SqlDataReader //commandString26); //txtjcname.DataBindings.Clear(); //txtjcname.DataBindings.Add(new Binding("Text", table26, "company_name", true)); //txtjcid.DataBindings.Clear(); //txtjcid.DataBindings.Add(new Binding("Text", table26, "cid", true)); //txtAddr.DataBindings.Clear(); //txtAddr.DataBindings.Add(new Binding("Text", table26, "address", true)); //stateComboBox.DataBindings.Clear(); //stateComboBox.DataBindings.Add(new Binding("Text", table26, "state", true)); //mskedtxtphone.DataBindings.Clear(); //mskedtxtphone.DataBindings.Add(new Binding("Text", table26, "phone_number", true)); //txtcontact.DataBindings.Clear(); //txtcontact.DataBindings.Add(new Binding("Text", table26, "contact_person", true)); ////////////////////////////////////////////////////////////////////////////////////////////////// ///////////////grab company info with cid string commandString27 = ("SELECT company_name, address, state, phone_number, contact_person FROM company WHERE cid = '" + txtjcid.Text + "'"); MySqlCommand mysqlcommand27 = new MySqlCommand(commandString27, MySqlConn); companyTableAdapter getcomp27 = new companyDSTableAdapters.companyTableAdapter(); //DataTable table = new companyDS.companyDataTable(); DataTable table27 = GetDataTable( // Pass open database connection to function ref MySqlConn, // Pass SQL statement to create SqlDataReader commandString27); txtjcname.DataBindings.Clear(); txtjcname.DataBindings.Add(new Binding("Text", table27, "company_name", true)); txtjaddr.DataBindings.Clear(); txtjaddr.DataBindings.Add(new Binding("Text", table27, "address", true)); txtjstate.DataBindings.Clear(); txtjstate.DataBindings.Add(new Binding("Text", table27, "state", true)); mskdjphone.DataBindings.Clear(); mskdjphone.DataBindings.Add(new Binding("Text", table27, "phone_number", true)); txtjcontact.DataBindings.Clear(); txtjcontact.DataBindings.Add(new Binding("Text", table27, "contact_person", true)); } catch (MySqlException ex) { MessageBox.Show(ex.Message); } }
//*************************************************************THis function Inserts new job //*************************************************************THis function is good for second round modifications private void jobins_Click(object sender, EventArgs e) { //string connectionString = "Data Source=localhost" + "; Database=commissionrepo" + "; User ID=root" + "; Password=141210;"; string connectionString = ConfigurationManager.ConnectionStrings["CommDB"].ConnectionString; MySqlConnection MySqlConn = new MySqlConnection(connectionString); if (txtjobname.Text == string.Empty) { errorProvider1.SetError(txtjobname, "Please Enter a Job Name"); } if (txtjobaddr.Text == string.Empty) { errorProvider1.SetError(txtjobaddr, "Please Enter a Job Address"); } if (txtjobcity.Text == string.Empty) { errorProvider1.SetError(txtjobcity, "Please Enter a city for the job"); } if (cbojobstate.Text == string.Empty) { errorProvider1.SetError(cbojobstate, "Please Enter a state for the job"); } if (txtjobzip.Text == string.Empty) { errorProvider1.SetError(txtjobzip, "Please Enter a zip for the job"); } else { try { MySqlConn.Open(); String r = txtjobname.Text; String u = r.Replace("'", ""); txtjobname.Text = u; //string commandString6 = ("INSERT into job SET cid = '" + txtjcid.Text + "', company_name = '" + txtjcname.Text.Trim() + "', sale_price = '" + txtsp.Text + "', " + "esitmated_percentage = '" + txtesp.Text + "', actual_cost = '" + txtac.Text + "', gross_profit = '" + txtgp.Text + "', estimator_percentage = '" + txtestpercent.Text + "', sales_percentage = '" + txtsalespercent.Text + "', pm_percentage = '" + txtpmpercent.Text + "', current_billing = '" + txtcb.Text + "', prog_billing_one = '" + txtpb1.Text + "', prog_billing_one_total = '" + txtpb1total.Text + "', prog_billing_two = '" + txtpb2.Text + "', prog_billing_two_total = '" + txtpb2total.Text + "', prog_billing_three = '" + txtpb3.Text + "', prog_billing_four = '" + txtpb4.Text + "', prog_billing_four_total = '" + txtpb4total.Text + "', final_billing = '" + txtfb.Text + "', final_billing_total = '" + txtfbtotal.Text + "', jtype = '" + txtjtype.Text + "'"); string commandString6 = ("INSERT into job SET cid = '" + txtjcid.Text + "', job_name = '" + txtjobname.Text.Trim() + "', job_address = '" + txtjobaddr.Text.Trim() + "', job_city = '" + txtjobcity.Text.Trim() + "', job_state = '" + cbojobstate.Text.Trim() + "', job_zip = '" + txtjobzip.Text.Trim() + "'"); MySqlCommand mysqlcommand = new MySqlCommand(commandString6, MySqlConn); companyTableAdapter getcomp = new companyDSTableAdapters.companyTableAdapter(); //DataTable table = new companyDS.companyDataTable(); DataTable table = GetDataTable( // Pass open database connection to function ref MySqlConn, // Pass SQL statement to create SqlDataReader commandString6); //CLEAR the text boxes after insert and update the data grid view txtjid.Text = " "; txtjobname.Text = " "; txtjobaddr.Text = " "; txtjobcity.Text = " "; cbojobstate.Text = " "; txtjobzip.Text = " "; //THis updates the data grid view with the newly inserted record string commandString15 = ("SELECT jid, job_name, job_address, job_city, job_state, job_zip from job WHERE job.cid = '" + txtjcid.Text + "'"); MySqlCommand mysqlcommand15 = new MySqlCommand(commandString15, MySqlConn); DataTable table7 = GetDataTable(ref MySqlConn, commandString15); dataGridView1.DataSource = table7; dataGridView1.Refresh(); cbojoblist.Items.Clear(); foreach (DataRow row in table7.Rows) { String item = (row["job_name"].ToString()); cbojoblist.Items.Add(item); //cbocname.Items.Add(item); //cbojcname.Items.Add(item); } } catch (MySqlException ex) { MessageBox.Show(ex.Message); } } }