Searching gridview results

Andy Woodward · December 11th, 2008, 04:48 PM

Hi

I have just finished 'Beginning ASP.NET 3.5 in C#' What a great read. I constructed the PlanetWrox site by following the book and it works great.

I'm now constructing my first web site based on everything I learned from the book.
I have a page with a gridview on bound to sqlDataSource which displays all the records in the database. I need to add a search facility to this with just a simple text box and search button and display the results of the text search in the gridview. I need to search in four columns with the text that is typed in the text box.

I have tried several times to come up with code for his but all I get is the text box and search button. When I enter the text and click search nothing happens. The grid view is wrapped in an update panel like you suggest in the book. The sorting and paging works great but not the search.

Can you suggest any code to rectify this problem that sounds so simple but i'm tearing my hair out.

Thanx in anticipation

Andy

Imar · December 12th, 2008, 03:18 AM

Hi Andy,

Good to hear you like the book so much....

Two things you can try:

1. Remove the UpdatePanel. Maybe an error occurs but the UpdatePanel is hiding it.

2. Post the source for the page (after trying 1.) , together with a description of what it is you're trying to do, how it's supposed to work and so on. Would be helpful to create a test page that exhibits the behavior against the PlanetWrox web site so I can test it out.

Cheers,

Imar

Andy Woodward · December 12th, 2008, 07:38 AM

Hi Imar

Thanx for your prompt reply.
I think I am nearly there. I've took out the update panel as you suggested.The search feature is now working but it will only accept numbers in the text box. If I put in a name (Mark for example) I get an error saying 'Input string was not in a correct format'.

Also if there is no record to display I would like it produce a message saying "There is no record matching your search".

I have included my code for you to look at.

Thank you for your help

Andy

Markup code is:

Code:

<%@PageTitle="Search Test"Language="C#"MasterPageFile="~/MasterPages/MasterPage.master"AutoEventWireup="true"CodeFile="SearchTest.aspx.cs"Inherits="SearchTest" %>
<asp:ContentID="Content1"ContentPlaceHolderID="head"Runat="Server">
</asp:Content>
<asp:ContentID="Content2"ContentPlaceHolderID="cpMainContent"Runat="Server">
<asp:GridViewID="GridView1"runat="server"AutoGenerateColumns="False"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundFieldDataField="FirstName"HeaderText="FirstName"SortExpression="FirstName"/>
<asp:BoundFieldDataField="LastName"HeaderText="LastName"SortExpression="LastName"/>
<asp:BoundFieldDataField="PinCode"HeaderText="PinCode"SortExpression="PinCode"/>
<asp:BoundFieldDataField="Filter"HeaderText="Filter"SortExpression="Filter"/>
<asp:CheckBoxFieldDataField="DeletePending"HeaderText="DeletePending"SortExpression="DeletePending"/>
</Columns>
</asp:GridView>
<asp:TextBoxID="TextBox1"runat="server"></asp:TextBox>
<asp:ButtonID="Button1"runat="server"OnClick="Button1_Click"Text="search"/>
<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:TelephonyAdminConnectionString1 %>"
SelectCommand="SELECT [FirstName], [LastName], [PinCode], [Filter], [DeletePending] FROM [PinEntries] WHERE (([FirstName] = @FirstName) OR ([LastName] = @LastName) OR ([PinCode] = @PinCode) OR ([Filter] = @Filter))">
<SelectParameters>
<asp:ControlParameterControlID="TextBox1"Name="FirstName"PropertyName="Text"Type="String"/>
<asp:ControlParameterControlID="TextBox1"Name="LastName"PropertyName="Text"Type="String"/>
<asp:ControlParameterControlID="TextBox1"Name="PinCode"PropertyName="Text"Type="Int32"/>
<asp:ControlParameterControlID="TextBox1"Name="Filter"PropertyName="Text"Type="String"/>
</SelectParameters>
</asp:SqlDataSource>
</asp:Content>

Code behind is:

Code:


using System;
using System.Text;
using System.Data;
using System.Configuration;
using System.Collections.Generic;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Linq;
using System.Data.SqlClient; 
publicpartialclassSearchTest : BasePage
{
protectedvoid Page_Load(object sender, EventArgs e)
{
 
}
protectedvoid Button1_Click(object sender, EventArgs e)
{
SqlDataSource1.DataBind();
}
}

Imar · December 12th, 2008, 03:46 PM

Because the PinCode column is an integer, you can't simply compare it with a string that doesn't represent an integer. That is, in SQL this:

