Welcome Guest ( Log In | Register )

Outline · [ Standard ] · Linear+

.NET asp.net datetime problem, SQL/asp.net

views
     
TSsameer27
post Nov 26 2009, 10:09 AM, updated 15y ago

PlanTheFutureForABetterPast
****
Senior Member
684 posts

Joined: Oct 2009


I've been trying to get asp.net to filter according to the start date and end date for user based on the record from create date on the sql server but it doesn't seem to work out. I keep getting error's like datetime is not recognize, is not a string. I'm using VWD 08. I do not want to change the codes in the jscript file nor do i want to change anything in the back end. Is it possible to do it?
I've tried googling around, am yet to find a solution to it.
Thank you in advance.

CODE
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="rpt_feedback.aspx.cs" Inherits="WebApplication1.rpt_feedback" %>

<!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>Feedback Report</title>
   <script type="text/javascript" src="jquery-1.3.2.min.js"></script>
   <script type="text/javascript" src="jquery-ui-1.7.1.custom.min.js"></script>
   <link href="jquery-ui-1.7.1.custom.css"rel="stylesheet" type="text/css" />  
   
</head>
<body>
   <form id="form1" runat="server">
   <div>
       
       Start Date:<span lang="en-us">&nbsp; </span><asp:TextBox ID="txtStartDate" runat="server"></asp:TextBox> <br />
       End Date:<span lang="en-us">&nbsp;&nbsp; </span><asp:TextBox ID="txtEndDate" runat="server"></asp:TextBox><br />
       <br />
       <asp:Label ID="Label1" runat="server" Text="Category"></asp:Label>
       <span lang="en-us">:<asp:RadioButtonList ID="RadioButtonList1" runat="server" onselectedindexchanged="Button1_Click"
           RepeatDirection="Horizontal">
           <asp:ListItem>1</asp:ListItem>
           <asp:ListItem>2</asp:ListItem>
           <asp:ListItem>3</asp:ListItem>
       </asp:RadioButtonList>
       <br />
       </span>
       <asp:Button ID="Button1" runat="server" Text="Search" onclick="Button1_Click"/>
       <br />
       <br />
       <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
           DataKeyNames="feedback_code" DataSourceID="SqlDataSource1"
           AllowPaging="True" AllowSorting="True" CellPadding="4" ForeColor="#333333"
           GridLines="None">
           <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
           <Columns>
               <asp:BoundField DataField="feedback_code" HeaderText="feedback_code"
                   InsertVisible="False" ReadOnly="True" SortExpression="feedback_code" />
               <asp:BoundField DataField="create_date" HeaderText="create_date"
                   SortExpression="create_date" />
               <asp:BoundField DataField="Email_address" HeaderText="Email_address"
                   SortExpression="Email_address" />
               <asp:BoundField DataField="Message" HeaderText="Message"
                   SortExpression="Message" />
               <asp:BoundField DataField="Category" HeaderText="Category"
                   SortExpression="Category" />
               <asp:BoundField DataField="status" HeaderText="status"
                   SortExpression="status" />
               <asp:BoundField DataField="Reply_status" HeaderText="Reply_status"
                   SortExpression="Reply_status" />
           </Columns>
           <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
           <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
           <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
           <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
           <EditRowStyle BackColor="#999999" />
           <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
       </asp:GridView>
       <asp:SqlDataSource ID="SqlDataSource1" runat="server"
           ConnectionString="<%$ ConnectionStrings:gohomeproConnectionString %>"
           SelectCommand="select * from feedback
where category = @category AND create_DATE LIKE '%@create_DATE%'
order by feedback_code desc">

           <SelectParameters>
               <asp:ControlParameter ControlID="RadioButtonList1" Name="category"
                   PropertyName="SelectedValue" />
               <asp:ControlParameter ControlID="txtStartDate" Name="create_date"
                   PropertyName="" Type="DateTime" DefaultValue="24/12/1950"/>
               <asp:ControlParameter ControlID="txtEndDate" Name="create_date"
                   PropertyName="" Type="DateTime" DefaultValue="24/12/1950"/>
           </SelectParameters>
       </asp:SqlDataSource>
       
   </div>
   </form>
