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. vEmployeeDepart ment
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="Defau lt.aspx.cs" Inherits="_Defa ult" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://
www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dt d">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitl ed Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSou rce ID="SqlDataSour ce1" runat="server"
ConnectionStrin g="<%$
ConnectionStrin gs:AdventureWor ksConnectionStr ing2 %>"
SelectCommand=" EXECUTE HumanResources. usp_My_Search @p1">
<SelectParamete rs>
<asp:ControlPar ameter ControlID="Text Box1" Name="p1"
PropertyName="T ext" />
</SelectParameter s>
</asp:SqlDataSour ce>
</div>
<asp:DropDownLi st ID="DropDownLis t1" runat="server"
DataSourceID="S qlDataSource1"
DataTextField=" LastName" DataValueField= "LastName">
</asp:DropDownLis t>
<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
programmaticall y 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('%',sear ch_string,'%');
SELECT NDB_No,Long_Des c 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:
<LoggedInTempla te>
<asp:SqlDataSou rce ID="SqlDataSour ce1" runat="server"
ConnectionStrin g="<%$ ConnectionStrin gs:sr19Connecti onString %>"
ProviderName="< %$
ConnectionStrin gs:sr19Connecti onString.Provid erName %>"
SelectCommand=" CALL sp_find_food('@ ss')">
<SelectParamete rs>
<asp:ControlPar ameter
ControlID="sear ch_string" Name="@ss" PropertyName="T ext" />
</SelectParameter s>
</asp:SqlDataSour ce>
<asp:SqlDataSou rce ID="SqlDataSour ce2" runat="server"
ConnectionStrin g="<%$ ConnectionStrin gs:sr19Connecti onString %>"
ProviderName="< %$
ConnectionStrin gs:sr19Connecti onString.Provid erName %>"
SelectCommand=" SELECT NDB_No,Long_Des c FROM sr19.food_des WHERE
Long_Desc LIKE '@ss'">
<SelectParamete rs>
<asp:ControlPar ameter
ControlID="sear ch_string" Name="@ss" PropertyName="T ext" />
</SelectParameter s>
</asp:SqlDataSour ce>
<table width=100%>
<tr>
<td>Hello <asp:LoginNam e runat="server" /></
td>
<td align="right">< asp:LoginStatus
ID="LoginStatus 1" 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_stri ng"
runat="server" AutoPostBack="T rue"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 30%">select a food</td><td
style="width: 70%">
<asp:DropDownLi st ID="DropDownLis t1"
runat="server" AutoPostBack="T rue" DataSourceID="S qlDataSource1"
DataTextField=" Long_Desc"
DataValueField= "NDB_No" Width="100%">
</asp:DropDownLis t></td>
</tr>
<tr>
<td style="width: 30%">select a food</td>
<td style="width: 70%">
<asp:DropDownLi st ID="DropDownLis t2"
runat="server" AutoPostBack="T rue" DataSourceID="S qlDataSource2"
DataTextField=" Long_Desc"
DataValueField= "NDB_No" Width="100%">
</asp:DropDownLis t>
</td>
</tr>
</table>
</LoggedInTemplat e>
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
