asked on

ASP.NET

i Created a report.
but when i run it i have the error:SqlDateTime overflow
pls review the codebehind and aspx.
i want when the page is load and time is selected by user the required record is fetch from the datebase.
NB: DateCreated " Field in the db is :Datetime.
pls help

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Showrejected.aspx.cs" Inherits="GambiaStat.Showrejected" %>
<%@ Register Assembly ="eWorld.UI, Version=2.0.6.2393, 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" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
                     <title>Overview of Rejected Applicants</title>
                 

                      <script type = "text/css">
          p {border-style:double; border-width:thick;border-color: ActiveBorder;}
          img1{float:left}
          img1{float:left}
          p {text-align:center; font-size:24pt;color:white;background-color:Maroon;
            width: 1020px;
            margin-left: 4px;}
        
        </script> 
           
                     <style type="text/css">
                         .style4
                         {
                             width: 273px;
                         }
                         .style5
                         {
                             width: 211px;
                         }
                     </style>
           
</head>
<body>
    <form id="form1" runat="server">
      
    <div>
         <table>
                    <tr><td valign= "top"><img src = "statistics.gif"  style= "width:200px; height:100px"/></td></tr>
                            
          </table>        
            
              <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>
            
   
    </div>
    <div>
    <table border ="1"   style="width: 720px">
       <%-- <tr>
        <td class="style2"  align="left">
            <asp:DropDownList ID="DropDownList1" runat="server" Width="131px" Height="22px">
           
            <asp:ListItem>BJL</asp:ListItem>
            <asp:ListItem>BK</asp:ListItem>
            <asp:ListItem>BS</asp:ListItem>
            <asp:ListItem >FF</asp:ListItem>
            <asp:ListItem>SK</asp:ListItem>
            <asp:ListItem>ALL</asp:ListItem>
            <asp:ListItem Selected="True">STATION:</asp:ListItem>
                
            </asp:DropDownList>
            
        </td>
        </tr>--%>
      <tr>
      <td><asp:Label ID="Label1" runat="server" Text="STARTDATE:" ForeColor="Maroon"></asp:Label>
      </td>

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

                <div>

     
        
  

                    <asp:GridView ID="GridView1" AutoGenerateColumns = "false" runat="server" Width="717px" 
                        Caption="RESULT:" BackColor="White" BorderColor="#CC9966" 
                        BorderStyle="None" BorderWidth="1px" CellPadding="4" 
                        DataSourceID="SqlDataSource1" 
                        onselectedindexchanged="GridView1_SelectedIndexChanged">
                        <FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />
                        <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" />
                        <PagerStyle BackColor="#FFFFCC" ForeColor="#330099" HorizontalAlign="Center" />
                        <RowStyle BackColor="White" ForeColor="#330099" />
                        <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="#663399" />
                        <SortedAscendingCellStyle BackColor="#FEFCEB" />
                        <SortedAscendingHeaderStyle BackColor="#AF0101" />
                        <SortedDescendingCellStyle BackColor="#F6F0C0" />
                        <SortedDescendingHeaderStyle BackColor="#7E0000" />
                        <Columns>
                       <asp:BoundField DataField = "Nationality" HeaderText = "NATIONALITY" />
                       <asp:BoundField DataField = "Nin" HeaderText = "NIN" />
                       <asp:BoundField DataField = "ApprovalStatus" HeaderText = "APPROVALSTATUS" />
                       <asp:BoundField DataField = "gender" HeaderText = "GENDER" />
                        </Columns>
                        
                        
                        </asp:GridView>

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

                    <SelectParameters>
                    <asp:ControlParameter Name="FirstDate" ControlID="period1" PropertyName="SelectedDate" />
                     <asp:ControlParameter Name="SecondDate" ControlID="period2" PropertyName="SelectedDate" />
                    </SelectParameters>
                    </asp:SqlDataSource>

            </div> 
            
    </form>
</body>
</html>

Open in new window

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;
        }

        protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

      
        


        

        
        
        
      }









       



















    
}

Open in new window

ASKER CERTIFIED SOLUTION

Paulmc999

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Paulmc999

You can download a free profiler called anjLab just do a google search

Paul MacDonald

As the other PaulM notes, this is likely an SQL query issue rather than an ASP.Net issue. Can you post your query?

Atouray

ASKER

this is the query:
SelectCommand="
Select Nationality,Nin,ApprovalStatus,gender From Bio_data where DateCreated between @FirstDate and @SecondDate">

the two method from from the codebehind is what i want to call

Paul MacDonald

And what would you expect @FirstDate and @SecondDate to be when this query fires?

Paulmc999

