使用C#向SQL Server插入数据遇语法错误：system.data.sqlclient.sqlexception

Hey there! Let's break down why you're hitting that System.Data.SqlClient.SqlException: incorrect syntax near ')' error. The issue boils down to how you're handling column names with spaces/special characters and invalid parameter names in your SQL query.

What Went Wrong #

• Column Names with Special Characters: SQL Server requires identifiers (like column names) that contain spaces, parentheses, or other special characters to be wrapped in square brackets []. Your original query uses Quantity (Gs.) and Quantity (USD) without these brackets, which confuses the SQL parser.
• Invalid Parameter Names: Parameter names in ADO.NET can't include spaces or parentheses. Your parameters like @Quantity (Gs.) are invalid and cause syntax breakdowns.

Corrected Code #

Here's the fixed version of your button click handler, plus improvements for better resource management:

private void button1_Click(object sender, EventArgs e)
{
    if (MessageBox.Show("Are you sure you want to save changes?", "Confirmation", MessageBoxButtons.OKCancel) == DialogResult.OK)
    {
        // Use using statements to auto-dispose connections/commands (prevents resource leaks)
        using (SqlConnection con = new SqlConnection(@"Data Source = ACER\SQLEXPRESS2016; Initial Catalog = Flujo_Efectivo; Integrated Security = True"))
        {
            con.Open();
            // Wrap special column names in [] and use valid parameter names
            string sqlQuery = @"INSERT INTO Entrada_Datos([Description], [Quantity (Gs.)], [Quantity (USD)]) 
                               VALUES (@Description, @QuantityGs, @QuantityUSD)";
            
            using (SqlCommand cmd = new SqlCommand(sqlQuery, con))
            {
                // Map text box values to clean parameter names
                cmd.Parameters.AddWithValue("@Description", textBox1.Text);
                cmd.Parameters.AddWithValue("@QuantityGs", textBox2.Text);
                cmd.Parameters.AddWithValue("@QuantityUSD", textBox3.Text);
                
                cmd.ExecuteNonQuery();
                MessageBox.Show("The changes have been saved", "Notification");
            }
        }
    }
}

Key Improvements Explained #

1. Bracketed Column Names: [Quantity (Gs.)] and [Quantity (USD)] tell SQL Server to treat these as valid column names instead of parsing spaces/parentheses as syntax.
2. Valid Parameter Names: @QuantityGs and @QuantityUSD replace the invalid parameter names, ensuring ADO.NET can correctly map values to the query.
3. Using Statements: These automatically close and dispose of SqlConnection and SqlCommand objects when they go out of scope, preventing resource leaks and issues with SQL connection pooling.

Bonus Tip #

If you can, consider renaming your database columns to avoid spaces and special characters (e.g., QuantityGs instead of Quantity (Gs.)). This will make your SQL queries cleaner and less prone to syntax errors long-term.

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