Btukfyl

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

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]

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