But where do @firstdate and @seconddate come from??? you need something like

'...BETWEEN ' + @Firstdate +' AND ' + @Seconddate .... '

and @Firstdate and @Seconddate would already have to have been converted to string format like i gave you above

Atouray

ASKER

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Showrejected.aspx.cs" Inherits="GambiaStat.Showrejected" %>
<%@ Register Assembly ="eWorld.UI, Version=2.0.6.2393, 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" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Overview of Rejected Applicants</title>

<script type = "text/css">
p {border-style:double; border-width:thick;border-color: ActiveBorder;}
img1{float:left}
img1{float:left}
p {text-align:center; font-size:24pt;color:white;background-color:Maroon;
width: 1020px;
margin-left: 4px;}

</script>

<style type="text/css">
.style4
{
width: 273px;
}
.style5
{
width: 211px;
}
</style>

</head>
<body>
<form id="form1" runat="server">

<div>
<table>
<tr><td valign= "top"><img src = "statistics.gif" style= "width:200px; height:100px"/></td></tr>

</table>

<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>

</div>
<div>
<table border ="1" style="width: 720px">
<%-- <tr>
<td class="style2" align="left">
<asp:DropDownList ID="DropDownList1" runat="server" Width="131px" Height="22px">

<asp:ListItem>BJL</asp:ListItem>
<asp:ListItem>BK</asp:ListItem>
<asp:ListItem>BS</asp:ListItem>
<asp:ListItem >FF</asp:ListItem>
<asp:ListItem>SK</asp:ListItem>
<asp:ListItem>ALL</asp:ListItem>
<asp:ListItem Selected="True">STATION:</asp:ListItem>

</asp:DropDownList>

</td>
</tr>--%>
<tr>
<td><asp:Label ID="Label1" runat="server" Text="STARTDATE:" ForeColor="Maroon"></asp:Label>
</td>

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

</tr>

</table>
</div>

<div>

<asp:GridView ID="GridView1" AutoGenerateColumns = "false" runat="server" Width="717px"
Caption="RESULT:" BackColor="White" BorderColor="#CC9966"
BorderStyle="None" BorderWidth="1px" CellPadding="4"
DataSourceID="SqlDataSource1"
onselectedindexchanged="GridView1_SelectedIndexChanged">
<FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />
<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" />
<PagerStyle BackColor="#FFFFCC" ForeColor="#330099" HorizontalAlign="Center" />
<RowStyle BackColor="White" ForeColor="#330099" />
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="#663399" />
<SortedAscendingCellStyle BackColor="#FEFCEB" />
<SortedAscendingHeaderStyle BackColor="#AF0101" />
<SortedDescendingCellStyle BackColor="#F6F0C0" />
<SortedDescendingHeaderStyle BackColor="#7E0000" />
<Columns>
<asp:BoundField DataField = "Nationality" HeaderText = "NATIONALITY" />
<asp:BoundField DataField = "Nin" HeaderText = "NIN" />
<asp:BoundField DataField = "ApprovalStatus" HeaderText = "APPROVALSTATUS" />
<asp:BoundField DataField = "gender" HeaderText = "GENDER" />
</Columns>

</asp:GridView>

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

<SelectParameters>
<asp:ControlParameter Name="FirstDate" ControlID="period1" PropertyName="SelectedDate" />
<asp:ControlParameter Name="SecondDate" ControlID="period2" PropertyName="SelectedDate" />
</SelectParameters>
</asp:SqlDataSource>

</div>

</form>
</body>
</html>

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
    {

        
   


        protected void Page_Load(object sender, EventArgs e)
        {


          

           
            
        }

        private DateTime FirstDate()
        {
            return period1.SelectedDate;

        }
        private DateTime SecondDate()
        {
            return period2.SelectedDate;
        }

        protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

      
        


        

        
        
        
      }









       



















    
}

Open in new window

Atouray

ASKER

i change that now the error is :
Line 76:
Line 77: <td class="style5">
Line 78: <ew:CalendarPopup runat="server" id="period1" Nullable="True" ShowGoToToday="True"
Line 79: PopupLocation="Bottom" SelectedDate = "FistDate().ToString()"/>
Line 80: </td>

Atouray

ASKER

Parser Error Message: Cannot create an object of type 'System.DateTime' from its string representation 'FistDate().ToString()' for the 'SelectedDate' property.

Paul MacDonald

SelectedDate = "FistDate().ToString()"/>

should be

SelectedDate = "FirstDate().ToString()"/>

SOLUTION

Paulmc999

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Paulmc999

ok def helps to see the error produced ;)

SOLUTION

Atouray