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.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
