Printable View
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.
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?
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:
VB: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>
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 = "• 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
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 ...
I wouldn't have answered this as I'm sure others will give you the exact answer - but, just in case it helps ...Code:<SelectParameters>
<asp:ControlParameter Name="Search"
ControlID="txtSearch" PropertyName="Text" />
</SelectParameters>