Change Gridview SQL Select statement on button click.

**PeteLeHoq** · Apr 14th, 2011, 12:08 PM

I want to change the result set of my gridview on the click of a button.

Anyone have any sample code to do this?

I've already got a gridview set up which can insert,update,edit etc.

Just not sure how to change the select statement within the gridview setup. I've seen selectcommandtype="Text" or "storeprocedure" I guess it's along those lines?

Thanks.

**Webskater** · Apr 14th, 2011, 04:32 PM

Probably be helpful if you posted the code you use to populate the gridview in the first place.

In simple terms you use the button's OnClick event to call a procedure in which you'll use whatever different code is appropriate to return the data you want. But I guess you must already know that if you already have a Gridview set up with inserts and updates working.

Or, if you populate your Gridview by populating a DataSet - you can filter that DataSet to show just the data you want - if that is appropriate?

**PeteLeHoq** · Apr 15th, 2011, 03:19 PM

Ok, here's the code i'm using at the mo, there's a lot of other fields but I've cut them out for ease.

ASP:

HTML Code:

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="ASPVB.aspx.vb" Inherits="Class1" validateRequest="false"%>
<%@ Import Namespace="System.Drawing" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">*
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title></title>
</head>
<body>
<form method="post" action="ASP.aspx" id="form1" runat="server">
<div class="body">
<div class="page">
<asp:Label id="EditMSG" Height="25" ForeColor="Red" Runat="Server" EnableViewState="False"/></asp:Label>
<asp:Label id="ConfirmDelete" Visible="False" Runat="Server"
EnableViewState="False" Height="25">
  <asp:Label ID="Label1" Text="Delete this record? " Runat="Server"
    ForeColor="Red" EnableViewState="False"/>
  <asp:Button ID="Button1" Text="Yes" OnClick="Delete_Record" Runat="Server"
    Font-Size="7pt" Width="30px"/>
  <asp:Button ID="Button2" Text="No" OnClick="Cancel_Delete" Runat="Server"
    Font-Size="7pt" Width="30px"/>
</asp:Label>
<asp:FormView id="AddForm" DataSourceID="AddSource" Runat="Server"
  InsertRowStyle-BackColor="#00EE00"
  OnItemInserting="Validate_Insert_Data"
  OnItemInserted="Insert_Record">
  
  <HeaderTemplate>
  <table id="Head" border="1">
  <tr>
    <th><asp:Label ID="Label2" Text="Edit" Width="35px" Runat="Server"/></th>
    <th><asp:Label ID="Label4" Text="Name" Width="150px" Runat="Server"/></th>
    <th><asp:Label ID="Label5" Text="Type" Width="80px" Runat="Server"/></th> 
  </tr>
  </table>
  </HeaderTemplate>
  
  <ItemTemplate>
  <table id="Edit" border="1">
  <tr>
    <td><asp:Button ID="Button3" Text="New" CommandName="New" Runat="Server"
        Font-Size="7pt" Width="35px"/></td>
  </tr>
  </table>
  </ItemTemplate>
  
  <InsertItemTemplate>
  <table id="Insert" border="1">
  <tr>
    <td nowrap>
      <asp:Button ID="Button4" Text="Insert" CommandName="Insert" Runat="Server"
        Font-Size="7pt" Width="35px"/>
      <asp:Button ID="Button5" Text="Cancel" CommandName="Cancel" Runat="Server"
        Font-Size="7pt" Width="35px"/></td>
    <td><asp:TextBox id="AddName" Runat="Server"
          Text='<%# Bind("Name") %>'
          Font-Size="8pt" Width="150"/></td>
          <td><asp:DropDownList id="AddType" Runat="Server"
          DataSourceID="TypeSource"
          DateTextField="Type"
          DataValueField="Type"
          SelectedValue='<%# Bind("Type") %>'
          Font-Size="8pt" Width="80"/></td>
  </tr>
  </table>
  </InsertItemTemplate>

</asp:FormView>

