Akhilesh Sehgal Akhilesh Sehgal - 6 months ago 30
SQL Question

How can I use a WHERE clause in GridView control?

I want to create a where clause so that I can get the values from the database to the gridview with some particular id which I have stored in ViewState on page load. Where clause is marked in stars in the code

<asp:GridView ID="gvView" runat="server" AutoGenerateColumns="false" DataKeyNames="ID"
DataSourceID="SqlDataSource" AllowPaging="true" PageSize="50" Width="100%"
EmptyDataText="--- No records yet. ---" PagerStyle-HorizontalAlign="Center" PagerSettings-PageButtonCount="5"
EmptyDataRowStyle-ForeColor="#888581" EmptyDataRowStyle-Font-Size="14px" EmptyDataRowStyle-Height="30px"
EmptyDataRowStyle-Font-Italic="true" AlternatingRowStyle-BackColor="#E2E2E2"
<asp:TemplateField HeaderText="Select">
<asp:CheckBox ID="RowSelector" runat="server" />
<asp:BoundField DataField="ID" HeaderText="ID" SortExpression="ID" Visible="false" />
<asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />

<asp:SqlDataSource ID="SqlDataSource" runat="server"
ProviderName="System.Data.SqlClient" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT ID, Title FROM Table **WHERE AnotherID=@AnotherID** ORDER BY ID">


You can call this methord to bind your gridview

protected void BindGridview(int anotherid)
   DataSet ds = new DataSet();
   using (SqlConnection con = new SqlConnection("Data Source=source;Integrated Security=true;Initial Catalog=MySampleDB"))
      SqlCommand cmd = new SqlCommand("SELECT ID, Title FROM Table WHERE AnotherID='"+anotherid+"' ORDER BY ID", con);
      SqlDataAdapter da*emphasized text* = new SqlDataAdapter(cmd);
      gvView.DataSource = ds;

if you want to bind with sqldatasource

    SqlDataSource SqlDataSource = new SqlDataSource();
    SqlDataSource.ID = "SqlDataSource";
    SqlDataSource.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
    SqlDataSource.SelectCommand = "SELECT ID, Title FROM Table WHERE AnotherID='"+anotherid+"' ORDER BY ID";
    gvView.DataSource = SqlDataSource;