How do I insert the auto-increment ID into a MySQL table using MySQL ODBC with ASP.Net/C#?

Question

I am using MySQL ODBC to insert data into a MySQL table. The first column in the table is an ID that is of type int and auto increments. When I insert the data for the very first row, what should the value be for @ReqID, as shown below? Also, how do I ensure that subsequent executions are auto incrementing for the ID?

Here is the C#:

            string conString = WebConfigurationManager.ConnectionStrings["mysql"].ConnectionString;
        using (OdbcConnection con = new OdbcConnection(conString))
        {
            con.Open();
            using (OdbcCommand cmd = con.CreateCommand()) {
                cmd.CommandText = "INSERT INTO GraphicsRequest (RequestID, Graphic1Desc, Graphic2Desc, Graphic3Desc, ColorChart, Hex1, Hex2, Hex3, Hex4) VALUES (@reqID, @g1d, @g2d, @g3d, @colorChart, @hex1, @hex2, @hex3, @hex4)";
                cmd.Parameters.AddWithValue("@reqID", 1);
                cmd.Parameters.AddWithValue("@g1d", txtGraphic1Desc.Text);
                cmd.Parameters.AddWithValue("@g2d", txtGraphic2Desc.Text);
                cmd.Parameters.AddWithValue("@g3d", txtGraphic3Desc.Text);

                cmd.Parameters.AddWithValue("@colorChart", ddlColorChart.SelectedValue);
                cmd.Parameters.AddWithValue("@hex1", lblColor1.Text);
                cmd.Parameters.AddWithValue("@hex2", lblColor2.Text);
                cmd.Parameters.AddWithValue("@hex3", lblColor3.Text);
                cmd.Parameters.AddWithValue("@hex4", lblColor4.Text);
                cmd.ExecuteNonQuery();
            }
        }

I am not sure I understand. You shouldn't need to insert data for an auto increment column. — kush, Mar 13 '13 at 18:04
Am I supposed to instead replace '@reqID' with a question mark for the VALUES section? — Monica, Mar 13 '13 at 18:05
@Monica...not sure about MySql, but in SQL Server, I just skip it — MikeTWebb, Mar 13 '13 at 18:07
Additionally, you should install the MySQL Connection and use the MySQL specific objects instead of the generic OLEDB objects: http://www.mysql.com/downloads/connector/net/ — Mark Kram, Mar 13 '13 at 18:07
@MarkKram, I tried using that but couldn't find much help for it. — Monica, Mar 13 '13 at 18:09
@MonicaRice please see my answer regarding the use of the MySQL Connector — Mark Kram, Mar 13 '13 at 18:26

score 3 · Accepted Answer · answered Mar 13 '13 at 18:08

In MySQL you shouldn't supply the ID-field during an INSERT if you want to use the auto incrementing feature of the database itself.

So that would be in your case.

        string conString = WebConfigurationManager.ConnectionStrings["mysql"].ConnectionString;
    using (OdbcConnection con = new OdbcConnection(conString))
    {
        con.Open();
        using (OdbcCommand cmd = con.CreateCommand()) {
            cmd.CommandText = "INSERT INTO GraphicsRequest (Graphic1Desc, Graphic2Desc, Graphic3Desc, ColorChart, Hex1, Hex2, Hex3, Hex4) VALUES (@g1d, @g2d, @g3d, @colorChart, @hex1, @hex2, @hex3, @hex4)";
            cmd.Parameters.AddWithValue("@g1d", txtGraphic1Desc.Text);
            cmd.Parameters.AddWithValue("@g2d", txtGraphic2Desc.Text);
            cmd.Parameters.AddWithValue("@g3d", txtGraphic3Desc.Text);

            cmd.Parameters.AddWithValue("@colorChart", ddlColorChart.SelectedValue);
            cmd.Parameters.AddWithValue("@hex1", lblColor1.Text);
            cmd.Parameters.AddWithValue("@hex2", lblColor2.Text);
            cmd.Parameters.AddWithValue("@hex3", lblColor3.Text);
            cmd.Parameters.AddWithValue("@hex4", lblColor4.Text);
            cmd.ExecuteNonQuery();
        }
    }

This way the database will INSERT a new row with the next available the ID for you.

score 0 · Answer 2 · answered Mar 13 '13 at 18:25

Since the other have answered your question, I would suggest that you use the MySQL Connector in your project instead of using OLEDB objects. Download and install the MySQL Connector and then add a reference in your project to the MySQL.Data extension and then add the MySql.Data.MySqlClient using statement to your class and then update your code as follows:

string conString = WebConfigurationManager.ConnectionStrings["mysql"].ConnectionString;
using(MySqlConnection con = new MySqlConnection(connectionString))
{
     con.Open();
     using(MySqlCommand cmd = new MySqlCommand() 
     {
            cmd.Connection = con; //<-- It looks like you are missing this line in your code
            cmd.CommandText = "INSERT INTO GraphicsRequest (Graphic1Desc, Graphic2Desc, Graphic3Desc, ColorChart, Hex1, Hex2, Hex3, Hex4) VALUES (@g1d, @g2d, @g3d, @colorChart, @hex1, @hex2, @hex3, @hex4)";
            cmd.Parameters.AddWithValue("@g1d", txtGraphic1Desc.Text);
            cmd.Parameters.AddWithValue("@g2d", txtGraphic2Desc.Text);
            cmd.Parameters.AddWithValue("@g3d", txtGraphic3Desc.Text);
            cmd.Parameters.AddWithValue("@colorChart", ddlColorChart.SelectedValue);
            cmd.Parameters.AddWithValue("@hex1", lblColor1.Text);
            cmd.Parameters.AddWithValue("@hex2", lblColor2.Text);
            cmd.Parameters.AddWithValue("@hex3", lblColor3.Text);
            cmd.Parameters.AddWithValue("@hex4", lblColor4.Text);
            cmd.ExecuteNonQuery();
     }
}

Okay. I have another question. Do I need to add the System DSN to my machine in order to connect to a remote MySQL server? — Monica, Mar 13 '13 at 20:32
No, just create a a Connection String: string connString = string.Format(@"Provider=MySQLProv;Location={0};Data Source={1};USER ID={2};PASSWORD={3};option=3;port=3306;", ServerName, DatabaseName, LoginName, Password); — Mark Kram, Mar 13 '13 at 20:34
Yes, but you should be able to use your existing connection string. — Mark Kram, Mar 13 '13 at 20:59

score 0 · Answer 3 · edited Jun 04 '16 at 09:57

0

probably this would solve the problem .

strong textcom.ExecuteNonQuery();
long id = com.LastInsertedId;

edited Jun 04 '16 at 09:57

Bhushan Gadekar

13,485
21
82
131

answered Jun 04 '16 at 09:01

ammarsoftware

1
2

How do I insert the auto-increment ID into a MySQL table using MySQL ODBC with ASP.Net/C#?

3 Answers3