</body>
</html>
<script type="text/javascript">
       $(document).ready(function() {
       $("#<%=txtStartDate.ClientID%>").datepicker({ dateFormat: 'dd/mm/yy' });
       $("#<%=txtEndDate.ClientID%>").datepicker({ dateFormat: 'dd/mm/yy' });
   });
</script>


This post has been edited by sameer27: Nov 26 2009, 10:15 AM
unrealweapon
post Nov 26 2009, 10:12 AM

it's painful.
*****
Senior Member
865 posts

Joined: Jan 2008
From: Paradise City


QUOTE(sameer27 @ Nov 26 2009, 10:09 AM)
I've been trying to get asp.net to filter according to the start date and end date for user based on the record from create date on the sql server but it doesn't seem to work out. I keep getting error's like datetime is not recognize, is not a string. I'm using VWD 08. I do not want to change the codes in the jscript file nor do i want to change anything in the back end. Is it possible to do it?
I've tried googling around, am yet to find a solution to it.
Thank you in advance.

CODE
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="rpt_feedback.aspx.cs" Inherits="WebApplication1.rpt_feedback" %>

<!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>Feedback Report</title>
   <script type="text/javascript" src="jquery-1.3.2.min.js"></script>
   <script type="text/javascript" src="jquery-ui-1.7.1.custom.min.js"></script>
   <link href="jquery-ui-1.7.1.custom.css"rel="stylesheet" type="text/css" />  
   
</head>
<body>
   <form id="form1" runat="server">
   <div>
       
       Start Date:<span lang="en-us">&nbsp; </span><asp:TextBox ID="txtStartDate" runat="server"></asp:TextBox> <br />
       End Date:<span lang="en-us">&nbsp;&nbsp; </span><asp:TextBox ID="txtEndDate" runat="server"></asp:TextBox><br />
       <br />
       <asp:Label ID="Label1" runat="server" Text="Category"></asp:Label>
       <span lang="en-us">:<asp:RadioButtonList ID="RadioButtonList1" runat="server" onselectedindexchanged="Button1_Click"
           RepeatDirection="Horizontal">
           <asp:ListItem>1</asp:ListItem>
           <asp:ListItem>2</asp:ListItem>
           <asp:ListItem>3</asp:ListItem>
       </asp:RadioButtonList>
       <br />
       </span>
       <asp:Button ID="Button1" runat="server" Text="Search" onclick="datepicker"/>
       <br />
       <br />
       <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
           DataKeyNames="feedback_code" DataSourceID="SqlDataSource1"
           AllowPaging="True" AllowSorting="True" CellPadding="4" ForeColor="#333333"
           GridLines="None">
           <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
           <Columns>
               <asp:BoundField DataField="feedback_code" HeaderText="feedback_code"
                   InsertVisible="False" ReadOnly="True" SortExpression="feedback_code" />
               <asp:BoundField DataField="create_date" HeaderText="create_date"
                   SortExpression="create_date" />
               <asp:BoundField DataField="Email_address" HeaderText="Email_address"
                   SortExpression="Email_address" />
               <asp:BoundField DataField="Message" HeaderText="Message"
                   SortExpression="Message" />
               <asp:BoundField DataField="Category" HeaderText="Category"
                   SortExpression="Category" />
               <asp:BoundField DataField="status" HeaderText="status"
                   SortExpression="status" />
               <asp:BoundField DataField="Reply_status" HeaderText="Reply_status"
                   SortExpression="Reply_status" />
           </Columns>
           <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
           <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
           <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
           <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
           <EditRowStyle BackColor="#999999" />
           <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
       </asp:GridView>
       <asp:SqlDataSource ID="SqlDataSource1" runat="server"
           ConnectionString="<%$ ConnectionStrings:gohomeproConnectionString %>"
           SelectCommand="select * from feedback
where category = @category AND create_DATE LIKE '%@create_DATE%'
order by feedback_code desc">

           <SelectParameters>
               <asp:ControlParameter ControlID="RadioButtonList1" Name="category"
                   PropertyName="SelectedValue" />
               <asp:ControlParameter ControlID="txtStartDate" Name="create_date"
                   PropertyName="" Type="DateTime" DefaultValue="24/12/1950"/>
               <asp:ControlParameter ControlID="txtEndDate" Name="create_date"
                   PropertyName="" Type="DateTime" DefaultValue="24/12/1950"/>
           </SelectParameters>
       </asp:SqlDataSource>
       
   </div>
   </form>
