locked
C# - mySQL SELECT LIKE query not behaving properly RRS feed

  • Question

  • User-1915759290 posted

    Hello,

    I've run into a problem using a SELECT statement with LIKE clause. I am matching first names and last names with input from a text box.  The problem is that I have a record say... "Tommy" and when i enter "T" or "To" or "Tom" it matches up fine, but when i enter the whole name it doesn't return any results.  I have tried using parameters,but it still doesn't work.  Any ideas?  I have tried my exact query into mysql command line and it returns the record just fine.  So i'm losing something somewhere.  Anyone run into this before? Any ideas,

     Here is my class i'm using.

     

    1    public partial class Form1 : Form
    2        {
    3            public Form1()
    4            {
    5                InitializeComponent();
    6            }
    7    
    8            private void txtSearch_TextChanged(object sender, EventArgs e)
    9            {
    10               
    11               lb_Results.Items.Clear();
    12   
    13               string my_Param = txtSearch.Text;
    14               /* Nothing is entered, clear the ListBox */
    15               if (my_Param == "")
    16                   {
    17                   lb_Results.ClearSelected();
    18                   }
    19   
    20               MySql.Data.MySqlClient.MySqlParameter myParam = new MySql.Data.MySqlClient.MySqlParameter();
    21               myParam.ParameterName = "@my_Param";
    22               myParam.Value = my_Param;
    23   
    24               string myQuery = "SELECT FName, LName, CellPhone, Extension FROM agents_master WHERE FName LIKE '%" + @my_Param + "%' OR LName LIKE '%" + @my_Param + "%'";
    25               textBox1.Text = myQuery;
    26               MySql.Data.MySqlClient.MySqlConnection myConnection = new MySql.Data.MySqlClient.MySqlConnection("DataSource=localhost;database=portaldb;User Id=root;Password=;pooling=false;");
    27               
    28               MySql.Data.MySqlClient.MySqlCommand myCommand = new MySql.Data.MySqlClient.MySqlCommand(myQuery, myConnection);
    29   
    30               myCommand.Parameters.Add(myParam);
    31   
    32               MySql.Data.MySqlClient.MySqlDataReader myDataReader;
    33   
    34               /* Open up the Connection to the Database */
    35               myConnection.Open();
    36   
    37               /* Execute the DataReader with our Search Query */
    38               myDataReader = myCommand.ExecuteReader();
    39               myDataReader.Read();
    40   
    41               /* Tell the Paging System what to Display based on Results of the Search Query */
    42               if (myDataReader.HasRows)
    43                   {
    44                       /* Matches are found, format and display */
    45                       while(myDataReader.Read())                        {
    46                               string strFName = myDataReader.GetString("FName").ToString();
    47                               string strLName = myDataReader.GetString("LName").ToString();
    48                               string strCellPhone = myDataReader.GetString("CellPhone").ToString();
    49                               string strExtension = myDataReader.GetString("Extension").ToString();
    50                               lb_Results.Items.Add(strLName + ", " + strFName + " -- Mailto: " + strCellPhone);
    51                           }
    52                   }
    53               /* Nothing was found based on Search Criteria, Tell the System */
    54               else
    55                   {
    56                   lb_Results.Items.Add("No matches were found, try again.");
    57                   }
    58   
    59               /* Close the DataReader and The Database Connection */
    60               myDataReader.Close();
    61               myConnection.Close();
    62   
    63           }
    64   
    65   
    66   
    67   
    68       }
    

     

     

    I would appreciate any help. Thanks!

    Thursday, January 17, 2008 12:31 PM

