How to pass a value into a SqlDataSource Query?

Question

I'm creating a ListView in ASP.NET and have based mine on the example given by CodeProject here. I want to make the Select Command of the SqlDataSource dynamic so that a value is generated from one provided from the session. Ive tried a fue different possibilities, here is an example of what I want:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="<%$ ConnectionStrings:TestDatabaseConnectionString %>" 
    SelectCommand="SELECT * FROM [Contacts] WHERE [Name] = <%# Eval("value") %> " >
</asp:SqlDataSource>

How would I pass such a value using ASP? Ive also tried creating the query in the C# back page and linking to it like SelectCommand = "<%# Eval("Query") %>" and also by using the @value syntax. neither work!

Indi · Accepted Answer · 2012-10-05 14:02:17Z

2

This should do the trick. Define a SessionParameter as follows and make sure Name=Sql parameter name and SessionField is same as your session field. DBType and DefaultValue as required...

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    SelectCommandType="Text"
    ConnectionString="<%$ ConnectionStrings:TestDatabaseConnectionString %>" 
    SelectCommand="SELECT * FROM [Contacts] WHERE [Name] = @ParaName"

    <SelectParameters>
        <asp:SessionParameter 
               Name="ParaName" 
               SessionField="YourSessionFieldName" 
               DbType="String" 
               DefaultValue="" />
    </SelectParameters>
</asp:SqlDataSource>

answered Oct 5, 2012 at 14:02

Indi

33.9k7 gold badges61 silver badges80 bronze badges

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

1 Comment

David East Over a year ago

This is a decent workaround, but I think the user would be better off programatically setting the data and binding it to a control rather than using a SqlDataSource.

Aghilas Yakoub · Accepted Answer · 2012-10-05 13:55:08Z

1

Replace with

SelectCommand="SELECT * FROM [Contacts] WHERE [Name] = @Name"

And define your @Name as parameter

<SelectParameters>
    <asp:Parameter  DefaultValue="<%# Eval("Query") %>" Name="Name"  DbType="..." />
</SelectParameters>

answered Oct 5, 2012 at 13:55

Aghilas Yakoub

29k5 gold badges49 silver badges51 bronze badges

1 Comment

alwaysVBNET Over a year ago

I'm trying to use this now, but how can I get the "<%# Eval("Query") %>" of an item from the parent DataList?

user1437891 · Accepted Answer · 2012-10-05 14:19:42Z

1

The solutions provided are very good. It should also be noted that trying to place your "value" directly in the query, you are opening yourself up to SQL injection attacks. Using the select parameters prevents and protects you from this.

answered Oct 5, 2012 at 14:19

user1437891

1121 silver badge9 bronze badges

Comments

Joshua Van Hoesen · Accepted Answer · 2012-10-05 13:54:10Z

0

I had the same issue and my lazy approach to solving it was to do as follows:

command.CommandText = item.Query.Replace("@Value", Value);
command.ExecuteNonQuery();

Dirty, easy and most likely not the proper way to do it.

answered Oct 5, 2012 at 13:54

Joshua Van Hoesen

1,76115 silver badges33 bronze badges

2 Comments

user1437891 Over a year ago

This is not good. It opens your query up to a sql injection attack. Never directly replace variables in a SQL query without scrubbing them first.

Joshua Van Hoesen Over a year ago

Like I stated, not the proper way to do it, also doesn't cause an issue if you have already validated Value before doing the replacement

David East · Accepted Answer · 2012-10-05 15:18:02Z

I would really recommend against using a SqlDataSource control. A SqlDataSource provides very little in the realm is reuse.

Programatically make the DB call

If you make the call in a separate class (or even better in a DAL) you will be able to use it across multiple pages with ease. Also, when a change occurs to your query you will just have to change it in one place.

Here is a sample below that uses the Entity Framework to access the database

Mark up

<asp:DropDownList ID="ddlTest" runat="server"></asp>

Code Behind

public List<Record> GetAllRecordsByUserName(string credentials)
{
    List<Record> recordList;
    using (CustomEntities context = new CustomEntities())
    {

        IQueryable<Record> recordQuery = from records in context.Records
                                              where records.UserName == credentials
                                              select records; 
        recordList = recordQuery.ToList<Record>();
    }
    return recordList;
}

public void ValidateAndBind(string username)
{
    List<Record> recordList = GetAllRecordsByUserName(username);

    // Do validation here

    ddlTest.DataSource = recordList;
    ddlTest.DataBind();
}

protected void Page_Load(object sender, EventArgs e)
{
    ValidateAndBind("test.username");
}

Winnifred · Accepted Answer · 2018-07-02 23:38:03Z

0

Another way to achieve your goal without code behind is using an Hidden Field and doing the following:

<asp:HiddenField runat="server" ID="HfieldID" Value='<%# Eval("value")%>'/>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TestDatabaseConnectionString %>" SelectCommand="SELECT * FROM [Contacts] WHERE [Name] = @Name">
    <SelectParameters>
        <asp:ControlParameter Name="Name" PropertyName="Value" ControlID="HfieldID" DbType="String" />
    </SelectParameters>
</asp:SqlDataSource>

Be sure to put this before any controls that will use this SqlDataSource.

answered Jul 2, 2018 at 23:38

Winnifred

1,2221 gold badge8 silver badges10 bronze badges

Collectives™ on Stack Overflow

How to pass a value into a SqlDataSource Query?

6 Answers 6

1 Comment

1 Comment

Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

1 Comment

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related