sql 2005-asp gridview-日期混淆 [英] sql 2005 - asp gridview - dates confusion

问题描述

嗨.我对sql2005，asp.net gridview之间的操作关系完全感到困惑.我正在尝试使用vs2008开发数据库.我在sql2005中有一个数据库表.我通过sqldatasource连接到它，并从中创建一个数据网格.到目前为止，一切都很好(有人会说不是重新使用sqldatasource，但是我正在学习，因此对我和此应用程序都适用).我的问题是输入日期.

日期在网格内显示为en-GB，如果我更改为en-US(不想但要尝试)，则日期仍显示为en-GB.可以吗?...如果我打开sql表，则所有日期都显示为en-GB格式，即23/12/2010.如果我在表格中输入23/12/2010，则接受日期.所以在我看来，桌子正在接受en-GB syle约会(这是我想要的). (我尝试直接在表格中输入2010年12月23日，并引发了错误)所以一切似乎还好吗?但事实并非如此.因为当我尝试在GridView编辑文本框中输入23/12/2010时，我收到非常烦人的更新0行.将表达式转换为数据类型为datetime的算术溢出错误.该语句已终止".在我看来，在日期命中率到达sql表之前，我必须对其进行逆转，但是我无法管理，但是令我困惑的是直接以en-GB格式将其输入到它想要的表中吗?

第二个令人烦恼和困惑的地方是:如果我选择编辑"，然后选择更新"而不更改数据，则该行中的日期(有3个)全部为反向"(如果它们的日期将反向而不会抛出该错误).上面的错误)，请选择编辑"，然后进行更新，然后日期会再次反转.如何在表格中直接输入，它将接受正确的日期，即23/12/2010，但是当使用gridview update时，会引发错误，以及为什么要逆转日期.到目前为止，我只能说的是裤子.我已经阅读了相关的丛书，现在已经尝试关闭60个小时，以使事情正确执行，但我不能.我确实需要帮助来了解正在发生的事情以及如何纠正此问题.我已经使用了无数的格式化思想，等等(哦，我正在使用模板字段)，但是似乎什么都没有用，这让我很惊讶，它正在逆转数据.那只是做不到！我真的需要帮助.谢谢Raymond

Hi. I am totally confused about the operationing relationship between sql2005, asp.net gridview. I am trying to develop a database using vs2008. I have a database table in sql2005. I connect to it via a sqldatasource and create a datagrid from it. So far so good (some would say not re the sqldatasource, but Im learning so it sfine for me and this application). The problem I have is with the input of dates.

The dates are displaying en-GB within the grid, and if I change to en-US (dont want to but experimenting) the dates still display as en-GB. ok?... If I open the sql table the all the dates are shown as en-GB format ie the 23/12/2010. If I input 23/12/2010 intothe table the date is accepted. So to my mind the table is accepting en-GB syle dating (which is what I want). (I tried entering 12/23/2010 direct into the table and an error was thrown)so everything seems ok? but it isn''t. BECAUSE when I try to enter 23/12/2010 into the GridView edit textbox I receive the very annoying "0 rows updated. Arithmetic overflow error converting expression to data type datetime. The statement has been terminated". It seems to me that I have to reverse the date paremeter before it hits the sql table but I just cant manage that, but what confuses me is the inputting direct into the table it wants it in en-GB format?

The second annoying and confusing bit for me is: If I select edit and then select update without changing data the dates (there are 3) within the row all ''reverse'' (if they are of a date that will reverse without throwing the above error), select edit then update and the dates reverse again. How is it possible to type direct into the table and it will accept the correct date ie 23/12/2010, but when using the gridview update an error is thrown, and why is it reversing the dates. So far all I can say about this technology is, it is pants. I have read bundles on this , I have tried now for close on 60 hours to get the thing to act correctly and I cant. I really need help understanding what is happening and how I can correct the matter. I have used countless formatting ideas etc, (Oh I''m using templated fields) but nothing seems to work and it is blowing my mind that it is reversing the dats. That just won''t do! I really need help with this. Thanks Raymond

推荐答案

这是您可以做的.

1.选择数据并绑定到网格视图时，请确保以以下格式转换日期时间字段.我创建了一个数据库功能，可以帮助您做到这一点

Here is what you can do.

1. When you select the data and bind to the grid view, make sure to convert the date time field in the following format. I created a database function that will help you to do this

CREATE FUNCTION [dbo].[ufn_GetDateOnly] (@inputDate DATETIME )
RETURNS NVARCHAR(10)
BEGIN
    RETURN CONVERT(NVARCHAR(10), @inputDate, 103) -- dd/mm/yyyy format
END

如何使用此功能.

只需在您的选择语句中说，

How to use this function.

Just call in your select statement say,

SELECT HotelNo ,dbo.ufn_GetDateOnly(DateFrom) AS DateFrom,dbo.ufn_GetDateOnly(DateTo) AS DateTo FROM Booking

注意:DateFrom和DateTo是DateTime数据类型.

2.从UI角度来看，如果您愿意使用 jQuery日期选择器 [自定义日期验证 [ ^ ]规则一定会为您提供帮助.

祝你好运.

Note: DateFrom and DateTo are DateTime data types.

2. From the UI perspective, If you are comfortable to use jQuery Date picker[^], this Custom Date validation[^] rule will definately help you.

Good luck.

这篇关于sql 2005-asp gridview-日期混淆的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！