[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

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

0
Atouray
Asked:
Atouray
  • 6
  • 5
  • 3
3 Solutions
 
Paulmc999Commented:
em personally i would like to see your SQL code and you may find it useful to actually use the SQL profiler to see what is being queried from the database cause it sounds like the format in the query is wrong. Usually the format is something like '2011-10-28 00:00:00'
0
 
Paulmc999Commented:
You can download a free profiler called anjLab just do a google search
0
 
Paul MacDonaldDirector, Information SystemsCommented:
As the other PaulM notes, this is likely an SQL query issue rather than an ASP.Net issue.  Can you post your query?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
AtourayAuthor Commented:
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
0
 
Paul MacDonaldDirector, Information SystemsCommented:
And what would you expect @FirstDate and @SecondDate to be when this query fires?
0
 
Paulmc999Commented:
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
0
 
AtourayAuthor Commented:
<%@ 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

0
 
AtourayAuthor Commented:
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>
0
 
AtourayAuthor Commented:
Parser Error Message: Cannot create an object of type 'System.DateTime' from its string representation 'FistDate().ToString()' for the 'SelectedDate' property.
0
 
Paul MacDonaldDirector, Information SystemsCommented:
SelectedDate = "FistDate().ToString()"/>

should be

SelectedDate = "FirstDate().ToString()"/>
0
 
Paulmc999Commented:
I'll look for the code but in my opinion you have to convert the datetimes to String format 'YYYY-MM-DD HH:MM:SS' and place in the SQL statement like i have shown above. I have no other suggestions.
0
 
Paulmc999Commented:
ok def helps to see the error produced ;)
0
 
AtourayAuthor Commented:
that sound good.
i will have to do the conversion
thanks
0
 
AtourayAuthor Commented:
a
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 6
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now