<div id="search">
 <asp:TextBox ID="txtSearch" runat="server" />
<asp:ImageButton ID="btnSearch"  runat="server" Value="Submit"/>
<asp:ImageButton ID="btnClear" runat="server" Value="Clear" />
</div>

<asp:ImageButton ID="checkduplicates2" Value="CHECK DUPLICATES" Runat="Server" Font-Size="12pt" Width="120px"/>
        
<asp:GridView id="EditGrid" DataSourceID="EditSource" Runat="Server"
  AutoGenerateColumns="False"
  DataKeyNames="ID_KEY"
  ShowHeader="False"
  AllowPaging="True"
  AllowSorting="true"
  PageSize="15"
  EditRowStyle-BackColor="#FFFF00"
  PagerStyle-BackColor="#E0E0E0"
  RowStyle-VerticalAlign="Top"
  RowStyle-Font-Size="10pt"
  cellpadding="3"
  OnRowUpdating="Validate_Update_Data"
  OnRowUpdated="Update_Record"
  OnRowDeleting="Confirm_Delete" > 
 <PagerSettings FirstPageText="First" LastPageText="Last" 
    Mode="NumericFirstLast" PageButtonCount="10" position="Bottom" />
  <PagerStyle CssClass="pagination" HorizontalAlign="Center" 
 VerticalAlign="Middle"/>


  <Columns>
  <asp:TemplateField 
    ItemStyle-Wrap="False">
    <ItemTemplate>
      <asp:Button ID="Button6" Text="Edit" CommandName="Edit" Runat="Server"
        Font-Size="7pt" Width="35px"/><br />
      <asp:Button ID="Button7" Text="Delete" CommandName="Delete" Runat="Server"
        Font-Size="7pt" Width="35px"/>
    </ItemTemplate>
    <EditItemTemplate>
      <asp:Button ID="Button8" Text="Update" CommandName="Update" Runat="Server"
        Font-Size="7pt" Width="35px"/><br />
      <asp:Button ID="Button9" Text="Cancel" CommandName="Cancel" Runat="Server"
        Font-Size="7pt" Width="35px"/>
    </EditItemTemplate>
  </asp:TemplateField>
  
  <asp:TemplateField SortExpression="Name">
    <ItemTemplate>
      <asp:Label ID="Label14" Text='<%# Eval("Name") %>' Runat="Server"
        Width="150"/>
    </ItemTemplate>
    <EditItemTemplate>
      <asp:TextBox id="EditName" Runat="Server"
        Text='<%# Bind("Name") %>'
        Width="150" Font-Size="8pt"/>
    </EditItemTemplate>
  </asp:TemplateField>	
  
  <asp:TemplateField>
    <ItemTemplate>
      <asp:Label ID="Label15" Text='<%# Eval("Type") %>' Runat="Server"
        Width="80"/>
    </ItemTemplate>
    <EditItemTemplate>
      <asp:DropDownList id="EditType" Runat="Server"
        DataSourceID="TypeSource"
        DateTextField="Type"
        DataValueField="Type"
        SelectedValue='<%# Bind("Type") %>'  
        Font-Size="8pt"/>
    </EditItemTemplate>
  </asp:TemplateField>  
  </Columns>
</asp:GridView>


<asp:SqlDataSource id="AddSource" runat="server" DataSourceMode="DataReader" ConnectionString="<%$ ConnectionStrings:SqlConnection%>"
          SelectCommand="SELECT * FROM Table1"
          InsertCommand="INSERT INTO Table1 ([Type],Name) VALUES (@Type, @Name"/>
</asp:SqlDataSource>

<asp:SqlDataSource id="TypeSource" Runat="Server" DataSourceMode="DataReader" ConnectionString="<%$ ConnectionStrings:SqlConnection%>"
  SelectCommand="SELECT  Type FROM Table1 ORDER BY Type"/>
</asp:SqlDataSource>


