Click here to Skip to main content
12,444,790 members (54,426 online)
Click here to Skip to main content
Add your own
alternative version

Stats

16.7K views
6 bookmarked
Posted

MySqlCommand, MySqlParameter and "LIKE" with Percent Symbol

, 5 Aug 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
How to search text with LIKE with MySQLParameter in MySQL .NET / Connector

Introduction

Using GetSqlStringCommand with a text comparative, with LIKE, in ADO.NET and the MySqlParamenter gets you different results between executing by hand the command in a MySQL client and executing it through ADO.NET.

Background

This occurs when you write a command like this "SELECT * FROM users WHERE name LIKE '%John%'", this will return:

John Frank
Johnny Philips
H. F. John

But for ADO.NET if you set a var, like "@name" and update the command like this "SELECT * FROM users WHERE name LIKE '%@name%'", ADO.NET treats it as the string '@name' you will return 0 result, because there does not exist any user with the name @name or the email @name, but yes someone with an email of the domain "name.com", like alberto@name.com, but this is a casualty and not, what we expect.

So you need to remove the simple quota and set the value appending and preceding with "%".

Using the Code

//Wrong way
MySqlCommand cmd = oldDb.GetSqlStringCommand(CommandType.Text,"SELECT * _
FROM users WHERE name LIKE '%@name%'");
MySqlParameter nameParameter= cmd.CreateParameter();
nameParameter.DbType = DbType.String;
nameParameter.ParameterName = "@name";
nameParameter.Value = "John";

//Good way
MySqlCommand cmd = oldDb.GetSqlStringCommand(CommandType.Text,"SELECT * _
FROM users WHERE name LIKE @searchText");
MySqlParameter nameParameter= cmd.CreateParameter();
nameParameter.DbType = DbType.String;
nameParameter.ParameterName = "@searchText"
nameParameter.Value = "%John%"; 

License

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

Share

About the Author

AlbertoLeon CSharpMan
Technical Lead Get Me An Apartment
Spain Spain
I'm developer fall in love with C#, .NET and Mono.
Designing and programming websites since 2001.
I like literature (to read and to write), playing football (soccer)... and of course I'm great fan of La Roja (Spain football team)

At now I work in GetMeAnApartment, I do Research and Development features to upgrade our website (asp.net, MVC, MySQL) and business model (rental short stay apartments)


My Knowledge:
LANGUAGES: C#, XML, HTML, JavaScript, ActionScript, php, VisualBasic Script

TECHNOLOGIES: asp.net, asp.net mvc, php, flash, GTK#

APPLICATIONS: wordpress, wikimedia, I programmed modules for Macromedia Flash MX for myself

FRAMEWORKS: .Net Framework, Mono

SERVERS: IIS, apache2

SO: Windows, Debian

SOFTWARE: Visual Studio 2010, 2008. Monodevelop. Glade. Illustrator. Indesign. Photoshop. Dreamweaver.


Employment:

GetMeAnApartment
IT Director and website developer, 2010 - present

Círculo Rojo Publicidad y Comunicación
IT Director and websites developer, 2007 - 2010

Dédalo Creative
Co-founder,Programmer and Developer, Graphical designer 2006 - 2007

Costa Mediterránea Comunicación
Chief writer, 2004 - 2006

Stalker Creacion
Web designer and developer, 2001 - 2003

Education
Myself
Designer and Programmer, 2001 - present

Jaime I, University
Advertising, 2000 - 2005

You may also be interested in...

Comments and Discussions

 
Question@name >> @searchText Pin
adriancs4-Aug-13 17:28
mvpadriancs4-Aug-13 17:28 
AnswerRe: @name >> @searchText Pin
AlbertoLeon CSharpMan4-Aug-13 22:26
memberAlbertoLeon CSharpMan4-Aug-13 22:26 
QuestionMistype Pin
funnieguy4-Aug-13 7:15
memberfunnieguy4-Aug-13 7:15 
AnswerRe: Mistype Pin
AlbertoLeon CSharpMan4-Aug-13 7:48
memberAlbertoLeon CSharpMan4-Aug-13 7:48 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160811.3 | Last Updated 5 Aug 2013
Article Copyright 2013 by AlbertoLeon CSharpMan
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid