Submitting form to show grid

[sanman02150]

I am very new at this so if this is too easy a question, please refer me to an example page/video. Otherwise here's my quesion.

I have a table with 3 columns for example

ProductName
ProductColor (RED,WHITE,BLUE)
ProductDate

What I want to do is create a form to search for products by allowing partial name of the product, allow users to choose from a listbox to select one or more colors, and a productdate begin and productdate end to find out the timeframe of product creation.

I know how to create a radgrid and display it using a selected query.

How would I go about creating the initial search screen and using the form fields to modify the grid?

Any pointers, samples appreciated!

[Princy]

Hi,

RadGrid has got inbuilt Filter functionality, which filter the RadGrid with the displayed data. This is a demo about RadGrid Filter basic Functionality. You can customize the RadGrid Filter Item also, for example to show ListBox for filtering the ProductColor. Please check this link which demonstrate how to add a Combobox in filter Template. You can filter a GridDateTimeColumn with From and To Range also. Please see this demo.

Other than the Filter functionality in RadGrid, You can add external TextBoxes and ListBox to filter the content of RadGrid. Get the values of those controls and on a button click , you can query the DataBase and Populate the RadGrid. Here is a sample code I tried.

ASPX:

<div>
    <asp:TextBox ID="txtProductName" runat="server"></asp:TextBox>
    <asp:ListBox ID="lstProductColor" runat="server">
        <asp:ListItem Text="Red"></asp:ListItem>
        <asp:ListItem Text="Blue"></asp:ListItem>
        <asp:ListItem Text="Black"></asp:ListItem>
    </asp:ListBox>
    <asp:TextBox ID="dateFrom" runat="server"></asp:TextBox>
    <asp:TextBox ID="dateTo" runat="server"></asp:TextBox>
    <asp:Button ID="btnSerach" runat="server" Text="Search" OnClick="btnSerach_Click" />
</div>

C#:

protected void btnSerach_Click(object sender, EventArgs e)
{
    string ProductName = txtProductName.Text;
    string ProductColor = lstProductColor.SelectedItem.Text;
    String FromDate =dateFrom.Text;
    String ToDate = dateTo.Text;
    SqlConnection con = new SqlConnection(WebConfigurationManager.ConnectionStrings["NorthwindConnectionString3"].ConnectionString);
    SqlCommand cmd = new SqlCommand("select * FROM [ProductDetails] where ProductName LIKE '%" + ProductName + "%' and ProductColor='" + ProductColor + "' and ProductDate between '" + FromDate + "' and '" + ToDate + "'; ", con);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    da.Fill(dt);
    RadGrid1.DataSource = dt;
    RadGrid1.DataBind();
}

Please let me know if you have any concern.

Thanks,
Princy.

[sanman02150]

Hi Princy, Thank you so much, that helps a lot!!

Thank you for the code example for the form submission to fill in the RadGrid. It works, and I have a few follow up question on this which will help me immensely.

1. How to show everything if a person does not select anything or fill any of the form fields?

2. If we make the listbox selectionmode as multiple, then how to show all products containing the selected colors? For example if Red and Black is selected how to show all items containing black and red?

3. As far as dateformat, how to change the dateformat displayed in the grid through the code page?

[Princy]

Hi ,

Please try the below code snippet that depicts your required scenario.

ASPX:

<telerik:RadGrid ID="RadGrid1" runat="server" AutoGenerateColumns="false" GridLines="None"
    OnNeedDataSource="RadGrid1_NeedDataSource">
    <MasterTableView>
        <Columns>
            <telerik:GridBoundColumn UniqueName="ProductName" DataField="ProductName" HeaderText="ProductName" />
            <telerik:GridBoundColumn DataField="ProductColor" HeaderText="ProductColor" UniqueName="ProductColor" />
            <telerik:GridBoundColumn DataField="ProductDate" HeaderText="ProductDate" UniqueName="ProductDate" />
        </Columns>
    </MasterTableView>
    <ClientSettings Scrolling-AllowScroll="true">
    </ClientSettings>
</telerik:RadGrid>
<asp:TextBox ID="txtProductName" runat="server"></asp:TextBox>
<telerik:RadListBox ID="lstProductColor" runat="server" SelectionMode="Multiple">
    <Items>
        <telerik:RadListBoxItem Text="Empty" />
        <telerik:RadListBoxItem Text="Red" />
        <telerik:RadListBoxItem Text="Blue" />
        <telerik:RadListBoxItem Text="Black" />
    </Items>
</telerik:RadListBox>
<asp:TextBox ID="dateFrom" runat="server"></asp:TextBox>
<asp:TextBox ID="dateTo" runat="server"></asp:TextBox>
<asp:Button ID="btnSerach" runat="server" Text="Search" OnClick="btnSerach_Click" />

C#:

SqlConnection con = new SqlConnection(WebConfigurationManager.ConnectionStrings["Northwind_newConnectionString3"].ConnectionString);

DataTable dt;

protected void Page_Load(object sender, EventArgs e)

{

    if (!IsPostBack)

    {

        GridBoundColumn col;

        col = (GridBoundColumn)RadGrid1.MasterTableView.GetColumn("ProductDate");

        col.DataFormatString = "{0:MM/dd/yyyy}";       


    }

}

protected void btnSerach_Click(object sender, EventArgs e)

{

    string parameterString=string.Empty;

    if ((txtProductName.Text == "") && (lstProductColor.SelectedItem.Text == "Empty") && (dateFrom.Text == "") && (dateTo.Text == ""))

    {

        RadGrid1.Rebind();

    }

    else

    {

        try

        {

            string ProductName = txtProductName.Text;

            int Color = lstProductColor.SelectedItems.Count;

            for (int i = 0; i < Color; i++)

            {

                parameterString += String.Concat("'", lstProductColor.SelectedItems[i].Text, "'");

                if (i < (Color - 1))

                {

                    parameterString += ",";

                }

            }

            String FromDate = dateFrom.Text;

            String ToDate = dateTo.Text;

            SqlCommand cmd = new SqlCommand("Select * FROM [ProductDetails] where ProductName LIKE '%" + ProductName + "%' and ProductColor IN (" + parameterString + ") and ProductDate between '" + FromDate + "' and '" + ToDate + "'", con);

            con.Open();

            SqlDataAdapter da = new SqlDataAdapter(cmd);

            dt = new DataTable();

            da.Fill(dt);

            RadGrid1.DataSource = dt;

            RadGrid1.DataBind();

        }

        catch (Exception ex)

        {

            // handle error

        }

        finally

        {

            con.Close();

        }

    }      

}

protected void RadGrid1_NeedDataSource(object sender, Telerik.Web.UI.GridNeedDataSourceEventArgs e)

{

    SqlCommand cmd1 = new SqlCommand("Select * FROM [ProductDetails]", con);

    SqlDataAdapter da1 = new SqlDataAdapter(cmd1);

    DataTable dt1 = new DataTable();

    da1.Fill(dt1);

    RadGrid1.DataSource = dt1;      

}

Thanks,
Princy

[sanman02150]

Hi Princy that works great when I submit it.

After submitting, if i try to sort by clicking on any column or if i try to go to next page, it does not retain the query and ends up showing everything in the table.

How do I fix that?

[Maria Ilieva]

Hello San,

As the filter logic is executed on ButtonClick event after sorting the RadGrid passes its lifecycle and resets this settings. therefore this logic should be placed in the NeedDataSource event. The code for setting the filter must be placed inside an if statement that checks that Page.IsPostBack is False. Note that this is applicable for non auto generated columns.

Regards,
Maria Ilieva
Telerik