<asp:SqlDataSource id="EditSource" Runat="Server" 
        DataSourceMode="DataSet" ConnectionString="<%$ ConnectionStrings:SqlConnection%>"
  
  FilterExpression="Name like '%{0}%'"
  
  SelectCommand="SELECT * FROM Table1 ORDER BY Name DESC"
   

    UpdateCommand="UPDATE Table SET [Type]=@Type, Name=@Name">
                 
                 <FilterParameters>
        		<asp:ControlParameter Name="Name" ControlID="txtSearch" PropertyName="Text" Runat="Server" DefaultValue="Bob"/>
                <asp:Parameter DefaultValue="Bob" Name="Name" />
    			</FilterParameters>
              
</asp:SqlDataSource>

</form>
</body>
</html>

VB:

Code:

Imports System.Drawing
Imports System.Text.RegularExpressions
Imports System.Data.SqlClient
Imports System.Data


Public Class Class1
    Inherits System.Web.UI.Page

    ' Create a String to store our search results
    Dim SearchString As String = ""

    Sub Insert_Record(ByVal Src As Object, ByVal Args As FormViewInsertedEventArgs)
        EditGrid.DataBind()
    End Sub

    Sub Confirm_Delete(ByVal Src As Object, ByVal Args As GridViewDeleteEventArgs)
        Args.Cancel = True
        ConfirmDelete.Visible = True
        Dim Row As GridViewRow = EditGrid.Rows(Args.RowIndex)
        Row.BackColor = Color.FromName("#FF3333")
        Row.ForeColor = Color.FromName("#FFFFFF")
        ViewState("RowIndex") = Args.RowIndex
        ViewState("ID_KEY") = Args.Keys("ID_KEY")
    End Sub

    Sub Delete_Record(ByVal Src As Object, ByVal Args As EventArgs)
        EditSource.DeleteCommand = "DELETE FROM Table1 WHERE ID_KEY = '" & ViewState("ID_KEY") & "'"
        EditSource.Delete()
        Dim Row As GridViewRow = EditGrid.Rows(ViewState("RowIndex"))
        Row.BackColor = Color.FromName("#FFFFFF")
        Row.ForeColor = Color.FromName("#000000")
        EditMSG.Text = "&bull; Record " & ViewState("ID_KEY") & " deleted"
    End Sub

    Sub Cancel_Delete(ByVal Src As Object, ByVal Args As EventArgs)
        Dim Row As GridViewRow = EditGrid.Rows(ViewState("RowIndex"))
        Row.BackColor = Color.FromName("#FFFFFF")
        Row.ForeColor = Color.Fromname("#000000")
        ConfirmDelete.Visible = False
    End Sub


    Protected Sub btnClear_Click(ByVal sender As Object, ByVal e As  _
            System.Web.UI.ImageClickEventArgs) Handles btnClear.Click
        ' Simple clean up text to return the Gridview to it's default state
        txtSearch.Text = ""
        SearchString = ""
        Editgrid.DataBind()
    End Sub

    Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As  _
         System.Web.UI.ImageClickEventArgs) Handles btnSearch.Click
        ' Set the value of the SearchString so it gets 
        SearchString = txtSearch.Text
		'????? DONT KNOW WHAT TO DO HERE
    End Sub
	
End Class

**Webskater** · Apr 15th, 2011, 05:29 PM

Sorry, can't be much use as I never use SqlDataSource - I have all database access in a separate DataAccess class which only uses Stored Procedures to access the database.

I seem to recall there is a <SelectParameter> collection for SqlDataSource that you can add ... something like ...

Code:

<SelectParameters>
<asp:ControlParameter Name="Search"
ControlID="txtSearch" PropertyName="Text" />
</SelectParameters>

I wouldn't have answered this as I'm sure others will give you the exact answer - but, just in case it helps ...

Thread: Change Gridview SQL Select statement on button click.

Thread Tools

Display

Change Gridview SQL Select statement on button click.

Re: Change Gridview SQL Select statement on button click.

Re: Change Gridview SQL Select statement on button click.

Re: Change Gridview SQL Select statement on button click.

Posting Permissions