Joe Brown
asked on
Searchable gridview in Visual Studio / SQL / C#
I would like to make my gridview searchable. I have added the text box and the button for it. I need help with passing the parameter. Can someone help me to be able to make this gridview searchable by entering the field [Out_Lab_LabID]. I tried to add the parameter to the WHERE clause but was not successful.
<asp:TextBox ID="SearchBox" runat="server"></asp:TextB ox>
<asp:Button ID="Search" runat="server" Text="Search" />
<asp:GridView ID="GridView1" runat="server" AllowPaging="True"
AllowSorting="True" AutoGenerateColumns="False " DataKeyNames="Out_Lab_LabI D"
DataSourceID="SqlDataSourc e1"
EmptyDataText="There are no data records to display." Width="895px"
onselectedindexchanged="Gr idView1_Se lectedInde xChanged">
<Columns>
<asp:hyperlinkfield datatextfield="Out_Lab_Lab ID" HeaderText="Lab ID"
InsertVisible="False" SortExpression="Out_Lab_La bID"
datanavigateurlfields="Out _Lab_LabID "
datanavigateurlformatstrin g="LabEntr y.aspx?Lab ID={0}"/>
<asp:TemplateField HeaderText="Lot" SortExpression="PC_Descrip tion">
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("Out_Lab_Lot") %>'></asp:Label>
<asp:Label ID="Label2" runat="server" Text='<%# Bind("Out_Lab_NonFTLot") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="Out_Lab_Type" HeaderText="Test Type"
SortExpression="Out_Lab_Ty pe" />
<asp:BoundField DataField="Out_Lab_PTCode" HeaderText="PT Code"
SortExpression="Out_Lab_PT Code" />
<asp:TemplateField HeaderText="Product Name" SortExpression="PC_Descrip tion">
<ItemTemplate>
<asp:Label ID="Label5" runat="server" Text='<%# Bind("Out_Lab_NonFTMatDesc ription") %>'></asp:Label>
<asp:Label ID="Label3" runat="server" Text='<%# Bind("PC_Description") %>'></asp:Label>
<br />
<asp:Label ID="Label4" runat="server" Text='<%# Bind("Out_Lot_CustomProduc tName") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="Out_Lab_SubDate " HeaderText="Submitted Date"
SortExpression="Out_Lab_Su bDate" DataFormatString="{0:dd MMM yyyy}" />
<asp:BoundField DataField="Out_Lab_ActualD ate"
HeaderText="Actual Date" SortExpression="Out_Lab_Ac tualDate"
DataFormatString="{0:dd MMM yyyy}" />
<asp:hyperlinkfield datatextfield="Out_Lab_PDF " HeaderText="CofA"
InsertVisible="False" SortExpression="Out_Lab_Lo t"
datanavigateurlfields="Out _Lab_Lot"
datanavigateurlformatstrin g="LabEntr y.aspx?Cof A={0}.pdf" />
</Columns>
<AlternatingRowStyle BackColor="#E3EAEB" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat= "server"
ConnectionString="<%$ ConnectionStrings:Florator yConnectio nString %>"
OldValuesParameterFormatSt ring="orig inal_{0}"
SelectCommand=" SELECT Out_LabData.*, Out_LotAssigned.*, ProductCode.PC_Description
FROM ProductCode
INNER JOIN Out_LotAssigned ON ProductCode.PC_ProductCode = Out_LotAssigned.Out_Lot_Pr oductCode
RIGHT OUTER JOIN Out_LabData ON Out_LotAssigned.Out_Lot_Lo t = Out_LabData.Out_Lab_Lot
WHERE (Out_LabData.IsDeleted = 0)
ORDER BY Out_LabData.Out_Lab_LabID DESC, Out_LabData.Out_Lab_SubDat e DESC">
<SelectParameters>
<asp:ControlParameter ControlID="SearchBox" DefaultValue="%" Name="Out_LabID" PropertyName="Text"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
<asp:TextBox ID="SearchBox" runat="server"></asp:TextB
<asp:Button ID="Search" runat="server" Text="Search" />
<asp:GridView ID="GridView1" runat="server" AllowPaging="True"
AllowSorting="True" AutoGenerateColumns="False
DataSourceID="SqlDataSourc
EmptyDataText="There are no data records to display." Width="895px"
onselectedindexchanged="Gr
<Columns>
<asp:hyperlinkfield datatextfield="Out_Lab_Lab
InsertVisible="False" SortExpression="Out_Lab_La
datanavigateurlfields="Out
datanavigateurlformatstrin
<asp:TemplateField HeaderText="Lot" SortExpression="PC_Descrip
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("Out_Lab_Lot") %>'></asp:Label>
<asp:Label ID="Label2" runat="server" Text='<%# Bind("Out_Lab_NonFTLot") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="Out_Lab_Type" HeaderText="Test Type"
SortExpression="Out_Lab_Ty
<asp:BoundField DataField="Out_Lab_PTCode"
SortExpression="Out_Lab_PT
<asp:TemplateField HeaderText="Product Name" SortExpression="PC_Descrip
<ItemTemplate>
<asp:Label ID="Label5" runat="server" Text='<%# Bind("Out_Lab_NonFTMatDesc
<asp:Label ID="Label3" runat="server" Text='<%# Bind("PC_Description") %>'></asp:Label>
<br />
<asp:Label ID="Label4" runat="server" Text='<%# Bind("Out_Lot_CustomProduc
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="Out_Lab_SubDate
SortExpression="Out_Lab_Su
<asp:BoundField DataField="Out_Lab_ActualD
HeaderText="Actual Date" SortExpression="Out_Lab_Ac
DataFormatString="{0:dd MMM yyyy}" />
<asp:hyperlinkfield datatextfield="Out_Lab_PDF
InsertVisible="False" SortExpression="Out_Lab_Lo
datanavigateurlfields="Out
datanavigateurlformatstrin
</Columns>
<AlternatingRowStyle BackColor="#E3EAEB" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat= "server"
ConnectionString="<%$ ConnectionStrings:Florator
OldValuesParameterFormatSt
SelectCommand=" SELECT Out_LabData.*, Out_LotAssigned.*, ProductCode.PC_Description
FROM ProductCode
INNER JOIN Out_LotAssigned ON ProductCode.PC_ProductCode
RIGHT OUTER JOIN Out_LabData ON Out_LotAssigned.Out_Lot_Lo
WHERE (Out_LabData.IsDeleted = 0)
ORDER BY Out_LabData.Out_Lab_LabID DESC, Out_LabData.Out_Lab_SubDat
<SelectParameters>
<asp:ControlParameter ControlID="SearchBox" DefaultValue="%" Name="Out_LabID" PropertyName="Text"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
I believe this would work as well. I chose to actually do a Response.Redirect and redirect to the same page when the LabID = the text in the search box (LabID)
http://www.aspdotnet-suresh.com/2011/12/search-records-in-gridview-and.html