VB6 Using U.S. and International Dates in Text Boxes, Data Grid and storing in SQL Server

Posted on 2011-10-16
Last Modified: 2012-05-12
I have a VB form that displays two text boxes: one defaults to today's date, txtCommentDate, and the other is blank for the user to enter some activity/comment associated with that date, txtComment. They can change the date in txtCommentDate if needed, but it defaults to today's date.  They can then click the AddComment button that updates this record to a relational table in SQL server, and re-displays this as a new record in the Comments data grid back on the form.

All worked fine when it was just U.S. users, but I now have Australia and I am having trouble displaying it differently for AUS, for example dd/mm/yy and then getting it into the database as the right date.  When I pass this formatted date to SQL, seems no matter what I do it puts it in incorrectly.  For example, if the date is displayed as today of 17/10/11, it goes into SQL as 10/11/2017.  I've tried re-formatting it as "mm/dd/yy" prior to passing to the SQL call, but this does nothing.

My questions are:
--I want to continue to display it one way for U.S. in the txtCommentDate text box and the other way for Australia.  How best to do this?
--When they click AddComment button, I need to pass the value of the txtCommentDate box into SQL Server as the valid date.  How do I do this?
--Since the DataGrid has its own properties, how best to display the date as U.S. or AUS in the Data Grid's display of this newly added CommentDate and Comment record.
Question by:VDanner
    LVL 75

    Accepted Solution

    >> I've tried re-formatting it as "mm/dd/yy" prior to passing to the SQL call, but this does nothing.<<
    When you format it for the database, always, always use an unambiguous date format such as YYYYMMDD.


    Author Closing Comment

    Thank you.  This got me on the right track.  With your suggestion in mind, I changed the format/display of the date to "mm-dd-yyyy" for the U.S. and "dd-mm-yyyy" for Australia.  Then at the time I wanted to insert it into the database, I applied the following:
    Dim strDate As String
    strDate = Format(txtCommentDate.Text, "yyyy-mm-dd")
    'Call the method to update the comment record
    lngRC = mobjclsDirects.InsertComment(txtAccountID.Text, CDate(strDate), txtComment.Text, strMsg(), gobjConn, mobjcmd)

    This passed it to the class function InsertComment that accepts it as a Date, and inserts it into SQL as a DBTimeStamp.

    That may not be exactly what you had in mind, but it worked.  Thanks to your suggestion for getting me on the right track.
    LVL 75

    Expert Comment

    by:Anthony Perkins
    >>That may not be exactly what you had in mind, but it worked.<<
    Actually, that is pretty close to what I had in mind.  I would have used Stored Procedures, but the outcome is the same.

    Author Comment

    Good I'm glad to read that this is pretty close to your approach.  I do often use Stored Procedures - just didn't in this case (older code).  Thanks!

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now