[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2009-04-27
7
Medium Priority
?
531 Views
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 = d.id and c.id = r.customerid and c.customerfk = '" + Acc + "' group by d.id";        

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

Any help is greatly appreciated.

Thanks,

0
Comment
Question by:roblaw84
  • 4
  • 3
7 Comments
 

Expert Comment

by:xiyuzu
ID: 24240087
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
0
 

Author Comment

by:roblaw84
ID: 24240112
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 = d.id
and c.id = r.customerid
 and c.customerfk = '" + Acc + "'
group by d.id";        


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

Author Comment

by:roblaw84
ID: 24240127
Could it be that becuase it's being passed as a parameter the whole statements will be in quotes?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Expert Comment

by:xiyuzu
ID: 24241258
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 = "Georoute.country" in the .cs instead of "georoute.country"

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

Author Comment

by:roblaw84
ID: 24241327
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?
0
 

Accepted Solution

by:
xiyuzu earned 375 total points
ID: 24243442
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Suggested Courses

872 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