[Gambas-user] datefields revisited

[Steven Lobbezoo]

Hi Benoit,

I read the database, and fill fields like this :

1. Part of my form open :
---------------------------------------------------------------------------------------------------
   rsV = db.Exec("Select Ventes.*, agents.agent, agents1.agent as agent1, 
Prix_net, Comm_TTC, homes.type as Htype,  " & 
    "concat(Clients.Nom, ', ', Clients.Prenom) as NomV, Clients.No as NoV, 
Mandats.Date as MDate, Mandats.Fin as MFin,  " &
    "concat(homes.address, '<br>', homes.zip, ' ', homes.city) as Haddr, 
Mandats.Servitudes,  " &
    "concat(ifnull(Clients.Telephone, ''), ' - ', 
ifnull(Clients.Portable, ' '), ' - ', ifnull(Clients.Travail, ' ')) as 
TelephoneV, " &
    "concat(Clients.Adresse, '<br>', Clients.Code_Postal, ' ', 
Clients.Ville, '<br>', Clients.Pays) as AdresseV, " &
    "Clients.EMail as emailV, " &
    "concat(NotaireV.Nom, ' (', NotaireV.Ville, ')') as NomNotV, NotaireV.No 
as NoVN, " &
    "concat(ifnull(NotaireV.Telephone, ''), ' - ', 
ifnull(NotaireV.Portable, ' '), ' - ', ifnull(NotaireV.Travail, ' ')) as 
TelNotV, " &
    "NotaireV.EMail as EmailNotV, " &
    "concat(ClientsA.Nom, ', ', ClientsA.Prenom) as NomA, " &
    "concat(ifnull(ClientsA.Telephone, ''), ' - ', 
ifnull(ClientsA.Portable, ' '), ' - ', ifnull(ClientsA.Travail, ' ')) as 
TelephoneA, " &
    "concat(ClientsA.Adresse, '<br>', ClientsA.Code_Postal, ' ', 
ClientsA.Ville, '<br>', ClientsA.Pays) as AdresseA, " &
    "ClientsA.EMail as emailA, ClientsA.No as NoA, " &
    "concat(NotaireA.Nom, ' (', NotaireA.Ville, ')') as NomNotA, NotaireA.No 
as NoAN, " &
    "concat(ifnull(NotaireA.Telephone, ''), ' - ', 
ifnull(NotaireA.Portable, ' '), ' - ', ifnull(NotaireA.Travail, ' ')) as 
TelNotA, " &
    "NotaireA.EMail as EmailNotA " &
    "from Ventes, agents, Mandats, agents as agents1, Clients, Clients as 
NotaireV, Clients as ClientsA, " &
    "Clients as NotaireA, homes " & 
    "where agents.id = Ventes.Commercial AND Mandats.Code = 
Ventes.Code_Mandat " &
    "AND Clients.No = Mandats.Proprio AND NotaireV.No = Ventes.Notaire_P " &
    "AND ClientsA.No = Ventes.Acheteur AND NotaireA.No = Ventes.Notaire_A " & 
    "AND homes.title = Ventes.Code_Mandat " & 
    "AND agents1.id = Mandats.Commercial AND Ventes.Code = " & Str(ME.Tag))
   IF rsV.available THEN
    try_fill(rsV)
----------------------------------------------------------------------------------------------
And the try_fill routine :
---------------------------------------------------------------------------------------------

PRIVATE SUB try_fill(rsV AS Result)
' try to fill the fields

DIM tmp AS String
DIM i AS Integer
DIM ctrl AS Control
DIM hResultField AS ResultField

      FOR EACH hResultField IN rsV.Fields
      tmp = hResultField.Name
      i = InStr(tmp, ".")
      IF i > 0 THEN 
        tmp = Mid(tmp, i + 1)
      END IF
      TRY ctrl = ME.Controls[tmp]
      IF ctrl THEN
        IF ctrl.Tag = "cur" THEN 
          Object.SetProperty(ctrl, "Tag", "")
          TRY Object.SetProperty(ctrl, "Text", 
Format$(rsV[ctrl.Name], "($,#)"))
          TRY Object.SetProperty(ctrl, "Tag", 
Format$(rsV[ctrl.Name], "($,#)"))
        ELSE
          TRY Object.SetProperty(ctrl, "Text", Str(rsV[ctrl.Name]))
          TRY Object.SetProperty(ctrl, "Tag", Str(rsV[ctrl.Name]))
        END IF
      END IF
    NEXT     
