向DataGridView插入autonumber并保存至数据库时遇Incorrect integer value错误求助

Hey there, let's work through this Incorrect integer value error you're facing when saving an autonumber column from your DataGridView to the database. This issue almost always boils down to a mismatch between how you're handling the auto-increment field in your code and how the database expects it to be handled. Here are the most common fixes:

1. Verify Your Database's Autonumber Setup #

First, double-check that your database table's autonumber field is properly configured to auto-generate values:

• For MySQL: Ensure the field has the AUTO_INCREMENT attribute set (it should also be the primary key).
• For SQL Server: Confirm the field uses the IDENTITY property (e.g., IDENTITY(1,1)).

If the field is set to auto-generate, you should not include it in your INSERT statement at all. Trying to pass a value (even NULL or an empty string) to this field will trigger the error.

2. Separate Display-Only Row Numbers from Database Autonumbers #

A common mistake is confusing a DataGridView's display-only row number column with the database's actual autonumber field. If you added a column to show row numbers in the grid (like "No."), this column isn't tied to the database and shouldn't be included in your save logic.

For example, if your grid has columns: RowNumber (display only), Name, Email — your INSERT statement should only reference Name and Email, not RowNumber.

3. Fix Your INSERT Statement & Parameterization #

Let's look at concrete code examples to see what's wrong vs. right.

Wrong Approach (Causes Error) #

Here, we're trying to pass a value for the auto-increment field (AutoID), which the database should generate itself:

// Bad: Includes AutoID in INSERT and passes a potentially invalid value
string sql = "INSERT INTO Customers (AutoID, Name, Email) VALUES (@AutoID, @Name, @Email)";
cmd.Parameters.AddWithValue("@AutoID", dataGridView1.Rows[currentRow].Cells["AutoID"].Value);
cmd.Parameters.AddWithValue("@Name", dataGridView1.Rows[currentRow].Cells["Name"].Value);
cmd.Parameters.AddWithValue("@Email", dataGridView1.Rows[currentRow].Cells["Email"].Value);
cmd.ExecuteNonQuery(); // Throws Incorrect integer value error

Correct Approach #

Omit the auto-increment field from the INSERT statement entirely, so the database handles generating the value:

// Good: Excludes AutoID, lets database generate it
string sql = "INSERT INTO Customers (Name, Email) VALUES (@Name, @Email)";
cmd.Parameters.AddWithValue("@Name", dataGridView1.Rows[currentRow].Cells["Name"].Value);
cmd.Parameters.AddWithValue("@Email", dataGridView1.Rows[currentRow].Cells["Email"].Value);
cmd.ExecuteNonQuery(); // No error (assuming other values are valid)

4. Handle New Rows in DataGridView #

When users add new rows to the grid, the autonumber column might show an empty value or a temporary placeholder. Make sure your save logic skips trying to read this value for new rows. You can check if a row is new using DataGridViewRow.IsNewRow:

foreach (DataGridViewRow row in dataGridView1.Rows)
{
    if (!row.IsNewRow)
    {
        // Only process non-new rows, and exclude the auto-increment field from INSERT
        string sql = "INSERT INTO Customers (Name, Email) VALUES (@Name, @Email)";
        // ... add parameters and execute ...
    }
}

5. Ensure Column Binding is Correct #

If your DataGridView is bound to a data source (like a DataTable), make sure the auto-increment column in the data source is set to read-only and configured to let the database generate the value. For example, in a DataTable:

DataColumn autoIdCol = new DataColumn("AutoID", typeof(int));
autoIdCol.AutoIncrement = false; // Let database handle it
autoIdCol.ReadOnly = true;
dataTable.Columns.Add(autoIdCol);

Quick Recap #

The key fix here is not trying to manually insert a value into the database's auto-increment field. Let the database do its job by omitting that field from your INSERT statement, and make sure your DataGridView's display-only row numbers don't get mixed up with the actual database autonumber column.

内容的提问来源于stack exchange，提问作者Bepooh