On Feb 15, 8:03 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> Ted wrote:
> > On Feb 15, 11:20 am, "Ted" <r.ted.by...@rogers.com> wrote:
> >> On Feb 15, 10:31 am, "Ted" <r.ted.by...@rogers.com> wrote:
>
> >>> On Feb 15, 3:52 am, Willem Bogaerts
> >>> <w.bogae...@kratz.maardanzonderditstuk.nl> wrote:
> >>>>> Here is what I tried.
> >>>>> CREATE PROCEDURE 'sr19'.sp_find_food(
> >>>>> IN search_string varchar(255)
> >>>>> ) LANGUAGE SQL
> >>>>> BEGIN
> >>>>> DECLARE ss VARCHAR(257);
> >>>>> SET ss = CONCAT('%',search_string,'%');
> >>>>> SELECT NDB_No,Long_Desc FROM 'sr19'.food_desc WHERE Long_Desc LIKE
> >>>>> ss;
> >>>>> END
> >>>> ...
> >>>>> The error message is relatively useless in that all it says is that
> >>>>> there is an error in the SQL syntax used for the select statement.
> >>>> I think you meant backticks for the sr19 table. But it is not a reserved
> >>>> word, so omitting the single quotes would be more clear.
> >>>> Best regards
> >>>> --
> >>>> Willem Bogaerts
> >>>> Application smith
> >>>> Kratz B.V.http://www.kratz.nl/
> >>> Thanks Willem
> >>> I get the right results when, in MySQL Browser, I execute the
> >>> following:
> >>> CALL sr19.sp_find_food('Butter');
> >>> In this database, the resultset from this query should contain 151
> >>> records. The above statement has that many records.
> >>> What is not clear to me is whether these results are displayed to
> >>> standard output from within the procedure or returned to the client
> >>> application for the latter to write the output. If I invoke this
> >>> procedure from an ASP.NET or JSF page, or a Java servlet using JDBC,
> >>> will the resultset be put in the appropriate datasource object or
> >>> disappear into never-never land?
> >>> Thanks
> >>> Ted- Hide quoted text -
> >>> - Show quoted text -
> >> Well, with about an hour of trial and error, it seems that although
> >> the stored procedure seems to work from within MySQL Broswer, it
> >> definitely does not work from an ASP.NET2 page. The statement gets
> >> executed, MS Visual Studio knows what columns are present in the
> >> resultset, but the resultset itself seems to disappear into never-
> >> never land. :-(
>
> >> How do I return the resultset to the calling client code?
>
> >> Ted- Hide quoted text -
>
> >> - Show quoted text -
>
> > With further experimentation, I am wondering if the problem might be
> > with the MySQL .NET provider since even the strategy I have used
> > successfully with MS SQL Server 2005, relating the text of a textfield
> > to the poarameter of a parameterized select statement to the items in
> > a dropdownlistbox has not worked. I do not yet know if I have missed
> > something simple or if there is inevitably a problem using MySQL with
> > an ASP.NET2 application.
>
> > Ted
>
> Ted,
>
> It's impossible to tell. All you've shown us is your Create Procedure
> code. You've shown us nothing about any code you're using to call it.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================- Hide quoted text -
>
> - Show quoted text -
OK. Here is some test ASP.NET 2 code.
<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 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
ropDownList ID="DropDownList1" runat="server"
AutoPostBack="True" DataSourceID="SqlDataSource1"
DataTextField="Long_Desc" DataValueField="NDB_No"
Width="100%">
</asp
ropDownList></td>
</tr>
<tr>
<td style="width: 30%">select a food</td><td style="width:
70%">
<asp
ropDownList ID="DropDownList2" runat="server"
AutoPostBack="True" DataSourceID="SqlDataSource2"
DataTextField="Long_Desc" DataValueField="NDB_No"
Width="100%">
</asp
ropDownList></td>
</tr>
</table><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 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
ropDownList ID="DropDownList1" runat="server"
AutoPostBack="True" DataSourceID="SqlDataSource1"
DataTextField="Long_Desc" DataValueField="NDB_No"
Width="100%">
</asp
ropDownList></td>
</tr>
<tr>
<td style="width: 30%">select a food</td><td style="width:
70%">
<asp
ropDownList ID="DropDownList2" runat="server"
AutoPostBack="True" DataSourceID="SqlDataSource2"
DataTextField="Long_Desc" DataValueField="NDB_No"
Width="100%">
</asp
ropDownList></td>
</tr>
</table>
When I do something similar, with data from an MS SQL Server 2005
database, changing the value in one control results in the items in
the other control (a drop down list) are changed. The principle
difference is that the code for data from MS SQL Server involved
testing for equality of integers while this code looks for records in
which one column contains a string that itself contains the string
provided in the parameter.
In the sample ASP.NET 2 code above, the controls are properly created,
but the dropdown lists never get any of the data that ought to have
been returned by the SQL statement.
Thanks,
Ted