parameterized mysql query in ASP.NET

I have a .net application written in C# that basically takes a name input by the user and will return results matching that input from a my sql database.  The problem I am having is returning results from the string input by the user.
The code is below but the problem section is really just:
WHERE (LastName LIKE '%?Name%')
I have tried tons of permutations of the like statement but with no luck.  Any ideas?

        <asp:SqlDataSource ID="mdSource" runat="server" 
            ConnectionString="<%$ ConnectionStrings:ConnectionStringMED %>" 
            ProviderName="<%$ ConnectionStrings:ConnectionStringMED.ProviderName %>" 
            SelectCommand="SELECT LastName, FirstName, MiddleName, AddressLine1, AddressLine2, City FROM patient WHERE (LastName LIKE '%?Name%') ORDER BY LastName, FirstName">
            <SelectParameters>
            <asp:ControlParameter ControlID="participantTextBox" Name="Name" 
                PropertyName="Text" Type="String" />
            </SelectParameters>

        </asp:SqlDataSource>

Open in new window

-AAA-Asked:
Who is Participating?
 
G_Hosa_PhatCommented:
Well then, I think I finally figured out what it was that I was overlooking...  The question I should've asked long ago was, "What data provider are you using?"  Apparently it's ODBC.  The syntax for the SELECT statement is based on both the data provider and type of database to which you're connecting.  Now I feel like a complete moron and absolutely NOT an expert.  I must be tired.  Yeah, that's it...

Now, let's go back to the FULL query and take a look at it.  When a syntax error is returned from MySQL, the nice thing is that we can usually determine where to look by the first character of the "problem" syntax.  Now that I wasted so much of your time, this syntax should work just fine
SELECT LastName, FirstName, MiddleName, AddressLine1, AddressLine2, City FROM patient WHERE (LastName LIKE CONCAT('%',?,'%')) ORDER BY LastName, FirstName

Open in new window

Make sure there's a space between "WHERE" and the opening parenthesis.  You may even want to take the parentheses away from the WHERE clause as they aren't necessary.  I find that eliminating those types of things really helps me when hammering out syntax errors.
0
 
G_Hosa_PhatCommented:
You'll need to use the MySQL CONCAT function to include the parameter's value in the query.  Right now, your SQL command is actually looking for a last name that contains "?Name".  Here's a query that SHOULD work for you:
SELECT LastName, FirstName, MiddleName, AddressLine1, AddressLine2, City FROM patient WHERE (LastName LIKE CONCAT('%','',?Name,'','%')) ORDER BY LastName, FirstName"

Open in new window

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
-AAA-Author Commented:
G_Hosa_Phat, I tried
WHERE (LastName LIKE CONCAT('%','',?Name,'','%'))
and
WHERE (LastName LIKE CONCAT('%' '',?Name,'' '%'))
each time i received this error:  Incorrect parameters in the call to native function 'CONCAT'
I am unsure what the problem is but it looks like concat was being used correctly

also i have one other small question as i revert a little bit in scope.  what do i need to change in this statement to have it execute correctly:  WHERE LastName LIKE ?Name
Right now i cant even search for exact match correctly nevermind trying to use LIKE.

0
 
G_Hosa_PhatCommented:
Well, one thing I just realized (I'm sure you caught it yourself) is that I accidentally left the double-quotes at the end.  Those should definitely be removed.

Secondly, I threw that string together in a little VB Windows form app I use for testing some database connections and such, so it should work, but I may have gotten a little overzealous with the single-quotes.  I'd been playing with a few too many variations from some of my code from different projects.  Perhaps using it this way instead:
WHERE (LastName LIKE CONCAT('%',?Name,'%'))

Open in new window


As far as I can see, the exact match should work, but , of course, you'll need to replace the word "LIKE" with the equal sign in the statement above.

If none of the above makes any difference, I guess I'm going to have to kick into "tech support" mode:  Can you run similar, regular SELECT queries on the MySQL server directly (i.e.,[]?  Do you know what version of MySQL server you're using (although it shouldn't necessarily matter)?  Do you get an accurate result if you ignore the parameter part of the equation completely and just use a literal value for the "?Name" parameter in the SQL Command?
0
 
-AAA-Author Commented:
Thanks G Hose Phat,
So I've backed up a bit and am just trying to get the exact match name.  So I have:
WHERE LastName = '?Name'
This results in no hits in my gridview so its almost like there's still a problem with the query itself.  I can do the same query with our oracle database as well as our MS sql database but each of those is different syntax so I just have to figure out this cursed syntax and get it but have been stuck for days now.

MySql is version 5.1

when i have this line in my code:
WHERE LastName LIKE '%'
I get a list of all the last name's which makes sense. so i am in fact able to access that data and populate the grid, just not which any type of further string like matching.

Likewise:
WHERE LastName LIKE 'S%'
results in a gridview with all the last names starting with s, now if i can just substitute the 'S' for my variable "Name" i would be all set!
0
 
G_Hosa_PhatCommented:
I'm using a MySQL v5.1 server myself, so that helps to make certain that the issue isn't related to the server version (even though I was fairly certain it wasn't anyway).  One thing I would say with the exact match is remember that the parameter in a MySQL statement is just like a variable in the ASP code, meaning you don't want to put the parameter name in quotes:
WHERE LastName = ?Name

Open in new window

NOT
WHERE LastName = '?Name'

Open in new window

I'm trying to dig a little deeper on the error message you reported you're getting since I haven't personally seen that one myself.  I'm not sure if I'm just overlooking something, or what, but I'll get back to you when I find something more.
0
 
-AAA-Author Commented:
your awesome, thanks again!

I agree that this should work
WHERE LastName = ?Name

Open in new window

but its the one giving me the odd error message:
ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.1.51-community]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Name ORDER BY LastName, FirstName' at line 1

It seems like if we can get that sorted then we can get to the bottom of the other issue.
0
 
G_Hosa_PhatCommented:
Okay, just for giggles, perhaps try just this:
WHERE LastName = ?

Open in new window

I know I've seen that syntax used, but I can't remember exactly why at the moment as I've always designated them by parameter name and never had any problems.
0
 
-AAA-Author Commented:
you're not going to believe this....
WHERE LastName = ?

Open in new window

it works in returning an exact match of the last name!!!

Now we just need to get the partial search working.  I tried this:
(LastName LIKE CONCAT('%',?,'%'))

Open in new window

with smi input and it gives this error.
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(LastName LIKE CONCAT('%','smi','%'))
If i try
(LastName LIKE CONCAT('%','?','%'))

Open in new window

its seems wrong since it had the '?' as opposed to the correct ?
any advice on what the correct syntax would be?  we are so close and I'm feeling much less stressed now :)
0
 
-AAA-Author Commented:
Well I'm a little confused now.  If I have the following code I get a 'You have an error in your SQL syntax' upon execution:
WHERE LastName LIKE ?

Open in new window

Shouldn't this work just fine and Finding any exact matches?
 
WHERE LastName = ?

Open in new window

works just fine and it seems to be failing with LIKE
0
 
-AAA-Author Commented:
well big mistake on my part, i accidentally deleted the "order" from the "order by" causing all these problems. the code below works perfect!!!
WHERE LastName LIKE Concat('%',?,'%')

Open in new window

G Hose Phat, thanks so much for your help you really made my day. cheers!
0
 
-AAA-Author Commented:
Awesome help from G Hose Phat@@@
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.