How do i pass a dynamic MySql statement to gridview datasource?

Posted on 2009-04-27
Last Modified: 2012-05-06
Hi Experts,

I'm trying to pass a dynamic SQL statement to SQLdatasource using the text property of a label. The select statement displays correctly in the label and the gridview works works if I hardcode the statement with values, but when passed as a parameter it says:

Server Error in '/GeoRoute' Application.
ERROR [42000] [MySQL][ODBC 3.51 Driver][mysqld-5.0.22-community-nt]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 ''SELECT ........."

In the .cs file i have:
 Label2.Text = "SELECT d.Name, r.Destination FROM Georoute.routing r, " + Table + " d, Georoute.customer c where r.routingid = and = r.customerid and c.customerfk = '" + Acc + "' group by";        

And in the .aspx file i have:    
 <asp:SqlDataSource ID="Ganymede" runat="server"
        ConnectionString="<%$ ConnectionStrings:Connection %>"
        ProviderName="<%$ ConnectionStrings:Connection.ProviderName %>"        
        SelectCommand= "?" >                                
            <asp:ControlParameter ControlID="Label2" PropertyName="Text" Name="SqlString1" Type="string" DefaultValue="%"/>                                                              

Any help is greatly appreciated.


Question by:roblaw84

    Expert Comment

    you have an error in your sql syntax.  Perhaps, put a breakpoint on the sql line in  the sql file and paste the actual  sql here rather than the C# code.

    Also, try copying this SQL string and executing it directly against the database

    Author Comment

    Here's the actual SQL (and below with hardcoded values), this works if a run in directly against the databse.

    SELECT d.Name, r.Destination FROM Georoute.routing r, " + Table + " d, Georoute.customer c
    where r.routingid =
    and = r.customerid
     and c.customerfk = '" + Acc + "'
    group by";        

    SELECT d.Name, r.Destination
    FROM Georoute.routing r, georoute.county d, Georoute.customer c
    where r.routingid =
    and = r.customerid
    and c.customerfk = 'PNC001'
    group by  

    Author Comment

    Could it be that becuase it's being passed as a parameter the whole statements will be in quotes?

    Expert Comment

    It should work, although not best practice to have in-line SQL in C# code.

    Depending on your database setup, it maybe case sensitive.  

    Can you try setting Table = "" in the .cs instead of ""

    Also, you could try to put the string that works in the database in place of the string temporarily.

    Author Comment

    The database is definately not case senstive.

    I've tried setting the select command to the working string and it works but when i set the label text to the working string and then using that as a parameterit doesn't work.

    so the issue must be with string being passed as a parameter from the label.

    Is there anthoer way to pass the select statement in?

    Accepted Solution


    Expert Comment


    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    User art_snob ( encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
    Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now