Hello All,
I was hoping that someone had some wise words of wisdom/experience on this. Any assistance appreciated... feel free to direct me to a more efficient way... but I'd prefer to keep using a stored proc.
I'm trying to pass the selected value of a dropdownlist as a stored procedure parameter but keep running into format conversion errors but I am able to test the query successfully in the SQLDatasource. What I would like to do is this: select * from tblPerson where lastnames Like "A%" . Where I pass the criteria after Like. I have the values of the drop down list as "%", "A%", "B%", ...
I've been successfully configuring all of the other params, which includes another dropdown list (values bound to a lookup table also)... but am stuck on the above...
Thank you for any assistance,
Hi
WildCard should work in parameterized queries(stored procedure):
cmd.Parameters.Add(New SQLParameter("@.Name", "%" & text1.text & "%"))
You could take a look atParameterized Queries for more.
|||thank you yyy8347,
I had seen that article but hadn't really found it helped until today.. along with another error I had the incorrect control ID referenced for my parameter.
I was stuck in the vicious circle believeing I wasn't using the correct escape characters in the dropdownlist for the value (" or ' s) but as it happened I was.
Thanks again and Happy Holidays,
|||
Hi
It seems you passed " or ' to the stored procedure. You just need to pass A% as parameter to procedure, Here is a simple Sample Code :
I use pub database,here is sproc:
alter procedure Test_Like (@.keywordvarchar(32))as beginselect *from titleswhere titlelike @.keywordend
And here is aspx:
<%@. Page Language="C#" AutoEventWireup="true" CodeFile="DDL_Filt_LikePara.aspx.cs" Inherits="DDL_Filt_LikePara" %><!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:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True"> <asp:ListItem>%</asp:ListItem> <asp:ListItem>T%</asp:ListItem> <asp:ListItem>S%</asp:ListItem> <asp:ListItem>M%</asp:ListItem> </asp:DropDownList> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:pubsConnectionString%>"SelectCommand="Test_Like" SelectCommandType="StoredProcedure"> <SelectParameters><asp:ControlParameter ControlID="DropDownList1" DefaultValue="%" Name="keyword" PropertyName="SelectedValue" Type="String" /> </SelectParameters> </asp:SqlDataSource> </div> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="title_id" DataSourceID="SqlDataSource1"> <Columns> <asp:BoundField DataField="title_id" HeaderText="title_id" ReadOnly="True" SortExpression="title_id" /> <asp:BoundField DataField="title" HeaderText="title" SortExpression="title" /> <asp:BoundField DataField="type" HeaderText="type" SortExpression="type" /> <asp:BoundField DataField="pub_id" HeaderText="pub_id" SortExpression="pub_id" /> <asp:BoundField DataField="price" HeaderText="price" SortExpression="price" /> <asp:BoundField DataField="advance" HeaderText="advance" SortExpression="advance" /> <asp:BoundField DataField="royalty" HeaderText="royalty" SortExpression="royalty" /> <asp:BoundField DataField="ytd_sales" HeaderText="ytd_sales" SortExpression="ytd_sales" /> <asp:BoundField DataField="notes" HeaderText="notes" SortExpression="notes" /> <asp:BoundField DataField="pubdate" HeaderText="pubdate" SortExpression="pubdate" /> </Columns> </asp:GridView> </form></body></html>
GridView will display title begin with T or S or M according your selection in DDL.
Hope this helps.
