How to use parameterized queries in SqlDataSource.SelectCommand in code behind?

Question

I have a code behind method that uses SqlDataSource object to retrieve data from database, then is used as a data source for my grid view to display the records. I need to conditionally add another condition to my WHERE clause of my SelectCommand query, and need to pass the data to the query through parameters. How do I do this?

SqlDataSource sds = new SqlDataSource();

sds.SelectCommand = "Select CustomerName, LoanNumber, LoanDate FROM Loan WHERE IsActive = 1 ";

if (filterRecord != "All") {   // DDL filter on page, all records by default
    sds.SelectCommand += "AND LoanType = ??";   //this is where I need to parameterize my query
}

I thought something like

if (filterRecord != "All") {  
    sds.SelectCommand += "AND LoanType = @LoanType";
    sds.SelectParameters.Add("@LoanType", "Mortgage");
}

But that does not appear to work.

But that does not appear to work. What doesn't work? Is there an exception? — LarsTech
– LarsTech, Commented Mar 21, 2016 at 17:10
Did you use the LoanType = ?? or the LoanType = @LoanType? — LarsTech
– LarsTech, Commented Mar 21, 2016 at 17:12
I am using LoanType = @LoanType. The first example was just suggesting I do not know what to put there — quirky purple
– quirky purple, Commented Mar 21, 2016 at 17:13

user6291531 · Accepted Answer · 2016-05-04 15:35:15Z

1

I would advise switching to an SqlCommand object To define your query and reading the records with an SqlDataReader object. You are able to add parameters to your query using something similar to following:

SqlCommand cmd = new SqlCommand(queryString, connection);
cmd.Parameters.AddWithValue("@paramName", value);

Afterwards, you can bind the SqlDataReader to the grid view as you normally would, by declaring it as the data source.

answered May 4, 2016 at 15:35

user6291531

262 bronze badges

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

1 Comment

quirky purple Over a year ago

Thanks. That's actually what I ended up doing. I never could figure out how to add parameters to the SqlDataSource

Amir · Accepted Answer · 2016-03-21 17:37:00Z

0

I did not considered that you are using the Datasource Object:

Add a ASP parameter to the SQL data source:

<asp:Parameter Name="LoanType" Type="String" DefaultValue="default" />

Change the select command to:

"Select CustomerName, LoanNumber, LoanDate FROM Loan WHERE IsActive = 1 AND (@LoanType="default" or LoanType =@LoanType)"

Then in your code behind do this:

if (filterRecord != "All") {  
    sds.SelectParameters("LoanType").DefaultValue="Mortgage"
}

Description: You need to define the sql parameter in the ASP webform before editing in the code behind. I defined a parameter with a dummy value "default". So if in the you do not change the parameter in code behind the parameter @LoanType is equal to "default" string and the @LoanType="default" will returns true for all rows and it will not consider the LoanType =@LoanType. However, If you change the parameter in code behind the @LoanType="default" returns false and it will check LoanType=@LoanType for each row which will result in what you want!

My OLD Answer: This is good when you do not use the sql data source and you are using your sql command

if (filterRecord != "All") {  
    SqlParameter Parameter = new SqlParameter();
    Parameter.ParameterName = "@LoanType";
    Parameter.SourceColumn = "LoanType";
    Parameter.SqlDbType = SqlDbType.NVarChar;
    Parameter.Value = "Mortgage";

    sds.SelectCommand += "AND LoanType = @LoanType";
    sds.SelectParameters.Add(Parameter);
}

edited Mar 21, 2016 at 17:37

answered Mar 21, 2016 at 17:13

Amir

2,12825 silver badges23 bronze badges

2 Comments

quirky purple Over a year ago

I am using the SQLDataSource in the code behind as well, not on the aspx page. Any advice there?

Amir Over a year ago

OK. I changed the answer. Check the new one

Collectives™ on Stack Overflow

How to use parameterized queries in SqlDataSource.SelectCommand in code behind?

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related