C# - System.Data.SqlClient.SqlException: 'Incorrect syntax near ')'.'

Question

I keep getting the above error when trying to execute the ExecuteReader function. Basically what I'm trying to do is establish a connection to a native Database/datatable, read a text file by reading each line and querying that against a certain column from my datatable to see if it returns any rows. If true that mark it as a certain type of variable, if false, move to the next piece of logic. Below is the shorten code I'm using

        SqlDataReader rdr = null;
        SqlConnection con = null;
        SqlCommand cmd = null;


        //Open file dialog
        if (openFileDialog1.ShowDialog() == DialogResult.Cancel)
        {
            return;
        }
        if (openFileDialog1.FileName != null)
        {
            inFile = new StreamReader(openFileDialog1.FileName);

        }

        try
        {
            string ConnectionString = @"Data Source=DESKTOP-MB38MQ0\NEDS_DATA;Initial Catalog=master;Integrated Security=True";
            con = new SqlConnection(ConnectionString);
            con.Open();
            string CommandText = "SELECT CSZ" +
                             " FROM master.dbo.NEDS_ZIP_CODE_2" +
                             " WHERE (CSZ LIKE @City%)";
            cmd = new SqlCommand(CommandText);
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;

            //looking into the file
            while (inFile.Peek() != -1)
            {
                //Grabbing each value in the text file 
                string x = inFile.ReadLine().Trim().ToUpper();
                string x5 = x.Substring(0, x.Length - 5); //for database search>Cities

                cmd.Parameters.AddWithValue("@City", x5);
                rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                  If(rdr.HasRows == True)
                  {
                  // assign a value
                  }
                }
           }
        catch (IOException ex)
        {
            Console.WriteLine(ex.ToString());
        }
        finally
        {
            inFile.Close(); //closing the file
            if (rdr != null)
                rdr.Close();
            if (con.State == ConnectionState.Open)
                con.Close();

        }

As the error explains, the syntax is invalid. You can't write `LIKE @City%`, you need to use `LIKE @City + '%'` — Panagiotis Kanavos, Nov 21 '18 at 15:37
BTW the way the code is written you'll get another error for the second row saying that you tried to add the same parameter twice. *Don't* use `AddWithValue`. Add the parameter *outside* the loop and simply set its value inside it — Panagiotis Kanavos, Nov 21 '18 at 15:38
Finally, *don't* declare variables at the top of the method. Declare them only where you need to use them. Readers, connections, commands, file streams should be declared inside `using()` statements to ensure they are closed and disposed even if an exception occurs. `try/finally` isn't the same as `using()` — Panagiotis Kanavos, Nov 21 '18 at 15:40
Will do, I'm still learning on the fly. I appreciate the help! — Clayton McKinney, Nov 21 '18 at 16:10

score 0 · Answer 1 · answered Nov 21 '18 at 15:36

0

You can't add a wildcard into the "LIKE" part of your query like that. You'd be better off appending a % sign to the string after you read it in from the file.

answered Nov 21 '18 at 15:36

MikeS

1,734
1
9
13

C# - System.Data.SqlClient.SqlException: 'Incorrect syntax near ')'.'

1 Answers1