END
-------------------------------------------------------------------------------------------------------
Here all is filled correctly

Then, to write it back after changement by the user took place :
-------------------------------------------------------------------------------------------------------
  ' si tout est bien on fait comme suit :
    tmp = Str(ME.Tag)
    IF tmp = "" THEN tmp = 1 ' juste pour avoir les noms du champ
    rsA = db.Exec("select * from Ventes where Code = " & tmp)
    IF rsA.available THEN
      FOR EACH hResultField IN rsA.Fields
        tmp = hResultField.Name
        i = InStr(tmp, ".")
        IF i > 0 THEN 
          tmp = Mid(tmp, i + 1)
        END IF
        FOR EACH ctrl IN ME.Controls
          IF ctrl.Name = tmp THEN
            IF ctrl.Tag <> ctrl.Text THEN 
              ' ok, we've changed something, let's write it back
              IF SqlPrt THEN SqlPrt = SqlPrt & ","
              SqlPrt = SqlPrt & " " & Object.GetProperty(ctrl, "Name") & " = 
\"" & Object.GetProperty(ctrl, "Text") & "\" "
            END IF
            BREAK
          END IF
        NEXT
      NEXT
    END IF
    ' now we write something
    ' check that we have the minimum first
    i = 0
    IF Code_Mandat.Text = "" THEN 
      i = message.Error("Le code mandat est 
obligatoire", "Annuler", "Réessayer")
    ELSE IF Date.Text = "" THEN 
      i = message.Error("Le date du compromis est 
obligatoire", "Annuler", "Réessayer")
    ELSE IF AA = "" THEN
      i = message.Error("Le date prévue pour l'acte authentique est 
obligatoire", "Annuler", "Réessayer")
    ELSE IF NomV = "" THEN 
      i = message.Error("Le Propriétaire doir être 
choisi", "Annuler", "Réessayer")
    ELSE IF NomA = "" THEN 
      i = message.Error("L'acquireur doit être 
choisi", "Annuler", "Réessayer")
    ELSE IF Prix_vendue = "" THEN 
      i = message.Error("Le prix net de vente est 
obligatoire", "Annuler", "Réessayer")
    ELSE IF Comm_Vendue = "" THEN 
      i = message.Error("Il est obligatoire d'entrée notre 
commision", "Annuler", "Réessayer")    
    END IF
    IF i = 1 THEN ' cancel button pressed
      ME.Close
      RETURN
    ELSE IF i = 2 
      RETURN 
    END IF
    IF rsV THEN 'we have changed something on an existing set
      Oper = "UPDATE "
      SqlTrm = " Where Code = " & Str(ME.Tag)
    ELSE ' we migth want to add something      
      Oper = "INSERT INTO "
      SqlTrm = ", Code = (SELECT MAX(Code) FROM Ventes) + 1 " 
    END IF
      ' let's just do this direct and execute an SQL statement   
    SqlStr = Oper & " Ventes SET" & SqlPrt & SqlTrm
    IF SqlPrt THEN
      rsA = db.Exec(SqlStr)
    END IF

-------------------------------------------------------------------------------------------------------

And then the date is false :-(

Steven



Le vendredi 18 mai 2007 22:41, Benoit Minisini a écrit :
> On vendredi 18 mai 2007, Steven Lobbezoo wrote:
> > Hi all,
> >
> > I still have a problem with datefields in an MySQL database.
> >
> > The field in the database is of type 'date'
> > I only use db.exec to read and update etc.
> > I handle the field in a normal text box.
> >
> > When I read a date from the database (written with an other program),
> > it's ok, I get a textbox with dd/mm/yy in it, just what i want. ;-)
> >
> > But when i update the field, f.i. I change 17/05/07 into 18/05/07 and
> > write it back into the database, i get written dd : 07, mm: 05 and yy :
> > 18 The database then reads 7 may 2018.
> >
> > There's some logic here that escapes me. If I read it simply -direct -
> > why cannot i write it back the same way ?
> > Or do I convert the textbox.Text into a date value with a rather complex
> > expression each time ?
> >
> > Steven
>
> How do you read the date? How do you write it?