sql 2005 - asp gridview - dates confusion

Question

4.00/5 (1 vote)

See more:

SQL-Server-2005

, +

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

Posted 26-Apr-11 11:32am

RayH1066

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wonde Tadesse · Answer 1 · 2011-04-26T13:31:00

Solution 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

SQL

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

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.

Posted 26-Apr-11 13:31pm

Wonde Tadesse

Updated 26-Apr-11 13:54pm

v3

Comments

[no name] 26-Apr-11 19:55pm

Hi Thanks. Im cerainly happy to use JQuery date picker, but it is not the client side that is giving a problem. The Ajx Calendar is fine. My problem is with the behaviour of the sql2005 table. I am trying to understand the way it is working because it doesnt make sense to me. Why for example can I input directly into the open table in UK date format but cannot update via a grid that way. I am trying to understand what is happening inbetween. Thank you for creating the function but I can't for the life of me see how I would call that via my slect statement. I use vs2008 to develop and I cant see how I would call this function while the select query is 'running'. BEcause - when I inject anything into the slect statement which is not the name of a database field the selection fails. here is my select SelectCommand="SELECT id, source, example, model_no, serial_no, nature_of_fault_etc, uplift_auth_no, debit_note_no, simple_instr_to_id, show, res_date FROM repair WHERE (show = @show) AND (model_no IS NOT NULL) AND (model_no > @model_no) ORDER BY res_date DESC"

[no name] 26-Apr-11 20:01pm

One question: What Language is the Create Function written in? I'm using c# and I can't see where to place this

Wonde Tadesse 6-Dec-11 10:25am

Create Function is database DDL(Data Definition Language). It is not c#.Look this link for detail(http://www.orafaq.com/faq/what_are_the_difference_between_ddl_dml_and_dcl_commands)

Just create the function in your database and use it appropriately.

Wonde Tadesse 6-Dec-11 10:26am

Or here is your select statement is going to be look like. Assuming that res_date is DateTime

SELECT id, source, example, model_no, serial_no, nature_of_fault_etc, uplift_auth_no, debit_note_no, simple_instr_to_id, show, CONVERT(VARCHAR(10), res_date, 103) AS res_date FROM repair WHERE (show = @show) AND (model_no IS NOT NULL) AND (model_no > @model_no) ORDER BY res_date DESC