Outline ·
[ Standard ] ·
Linear+
.NET asp.net datetime problem, SQL/asp.net
TSsameer27
|
Nov 26 2009, 10:09 AM, updated 15y ago
|
|
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"> </span><asp:TextBox ID="txtStartDate" runat="server"></asp:TextBox> <br /> End Date:<span lang="en-us"> </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
|
Nov 26 2009, 10:12 AM
|
|
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"> </span><asp:TextBox ID="txtStartDate" runat="server"></asp:TextBox> <br /> End Date:<span lang="en-us"> </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
|
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' });
|
|
|
|
unrealweapon
|
Nov 26 2009, 10:29 AM
|
|
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
|
Nov 26 2009, 10:32 AM
|
|
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 pmCODE <%@ 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"> </span><asp:TextBox ID="txtStartDate" runat="server"></asp:TextBox> <br /> End Date:<span lang="en-us"> </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
|
Nov 26 2009, 05:14 PM
|
Getting Started
|
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
|
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.
|
|
|
|
davidbilly87
|
Nov 29 2009, 04:51 AM
|
|
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
|
Nov 29 2009, 06:09 AM
|
New Member
|
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
|
Dec 1 2009, 12:17 PM
|
Getting Started
|
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...
|
|
|
|