471,049 Members | 1,929 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,049 software developers and data experts.

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

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

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

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://

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

<asp:DropDownList ID="DropDownList1" runat="server"
DataTextField="LastName" DataValueField="LastName">
<asp:TextBox ID="TextBox1" runat="server">%man</asp:TextBox>
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)
SET ss = CONCAT('%',search_string,'%');
SELECT NDB_No,Long_Desc FROM food_des WHERE Long_Desc LIKE ss;

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

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

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.



Feb 20 '07 #1
1 1669
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



Feb 22 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Alex Glaros | last post: by
reply views Thread by Andreas Reuleaux | last post: by
reply views Thread by Lloyd Dobbler | last post: by
4 posts views Thread by ItNerd | last post: by
7 posts views Thread by Ivan Marsh | last post: by
6 posts views Thread by Jim M | last post: by
8 posts views Thread by rdemyan via AccessMonster.com | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.