WHERE PinCode = 'SomeValue'

is illegal and cause an error. The trick is to convert the PinCode column to a nvarchar before you compare it:

.. OR ([LastName] = @LastName) OR (Convert(varchar(20), [PinCode]) = @PinCode) OR ([Filter] = ...

Hope this helps,

Imar

Andy Woodward · December 12th, 2008, 04:03 PM

Hi Imar

Thanx for the reply

I tried this but still getting the same error. The PinCode works fine. Its the names that don't work. The search will only accept integers, not words. The search is expecting the input to be an integer. Do I need to convert the other three columns into an integer? Or do I need something to say only accept strings and then convert the integer?

Andy

Imar · December 12th, 2008, 04:34 PM

Looks like you also need to convert the parameter to a String:

<asp:ControlParameter ControlID="TextBox1" Name="PinCode" PropertyName="Text" Type="String"/>

If you post code here, can you please add a few line breaks here and there? The page is now 3 feet wide.... ;-)

Imar

Andy Woodward · December 12th, 2008, 04:39 PM

Hi Imar

Sorry about the long lines.

I just tried your suggestion and it works great.

Thankyou ever so much for your help

Andy

Imar · December 12th, 2008, 04:42 PM

You're welcome. Glad it's working.

Cheers,

Imar

jminatel · December 12th, 2008, 08:15 PM

Testing reply with quote. Sorry for the test post.

Quote:

Originally Posted by Andy Woodward

Hi Imar

Thanx for your prompt reply.
I think I am nearly there. I've took out the update panel as you suggested.The search feature is now working but it will only accept numbers in the text box. If I put in a name (Mark for example) I get an error saying 'Input string was not in a correct format'.

Also if there is no record to display I would like it produce a message saying "There is no record matching your search".

I have included my code for you to look at.

Thank you for your help

Andy

Markup code is:

Code:

<%@PageTitle="Search Test"Language="C#"MasterPageFile="~/MasterPages/MasterPage.master"AutoEventWireup="true"CodeFile="SearchTest.aspx.cs"Inherits="SearchTest" %>
<asp:ContentID="Content1"ContentPlaceHolderID="head"Runat="Server">
</asp:Content>
<asp:ContentID="Content2"ContentPlaceHolderID="cpMainContent"Runat="Server">
<asp:GridViewID="GridView1"runat="server"AutoGenerateColumns="False"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundFieldDataField="FirstName"HeaderText="FirstName"SortExpression="FirstName"/>
<asp:BoundFieldDataField="LastName"HeaderText="LastName"SortExpression="LastName"/>
<asp:BoundFieldDataField="PinCode"HeaderText="PinCode"SortExpression="PinCode"/>
<asp:BoundFieldDataField="Filter"HeaderText="Filter"SortExpression="Filter"/>
<asp:CheckBoxFieldDataField="DeletePending"HeaderText="DeletePending"SortExpression="DeletePending"/>
</Columns>
</asp:GridView>
<asp:TextBoxID="TextBox1"runat="server"></asp:TextBox>
<asp:ButtonID="Button1"runat="server"OnClick="Button1_Click"Text="search"/>
<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:TelephonyAdminConnectionString1 %>"
SelectCommand="SELECT [FirstName], [LastName], [PinCode], [Filter], [DeletePending] FROM [PinEntries] WHERE (([FirstName] = @FirstName) OR ([LastName] = @LastName) OR ([PinCode] = @PinCode) OR ([Filter] = @Filter))">
<SelectParameters>
<asp:ControlParameterControlID="TextBox1"Name="FirstName"PropertyName="Text"Type="String"/>
<asp:ControlParameterControlID="TextBox1"Name="LastName"PropertyName="Text"Type="String"/>
<asp:ControlParameterControlID="TextBox1"Name="PinCode"PropertyName="Text"Type="Int32"/>
<asp:ControlParameterControlID="TextBox1"Name="Filter"PropertyName="Text"Type="String"/>
</SelectParameters>
</asp:SqlDataSource>
</asp:Content>

Code behind is:

Code:

using System;
using System.Text;
using System.Data;
using System.Configuration;
using System.Collections.Generic;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Linq;
using System.Data.SqlClient; 
publicpartialclassSearchTest : BasePage
{
protectedvoid Page_Load(object sender, EventArgs e)
{
 
}
protectedvoid Button1_Click(object sender, EventArgs e)
{
SqlDataSource1.DataBind();
}
}