Click here to Skip to main content
15,867,756 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hallo everyone im trying to use a select statement to filter data
I have a textbox which is the value that the like statement uses
the user logs in and that value gets stored in an session variable and gets used in the select statement my problem is if I ad the like clause the my select statement returns noting because the like returns more than one value.
Here is my select statement

MySqlCommand sqlC = new MySqlCommand("select * from T_users where Serial_Number like @filterText and where Company_Name = '" + CompanyName + "' ", myconfill);

@Filtertext is whatever the user types in it is a parameter which I define in earlier code


How mould I get aroundd this problem of the @filtertext being more than one value then not returning a result
Posted

1 solution

No, the chances are that the LIKE statement does not "returns noting because the like returns more than one value."

The chances are that the LIKE statement returns nothing because it finds no matches.

LIKE needs wildcard characters; without them it behaves exactly like an quality statement.
SQL
SELECT * FROM MyTable WHERE text LIKE 'hello'
returns exactly the same data as
SQL
SELECT * FROM MyTable WHERE text = 'hello'

If you want to use LIKE, then either your user or you need to add SQL wildcard characters (such as '%' which matches zero or more of any character):
SQL
SELECT * FROM MyTable WHERE text LIKE '%hello'
Will find all rows ending with "hello"
SQL
SELECT * FROM MyTable WHERE text LIKE 'hello%'
Will find all rows starting with "hello"
SQL
SELECT * FROM MyTable WHERE text LIKE '%hello%'
Will find all rows with "hello" in there somewhere.
 
Share this answer
 
Comments
mrDivan 22-Oct-12 7:47am    
Hallo thank you for your help it does have a wildcart in the variable so that works the problem is lets say I have a database with a company 4 users and each user has 4 recipients lets say user 4 has the following recipients Abe, Andrew, Allan and bart the user then types in an a in the filtertextbox to fillter on lets say he wants to return all the names containing an a then it will return Abe,Andrew and allen the problem Im expreriencing is my select statement will then look like this
select * from t recipients where CompanyName = @CompanyName"this is an session variable" and UserName = @UserName"this is an session variable" and recipient like @ filtertext "which is now Abe, Andrew, Allan" so there is the problem I just dont know how to fix it
OriginalGriff 22-Oct-12 8:01am    
Would you mind trying to explain that a bit more clearly. I was with you up to the example of the select statement.
Do you mean you don't know how to pass the session variable to the SQL? Or something else?
mrDivan 22-Oct-12 8:48am    
Hi Thank you No I know how to pass the session varaibles to the sql the problem is with mySql statememt and how to get it to work with the like statement returning more than one value
mrDivan 22-Oct-12 8:50am    
hallo here is my code

MySqlCommand sqlC = new MySqlCommand("select * from T_users where Active like @filterText and where Company_Name = '" + CompanyName + "' ", myconfill);
sqlC.Parameters.AddWithValue("@filtertext", '%' + txtFilter.Text + '%');
MySqlDataAdapter da = new MySqlDataAdapter(sqlC);
DataSet ds = new DataSet();
da.Fill(ds);
gdvauthors.DataSource = ds;
gdvauthors.DataBind();
myconfill.Close();

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900