0

I have updated my code with the help of you all, but still have the problem where '*' wildcard does not work with my program:

I have a C# program that has an array of words given to it by an input field.

I can run the following query with the (*) wild card in SQL Management studio without issues and I get the results I need:

SELECT Site, StreetAddress, City, State, Zip, PharmacyPhone, MDVersion, 
InstallDate, SiteCodes, SiteNotActive, CloseDate, SiteNotes 
FROM Sites 
WHERE contains(site, '"ev*"' ) OR contains  (StreetAddress, '"ev*"') 
OR contains(city, '"ev*"')

BUT when attempting to run the code below in my c#, I do not get any results. What am I doing wrong? the array would contain the string 'ev*'. c# code:

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

        string[] words = keyword.Split(' ');


        try
        {
            switch (words.Length)
            {
                case 1:
                    var select = "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)";

                    var conn = new SqlConnection(myconnstr);
                    SqlCommand cmd = new SqlCommand(select, conn);                            
                    cmd.Parameters.AddWithValue("@words0", words[0]);
                    SqlDataAdapter sda = new SqlDataAdapter(cmd);
                    var commandbuilder = new SqlCommandBuilder(sda);
                    var dt = new DataTable();
                    sda.Fill(dt);

                    dataGridSites.ReadOnly = true;
                    dataGridSites.DataSource = dt;
                    dataGridSites.CurrentCell = null;                                                                 
                    break;
Andriks Salvatierra
  • 11
  • 2
  • 3
    Can you show the actual code? The string concatenation you're showing isn't valid as it's currently written (there are no opening or closing quotes around the query string). Currently it appears that `'"+ words[0] +"'` is a string literal, not the concatenation of an item in an array. – Rufus L Nov 07 '18 at 20:11
  • Unless you mistyped it, they are different. The first `SELECT` statement is `WHERE contains(site, '"ev*"' )` and the second `SELECT` statement becomes `WHERE contains(site, 'ev*' )` – Icemanind Nov 07 '18 at 20:12
  • 2
    Do you actually have single and double quotes in the query you run in SQL Management Studio? – juharr Nov 07 '18 at 20:13
  • 1
    Possible duplicate of [What are good ways to prevent SQL injection?](https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection) – mjwills Nov 07 '18 at 20:24
  • @Icemanind I did not mistype it, that is what I currently have. how can I make the second code to be like the first? I tried adding a pair of double quotes like this: (site, '""+ words[0] + ""') but visual studios will not take it – Andriks Salvatierra Nov 07 '18 at 20:24
  • @juharr yes I do, and I get the records I need – Andriks Salvatierra Nov 07 '18 at 20:27
  • @AndriksSalvatierra - You need to do something like this: `WHERE contains(site, '\""+ words[0] +"\"' )`. Also, be careful because your code is vulnerable to SQL Injection. – Icemanind Nov 07 '18 at 20:28
  • It would be awesome if you could provide a [mcve]. – mjwills Nov 07 '18 at 20:29
  • 1
    If you can post the actual code and not just the query you want in plain text, we'll be more than happy to show you where you're getting your error. As of now, we can only guess what your actual problem is. If I had to guess, it's what Icemanind has mentioned and more than likely, you need to escape some quotes within the text. – Chris Nov 07 '18 at 20:30
  • as other are saying, the code you are showing does not compile, so it seems the code you are running is more or less different. As a first try, save your query in a string, and do a `System.Diagnostics.Debug.WriteLine(yourquery);` you will be able to see what is the actual query you are creating and how it is different from the working one – Gian Paolo Nov 07 '18 at 20:30
  • @Icemanind you're a genius. That's what I was looking for. Do you know why that worked or what the "\" means? thanks again! – Andriks Salvatierra Nov 07 '18 at 20:31
  • @AndriksSalvatierra - The `\"` escapes the quote and puts it literally in the string. – Icemanind Nov 07 '18 at 20:33
  • 3
    dear lord, please use the [SqlParameter](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlparameter?view=netframework-4.7.2) class – BurnsBA Nov 07 '18 at 20:39
  • 1
    `That's what I was looking for.` No - please read the duplicate I suggested. You have found a solution that _seems_ to work, but is immensely dangerous. You must not do this. – mjwills Nov 07 '18 at 21:05
  • @mjwills Thank you! I will read into that as I was not aware of the implications. I've got lots to read, thank you for the link! – Andriks Salvatierra Nov 08 '18 at 12:28
  • Since you are using `contains` instead of `like`, have you setup full text search? https://learn.microsoft.com/en-us/sql/relational-databases/search/get-started-with-full-text-search?view=sql-server-2017 – BurnsBA Nov 12 '18 at 15:21
  • @burnsBA yes, Full-text search is setup. When doing the test in SQL management studio, I am using the same DB as the program. It works in SQLMMS but not on the c# code. – Andriks Salvatierra Nov 12 '18 at 15:35

0 Answers0