I have a SqlDataSource
feeding a ListBox with this SelectCommand
:
<asp:SqlDataSource ID="DSPatients"
ConnectionString="<%$ ConnectionStrings:CSMain %>"
ProviderName="<%$ ConnectionStrings:CSMain.ProviderName %>"
SelectCommand="SELECT Id, dbo.PatientDescriptive(Id) Descriptive FROM Patient ORDER BY Id"
...
runat="server">
<asp:ListBox Rows="10" ID="patientsList" DataSourceID="DSPatients" DataValueField="Id" DataTextField="Descriptive" AutoPostBack="false" runat="server" />
It works well.
I have this TextBox
<asp:TextBox ID="tbPatient" runat="server" MaxLength="100" />
And a search button
<asp:Button ID="btnSearch" runat="server" Text="Search"
OnClick="btnSearch_Click" CausesValidation="false" />
Now, I want to modify the SelectCommand
so that when the user clicks btnSearch Button it search only the patient names LIKE the passed in tbPatient
textbox.
To do that I went to the code behind and tried:
protected void btnSearch_Click(object sender, EventArgs e)
{
DSPatients.SelectParameters.Add("Ptrn", tbPatient.Text);
DSPatients.SelectCommand = "SELECT Id, dbo.PatientDescriptive(Id) Descriptive FROM Patient WHERE Name LIKE @Ptrn ORDER BY Id";
DSPatients.Select(DataSourceSelectArguments.Empty); // Is the problem here? What I have to put inside?
DSPatients.SelectParameters.Clear();
}
When I run I get the following error:
Exception Details: System.Data.SqlClient.SqlException: Must declare the scalar variable "@Ptrn".
I want the ListBox the show only the patients with name LIKE the one entered in the textbox. How can I fix this?