Bug #62300 SQLDataSource Wizard places "?" instead input parameter @<param. name> in query
Submitted: 30 Aug 2011 11:17 Modified: 6 Oct 2011 20:57
Reporter: Vladimir Fridman Email Updates:
Status: Unsupported Impact on me:
None 
Category:Connector / NET Severity:S4 (Feature request)
Version:6.4.3 OS:Windows (Windows 7)
Assigned to: Gabriela Martinez Sanchez CPU Architecture:Any
Tags: connector, InputParameter, SqlDataSource, Visual Studio

[30 Aug 2011 11:17] Vladimir Fridman
Description:
In Visual Studio 2010 SQLDataSource Wizard places "?" instead input parameter (usualy @name) in SELECT ... WHERE <condition> in case of using mySQL connector.

Example of the result statement from the wizard output:

SELECT [Last_Name], [Passport_Name], [Patronymic] FROM [human_copy] WHERE ([Last_Name] = ?)

But it should be like this: 

SELECT [Last_Name], [Passport_Name], [Patronymic] FROM [human_copy] WHERE ([Last_Name] = @Last_Name)

Wizard works perfect in case of usage MS SQL Server, but doesn't in case of use mySQL with same tables and conditions.

How to repeat:
1. Install Connector and set SQLServerMode = true to work with square brackets [ ]
2. Create a new web application.
3. Create DropDownList, configure SQLDataSource for it with the simple SELECT DISTINCT (works in SQLDataSource Wizard as is has no WHERE statement).
3. Create GridView and configure SQLDataSource using wizard following way:
- Specify table name
- Specify required columns
- Click "Where" button, Select required column (in my case LastName), select operator (in my case = ), select "Control" as source.

After that you will see that wizard generates SQLExpression  "[LastName] = ?" instead "[LastName] = @LastName

Such generated statement doesn't work at all. 

Suggested fix:
Avoid using wizards in case of mySQLConnector which may force as a result switch to another database that Visual Studio fully support.
[30 Aug 2011 11:20] Vladimir Fridman
Forgot to place source example:

    <form id="form1" runat="server">
    <div>
    
    </div>
    <asp:DropDownList ID="DropDownList1" runat="server" 
        DataSourceID="DropSqlDataSource" DataTextField="Cloth" DataValueField="Cloth">
    </asp:DropDownList>
    <asp:SqlDataSource ID="DropSqlDataSource" runat="server" 
        ConnectionString="<%$ ConnectionStrings:eparhiaConnectionString %>" 
        ProviderName="<%$ ConnectionStrings:eparhiaConnectionString.ProviderName %>" 
        SelectCommand="SELECT DISTINCT [Last_Name] FROM [human_copy]">
    </asp:SqlDataSource>
    <asp:GridView ID="GridView1" runat="server" DataSourceID="GridSqlDataSource">
    </asp:GridView>
    <asp:SqlDataSource ID="GridSqlDataSource" runat="server" 
        ConnectionString="<%$ ConnectionStrings:eparhiaConnectionString %>" 
        ProviderName="<%$ ConnectionStrings:eparhiaConnectionString.ProviderName %>" 
        SelectCommand="SELECT [Last_Name], [Passport_Name], [Patronymic] FROM [human_copy] WHERE ([Last_Name] = ?)">
        <SelectParameters>
            <asp:ControlParameter ControlID="DropDownList1" DefaultValue="NULL" 
                Name="Last_Name" PropertyName="SelectedValue" Type="String" />
        </SelectParameters>
    </asp:SqlDataSource>
    </form>
[20 Sep 2011 8:21] vinod kotiya
I solved this problem by making a small program which recreates the query by replacing ? with @paramname

Code is here http://vinodkotiya.blogspot.com/2011/09/aspnet-with-mysql-problems.html
[6 Oct 2011 20:57] Gabriela Martinez Sanchez
Currently this is not supported and could be a feature request for a future version.