CODEWITHSUNDEEP
c#asp.netsql-server
Cinedine
Cinedine

Reputation: 23

Link a GridView with a Calendar

I have a database running with a column that has a DateTime property. The idea is that I grab that column and link it to a GridView. Then link a Calendar to it so I can select a day and all the record that are the same as the Date in the column of my database show up in the GridView.

So, how do I link a Calendar to a GridView?

Current setup

    protected void Calendar1_SelectionChanged(object sender, EventArgs e)
    {
        //build up a connection and fill the gridview
        SqlConnection conn;
        SqlCommand comm;
        SqlDataReader reader;
        string connectionString = ConfigurationManager.ConnectionStrings["LoginProject"].ConnectionString;
        conn = new SqlConnection(connectionString);
        comm = new SqlCommand("FillGridByCalendar", conn);
        comm.CommandType = CommandType.StoredProcedure;
        comm.Parameters.AddWithValue("@UserId", System.Data.SqlDbType.UniqueIdentifier);
        comm.Parameters.AddWithValue("@Firstname", System.Data.SqlDbType.NVarChar);
        comm.Parameters.AddWithValue("@Lastname", System.Data.SqlDbType.NVarChar);
        comm.Parameters.AddWithValue("@UserLoginPerDay", System.Data.SqlDbType.DateTime);

        try
        {
            conn.Open();
            reader = comm.ExecuteReader();
            GridView2.DataSource = reader;
            GridView2.DataBind();
            reader.Close();
        }
        catch (Exception ex)
        {
            dbErrorLabel.Text = Convert.ToString(ex);
        }

        finally
        {
            conn.Close();
        }
}

And the (altered) procedure.

ALTER PROCEDURE [dbo].[FillGridByCalendar] 
@UserLoginPerDay datetime,
@Firstname nvarchar,
@Lastname nvarchar,
@UserId uniqueidentifier
@datefilter -- Not sure what to add here 
AS
BEGIN
SELECT UserLoginToday.UserId, Gebruikers.Lastname, Gebruikers.Firstname, UserLoginToday.UserLoginPerDay 
FROM Gebruikers INNER JOIN UserLoginToday 
ON Gebruikers.UserId = UserLoginToday.UserId 
WHERE (UserLoginToday.UserLoginPerDay = ISNULL(@datefilter, UserLoginToday.UserLoginPerDay))
END

Upvotes: 0

Views: 1719

Answers (2)

mr100
mr100

Reputation: 4428

I understand that you want to apply filtering to a GridView by date. You might use SqlDataSource for that. Exemplary article showing how to apply the filtering to GridView (done with DropDowns but with Calendar this is very similar):

ASP.NET GridView Filtering with DropDownList

Upvotes: 0

Ethan Pelton
Ethan Pelton

Reputation: 1796

Welcome to Stack Overflow...uhmm...the donger...

You'll get great responses and advice here, but you should try to include any code you've tried. People here want to see that you've already attempted something, and it helps us diagnose your problem if we can see your code.

EDIT: I've added some detail - also, you don't need autopostback on the calendar control.

Assuming you have your sql datasource in place already, you need to add just a couple things.

In the selectcommand of the datasource, you need to add this...

WHERE yourtable.nameofdatefield = ISNULL(@datefilter, yourtable.nameofdatefield)

So it will look something like this...

SelectCommand="SELECT stuff FROM yourtable WHERE yourtable.nameofdatefield = ISNULL(@datefilter, yourtable.nameofdatefield)"

Right after your commands, you need to add this...

CancelSelectOnNullParameter="false"

in the parameter section of your sqldatasource, add this

<asp:ControlParameter Name="datefilter" ControlID="yourcalendarid" PropertyName="SelectedValue" />

so it will look like this...

<SelectParameters>
                            <asp:ControlParameter Name="datefilter" ControlID="yourcalendarid" PropertyName="SelectedValue" />
                        </SelectParameters>

Now, in your aspx, anywhere you like, you need a calendar with autopostback="true"

In design view, double click the calender, which will create a method in the code behind.

in the new method, write...

[id of your gridview].databind();

So here's what everything should look like...

your datasource should look something like this...

<asp:SqlDataSource ID="yoursqldatasourceid" runat="server" 
                        ConnectionString="<%$ ConnectionStrings:nameofyourconnection %>" 
                        DeleteCommand="you may or may not be deleting things" 
                        InsertCommand="you may or may not be inserting things" 
                        UpdateCommand="you may or may not be deleting things"
                        SelectCommand="SELECT stuff FROM yourtable WHERE yourtable.nameofdatefield = ISNULL(@datefilter, yourtable.nameofdatefield)" 
                        CancelSelectOnNullParameter="false"                        
                        >
                        <SelectParameters>
                            <asp:ControlParameter Name="datefilter" ControlID="yourcalendarid" PropertyName="SelectedValue" />
                        </SelectParameters>
                        <DeleteParameters>
                            <asp:Parameter Name="id" Type="Int32" />
                        </DeleteParameters>
                        <InsertParameters>
                            some insert parameters that you may or may not need...
                        </InsertParameters>
                        <UpdateParameters>
                            some update parameters that you may or may not need...
                        </UpdateParameters>
                    </asp:SqlDataSource>

And you should have a calendar control that looks like this...

<asp:Calendar ID="Calendar1" runat="server" OnSelectionChanged="Calendar1_SelectionChanged" ></asp:Calendar>

And a new method in your codebehind like this...

protected void Calendar1_SelectionChanged(object sender, EventArgs e)
{
    yoursqldatasourceid.databind();
}

If you're still lost, let me know and we can work through it...

Upvotes: 1

Related Questions