How to insert,delete,select,update values in datagridview in C# using MYSQL

Question

Below is the code to insert value into mysql database, using datagridview. But the selectcommand is working.It is not happening since i get error stating "Column 'username' cannot be null ".

This error does not pop up if i use ms access database. Can anyone help me on this. is there any other method to do so.

private void button1_Click(object sender, EventArgs e)
    {    //Even using functions we can easily update the datagridview
        //Select_function();

        try
        {   //The container which displays the details.
            dataGridView1.Visible = true;

            //The binding object which binds the datagridview with backend.
            BindingSource bs = new BindingSource();

            //The datatable through which data is exported to datagridview
            table = new DataTable();
            bs.DataSource = table;
            this.dataGridView1.DataSource = bs;

            MySqlConnection conn = new MySqlConnection(db);
            conn.Open();

            string s = "select *from user";
            cmd = new MySqlCommand(s, conn);

            da = new MySqlDataAdapter();
            da.SelectCommand = new MySqlCommand(s, conn);

            //There is issue in below sytax of insert command.
            MySqlCommand insertcommand = new MySqlCommand("insert into user(username,password) values(@username ,@password)", conn);
            insertcommand.Parameters.Add("username",MySqlDbType.VarChar,50,"username");
            insertcommand.Parameters.Add("password", MySqlDbType.VarChar, 50, "password");
            da.InsertCommand = insertcommand;


            //Demonstrates update command
            MySqlCommand updatecommand = new MySqlCommand("update user set username=@username,password=@password where (username=@username)", conn);
            updatecommand.Parameters.Add("@username", MySqlDbType.VarChar, 50, "username");
            updatecommand.Parameters.Add("@password", MySqlDbType.VarChar, 50, "password");

             da.UpdateCommand = updatecommand;



            //Demonstration of delete Command
            MySqlCommand deletecommand = new MySqlCommand("delete from user where username=@username", conn);
            deletecommand.Parameters.Add("@username", MySqlDbType.VarChar, 50, "username");
            da.DeleteCommand = deletecommand;

            da.Fill(table);
            conn.Close();

        }
        catch (Exception err) { MessageBox.Show(err.Message); } 

    }
    private void button2_Click(object sender, EventArgs e)
    {  da.Update(table);
    }

alt text

2 revs · Accepted Answer · 2009-10-05 11:00:04Z

3

I am rather unsure, but doesn't mysql use numbered parameters with a "?"-synatx or named parameters with a ":"-syntax instead of (mssql) at-syntax ?

Something like :

MySqlCommand insertcommand = new MySqlCommand("insert into user(username,password) values(?, ?)", conn);
insertcommand.Parameters.Add(1, MySqlDbType.VarChar,50,"username");
insertcommand.Parameters.Add(2, MySqlDbType.VarChar, 50, "password");

or

MySqlCommand insertcommand = new MySqlCommand("insert into user(username,password) values(:username, :pass)", conn);
insertcommand.Parameters.Add(":username", MySqlDbType.VarChar,50,"username");
insertcommand.Parameters.Add(":pass", MySqlDbType.VarChar, 50, "password");

edited Oct 5, 2009 at 11:00

community wiki

2 revs
Nils

Sign up to request clarification or add additional context in comments.

3 Comments

Srikanth V M Over a year ago

hey thanks for the answer. U were absolutely correct. It was just a small syntax error.

Srikanth V M Over a year ago

":" generates error. "?" works fine. Second Code snippet wont work.

Nils Over a year ago

This is somewhat strange: dev.mysql.com/doc/refman/5.0/en/… suggests that it is possible to use named parameters which are prefixed with an "@" - much like dariom's anwer displayed.

dariom · Accepted Answer · 2009-10-05 10:44:45Z

1

I think you need to change the names of the parameters added to the insert command to match the names specified in the INSERT SQL statement.

Try:

insertcommand.Parameters.Add("@username",MySqlDbType.VarChar,50,"username");
insertcommand.Parameters.Add("@password", MySqlDbType.VarChar, 50, "password");

answered Oct 5, 2009 at 10:44

community wiki

dariom

Comments

2 revs, 2 users 83% · Accepted Answer · 2012-10-29 11:47:37Z

0

Had a similar problem updating in mysql with datagrid (vb.net). Solved it with:

Dim cmb As New MySqlCommandBuilder(datGrid)
Me.datGrid.Update(datSet, "mysqlTableToUpdate")

edited Oct 29, 2012 at 11:47

community wiki

2 revs, 2 users 83%
LiTuNi

Collectives™ on Stack Overflow

How to insert,delete,select,update values in datagridview in C# using MYSQL

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related