none
Error when Saving to Ms Access Database using INSERT INTO - {"Missing semicolon (;) at end of SQL statement."} Exception RRS feed

  • Question

  • Hi,

      I have an error working with OleDb and INSERT INTO statement, I get an exception thrown which states that

    Missing semicolon (;) at end of SQL statement.

    I have stepped into the code and find that command.ExcecuteNonQuery() actually throws that exception.

                string insertSql = "INSERT INTO tblParts ([SerialNumber], [PartNumber], [ModuleName], [ProductNumber], [VendorID], [Model], [ModuleState], [ModuleVariant], [InstallBase], [Tag])" +
                    "VALUES (@SerialNumber, @PartNumber, @ModuleName, @ProductNumber, @VendorID, @Model, @ModuleState, @ModuleVariant, @InstallBase, @Tag))";
    
                using (OleDbConnection connection = new OleDbConnection(databaseConnString.GetConnectionString))
                {
                    connection.Open();
                    using (OleDbCommand command = new OleDbCommand(insertSql, connection))
                    {
                        command.Parameters.AddWithValue("@SerialNumber", SerialNumberTextBox.Text.Trim());
                        command.Parameters.AddWithValue("@PartNumber", partNumberTextBox.Text.Trim());
                        command.Parameters.AddWithValue("@ModuleName", moduleNameTextBox.Text.Trim());
                        command.Parameters.AddWithValue("@ProductNumber", productNumberTextBox.Text.Trim());
                        command.Parameters.AddWithValue("@VendorID", vendorTextBox.Text.Trim());
                        command.Parameters.AddWithValue("@Model", modelTextBox.Text.Trim());
                        command.Parameters.AddWithValue("@ModuleState", ModuleStateComboBox.SelectedText.ToString());
                        command.Parameters.AddWithValue("@ModuleVariant", moduleVariantTextBox.Text.Trim());
                        command.Parameters.AddWithValue("@InstallBase", installBaseTextBox.Text.Trim());
                        command.Parameters.AddWithValue("@Tag", tagTextBox.Text.Trim());
    
                        command.ExecuteNonQuery();
                        connection.Close();
                    }
                }

    I don't understand why am asked yet I have another method that Saves to the Ms Access 2007 database following the same specific steps. I get the same error when I employee another means, using INSERT INTO statement to save details.

    Here is the other method that throws same exception

      string insertSql = "INSERT INTO tblParts ([SerialNumber], [PartNumber], [ModuleName], [ProductNumber], [VendorID], [Model], [ModuleState], [ModuleVariant], [InstallBase], [Tag])" +
                    "VALUES (@SerialNumber, @PartNumber, @ModuleName, @ProductNumber, @VendorID, @Model, @ModuleState, @ModuleVariant, @InstallBase, @Tag))";    
    
            try
                {
                    using (OleDbConnection connection = new OleDbConnection(databaseConnString.GetConnectionString))
                    {
                        connection.Open();
                        using (OleDbCommand command = new OleDbCommand(insertSql, connection))
                        {
                            command.Parameters.Add("@SerialNumber", OleDbType.VarChar).Value = SerialNumberTextBox.Text.Trim();
                            command.Parameters.Add("@PartNumber", OleDbType.VarChar).Value = partNumberTextBox.Text.Trim();
                            command.Parameters.Add("@ModuleName", OleDbType.VarChar).Value = moduleNameTextBox.Text.Trim();
                            command.Parameters.Add("@ProductNumber", OleDbType.VarChar).Value = productNumberTextBox.Text.Trim();
                            command.Parameters.Add("@VendorID", OleDbType.VarChar).Value = vendorTextBox.Text.Trim();
                            command.Parameters.Add("@Model", OleDbType.VarChar).Value = modelTextBox.Text.Trim();
                            command.Parameters.Add("@ModuleState", OleDbType.VarChar).Value = ModuleStateComboBox.SelectedItem.ToString();
                            command.Parameters.Add("@ModuleVariant", OleDbType.VarChar).Value = moduleVariantTextBox.Text.Trim();
                            command.Parameters.Add("@InstallBase", OleDbType.VarChar).Value = installBaseTextBox.Text.Trim();
                            command.Parameters.Add("@Tag", OleDbType.VarChar).Value = tagTextBox.Text.Trim();
    
                            command.ExecuteNonQuery();
                            connection.Close();
                            MessageBox.Show("Saved the module's details successfully", "Operation successful",
                                MessageBoxButtons.OK, MessageBoxIcon.Information);
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Unable to save the module's details, An error occurred. Kindly try again. Details\n\n" + ex.Message,
                        "Saving unsuccessful", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }

    Please help mke understand what's wrong.


    Dennis

    Tuesday, August 5, 2014 12:02 PM

Answers

  • It looks like there is an extra closing ) on each of the Insert statements. If you remove that it might be OK.
    • Edited by JayChase Tuesday, August 5, 2014 12:07 PM
    • Proposed as answer by Nico Boey Tuesday, August 5, 2014 12:30 PM
    • Marked as answer by Dennis254 Tuesday, August 5, 2014 1:56 PM
    Tuesday, August 5, 2014 12:06 PM

All replies

  • It looks like there is an extra closing ) on each of the Insert statements. If you remove that it might be OK.
    • Edited by JayChase Tuesday, August 5, 2014 12:07 PM
    • Proposed as answer by Nico Boey Tuesday, August 5, 2014 12:30 PM
    • Marked as answer by Dennis254 Tuesday, August 5, 2014 1:56 PM
    Tuesday, August 5, 2014 12:06 PM
  • Thanks a lot, your response helped get it, it's the last ) that brought a problem since it wasn't matched by an opening tag, the string instead should have look like this.

      string insertSql = "INSERT INTO tblParts ([SerialNumber], [PartNumber], [ModuleName], [ProductNumber], [VendorID], [Model], [ModuleState], [ModuleVariant], [InstallBase], [Tag])" +
                    "VALUES (@SerialNumber, @PartNumber, @ModuleName, @ProductNumber, @VendorID, @Model, @ModuleState, @ModuleVariant, @InstallBase, @Tag)";

    That works perfectly, thank you once again for your time in addressing my error.



    Dennis

    Tuesday, August 5, 2014 1:59 PM