I have to start importing the whole MS Access database to the new SQL Server database that I have created.

Scenario:
a Date/Time data type was declared on some of the fields in MS Access. There will be an overflow-error when exporting these tables (structure & data) to SQL Server (with a DateTime data type), especially when the parameter is NULL. 

In my previous projects (as a VB-classic programmer), I have encountered some issues regarding this data type (DateTime) in SQL Server and my DTPicker tool.

My solution has always been, changing the data type declaration in SQL Server. From DateTime to Numeric (8) or nVarchar (10) and do whatever manipulation needed in my VB codes.

i.e.:

Date in database = 19991231

String manipulation:

intDate = 19991231

intYr = Left(intDate, 4)

intMo = Mid(intDate, 5, 2) 

intDd = Right(intDate, 2)

var = cstr(intYr) & "/" & cstr(intMo) & "/" & cstr(intDd)

Output: 1999/12/31

Format Date: 

stringDate = Format(Date,"yyyymmdd") 

To display a NULL value on DTPicker, try using this code:

Private Sub FormatDTPicker()

    With dtPicker

        If .value = vbNull Then

            .format = dtpCustom

            .customFormat = "X"

        Else

            .format = dtpShortDate

        End If

     End With

End Sub 

Private Sub dtPicker_CloseUp()

    FormatDTPicker 

End Sub 

Private Sub dtPicker_Format(ByVal CallbackField as String, Formatted_String as String) 

    If CallbackField = "X" Then

        FormattedString = " "

   End If

End Sub 

Inside the code sample:

    dtpBirthday.value = vbNull

    FormatDTPicker 

These have worked for me in VB classic. 

I am working on a VB.net right now.

Will they work still?

I have yet to discover. 

Lesson learned so far: It's fun to work with databases.
But 45 days will never be enough to work both on the database and the system.

I just wish people know that (or I wish I was never that st*pid to submit such proposal!).

I am going to need all the support the net could give me these days.

No one has commented on this article.

Please keep your comments brief and on topic, and remember that this is not a discussion thread.
Name :
E-mail :
Website :
Comment(s) :

J! Reactions 1.09.00 • General Site License
Copyright © 2006 S. A. DeCaro