How to bind gridview at pageload with dropdown list

Question

I have a dropdown list to search by categories. I need help to bind my gridview at page load, but at the same time, I also have a select command as votes. I know that there are codes such as Databinding in the pageload event. But for my case, i need to link the select command to a button to update votes. If i databind it, i could not grab the data key names to update my votes counter. Is there any way to bind the gridview, without removing the DataSourceID in the gridview itself?

My aspx codes are as follow.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
                        ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
                        SelectCommand="SELECT * FROM [Review] WHERE ([Category] = @Category)">
                        <SelectParameters>
                            <asp:ControlParameter ControlID="ddlCat" Name="Category" 
                                PropertyName="SelectedValue" Type="String" />
                        </SelectParameters>
                    </asp:SqlDataSource>



                      <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
                        ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
                        SelectCommand="SELECT [Category] FROM [ReviewCategory]">
                        </asp:SqlDataSource>


                    <asp:DropDownList ID="ddlCat" runat="server" 
                        DataSourceID="SqlDataSource2" DataTextField="Category" 
                        DataValueField="Category" AutoPostBack="True" 
                        onselectedindexchanged="SelectionChange">
                    </asp:DropDownList>


<asp:GridView ID="GridView1" runat="server" Width="1114px" 
    Height="272px" AutoGenerateColumns="False" PageSize="5" 
        DataSourceID="SqlDataSource1" AllowPaging="True" DataKeyNames="ReviewID">
<Columns>

    <asp:BoundField DataField="Votes" HeaderText="Votes" 
        SortExpression="Votes" />
    <asp:BoundField DataField="Category" HeaderText="Category" 
        SortExpression="Category" />

    <asp:CommandField SelectText="VOTE as your FAVOURITE!" 
        ShowSelectButton="True" />
</Columns>

c# code

 protected void btnVote_Click(object sender, EventArgs e)
{
    int reviewid = Convert.ToInt16(GridView1.SelectedDataKey.Value);

    SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True");
    string sqlstmt = "select Votes from Review where ReviewID = '" + reviewid + "'";
    SqlCommand comm = new SqlCommand(sqlstmt, conn);


    try
    {

        conn.Open();
        SqlDataReader read = comm.ExecuteReader();


        if (read.Read())
        {
            int votes = (int)read["Votes"];
            votes += 1;
            string updatestatement = "Update Review set Votes= '" + votes + "' Where ReviewID = '" + reviewid + "'";
            SqlCommand command = new SqlCommand(updatestatement, conn);
           read.Close();
            command.ExecuteNonQuery();
        }


    }
    finally { 
        conn.Close();
        GridView1.DataBind();
    }

}

     protected void SelectionChange(object sender, EventArgs e)
  {

    int stored = ddlCat.SelectedIndex;

    if (stored == 0)
    {
        SqlDataSource1.SelectCommand = "SELECT * from Review ORDER BY [Votes] DESC  ";


    }
    else { } 
}

Answer 1

Let us look into your requirements one by one:

1.) *Binding GridView at PageLoad with DropDownList:

In this case you need to retrieve the Value selected in dropdownList. Do the below setup to grab the Value from DropDownList

<asp:SqlDataSource ID="SqlDataSource2" runat="server" 
 ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
 SelectCommand="SELECT [Category] FROM [ReviewCategory] where Category=@Category">
<SelectParameters><asp:ControlParameter ControlID="ddlCat" Name="Category"
 PropertyName="SelectedValue" /></SelectParameters>
</asp:SqlDataSource>

What is Happenning:

• Each time a value is selected in dropdown, Postback will happen( AutoPostback="true").
• After the Page.PreRender Event, the DataSource controls [ SqlDatSource here ] performs the required queries and retrieve the data. So the selected DropDownList value will be used by SqlDataSource. Thus there is NO need to worry about changing/manipulating DataSourceID in any way.

2.) "But for my case, i need to link the select command to a button to update votes" '

In this case you have a Select button inside grid View and a 'vote' button outside GridView but somewhere in your page. So, once you select any row in grid view, click the 'Vote' button. You can access the SelectedRow and Index as usual.

protected void btnVote_Click1(object sender, EventArgs e)
{
     int i = CustomersGridView.SelectedIndex;
}

Note that the Click event of 'Vote' button fires before the DataSource controls perform their queries & retrieve data. So once you update the Vote count in btnVote_click event as you are doing currently, there is NO need to Bind data again. This part of your code seems fine to me.

Answer 2

You should implement the RowCommand event from the GridView. You alredy have the CommandField, so do something like this:

void GridView1_RowCommand(Object sender, GridViewCommandEventArgs e)
{
  //
  // Get the keys from the selected row
  //
  LinkButton lnkBtn = (LinkButton)e.CommandSource;    //the button
  GridViewRow myRow = (GridViewRow)lnkBtn.Parent.Parent;  //the row
  GridView myGrid = (GridView)sender; // the gridview
  int reviewid = Convert.ToInt32(GridView1.DataKeys[myRow.RowIndex].Value); //value of the datakey **strong text**

  // If multiple buttons are used in a GridView control, use the
  // CommandName property to determine which button was clicked.
  // In this case you are pressing the button Select, as ou already
  // defined this at the aspx code.
  if(e.CommandName=="Select")
  {
    // Put the logic from btnVote_Click here
  }
}

The another way could be implement the SelectIndexChanging or SelectIndexChanged, given that you will use the Select Button to fire the update magic. Here the example with SelectIndexChanging.

void GridView1_SelectedIndexChanging(Object sender, GridViewSelectEventArgs e)
{

  // Get the currently selected row. Because the SelectedIndexChanging event
  // occurs before the select operation in the GridView control, the
  // SelectedRow property cannot be used. Instead, use the Rows collection
  // and the NewSelectedIndex property of the e argument passed to this 
  // event handler.
  int reviewid = Convert.ToInt32(GridView1.DataKeys[e.NewSelectedIndex].Value); //value of the datakey **strong text**

  // Put the logic from btnVote_Click here
}