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

  • Thread starter Thread starter Ted
  • Start date Start date
T

Ted

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
 
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
 
Back
Top