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
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";
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%";
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