SqlDatasource in

Posted on 2011-10-27
Last Modified: 2012-05-12
Create a report that should show record between the two dates select by user from the eworld popup calender.
i create two methods:
each return date selected by user.
i want, On_page load ,sqldatasource select command query to include these two methods
so that the returned value from each will be the date to be read.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.UI.WebControls;

namespace GambiaStat

    public partial class Showrejected : System.Web.UI.Page

        private string Querystring = " ";

        protected void Page_Load(object sender, EventArgs e)


        private  DateTime FirstDate()
            return period1.SelectedDate;

        private DateTime SecondDate()
            return period2.SelectedDate;

        //private SqlDataSource  showreport()






Open in new window

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Showrejected.aspx.cs" Inherits="GambiaStat.Showrejected" %>
<%@ Register Assembly ="eWorld.UI, Version=, Culture=neutral, PublicKeyToken=24d65337282035f2" namespace="eWorld.UI" tagprefix="ew" %> 
<%@ Register Assembly="System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
    Namespace="System.Web.UI" TagPrefix="asp" %>
<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "System.Data.SqlClient" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "">

<html xmlns="">
<head runat="server">
                     <title>Overview of Rejected Applicants</title>

                      <script type = "text/css">
          p {border-style:double; border-width:thick;border-color: ActiveBorder;}
          p {text-align:center; font-size:24pt;color:white;background-color:Maroon;
            width: 1020px;
            margin-left: 4px;}
                     <style type="text/css">
                             width: 74px;
                             width: 273px;
                             width: 211px;
    <form id="form1" runat="server">
                    <tr><td valign= "top"><img src = "statistics.gif"  style= "width:200px; height:100px"/></td></tr>
              <p style="color: #008080; text-decoration: underline; font-size: x-large; text-transform: uppercase; font-variant: small-caps; font-style: inherit; font-weight: bold; font-family: 'Times New Roman', Times, serif"> STATISTIC OF REJECTED APPLICANTS PER STATION:</p>
              <h1 style="border-style: solid; background-color: #800000; font-family: 'Copperplate Gothic Bold'; font-size: medium; font-weight: normal; font-style: inherit; font-variant: small-caps; text-transform: uppercase; color: #FFFFFF; width: 944px;">Station and Time Period:</h1>
    <table border ="1"   style="width: 720px">
       <%-- <tr>
        <td class="style2"  align="left">
            <asp:DropDownList ID="DropDownList1" runat="server" Width="131px" Height="22px">
            <asp:ListItem >FF</asp:ListItem>
            <asp:ListItem Selected="True">STATION:</asp:ListItem>
      <td><asp:Label ID="Label1" runat="server" Text="STARTDATE:" ForeColor="Maroon"></asp:Label>

        <td class="style5">
        <ew:CalendarPopup runat="server" id="period1" Nullable="True" ShowGoToToday="True" 
        <td> <asp:Label ID="Label2" runat="server" Text="ENDDATE:" ForeColor="Maroon"></asp:Label>
        <td class="style4">
        <ew:CalendarPopup runat = "server" ID ="period2" Nullable="true" ShowGoToToday="true"
        PopupLocation ="Bottom"> </ew:CalendarPopup>


       <asp:GridView ID="GridView1"  AutoGenerateColumns="False" runat="server" Width="957px" 
                        BackColor="#DEBA84" BorderStyle="None" Font-Bold="True" 
                        CaptionAlign="Top" BorderColor="#DEBA84" BorderWidth="1px" CellPadding="3" 
                        CellSpacing="2" SelectedIndex="1" DataSourceID="SqlDataSource1">
       <asp:BoundField HeaderText="Nationality" DataField="Nationality" 
               SortExpression="Nationality" />
       <asp:BoundField HeaderText="Nin" DataField = "Nin" SortExpression="Nin"  />
       <asp:BoundField HeaderText="ApprovalStatus" DataField ="ApprovalStatus" 
               SortExpression="ApprovalStatus" />
           <asp:BoundField DataField="gender" HeaderText="gender" 
               SortExpression="gender" />
           <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
           <HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
           <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
           <RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
           <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
           <SortedAscendingCellStyle BackColor="#FFF1D4" />
           <SortedAscendingHeaderStyle BackColor="#B95C30" />
           <SortedDescendingCellStyle BackColor="#F1E5CE" />
           <SortedDescendingHeaderStyle BackColor="#93451F" />


                    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
                        ConnectionString="<%$ ConnectionStrings:IDKitConnectionString2 %>" SelectCommand="Select Nationality,Nin,ApprovalStatus,gender From Bio_data where DateCreated between 'FirstDate()'and 'SecondDate()' ">



Open in new window

Question by:Atouray
    LVL 20

    Accepted Solution

    You probably want to use a controlparameter to get where you're going. I put up a simple sample of how this operates <see snippet>.

    You can use this for further reference:

    Since you are using this calendar popup:
    <ew:CalendarPopup runat="server" id="period1" Nullable="True" ShowGoToToday="True" PopupLocation="Bottom"/>

    Your parameters would look something like this:
    <asp:ControlParameter Name="FirstDate" ControlID="period1" PropertyName="SelectedDate" />
    <asp:ControlParameter Name="SecondDate" ControlID="period2" PropertyName="SelectedDate" />

    Your selectcommand would probably look something like this:
    SelectCommand="Select Nationality,Nin,ApprovalStatus,gender From Bio_data where DateCreated between @FirstDate and @SecondDate'

    Its either that or setting up normal sql parameters:

    Then you could set their values in the code behind to use the values from your calendarpopups
    		<asp:Label ID="Period1" runat="server" Text="Daffy" />
    		<asp:GridView ID="GrdView" runat="server" DataSourceID="SqlDS" />
    		<asp:SqlDataSource ID="SqlDS" runat="server"
    		 ConnectionString="Data Source=WT101\WT101A;Initial Catalog=WellNess;Integrated Security=True"
    		 SelectCommand="SELECT * FROM WellnessChoice WHERE pi_fname=@FirstName">
    			<asp:ControlParameter Name="FirstName" ControlID="Period1" PropertyName="Text" />

    Open in new window


    Author Closing Comment

    that fine.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
    A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now