none
Incorrect syntax error message at runtime RRS feed

  • Question

  • I have this code. It is similar to many other procedures to access my SQL Server I have written before. Now it gives me a strange message and I don't understand what it is.

            private void combo_SQLCodePackages_PG1_SelectedIndexChanged(object sender, EventArgs e)
            {
                string tableName = (string)this.combo_SQLCodePackages_PG1.Text;
                Service service = new Service();
                Server srv = service.GetServer("SqlCodeSamples");
                using (SqlConnection conn = new SqlConnection
                                    (srv.ConnectionContext.ConnectionString))
                {
                    conn.Open();
                    SqlCommand cmdm = new SqlCommand();
                    cmdm.Connection = conn;
                    cmdm.CommandType = CommandType.Text;
                    cmdm.CommandText = "SELECT * FROM [dbo].[" + tableName +
                        "] WHERE 'RECORD_ID' = '" + numericUpDown_PG1.Value +"' )";
                    try
                    {
                        using (SqlDataReader rdr = cmdm.ExecuteReader(CommandBehavior.SequentialAccess)) <== ERROR Incorrect syntax near ')'
                        {
                            if (rdr.HasRows == true)
                            {
                                foreach (System.Data.Common.DbDataRecord row in rdr)
                                {
                                    numericUpDown_PG1.Value = (int)row["RECORD_ID"];  
                                    tBoxRecords_PG1.Text = (string)row["SQL_TITLE"];  
                                    richTextBox1_PG1.Text = (string)row["SQL_CODE"];  
                                }
                            }
                            if (rdr.IsClosed == false)
                            {
                                rdr.Close();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message + "  " + ex.InnerException);
                        TSP.textToSpeech2("Operation failed");
                    }
                }
            }      // combo_SQLCodePackages_PG1_SelectedIndexChanged

    The message is marked at the code line. It is "Incorrect syntax near ')' I get it at runtime, the code compiles well. There is a similar  routine in the same application. That one also compiles and causes no runtime problems.

    What is it?

    Thanks, - MyCatAlex


    • Edited by MyCatAlex Friday, November 15, 2019 8:05 PM
    Friday, November 15, 2019 8:04 PM

Answers

  • The error is not in the C# code (this would catch the incorrect syntax at compilation time) but in the SQL that you build inside a string. This returns the error at runtime because this is when the SQL is sent to the server for execution, and it returns back the "incorrect syntax" message to your C# code.

    Specifically, the error is the superfluous parenthesis at the end of the CommandText.

    Fix:

    cmdm.CommandText = "SELECT * FROM [dbo].[" + tableName +
                       
    "] WHERE 'RECORD_ID' = '" + numericUpDown_PG1.Value +"'";

    Note the removed parenthesis just before the last double quote.

    By the way, unless your RECORD_ID is a string, you should not be passing the single quotes around numericUpDown_PG1.Value. Also, there should not be single quotes around the word RECORD_ID. Otherwise the SQL statement is trying to compare the value of the numericupdown against the word "RECORD_ID" and of course it will never be equal, so you will never get any records returned by the query.
    Friday, November 15, 2019 8:13 PM
    Moderator

All replies

  • The error is not in the C# code (this would catch the incorrect syntax at compilation time) but in the SQL that you build inside a string. This returns the error at runtime because this is when the SQL is sent to the server for execution, and it returns back the "incorrect syntax" message to your C# code.

    Specifically, the error is the superfluous parenthesis at the end of the CommandText.

    Fix:

    cmdm.CommandText = "SELECT * FROM [dbo].[" + tableName +
                       
    "] WHERE 'RECORD_ID' = '" + numericUpDown_PG1.Value +"'";

    Note the removed parenthesis just before the last double quote.

    By the way, unless your RECORD_ID is a string, you should not be passing the single quotes around numericUpDown_PG1.Value. Also, there should not be single quotes around the word RECORD_ID. Otherwise the SQL statement is trying to compare the value of the numericupdown against the word "RECORD_ID" and of course it will never be equal, so you will never get any records returned by the query.
    Friday, November 15, 2019 8:13 PM
    Moderator
  • An eagle's eye! Brilliant!

    Many thanks. Everything works now.

    - MyCatAlex

    Friday, November 15, 2019 9:12 PM