All replies

  • User1187105292 posted

    I would go into MySql and execute the query with each of the four sample values you mentioned.

    If it works in MySql, the problem is in the C#.  If it doesn't work in MySql, the problem is with your sql or with MySql.

    Actually, it would be "TheirMySql" instead of "MyMySql". :)

    Thursday, January 17, 2008 4:35 PM
  • User-1915759290 posted

    david,

    Thanks for the reply.  Like I said earlier, I used the mysql client to query my database with the exact query from my code.  The querys came back fine. But when ran in my C# app, they drop records after 2 or 3 characters of input. That is why I am sure it is in the C#.  But I don't see what could be causing it to drop records like that after 2 or 3 characters.  Any ideas?

    Thanks for the suggestion.

    Thursday, January 17, 2008 4:53 PM
  • User1187105292 posted

    In Sql Server, I think you would want your query to look like this:

     = "SELECT FName, LName, CellPhone, Extension FROM agents_master WHERE FName LIKE '%' + @my_Param + '%' OR LName LIKE '%' + @my_Param + '%' ";

    The point of using parameterized queries is NOT to build the query by concatenating strings!

    I'm not sure what the MySql concatenation operator is.  In SqlServer, it's a + sign.  In Oracle, it's ||.

     

    Thursday, January 17, 2008 4:58 PM
  • User-1915759290 posted

    David,

    Thanks again for the reply.  I have tried your suggestion of adding the single quote after the "%".

    My SQL Query looks like this now.

    "SELECT FName, LName, CellPhone, Extension FROM database.table WHERE FName LIKE '%Timm%' OR LName LIKE '%Timm%';"

    I still am getting nowhere, that query returns the record of "Timmy" where as in my program, my listbox displays the record "Timmy" for the search parameter of "T" , "Ti" , "Tim" and then it gets no records beyond that. 

    Any ideas what the heck could be throwing this off as it does work in mysql client?

     Thanks for any suggestions.  Driving my crazy!

    --Jordan

    Friday, January 18, 2008 11:47 AM
  • User1187105292 posted

    I'm not a MySql programmer, so I'm making deductions based upon symptoms.

    20               MySql.Data.MySqlClient.MySqlParameter myParam = new MySql.Data.MySqlClient.MySqlParameter();
    21               myParam.ParameterName = "@my_Param";
    22               myParam.Value = my_Param;

    My guess is that this parameter object is truncating your value.

    I would put this parameter object into your debug watch window and root around in it to see if there are any parameters to set that deal with maximum length.

     

    Friday, January 18, 2008 12:25 PM
  • User1734617369 posted

    Hi,

    I think it is because of this row in the code section:

    39               myDataReader.Read();

    This makes the reader to miss the first row when you then iterate through the reader starting at row 45, try to remove line 39 and see if it helps. 

    Friday, January 25, 2008 9:32 AM
  • User192871164 posted

    My SQL Query looks like this now.

    "SELECT FName, LName, CellPhone, Extension FROM database.table WHERE FName LIKE '%Timm%' OR LName LIKE '%Timm%';"

    I still am getting nowhere, that query returns the record of "Timmy" where as in my program, my listbox displays the record "Timmy" for the search parameter of "T" , "Ti" , "Tim" and then it gets no records beyond that.  

     If they haven't change it I believe you only need to append the % sign to the end of the search item. ie: if it is Tim then it would be Tim%

     I use MySQL 4.1 and MySQL ODBC 3.51 Driver. also in C#. Been using them on an app now for 2 yrs. and always just appended to the end.

    Hope it helps

    Tuesday, January 29, 2008 3:32 PM
  • User1187105292 posted

     

    Did you remove the extra data reader call, per an earlier post?

    If you change the sql to an = sign that matches only one record (and not Timmy), do you get that record back?

    If you change it to an = sign that matches two records (again, not Timmy), do you get both back?

     

    Wednesday, January 30, 2008 8:59 AM
  • User192871164 posted

    Are you wanting to match the word  tim inside of another word? if so I did this and it works in code.

    string mySelectQuery = "SELECT Account,fname,lname from customerfile where fname like ";

    string item = "'%ton%'";       note here it is seperated   quotes "  single quote '%ton' single quote  "quotes

    then do mySelectQuery += item;

    this will match any string with    tim   in it, begining, middle, or end of the string.

     

    Wednesday, January 30, 2008 6:09 PM
  • User654887479 posted


    Hi jsiebecb

    im vinoth from colombo 

    Did u solv this prop im also geting error like this

    Monday, February 11, 2008 4:35 AM