Simple task works when MS SQL Server is the backend but not when MySQL is the backend.

Discussion in 'ASP .Net' started by Ted, Feb 20, 2007.

  1. Ted

    Ted Guest

    In MS SQL I used the following to create a stored procedure.

    USE AdventureWorks;
    GO
    IF OBJECT_ID ( 'HumanResources.usp_My_Search', 'P' ) IS NOT NULL
    DROP PROCEDURE HumanResources.usp_My_Search;
    GO
    CREATE PROCEDURE HumanResources.usp_My_Search
    @searchstring varchar(40)
    AS
    SELECT LastName, FirstName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment
    WHERE LastName LIKE @searchstring;
    GO
    HumanResources.usp_My_Search '%man%';
    GO

    Simple, obvious, and it works. Of course, if you have MS SQL Server
    2005, you will recognize the AdventureWorks database.

    Here is the whole webpage (the test project was created as a website):

    <%@ Page Language="C#" AutoEventWireup="true"
    CodeFile="Default.aspx.cs" Inherits="_Default" %>

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://
    www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

    <html xmlns="http://www.w3.org/1999/xhtml" >
    <head runat="server">
    <title>Untitled Page</title>
    </head>
    <body>
    <form id="form1" runat="server">
    <div>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$
    ConnectionStrings:AdventureWorksConnectionString2 %>"
    SelectCommand="EXECUTE HumanResources.usp_My_Search @p1">
    <SelectParameters>
    <asp:ControlParameter ControlID="TextBox1" Name="p1"
    PropertyName="Text" />
    </SelectParameters>
    </asp:SqlDataSource>

    </div>
    <asp:DropDownList ID="DropDownList1" runat="server"
    DataSourceID="SqlDataSource1"
    DataTextField="LastName" DataValueField="LastName">
    </asp:DropDownList>
    <asp:TextBox ID="TextBox1" runat="server">%man</asp:TextBox>
    </form>
    </body>
    </html>


    Everything in this trivially simple test works fine. If I change the
    content of TextBox1, the items in DropDownList1 are changed
    immediately. Perfect. Well almost. I haven't yet figured out how to
    programmatically change the value submitted to the stored procedure so
    that the user does not have to enter the leading or trailing '%'
    character, but that is a minor nuisance.

    But all is NOT rosy when I use MySQL instead of MS SQL.

    Here is the function I created in MySQL:

    CREATE PROCEDURE `sp_find_food`(
    IN search_string varchar(255)
    )
    BEGIN
    DECLARE ss VARCHAR(257);
    SET ss = CONCAT('%',search_string,'%');
    SELECT NDB_No,Long_Desc FROM food_des WHERE Long_Desc LIKE ss;
    END

    The similarity with my MS SQL Server stored procedure is obvious! And
    here is the markup that is supposed to exercise it:

    <LoggedInTemplate>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:sr19ConnectionString %>"
    ProviderName="<%$
    ConnectionStrings:sr19ConnectionString.ProviderName %>"
    SelectCommand="CALL sp_find_food('@ss')">
    <SelectParameters>
    <asp:ControlParameter
    ControlID="search_string" Name="@ss" PropertyName="Text" />
    </SelectParameters>
    </asp:SqlDataSource>
    <asp:SqlDataSource ID="SqlDataSource2" runat="server"
    ConnectionString="<%$ ConnectionStrings:sr19ConnectionString %>"
    ProviderName="<%$
    ConnectionStrings:sr19ConnectionString.ProviderName %>"
    SelectCommand="SELECT NDB_No,Long_Desc FROM sr19.food_des WHERE
    Long_Desc LIKE '@ss'">
    <SelectParameters>
    <asp:ControlParameter
    ControlID="search_string" Name="@ss" PropertyName="Text" />
    </SelectParameters>
    </asp:SqlDataSource>
    <table width=100%>
    <tr>
    <td>Hello <asp:LoginName runat="server" /></
    td>
    <td align="right"><asp:LoginStatus
    ID="LoginStatus1" runat="server" /></td>
    </tr>
    </table>
    <a href="ADProfile.aspx">Create your profile</a>&nbsp;
    <table width=100%>
    <tr>
    <td style="width: 30%">Enter part of a food
    name</td>
    <td style="width: 70%">
    <asp:TextBox ID="search_string"
    runat="server" AutoPostBack="True"></asp:TextBox></td>
    </tr>
    <tr>
    <td style="width: 30%">select a food</td><td
    style="width: 70%">
    <asp:DropDownList ID="DropDownList1"
    runat="server" AutoPostBack="True" DataSourceID="SqlDataSource1"
    DataTextField="Long_Desc"
    DataValueField="NDB_No" Width="100%">
    </asp:DropDownList></td>
    </tr>
    <tr>
    <td style="width: 30%">select a food</td>
    <td style="width: 70%">
    <asp:DropDownList ID="DropDownList2"
    runat="server" AutoPostBack="True" DataSourceID="SqlDataSource2"
    DataTextField="Long_Desc"
    DataValueField="NDB_No" Width="100%">
    </asp:DropDownList>
    </td>
    </tr>
    </table>
    </LoggedInTemplate>

    Again the parallel is obvious. But neither of these dropdownlists is
    ever populated! Since the second SQLDataSource uses a simple SELECT
    rather than my stored procedure, and it does not get populated either,
    my hunch is that there is something wrong with the MySQL .NET
    connector (MySQL Connector/Net 5.0.3). IS anyone using MySQL and this
    connector successfully? Can what I have done be fixed.

    How can I examine whatever is returned by the database, so I can find
    out where the problem is happening? I am aware that I can write code
    in the C# file corresponding to the page, but when I try, I can't seem
    to access the controls on the page. :-( This makes it hard to figure
    out whether the problem is with the database back end, the connector,
    or the ASP.NET page.

    Any help would be appreciated.

    Thanks

    Ted
     
    Ted, Feb 20, 2007
    #1
    1. Advertisements

  2. Ted

    Ted Guest

    Further information!

    I find I can get this connector to connect database tables to ASP.NET
    datasources, for display in an ASP.NET gridview. Even pagination and
    support for sorting appear to work. Where it fails to work properly
    is with statements to execute stored procedures and parameterized
    SELECT statements.

    Oh well, at least I can get MySQL to work with my Java programs and
    JDBC.

    Cheers

    Ted
     
    Ted, Feb 22, 2007
    #2
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.