Has anyone gotten MySQL Connector/Net 5.0.3 to work properly?
This is a discussion on Has anyone gotten MySQL Connector/Net 5.0.3 to work properly? within the MySQL Database forums, part of the Database Forums category; I am puzzled why my ASP.NET code works with MS SQL Server but not MySQL. In MS SQL Server ...
|
|||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-20-2007
|
|||
|
|||
Has anyone gotten MySQL Connector/Net 5.0.3 to work properly?
I am puzzled why my ASP.NET code works with MS SQL Server but not
MySQL. In MS SQL Server 2005 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.ProviderNam e %>" 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.ProviderNam e %>" 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> <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 
|
|
02-22-2007
|
|||
|
|||
|
Re: Has anyone gotten MySQL Connector/Net 5.0.3 to work properly?
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
|
Linear Mode
