I have a problem with my vb payroll project. When I click the Edit Button a msg pop-up saying that "Input string was not in a correct format." and when click the Delete button, msg is "Fatal error encountered during command execution."
I tried to change the values of @g to @emp_id from: cmd = new MySqlCommand("delete from empdata where emp_id = @g", conn);
but nothing happens. I don't know where is the errors, or something missing. please help!
here's the code:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data;
using MySql.Data.MySqlClient;
namespace PAYROLLSYSTEM
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
string connStr = "server=localhost;user=root;database=payroll;port=3306;password=''";
MySqlCommand cmd;
MySqlDataReader dr;
int a;
private void updategrid()
{
MySqlConnection conn = new MySqlConnection(connStr);
MySqlDataAdapter da = new MySqlDataAdapter("Select * from empdata", conn);
DataTable dt = new DataTable();
da.Fill(dt);
dataGridView1.DataSource = dt;
dataGridView1.Refresh();
}
private void refresharea()
{
updategrid();
emp_id.Text = "";
empln.Text = "";
empfn.Text = "";
empmn.Text = "";
nodp.Text = "";
rpd.Text = "";
sss.Text = "";
ph.Text = "";
tax.Text = "";
total.Text = "";
grosspay.Text = "";
netpay.Text = "";
}
private void btncompute_Click(object sender, EventArgs e)
{
string connStr = "server=localhost;user=root;database=payroll;port=3306;password='';";
MySqlConnection conn = new MySqlConnection(connStr);
try
{
conn.Open();
double nodpd;
double rpdd;
double sssd;
double phdd;
double taxd;
double totald;
double grosspayd;
double netpayd;
sssd = double.Parse(sss.Text);
phdd = double.Parse(ph.Text);
taxd = double.Parse(tax.Text);
nodpd = double.Parse(nodp.Text);
rpdd = double.Parse(rpd.Text);
grosspayd = nodpd * rpdd;
grosspay.Text = grosspayd.ToString();
totald = sssd + phdd + taxd;
total.Text = totald.ToString();
netpayd = grosspayd - totald;
netpay.Text = netpayd.ToString();
}
catch (MySqlException e1)
{
Console.WriteLine(e1.Message);
}
conn.Close();
}
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
}
private void btnadd_Click(object sender, EventArgs e)
{
MySqlConnection conn = new MySqlConnection(connStr);
try
{
conn.Open();
string sql = "INSERT INTO empdata (emp_id, empln, empfn, empmn, nodp, rpd, sss, ph, tax, total, grosspay, netpay)VALUES ('" + emp_id.Text + "','" + empln.Text + "','" + empfn.Text + "','" + empmn.Text + "','" + nodp.Text + "', '" + rpd.Text + "', '" + sss.Text + "', '" + ph.Text + "', '" + tax.Text + "', '" + total.Text + "', '" + grosspay.Text + "', '" + netpay.Text + "')";
cmd = new MySqlCommand(sql, conn);
cmd.ExecuteNonQuery();
MessageBox.Show("NEW DATA INSERTED");
updategrid();
refresharea();
}
catch (MySqlException e1)
{
Console.WriteLine(e1.Message);
}
conn.Close();
}
private void btnedit_Click(object sender, EventArgs e)
{
MySqlConnection conn = new MySqlConnection(connStr);
try
{
conn.Open();
cmd = new MySqlCommand("update empdata set empln=@a,empfn=@b,empmn=@c,nodp=@d,rpd=@e,sss=@f,ph@l,tax@h,total@i,grosspay@j,netpay@k where emp_id=@g", conn);
cmd.Parameters.AddWithValue("@a", empln.Text);
cmd.Parameters.AddWithValue("@b", empfn.Text);
cmd.Parameters.AddWithValue("@c", empmn.Text);
cmd.Parameters.AddWithValue("@d", nodp.Text);
cmd.Parameters.AddWithValue("@e", rpd.Text);
//cmd.Parameters.AddWithValue("@f", int.Parse(comboBox1.SelectedItem.ToString()));
cmd.Parameters.AddWithValue("@f", sss.Text);
cmd.Parameters.AddWithValue("@l", ph.Text);
cmd.Parameters.AddWithValue("@h", tax.Text);
cmd.Parameters.AddWithValue("@i", total.Text);
cmd.Parameters.AddWithValue("@j", grosspay.Text);
cmd.Parameters.AddWithValue("@k", netpay.Text);
cmd.Parameters.AddWithValue("@g", double.Parse(emp_id.Text));
int a = cmd.ExecuteNonQuery();
if (a > 0)
{
MessageBox.Show("Data Updated");
updategrid();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
conn.Close();
}
private void dataGridView1_CellContentClick_1(object sender, DataGridViewCellEventArgs e)
{
if (e.RowIndex >= 0)
{
DataGridViewRow row = this.dataGridView1.Rows[e.RowIndex];
emp_id.Text = row.Cells["emp_id"].Value.ToString();
empln.Text = row.Cells["empln"].Value.ToString();
empfn.Text = row.Cells["empfn"].Value.ToString();
empmn.Text = row.Cells["empmn"].Value.ToString();
nodp.Text = row.Cells["nodp"].Value.ToString();
rpd.Text = row.Cells["rpd"].Value.ToString();
sss.Text = row.Cells["sss"].Value.ToString();
ph.Text = row.Cells["ph"].Value.ToString();
tax.Text = row.Cells["tax"].Value.ToString();
total.Text = row.Cells["total"].Value.ToString();
grosspay.Text = row.Cells["grosspay"].Value.ToString();
netpay.Text = row.Cells["netpay"].Value.ToString();
}
}
private void btndel_Click(object sender, EventArgs e)
{
MySqlConnection conn = new MySqlConnection(connStr);
try
{
if (MessageBox.Show("Are you sure want to Delete Data", "Delete Info", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
cmd = new MySqlCommand("delete from empdata where emp_id = @g", conn);
//cmd.Parameters.AddWithValue("@g", int.Parse(emp_id.SelectedItem.ToString()));
//cmd.Parameters.AddWithValue("@g", double.Parse(emp_id.Text));
conn.Open();
a = cmd.ExecuteNonQuery();
if (a > 0)
{
MessageBox.Show("Data Deleted");
emp_id.Items.Remove(emp_id.SelectedItem.ToString());
updategrid();
refresharea();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
}
private void btnquit_Click(object sender, EventArgs e)
{
this.Close();
}
private void txtsearch_SelectedIndexChanged(object sender, EventArgs e)
{
MySqlConnection conn = new MySqlConnection(connStr);
try
{
MySqlDataAdapter da = new MySqlDataAdapter("Select * from empdata where pname like '" + "%" + txtsearch.Text + "%" + "'", conn);
DataTable dt = new DataTable();
da.Fill(dt);
dataGridView1.DataSource = dt;
dataGridView1.Refresh();
}
catch (MySqlException e1)
{
Console.WriteLine(e1.Message);
}
conn.Close();
}
private void btnsearch_Click(object sender, EventArgs e)
{
MySqlConnection conn = new MySqlConnection(connStr);
try
{
MySqlDataAdapter da = new MySqlDataAdapter("Select * from empdata where empfn='" + txtsearch.Text + "'", conn);
DataTable dt = new DataTable();
da.Fill(dt);
dataGridView1.DataSource = dt;
dataGridView1.Refresh();
}
catch (MySqlException e1)
{
Console.WriteLine(e1.Message);
}
conn.Close();
}
private void emp_id_SelectedIndexChanged(object sender, EventArgs e)
{
MySqlConnection conn = new MySqlConnection(connStr);
try
{
conn.Open();
cmd = new MySqlCommand("select * from empdata where emp_id=@a", conn);
cmd.Parameters.AddWithValue("@a", int.Parse(emp_id.SelectedItem.ToString()));
dr = cmd.ExecuteReader();
if (dr.HasRows)
{
if (dr.Read())
{
emp_id.Text = dr["emp_id"].ToString();
empln.Text = dr["pname"].ToString();
empfn.Text = dr["bday"].ToString();
empmn.Text = dr["age"].ToString();
nodp.Text = dr["category"].ToString();
rpd.Text = dr["location"].ToString();
sss.Text = dr["sss"].ToString();
ph.Text = dr["ph"].ToString();
tax.Text = dr["tax"].ToString();
total.Text = dr["total"].ToString();
grosspay.Text = dr["grosspay"].ToString();
netpay.Text = dr["netpay"].ToString();
}
}
dr.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
}
}
}
