• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • Last Modified:

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

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.
AddComment.jpg
0
VDanner
Asked:
VDanner
  • 2
  • 2
1 Solution
 
Anthony PerkinsCommented:
>> 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.

0
 
VDannerAuthor Commented:
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.
0
 
Anthony PerkinsCommented:
>>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.
0
 
VDannerAuthor Commented:
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!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now