C#+SQL processing reserved characters

[Microblitz]

I've written a program that loads in a text SQL file of commands Processes it and displays the result.

To test it I wrote a simple top 10 query which worked perfectly. Then I thought I'd like to test other scripts so in my text document I created:

select ItemDescription from PurchaseOrderIt ems
where ItemDescription like 'z%'

At which point my code balked at the like and whined about a syntax error in my SQL command code. I'm guessing that it doesnt like the ' symbol. How do I process this without altering the text file?

LOAD TEXT FILE
OPEN SQL CONNECTION
SEND TEXT SQL TO SERVER
DISPLAY RETURNED DATA
CLOSE SQL CONNECTION
END

DBSCAN.TXT contains
select ItemDescription from PurchaseOrderIt ems
where ItemDescription like 'z%'

       public void OpenDataBase(bool OK)
        {
            OK = true;
            string SQLinput = null;

            try
            {
                using (StreamReader sr = new StreamReader("c:\\dbscan.txt"))
                {
                    String line;
                    // Read and display lines from the file until the end of
                    // the file is reached.
                    while ((line = sr.ReadLine()) != null)
                    {
                        SQLinput = SQLinput + line;
                    }
                }
            }

            catch (Exception ex)
            {
                // Let the user know what went wrong.
                MessageBox.Show(ex.Message, "Error",
                MessageBoxButtons.OK,
                MessageBoxIcon.Exclamation,
                MessageBoxDefaultButton.Button1);
            }

            string SQLstring = @"" + SQLinput;

            //**********************
            try
            {
                SqlConnection myConnection = new SqlConnection(
                  "Data Source=sql-01;" +
                  "Initial Catalog=Training;" +
                  "Persist Security Info=True;" +
                  "User ID=xxx;Password=*xxx;");

                myConnection.Open();

                SqlDataReader reader = null;
                SqlCommand SQLcmd = new SqlCommand(SQLstring, myConnection);
                reader = SQLcmd.ExecuteReader();
                // *************************
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        //textBox1.Text = textBox1.Text + reader[0].ToString() + " : "+ reader[1].ToString() + Environment.NewLine;

                        textBox1.Text = textBox1.Text +reader.GetString(0)+Environment.NewLine;
                        
                    }
                }
                else
                {
                    MessageBox.Show("Error！");
                }

                myConnection.Close();
            }
            catch (SqlException ex)
            {
                OK = false; MessageBox.Show(ex.Message, "Error",
               MessageBoxButtons.OK,
               MessageBoxIcon.Exclamation,
               MessageBoxDefaultButton.Button1);
            }
            ovalShape1.FillColor = Color.Red;
        }
        private void ovalShape1_Click(object sender, EventArgs e)
        {

        }
    }

[Rabbit]

What line of the code is the error on? What is the exact error message? What does the SQL string look like before you submit it to the server?

[Microblitz]

It's being caught by the try catch exception. It doesn't report a line number but it should be the catch at 63 thats preventing an exception. The returned error string is "inccorect syntax near the keyword 'like'".

This program will happily process code which doesnt have reserved characters in it so I know the code works fine and if I manually create a string with the code in it it will process. It seems only an issue if it is read by stream reader from a text file. Something changes.

The problem is that I need this to load the SQL in from an
external source as I eventually want to process multiple SQL files on a timer basis to produce a graphic display, so this is a mission critical part of the system to resolve and it has me stumped.

[Rabbit]

Which try catch? You have more than one. What does the string look like before it is passed to the server? Have you tried escaping the single quotes?

[Microblitz]

... The catch at line number 63...

The string physically looks like the text document version.
If I copy paste that into SQL management and execute it it works perfectly.

For instance, if I change the dbtext.txt file to say 'select * from inventory' it works fine. So the code is concrete, but I think the syntax error being detected by the SQLcommand is the apostrophe at the beggining of '%z'.

[Microblitz]

Ok to find exactly what line number it faults in i removed the try catch and allowed it to exception.

It faults at line 43 with reader = SQLcmd.ExecuteR eader(); with the error "Incorrect syntax near the keyword like"

SQLcmd at this point is select ItemDescription from PurchaseOrderIt ems where ItemDescription like 'z%'

Hope this helps

[Rabbit]

Are you sure that's what's in the sql string that's being passed? Did you actually output it? What I was trying to lead you to is that the string you're passing is not what you think you're passing.

I am willing to bet that the string that's actually being passed is this:

select ItemDescription from PurchaseOrderItems whereItemDescription like 'z%'

This is because the ReadLine method does not contain the terminating character(s). So unless you insert white space or the file itself contains a space at the end of each line, then you're not going to get it when you append the lines together.

[Microblitz]

Youre right. Simply adding a space at the end of each line in the text file allowed the file to process properly.

Thank you.