</body>
</html>
<script type="text/javascript">
       $(document).ready(function() {
       $("#<%=txtStartDate.ClientID%>").datepicker({ dateFormat: 'dd/mm/yy' });
       $("#<%=txtEndDate.ClientID%>").datepicker({ dateFormat: 'dd/mm/yy' });
   });
</script>

*
try this @create_DATE instead of '%@create_DATE%' ?
TSsameer27
post Nov 26 2009, 10:16 AM

PlanTheFutureForABetterPast
****
Senior Member
684 posts

Joined: Oct 2009


I get "String was not recognized as a valid DateTime."
I was just wondering, could it be because of the

CODE
.datepicker({ dateFormat: 'dd/mm/yy' });

unrealweapon
post Nov 26 2009, 10:29 AM

it's painful.
*****
Senior Member
865 posts

Joined: Jan 2008
From: Paradise City


QUOTE(sameer27 @ Nov 26 2009, 10:16 AM)
I get "String was not recognized as a valid DateTime."
I was just wondering, could it be because of the

CODE
.datepicker({ dateFormat: 'dd/mm/yy' });

*
ermm..

dd/mm/yy
is
date/minutes/year

try using

dd/MM/yyyy

or

MM/dd/yyyy
TSsameer27
post Nov 26 2009, 10:32 AM

PlanTheFutureForABetterPast
****
Senior Member
684 posts

Joined: Oct 2009


Yeah, I totally ignored that. Now that I changed it, hoping it might work..
I get this "String was not recognized as a valid DateTime."


Added on November 26, 2009, 10:37 amCould it be that the sql statement is only passing 1 attribute but the control parameter is receiving 2 of it? I mean it's the same name attribute for the control parameter.


Added on November 26, 2009, 4:56 pm
CODE
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="rpt_feedback.aspx.cs" Inherits="WebApplication1.rpt_feedback" %>

<!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>Feedback Report</title>
   <script type="text/javascript" src="jquery-1.3.2.min.js"></script>
   <script type="text/javascript" src="jquery-ui-1.7.1.custom.min.js"></script>
   <link href="jquery-ui-1.7.1.custom.css"rel="stylesheet" type="text/css" />  
   
</head>
<body>
   <form id="form1" runat="server">
   <div>
       
       Start Date:<span lang="en-us">&nbsp; </span><asp:TextBox ID="txtStartDate" runat="server"></asp:TextBox> <br />
       End Date:<span lang="en-us">&nbsp;&nbsp; </span><asp:TextBox ID="txtEndDate" runat="server"></asp:TextBox><br />
       <br />
       <asp:Label ID="Label1" runat="server" Text="Category"></asp:Label>
       <span lang="en-us">:<asp:RadioButtonList ID="RadioButtonList1" runat="server" onselectedindexchanged="Button1_Click"
           RepeatDirection="Horizontal">
           <asp:ListItem>1</asp:ListItem>
           <asp:ListItem>2</asp:ListItem>
           <asp:ListItem>3</asp:ListItem>
       </asp:RadioButtonList>
       <br />
       </span>
       <asp:Button ID="Button1" runat="server" Text="Search" onclick="Button1_Click"/>
       <br />
       <br />
       <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
           DataKeyNames="feedback_code" DataSourceID="SqlDataSource1"
           AllowPaging="True" AllowSorting="True" CellPadding="4" ForeColor="#333333"
           GridLines="None">
           <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
           <Columns>
               <asp:BoundField DataField="feedback_code" HeaderText="feedback_code"
                   InsertVisible="False" ReadOnly="True" SortExpression="feedback_code" />
               <asp:BoundField DataField="create_date" HeaderText="create_date"
                   SortExpression="create_date" />
               <asp:BoundField DataField="Email_address" HeaderText="Email_address"
                   SortExpression="Email_address" />
               <asp:BoundField DataField="Message" HeaderText="Message"
                   SortExpression="Message" />
               <asp:BoundField DataField="Category" HeaderText="Category"
                   SortExpression="Category" />
               <asp:BoundField DataField="status" HeaderText="status"
                   SortExpression="status" />
               <asp:BoundField DataField="Reply_status" HeaderText="Reply_status"
                   SortExpression="Reply_status" />
           </Columns>
           <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
           <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
           <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
           <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
           <EditRowStyle BackColor="#999999" />
           <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
       </asp:GridView>
       <asp:SqlDataSource ID="SqlDataSource1" runat="server"
           ConnectionString="<%$ ConnectionStrings:gohomeproConnectionString %>"
           
           SelectCommand="SELECT feedback_code, create_date, Email_address, Message, Category, status, Reply_status FROM Feedback WHERE (Category = @category) AND (create_date BETWEEN '@startdate' AND '@enddate') ORDER BY feedback_code DESC">

           <SelectParameters>
               <asp:ControlParameter ControlID="RadioButtonList1" Name="category"
                   PropertyName="SelectedValue" />
               <asp:ControlParameter ControlID="txtStartDate" Name="startdate"
                   PropertyName="" Type="DateTime" />
               <asp:ControlParameter ControlID="txtEndDate" Name="enddate"
                   PropertyName="" Type="DateTime" />
           </SelectParameters>
       </asp:SqlDataSource>
       
   </div>
   </form>
