SqlDataSource SelectParameters code behind

Question

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?

See stackoverflow.com/questions/17000390/…

BCdotWEB
– BCdotWEB

2015-02-21 17:12:05 +00:00
Commented Feb 21, 2015 at 17:12 — BCdotWEB
– BCdotWEB, Commented Feb 21, 2015 at 17:12

Scott Bamforth · Accepted Answer · 2015-05-12 14:32:19Z

0

Not sure if this is definitely the problem but this,

DSPatients.SelectParameters.Add("Ptrn", tbPatient.Text);

The parameter should include the @ symbol, like so

DSPatients.SelectParameters.Add("@Ptrn", tbPatient.Text);

answered May 12, 2015 at 14:32

Scott Bamforth

14812 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SqlDataSource SelectParameters code behind

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related