//********************************************************************************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);
}
}
}
