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();
            }

        }
    }
}

Recommended Answers

All 11 Replies

You've commented out the line that defines "@g" cmd.Parameters.AddWithValue... (ensure that the data type matches that of the data type in the database).

Also, in your insert, use parameterized queries.

Check your database, reading your codification, I understand that Field emp_id in your Data Table is the Prymary key, but you declre its Data Type is Double(God knows why?). This is not a good job. Take it as Varchar type and make the SQL commands on its base and modify the codes where needed. In updategrid() procedure at the end you didn't Dispose the DataAdapter and close the connection. And also parametterized all SQL Commands to protect from malicious users.
You do not mention the direction of the Parameter. Some times direction less Parameters also make various troubles.

I forgot to edit the delete button click it should not commented out, but still not working. a msg says: "object reference not set to an instance of an object"

and I tried to use the

cmd.Parameters.AddWithValue("@g",int.Parse(emp_id.SelectedItem.ToString()));

and commented out the

cmd.Parameters.AddWithValue("@g", int.Parse(emp_id.Text));

in the button edit click, but the same as "object reference not set to an instance of an object"

Sorry, but i dont know how to use the parameterized queries. my prof doesnt teach us about that. :D

In line No 127: in btn_edit Click event, What did you write?

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);

In btndel_Click event, at line 195 you call the updategrid() Procedure. You did not close or dispose the DataAdapter, DataTable or Connection at the end, this could be the cause of your Fatal Error in the Delete Procedure.
Hopefull, this should be came in your help.

i dont understand what do you mean, but this is just the code i wrote:

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);

I'll try to figure out. I'll update you later. thank you very much.

I would have to say that your biggest problem is that this isn't vb.net code. I'm guessing it is C#. Please let me know for sure and I'll move it to the correct forum.

@funnym3
Why didn't you understand, what I try to say?
You make a great mistake at SQL Update syntax. At the time of assign value to a field you have to write <Field Name>=<Parameter Name>.
To insert a new row into the table the syntax is

Insert Into <TableName> (Field1, Field2, Field3,.......) Values (Parameter1, Parameter2, Parameter3,..........)

To Modify the value of a perticular row is

Update <TableName> Set Field1=Parameter1, Field2=Parameter2, Field3=Parameter3..... Where <Condition>

Here you didn,t write the fields name for which Parameters supplied

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);

try to write the proper syntax.

@funnym3
From my opinion, in your code it does not create the proper instance of the SQLCommand Object. You declare the object at FormLvel. So you have to maintain the situations when you should Open it or Dispose.
Everytime, when you open any instance of SQLCommand Type Variable, at last you should be Dispos i.e. cmd.Dispose() it and also close the DataReader before it.

Do it and try.
After that If you get the same. Simply, Declare the SQLCommand Vaiable at Procedure Level. But don't forget to Close or Dispose them at the end of the procedure.

@funnym3.
You say Your Prof. doesn't teach about parameterized Quarry. How does you use @a, @b, @c and so on.

First have to know what is Parameter?

The variables through which any type of Procedure try to receive values from outside or inside the Procedure, are called Parameters.

In SQLCommand the variables, through you are tried to asign the values to the Fields of the Table.
Prefixing a "@" to a Parametername is the convention to write SQLParameters.
Syntax: "Select * From <TableName> Where Field1=@ParmName"

Most SQL Statements accept parameters, and you should pass values for each parameter before executing the query.
Assuming the above simple SQL Statement, the @ParamName parameter must be set to a value, or an exception will be thrown.

Most of the query you use single quatation mark, like this.
In Line 223 You use like '" + "%" + txtsearch.Text + "%" + "'". This is most Dangerous codification to take comparing condition from TextBox directly.

Any malicious user can try to corrupt your database through your query.

I try to do a example how they can do it.
Your Syntax was MySqlDataAdapter da = new MySqlDataAdapter("Select * from empdata where pname like '" + "%" + txtsearch.Text + "%" + "'", conn);.
Amalicious user might enter the following value into the Txtsearch TextBox and run the query.
The user write "xxx' ; Drop Table empdata --" into the TextBox and run the query. What would it done?
Your SQL syntax looks like "MySqlDataAdapter da = new MySqlDataAdapter("Select * from empdata where pname like '% xxx' ; Drop Table empdata -- %'", conn);
The statement contains two SQL statements and a comment, The Select sattement, followed by another statement that Drops the empdata table. The comments symbol in SQL (two consecutive Dashes) is required to disable the last part of the original statement.

Reverend Jim is correct. This is C#, not VB.NET.

@ Reverend Jim, thank your for fixing my thread.

@Santanu, oh I didn't notice the rpd=@e,sss=@f,ph@l,tax@h,total@i,grosspay@j,netpay@k.. there's a "=" missing
I have fixed it now. I tried to run the application, the edit and delete button is now working. thank you very much!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.