</body>
</html>
<script type="text/javascript">
       $(document).ready(function() {
       $("#<%=txtStartDate.ClientID%>").datepicker({ dateFormat: 'dd-mm-yy' });
       $("#<%=txtEndDate.ClientID%>").datepicker({ dateFormat: 'dd-mm-yy' });
   });
</script>


I've modified the codes but I'm getting this error "Conversion failed when converting character string to smalldatetime data type."

I've even tried using hiddenfield for the textboxes and modify the source codes accordingly but to no avail.

Anyone out there who can help me?

This post has been edited by sameer27: Nov 26 2009, 04:56 PM
ghost181
post Nov 26 2009, 05:14 PM

Getting Started
**
Junior Member
216 posts

Joined: Feb 2006
From: 神秘的な地獄


CODE
select * from feedback
where category = @category AND convert(nvarchar,create_DATE,103) >= '06/11/2009' AND convert(nvarchar,end_DATE,103) <= '06/12/2009'
order by feedback_code desc


try this
unrealweapon
post Nov 26 2009, 06:09 PM

it's painful.
*****
Senior Member
865 posts

Joined: Jan 2008
From: Paradise City


SELECT feedback_code, create_date, Email_address, Message, Category, status, Reply_status FROM Feedback WHERE (Category = @category) AND (create_date BETWEEN '@startdate' AND '@enddate') ORDER BY feedback_code DESC


from this query... i can only see 1 parameter.. BECOZ.. the '' make '@startdate' AND '@enddate' strings .

remove the '' and try again.
davidbilly87
post Nov 29 2009, 04:51 AM

On my way
****
Senior Member
513 posts

Joined: Jul 2007



try this ? SQL > DATE_FORMAT(column_name,'Date : %d %b %Y / Time : %T : %i : %s') as date_time , i think the sql will auto detect ur date and time data on ur colum....column type : TIMESTAMP....btw i using mysql....it work for me...

This post has been edited by davidbilly87: Nov 29 2009, 04:53 AM
edthered
post Nov 29 2009, 06:09 AM

New Member
*
Junior Member
25 posts

Joined: Oct 2005
this is definitely a date format issue. by default, sql server will compare the date in the format of MM/dd/yy (US default format), Asian countries like us use dd/MM/yy.

Whether MM/dd/yy or dd/MM/yy (or maybe other format) is used or not depends on the computer itself. Check Control Panel > Regional And Languages Options .. check the Short Date format.

The best way is to do what ghost181 did. Reformat the date from sql to the format you are comparing it to. Otherwise, the code might work in 1 computer but not when you host it on another computer.

My 2 cents.
endymeon
post Dec 1 2009, 12:17 PM

Getting Started
**
Junior Member
193 posts

Joined: Jun 2007


QUOTE(unrealweapon @ Nov 26 2009, 06:09 PM)
SELECT feedback_code, create_date, Email_address, Message, Category, status, Reply_status FROM Feedback WHERE (Category = @category) AND (create_date BETWEEN '@startdate' AND '@enddate') ORDER BY feedback_code DESC
from this query... i can only see 1 parameter.. BECOZ..  the '' make '@startdate' AND '@enddate' strings .

remove the '' and try again.
*
I think that's the problem too...

 

Change to:
| Lo-Fi Version
0.0174sec    0.36    5 queries    GZIP Disabled
Time is now: 29th March 2024 - 12:18 AM