Strange Date Problem

Hi all,

i am using visual studio 2005 and sql server 2005, i am developing a web application using visual basic 2005. i also let you know that i am in UK.

i am having a strange problem. i have got a form from whcih i need to insert the date values into the data base my querystring is as follows.

StrSql = "insert into tasks (TaskName,Sdate,PropEdate,InitID,TaskManager) values (" & _
"'" & Me.TxtTaskName.Text & "'," & _
"'" & Me.TxtSDate.Text & "'," & _
"'" & Me.TxtEdate.Text & "'," & _
Session("InitID") & "," & _
Me.DropDownList1.SelectedValue & _
")"

which is working fine except that it is storing date in different format that is mm/dd/yyyy.

on my other form i again want to insert the date value wich is not working.
my querystring is

StrSql = "insert into tasklog (taskid,currentstatus,updatedon,comments) values (" & _
Me.GridView1.SelectedValue & "," & _
Me.TxtRecentpos.Text & "," & _
"'" & Me.TxtDate.Text & "'," & _
"'" & Me.TxtComments.Text & "'" & _
")"

when i try to run this query it gives me the following error message:

The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value..

i have checked in the sqlserver and both the tables have got the smalldatetime datatype for my fields. it's really scaring for me that it is working on one place and not working on other.
any help would be highly appretiated.
thanksNo matter what the settings are in your code/computer/database, when putting date values into SQL statements, you cannot use the UK format (dd/mm/yyyy).

You must use either the US format (mm/dd/yyyy), or better a format which cannot be mis-interpreted (like yyyy/mm/dd).

If you format the values in this style, you will probably find that the code works perfectly.
Hi si,

i have tried both us format and the other format you told me. but sitll giving the same erroe message.

regards
Faizee
You must use either the US format (mm/dd/yyyy), or better a format which cannot be mis-interpreted (like yyyy/mm/dd).

If you format the values in this style, you will probably find that the code works perfectly.[/QUOTE]

Hi Si sorry i have tried (mm-dd-yyyy) and (yyyy-mm-dd) both are working fine. is it not possible that user enter the date in UK format ( i am using vb 2005) and then i format this to us format or other format to save in the SQL Server so that the users are not confused?

regards
Of course... you just have to convert it before using it in an SQL string.

I haven't used .Net enough to suggest the code you would use to convert it, but can recommend that you use a special date control rather than a textbox - as this is always clear to the user, and to your code too (no matter what settings the PC has).
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value..

This can be caused by trying to put a date less than 1/1/1900 or larger than 6/6/2079

Also check out using parameters with your SQL strings, it prevents sql injection as well has handles special characters.
Thanks Si and Bill,

i am using RJS Calendar control now and it's working fine.

thanks a lot for your help

Faizee