C# Mysql Insert Data not work

Question

I am Nubie in C#, I Try to learn CRUD. Select data Succes but I cant Save data to mysql. my table

mahasiswa

   ID| namae | jurusan | email
  _____________________________
  1 |  Bill  | IT      | bill@gmail.com
  2 | Tony   | IT      | Tony@gmail.com

ID is set to auto increment in Mysql

and this my script for btn save

 void btnsave_Click(object sender, EventArgs e)
        {
            try 
            {
                if (txtid.Text != "" && txtnama.Text != "" && txtjurusan.Text != "" && txtemail.Text != "")
                {
                    query = string.Format("INSERT INTO mahasiswa values ('{1}','{2}','{3}');", txtnama.Text, txtjurusan.Text, txtemail.Text);

                    koneksi.Open();
                    perintah = new MySqlCommand(query, koneksi);
                    adapter = new MySqlDataAdapter(perintah);
                    int res = perintah.ExecuteNonQuery();
                    koneksi.Close();

                    if (res == 1)
                    {
                        MessageBox.Show("Input Data Sukses...");
                    }
                    else
                    {
                        MessageBox.Show("Input Data Gagal... ");
                    }

                }
                else
                {
                    MessageBox.Show("Data tidak lengkap");
                }
            }
            catch(Exception ex)
            {
                  MessageBox.Show(ex.ToString());
            }
        }

That Script can run, but after input data and click save buttonm the program stop.

Can anybody help me.

Im very Appreciated your answer

Thanks

form load

 void Form1_Load(object sender, EventArgs e)
        {
            try
            {

                koneksi.Open();
                query = string.Format("SELECT * FROM mahasiswa");
                perintah = new MySqlCommand(query, koneksi);
                adapter = new MySqlDataAdapter(perintah);
                perintah.ExecuteNonQuery();
                ds.Clear();
                adapter.Fill(ds);
                koneksi.Close();
                dgv1.DataSource = ds.Tables[0];
                dgv1.Columns[0].Width = 50;
                dgv1.Columns[0].HeaderText = "ID";
                dgv1.Columns[1].Width = 120;
                dgv1.Columns[1].HeaderText = "Nama Mahasiswa";
                dgv1.Columns[2].Width = 120;
                dgv1.Columns[2].HeaderText = "Jurusan";
                dgv1.Columns[3].Width = 120;
                dgv1.Columns[3].HeaderText = "Email";
                //txtid.clear();
                txtnama.Clear();
                txtjurusan.Clear();
                txtemail.Clear();
                btnedit.Enabled = false;
                btndelete.Enabled = false;
                btnsave.Enabled = true;
                btnsearch.Enabled = true;


            }
            catch (Exception ex) {

                MessageBox.Show(ex.ToString());
            }
        }

try to change your sql string like "INSERT INTO mahasiswa values ('{0}','{1}','{2}');" — tolga güler, Jul 05 '13 at 08:33

score 1 · Answer 1 · answered Jul 05 '13 at 08:20

Also if your learning CRUD it would be helpful if you made the necessary stored procedures within SQL aswell as attempting it this way.

Just create a CREATE, INSERT, UPDATE, DELETE procedure. Then in your code for an insert example you have this:

public bool Add(string example)
    {
        try
        {

            using (SqlConnection con = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand("[Proc name]", con);
                cmd.CommandType = CommandType.StoredProcedure;
                if (con.State == ConnectionState.Closed)
                    con.Open();
                cmd.Parameters.AddWithValue("@Example", example);

                cmd.ExecuteNonQuery();
                return true;
            }
        }

This allows you to view what happens and to ensure your procedures are working correctly. This way allows you to catch exceptions easier, and also validate your inputs easier.

Thanks For Your Advice, but I am still nubie, its hard to me to create the procedure specially in VS 2010. Usually I code with PHP and create Function on new file. — Uchsun, Jul 05 '13 at 09:01
From my example it would be better to create the proc in SQL, using an INSERT statement and then this code in my example would be the only code you need to add the values into your SQL table. — Philip Gullick, Jul 05 '13 at 09:03

Satya · Accepted Answer · 2013-07-05T09:02:46.310

0

try this

"INSERT INTO mahasiswa (name,jurusan,mail) values ('{1}','{2}','{3}')", txtnama.Text, txtjurusan.Text, txtemail.Text)";

as your query will instruct mysql to look for 4 values whereas you are passing only 3 values.

edited Jul 05 '13 at 09:02

answered Jul 05 '13 at 08:14

Satya

8,693
5
34
55

what is the rror you are geting – Satya Jul 05 '13 at 08:59
i Just modified my query can you try with the new query – Satya Jul 05 '13 at 09:02
hi @satya work, but i Modify like this "INSERT INTO mahasiswa (nama,jurusan,email) values ('{0}','{1}','{2}');", txtnama.Text, txtjurusan.Text, txtemail.Text, it work but I still not understand why it work. and the datagrid not update in real time. – Uchsun Jul 05 '13 at 09:17
it workeds becaused you numbered the parameters correctly this time. About datagrid need to re-check, and where is the code for datagrid? – Satya Jul 05 '13 at 09:19

score 0 · Answer 3 · edited May 23 '17 at 11:56

Do not use string concatenation to build sql command text, use always a parameterized query

query = "INSERT INTO mahasiswa VALUES (@p1,@p2,@p3);";
using(MySqlConnection koneksi = new MySqlConnection(connectionString))
using(MySqlCommand perintah = new MySqlCommand(query, koneksi))
{
     koneksi.Open();
     perintah.Parameters.AddWithValue("@p1", txtnama.Text);
     perintah.Parameters.AddWithValue("@p2", txtjurusan.Text);
     perintah.Parameters.AddWithValue("@p3", txtemail.Text);
     int res = perintah.ExecuteNonQuery();
     if (res == 1)
         MessageBox.Show("Input Data Sukses...");
     else
         MessageBox.Show("Input Data Gagal... ");
}

If you use string concatenation your code will be open to sql injection where a malicious user could wreak havoc with your database (Look at this funny example)

Also your format statement is totally wrong, I doubt that your code reaches the point where the database command is executed because you list the arguments for string.Format from the index 1 to index 3 and you supply 3 arguments, but the index should start from zero and end at two. So you should get an exception on that line.

Another point to keep note is the using statement. As you can see, in my code the using statement will ensure the proper closing and disposing of the connection and command objects. The connection is particularly important to dispose properly because a failure here could break your program later.

C# Mysql Insert Data not work

3 Answers3