Date format

VbStudent9 · October 23rd, 2010, 02:31 AM

I have a access database,data type are "TEXT" for all fields
When i update the database date field appears as 2010-10-22. I want to load it as 10/22/2010. Please advise me what is wrong with my code

Code:

 mydate = CalScheduler.SelectionStart()	 'Date selected on month Calander
       
       
        txtDay.Text = mydate.DayOfWeek.ToString	 'extracting the day of week from the date
       
     

        txDate.Text =    mydate.ToShortDateString()
        
        
   	 Try
            newrow = DsDateExt1.tblDate.NewRow
            'Schedule.newrow = newrow
            newrow("SchDate") = CStr(txtDate.Text)
            newrow("SchTime") = CStr(txtTime.Text)
            newrow("JobType") = CStr(txtType.Text)
            newrow("CustomerID") = CStr(txtID.Text)
            newrow("Comments") = CStr(txtRemarks.Text)           

           
                DsDate1.tblDate.Rows.Add(newrow)
                daDate.Update(DsDate11)

i did try to format date using this code

Code:

Dim theDate As DateTime = txtDate.Text
        Dim ymd As String = theDate.ToString("MM/dd/yyyy")

and then replacingthe txtdate.text to ymd in row update code
newrow("SchDate") = CStr(txtDate.Text)

but result is same. It appears in table as 2010-10-22. The real question here is not the date format but what is happening while updating the table in data base

malcolmdixon · October 24th, 2010, 02:15 PM

First question, why is a date field being defined as Text in Access?

The second thing is I don't think there is a need to convert txtDate.Text to a string since it already is, so strip out all the CStr() functions and try that.

Malc.

VbStudent9 · October 24th, 2010, 06:48 PM

You are right but it dont make no difference. I tried both with and without cstr for text box but the table value is stored as yyyy-mm-dd. I know textbox values are string values.
The problem with this format is when I search a record for mm/dd/yyyyy it doesnt find. When I search for yyyy-mm-dd it finds the record. when I load datagrid it loads the date as yyyy-mm-dd.

This format is not popular in US. I am thinking ahead. What if i have to do something down the road for a customer and customer doesnt like the format.

If I dont understand what or why this is happening, I may have some serious problem in future.

I will greatly appreciate if some one tells me why or what is happening and what to do to prevent it.

I was using text in access database because I read some where, handling date as string is easier then handling them as date

gbianchi · October 24th, 2010, 10:16 PM

Well, access is handling dates as the OS format is telling it..

You are getting the date from a calendar, so in your case the calendar is returning the date in the format that is spected by the OS. When you convert it to string, it doesn't matter since the short string from the date is still handle by OS. If you change it in the OS it will also change in the calendar control and probably on the database also.

Yasho · November 3rd, 2010, 01:04 AM

I am no expert but a little 2 cent worth
"no matter how the date/time was formatted in the database product, itself, that format is completely lost when the value only is retrieved into ADO and VBScript.

may be this is what happening.