asked on

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

Rahul Agarwal

Please check these links:

http://www.vbdotnetforums.com/mysql/42753-parameterized-query-asp-net-visual-basic-mysql.html
http://preetul.wordpress.com/2010/05/06/parameterized-query/
http://stackoverflow.com/questions/652978/parameterized-query-for-mysql-with-c

G_Hosa_Phat

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

-AAA-

ASKER

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.

G_Hosa_Phat

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?

-AAA-

ASKER

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!

G_Hosa_Phat

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.

-AAA-

ASKER

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.

G_Hosa_Phat

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.

-AAA-

ASKER

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 :)

-AAA-

ASKER

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: