mishmomo mishmomo - 6 months ago 47
SQL Question

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

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

Answer

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.

Comments