Best way to edit SQL datetime

Plateforme Level Extreme

Abonnement

Profil corporatif

Produits & Services

Support

Légal

English

Best way to edit SQL datetime

Message

15/02/2007 12:32:29

Marcia Akins
Arkansas, États-Unis

12/02/2007 12:00:55

Lynda Taylor
Bellevue, Washington, États-Unis

Information générale

Forum:

Visual FoxPro

Catégorie:

Gestionnaire d'écran & Écrans

Titre:

Re: Best way to edit SQL datetime

Divers

Thread ID:

01194784

Message ID:

01196131

Vues:

I'm looking for suggestions on entering data into SQL datetime fields. The client currently inputs the date portion into a textbox, the hour and minutes in 12-hour format using separate spinners for each and the am/pm portions using radio buttons. All 7 of these controls are bound to variables that are built into or parsed from time fields in the table each time one of the controls is accessed. Now that the data will be in SQL, I'd like to simplify the entry and editing if possible. I still need to display the date, start time and end time separately for the event. Does anyone have an easy/intuitive way to handle datetime entry?

How is this?

**************************************************
*-- Class:        cntdtspin 
*-- ParentClass:  container
*
Define Class cntdtspin As container

  Width = 171
  Height = 25
  *-- DateTime Value
  tdtime = {}
  *-- Year Portion of the Value
  nyear = 0
  *-- Month  Portion of the Value
  nmonth = 0
  *-- Day  Portion of the Value
  nday = 0
  *-- Hour  Portion of the Value
  nhour = 0
  *-- Minute Portion of the Value
  nmin = 0
  *-- Seconds  Portion of the Value
  nsec = 0
  *-- Location of Highlight in TextBox
  nstartpos = 0
  *-- Selected Length
  nhighlit = 2
  *-- Default Setting of Century - we need it ON in this control
  ccentury = ""
  *-- Character String representation of the Value for display and update
  cdtime = ""
  Name = "cntdtspin"


  Add Object spndtime As spnbase With ;
    Height = 22, ;
    Left = 148, ;
    Top = 1, ;
    Width = 20, ;
    ZOrderSet = 0, ;
    cobjmode = "ALL", ;
    uoldval = 0, ;
    Name = "spnDTime"


  Add Object txtdtime As txtbase With ;
    Alignment = 3, ;
    Value = "", ;
    ControlSource = "This.Parent.cDTime", ;
    Format = "", ;
    Height = 22, ;
    Left = 1, ;
    SelectOnEntry = .T., ;
    Top = 1, ;
    Width = 147, ;
    ZOrderSet = 1, ;
    cobjmode = "ALL", ;
    Name = "txtDTime"


  *-- Update the Display Value from components - called from Refresh() automatically
  Procedure updatevalue
    *** Update the Value Property from the individual components
    Local lcTimeStr
    With This
      *** Set the DateTime Value
      lcTimeStr = Transform( .nyear ) + "," + Transform( .nmonth ) + "," + Transform( .nday ) + ","
      lcTimeStr = lcTimeStr + Transform( .nhour ) +  + "," + Transform( .nmin ) + ", 0"
      This.tdtime =  Datetime( &lcTimeStr )
      *** Set the Character string Value
      lcTimeStr = Padl( .nmonth, 2, '0' ) + "/" + Padl( .nday, 2, '0' ) + "/" + Transform( .nyear ) + " "
      If Between( .nhour, 1, 11 )
        lcHour = Padl( .nhour, 2, '0' )
      Else
        If Between( .nhour, 13, 23 )
          lcHour = Padl( .nhour - 12, 2, '0' )
        Else
          lcHour = '12'
        Endif
      Endif
      lcTimeStr = lcTimeStr + lcHour + ":" + Padl( .nmin, 2, '0' ) + " "
      lcTimeStr = lcTimeStr + Iif( Between( .nhour, 12, 23 ), 'PM', 'AM' )
      This.cdtime = lcTimeStr
    Endwith
  Endproc


  *-- Changes the Value when spinner is clicked. Called from UpClick and DownClick Events
  Procedure changeval
    Lparameters tnChangeBy
    Local lnSelLen, lnSelSt

    With This
      *** First we have to figure out what is to be changed
      lnSelLen = .nhighlit
      lnSelSt  = .nstartpos

      Do Case
        Case ( lnSelSt = 0 And lnSelLen = 0 ) Or ( lnSelLen > 20 )
          *** Either nothing is selected or the entire DateTime is selected
          *** So alter by a whole day
          .nday = .ValidDay( .nday + tnChangeBy )

        Case Between( lnSelSt, 0, 1 )
          *** We are on the month portion
          .nmonth = .nmonth + tnChangeBy
          .nday = .ValidDay( .nday )
          .nmonth = Icase( .nmonth > 12, 1, .nmonth < 1, 12, .nmonth )
          lnSelLen = 2
          lnSelSt = 0

        Case Between( lnSelSt, 3, 4 )
          *** We have the day portion highlighted
          .nday = .ValidDay( .nday + tnChangeBy )
          lnSelLen = 2
          lnSelSt = 3

        Case Between( lnSelSt, 6, 9 )
          *** We have the year portion highlighted
          .nyear = .nyear + tnChangeBy
          lnSelLen = 4
          lnSelSt = 6

        Case Between( lnSelSt, 11, 12 )
          *** We have the hour portion highlighted
          .nhour = .nhour + tnChangeBy
          .nhour = Icase( .nhour < 0, 23, .nhour > 23, 0, .nhour )
          lnSelLen = 2
          lnSelSt = 11

        Case Between( lnSelSt, 14, 15 )
          *** We have the minute portion highlighted
          .nmin = .nmin + tnChangeBy
          .nmin = Icase( .nmin > 59, 0, .nmin < 0, 59, .nmin )
          lnSelLen = 2
          lnSelSt = 14

        Case Between( lnSelSt, 17, 18 )
          *** We have the AM/PM portion highlighted
          If tnChangeBy > 0
            .nhour = Iif( (.nhour + 12) > 23, .nhour - 12, .nhour + 12 )
          Else
            .nhour = Iif( (.nhour - 12) < 0, .nhour + 12, .nhour - 12 )
          Endif
          lnSelLen = 2
          lnSelSt = 17

        Otherwise
          *** Do nothing - this is not a valid selection

      Endcase

      *** If we get to here, just update the value
      .Refresh()

      *** Restore the Highlight
      .txtdtime.SetFocus()
      .txtdtime.SelStart = lnSelSt
      .txtdtime.SelLength = lnSelLen

    Endwith
    Return
  Endproc


  *-- Check that a Day is valid
  Procedure ValidDay
    Lparameters tnDayNum
    Local lnRetVal
    With This
      Do Case
        Case Inlist( .nmonth, 4, 6, 9, 11 )
          *** This is a 30 day month
          lnRetVal = Icase( tnDayNum < 1, 30, tnDayNum > 30, 1, tnDayNum )

        Case Not .nmonth = 2
          *** Must be a 31 Day Month
          lnRetVal = Icase( tnDayNum < 1, 31, tnDayNum > 31, 1, tnDayNum )

        Otherwise
          *** Must be February!
          If Mod( .nyear, 4 ) = 0
            *** This is probably a leap year
            *** Yes, I know that Century and Millenia are exceptions but who cares?)
            lnRetVal = Icase( tnDayNum < 1, 29, tnDayNum > 29, 1, tnDayNum )
          Else
            *** Not a leap year so only 28 days for sure
            lnRetVal = Icase( tnDayNum < 1, 28, tnDayNum > 28, 1, tnDayNum )
          Endif

      Endcase
    Endwith
    Return lnRetVal

  Endproc


  Procedure tdtime_assign
    Lparameters ttDTime
    With This
      If Vartype( ttDTime ) = "T"
        .nyear = Year( ttDTime )
        .nmonth = Month( ttDTime )
        .nday = Day( ttDTime )
        .nhour = Hour( ttDTime )
        .nmin = Minute( ttDTime )
        .tdtime = ttDTime
      Else
        *** Ignore it
      Endif
    Endwith
  Endproc


  *-- Handle Left/Right cursor key navigation within the control
  Procedure Navigate
    Lparameters tnMove
    Local lnPos

    *** Where is the cursor now?
    lnPos = This.txtdtime.SelStart
    If tnMove = 4
      *** Navigate to the right
      Do Case
        Case Between( lnPos, 0, 1 )
          lnGoTo = 3
          lnLite = 2
        Case Between( lnPos, 3, 4 )
          lnGoTo = 6
          lnLite = 4
        Case Between( lnPos, 6, 9 )
          lnGoTo = 11
          lnLite = 2
        Case Between( lnPos, 11, 12 )
          lnGoTo = 14
          lnLite = 2
        Case Between( lnPos, 14, 15 )
          lnGoTo = 17
          lnLite = 2
        Otherwise
          *** Don't move
          Return
      Endcase
    Else
      *** Navigate to the left
      Do Case
        Case Between( lnPos, 17, 18 )
          lnGoTo = 14
          lnLite = 2
        Case Between( lnPos, 14, 15 )
          lnGoTo = 11
          lnLite = 2
        Case Between( lnPos, 11, 12 )
          lnGoTo = 6
          lnLite = 4
        Case Between( lnPos, 6, 9 )
          lnGoTo = 3
          lnLite = 2
        Case Between( lnPos, 3, 4 )
          lnGoTo = 0
          lnLite = 2
        Otherwise
          *** Don't move
          Return
      Endcase
    Endif
    With This
      .Refresh()
      .nstartpos = lnGoTo
      .nhighlit = lnLite
      .txtdtime.SelStart = lnGoTo
      .txtdtime.SelLength = lnLite
    Endwith
    Return
  Endproc


  Procedure GotFocus
    DoDefault()
    This.txtdtime.SetFocus()
  Endproc


  Procedure Refresh
    *** Update the value and display it
    If Not Empty( DoDefault())
      This.updatevalue()
    Endif
  Endproc


  Procedure spndtime.DownClick
    This.Parent.changeval( -1 )
  Endproc


  Procedure spndtime.UpClick
    This.Parent.changeval( 1 )
  Endproc


  Procedure txtdtime.KeyPress
    Lparameters nKeyCode, nShiftAltCtrl
    *** Allow the user to navigate within control with cursor keys
    If Inlist( nKeyCode, 4, 5, 19, 24 )
      *** Where is the cursor now?
      lnPos = This.SelStart
      If nKeyCode = 5
        *** Up Arrow
        This.Parent.changeval( 1 )
      Else
        If nKeyCode = 24
          *** Down Arrow
          This.Parent.changeval( -1 )
        Else
          If nKeyCode = 4
            *** Right Arrow navigation
            Do Case
              Case Between( lnPos, 0, 1 )
                lnGoTo = 3
                lnLite = 2
              Case Between( lnPos, 3, 4 )
                lnGoTo = 6
                lnLite = 4
              Case Between( lnPos, 6, 9 )
                lnGoTo = 11
                lnLite = 2
              Case Between( lnPos, 11, 12 )
                lnGoTo = 14
                lnLite = 2
              Case Between( lnPos, 14, 15 )
                lnGoTo = 17
                lnLite = 2
              Otherwise
                *** Don't move
                Return
            Endcase
          Else
            *** Navigate to the left
            Do Case
              Case Between( lnPos, 17, 18 )
                lnGoTo = 14
                lnLite = 2
              Case Between( lnPos, 14, 15 )
                lnGoTo = 11
                lnLite = 2
              Case Between( lnPos, 11, 12 )
                lnGoTo = 6
                lnLite = 4
              Case Between( lnPos, 6, 9 )
                lnGoTo = 3
                lnLite = 2
              Case Between( lnPos, 3, 4 )
                lnGoTo = 0
                lnLite = 2
              Otherwise
                *** Don't move
                Return
            Endcase
          Endif
          This.Parent.nstartpos = lnGoTo
          This.Parent.nhighlit = lnLite
          This.SelStart = lnGoTo
          This.SelLength = lnLite
        Endif
      Endif
      *** And swallow the keystroke
      Nodefault
    Endif
  Endproc


  Procedure txtdtime.GotFocus
    With This
      DoDefault()
      .SelStart = This.Parent.nstartpos
      .SelLength = This.Parent.nhighlit
    Endwith
  Endproc


  Procedure txtdtime.LostFocus
    With This.Parent
      .nstartpos = This.SelStart
      .nhighlit = This.SelLength
    Endwith
  Endproc


Enddefine

Répondre

Fil

Voir

Click here to load this message in the networking platform