SQL Server contains predicate not working with multiple search terms

Question

I have a c# program that searches a table by the user's input.

The keywords are split by a space and saved into an array.

Then the switch statement will select the correct case based on having only entered one word, or two words.

My switch statement only fills my datagrid for first case, but when attempting to use 2nd case, my program goes to the catch exception.

I tried debugging, but the only thing I see is that when I enter case 2, it does not step beyond sda1.Fill(dt1);

Updated code:

static string myconnstr = ConfigurationManager.ConnectionStrings["connstrng"].ConnectionString;       

private void btnSearch_Click(object sender, EventArgs e)
{
    //Get the value from textbox
    string keyword = txtboxKeyword.Text;
    string[] words = keyword.Split(' ');

    //SQL Connection
    var conn = new SqlConnection(myconnstr);

    try
    {
        switch (words.Length)
        {
            case 1:
                    //Declare Command object with parameter
                    SqlCommand cmd = new SqlCommand("SELECT Site, StreetAddress, City, State, Zip, PharmacyPhone, MDVersion, InstallDate, SiteCodes, SiteNotActive, CloseDate, SiteNotes " +
                                    "FROM Sites WHERE contains(site, @words0) OR contains (StreetAddress, @words0) OR contains(city, @words0)", conn);

                    cmd.Parameters.AddWithValue("@words0", words[0]);

                    SqlDataAdapter sda = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    sda.Fill(dt);
                    dataGridSites.ReadOnly = true;
                    dataGridSites.DataSource = dt;
                    dataGridSites.CurrentCell = null;
                    break;
                case 2:
                    //Declare Command object with parameter
                    SqlCommand cmd1 = new SqlCommand("SELECT Site, StreetAddress, City, State, Zip, PharmacyPhone, MDVersion, InstallDate, SiteCodes, SiteNotActive, CloseDate, SiteNotes " +
                                    "FROM Sites WHERE contains(site, @words0, @words1) OR contains (StreetAddress, @words0, @words1) OR contains(city, @words0, @words1)", conn);

                    cmd1.Parameters.AddWithValue("@words0", words[0]);
                    cmd1.Parameters.AddWithValue("@words1", words[1]);

                    SqlDataAdapter sda1 = new SqlDataAdapter(cmd1);
                    DataTable dt1 = new DataTable();
                    sda1.Fill(dt1);
                    dataGridSites.ReadOnly = true;
                    dataGridSites.DataSource = dt1;
                    dataGridSites.CurrentCell = null;
                    break;                   
        }
    }
    catch (Exception)
    {
        MessageBox.Show("Search cannot be blank.");
    }            
}

Here is updated exception error I am getting:

System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near '@words1'. Error Number:102,State:1,Class:15

Getting more information about the exception would help us tremendously, but I haven't seen contains() used with "and" before. Try replacing `contains(city, @words0 and @words1)` with `contains(city, @words0, @words1)` — Thorin Jacobs, Nov 28 '18 at 20:11
I would rethink your approach, I would create a valid method for each filter instance and then create a `Dictionary>` to call a dictionary with a method to be executed explicitly. Then simply call the key you want, then use `.Invoke` to execute the proper implementation rather than a switch with case. It will be more expressive in my opinion. You receive the valid model of the data from the filter, then bind to your control. — Greg, Nov 28 '18 at 20:16

mjwills · Accepted Answer · 2018-11-28T21:01:05.177

The reason your existing code doesn't work is that contains doesn't support three parameters in the way you are currently trying to call it.

As per the documentation, I would suggest changing:

FROM Sites WHERE contains(site, @words0) OR contains (StreetAddress, @words0) OR contains(city, @words0)

to:

FROM Sites WHERE contains((site, StreetAddress, city), @words0)

And:

FROM Sites WHERE contains(site, @words0, @words1) OR contains (StreetAddress, @words0, @words1) OR contains(city, @words0, @words1)

to:

FROM Sites WHERE contains((site, StreetAddress, city), @words0) OR contains((site, StreetAddress, city), @words1)

If you really want to use your current more verbose style, then Example I suggests that:

FROM Sites WHERE contains(site, @wordsConcat) OR contains (StreetAddress, @wordsConcat) OR contains(city, @wordsConcat)

may work, where @wordsConcat has been set (by C#) to:

words[0] + " OR " + words[1]

Thank you! I made the changes per your suggestion and this fixed my issue. — Andriks Salvatierra, Nov 29 '18 at 14:17

score -6 · Answer 2 · answered Nov 28 '18 at 20:09

-6

I think it is a scope problem' knowen with switch case, for historical reasons.That why we have to put break after each statment. Try to have a look here. Variable declaration in a C# switch statement

answered Nov 28 '18 at 20:09

Netanel R

126
1
7

4

You don't put a `break` after each statement. You only put it **once** after the whole `case` block is done to prevent fall through. – Xiaoy312 Nov 28 '18 at 20:15
1

I don't understand how this is relevant to the question. The link provided (which links shouldn't be used as answers) is referring to declaring variables within the switch case using the same names as others declared within the following switch case(s). The OP has used unique variable names in each switch case here already. – Michael Puckett II Nov 28 '18 at 20:20

SQL Server contains predicate not working with multiple search terms

2 Answers2