CODEWITHSUNDEEP
c#asp.net
Patrick Keane
Patrick Keane

Reputation: 673

How to pass a value into a SqlDataSource Query?

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!

Upvotes: 1

Views: 4309

Answers (6)

Winnifred
Winnifred

Reputation: 1222

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.

Upvotes: 0

David East
David East

Reputation: 32624

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");
}

Upvotes: 0

user1437891
user1437891

Reputation: 112

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.

Upvotes: 1

Kaf
Kaf

Reputation: 33839

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>

Upvotes: 2

Aghilas Yakoub
Aghilas Yakoub

Reputation: 29000

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>

Upvotes: 1

Joshua Van Hoesen
Joshua Van Hoesen

Reputation: 1732

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.

Upvotes: 0

Related Questions