1 Reply Latest reply on Feb 5, 2008 10:32 AM by Chunick

    Ado Xtra/date

    Xposure Interactive
      I'm having problems when trying to send a date to an access database
      rec.fields["lastDate"] = the short date
      The database field is set as a date with the format set to short date. I get the following error message:

      80040e07
      Data type mismatch in criteria expression.

      If I explicitly cast the date to a string and send it to a text field in the database it works fine. Here is my code:

      on startMovie
      member("status").text = ""
      member("name1").text = ""
      member("name2").text = ""
      member("day").text = ""
      member("month").text = ""
      member("year").text = ""
      member("company").text = ""
      member("yesCheck").hilite = FALSE
      member("noCheck").hilite = FALSE
      gLocation = the moviePath & "records\arteniusTraining.mdb"
      gConnectionString= "Provider=Microsoft.Jet.OLEDB.4.0;Mode=Read|Write;Data Source=" & gLocation & "; Jet OLEDB:Database Password=artisticgenius"
      gNme = ""
      gDoB = ""
      gCpny = ""
      gJob = ""
      gFirstDate = ""
      gLastDate = ""
      gContractor = ""
      gOffice = ""
      gPTA = ""
      gMaterialHandling = ""
      gM5 = ""
      gNewbie = 0
      end

      on record
      rec=CreateObject(xtra "ADOxtra",#Recordset)
      if objectP(rec) then
      else
      alert "Error:"&& rec.lastError
      end if
      getThis = "SELECT Id, name, dob, company, job, firstDate, lastDate, contractor, office, PTA, materialHandling, M5 FROM induction WHERE name='" & gNme & "' AND dob='" &gDoB& "'"
      rec.Open(getThis,gConnectionString,rec.adOpenKeyset,rec.adLockPessimistic)
      if rec.succeeded then
      else
      alert "Error:" && rec.lastError
      end if
      if rec.fields["name"] = "" then
      newUser
      else
      gNme = rec.fields["name"]
      gDoB = rec.fields["dob"]
      gFirstDate = rec.fields["firstDate"]
      gContractor = rec.fields["contractor"]
      gOffice = rec.fields["office"]
      gPTA = rec.fields["PTA"]
      gMaterialHandling = rec.fields["materialHandling"]
      gM5 = rec.fields["M5"]
      end if
      end

      on newUser
      gNewbie = 1
      gFirstDate = the short date
      cnn=CreateObject(xtra"ADOxtra",#Connection)
      if objectP(cnn) then
      nothing
      else
      alert "Error:" && cnn.Errors
      end if
      cnn.Open(gConnectionString)
      if cnn.succeeded then
      nothing
      else
      alert "Error:" && cnn.Errors
      end if
      cnn.Execute("INSERT INTO induction (name, dob, company, job, firstDate, lastDate, contractor, office, PTA, materialHandling, M5) VALUES ('"&gNme&"', '"&gDoB&"', '"&gCpny&"', '"&gJob&"', '"&gFirstDate&"', '"&gLastDate&"', '"&gContractor&"', '"&gOffice&"', '"&gPTA&"', '"&gMaterialHandling&"', '"&gM5&"')")
      if cnn.succeeded then
      cnn.Close()
      record
      else
      alert cnn.Errors
      cnn.Close()
      end if
      end

      on stopMovie
      rec.fields["name"] = gNme
      rec.fields["dob"] = gDoB
      rec.fields["company"] = gCpny
      rec.fields["lastDate"] = the short date
      rec.fields["contractor"] = gContractor
      rec.fields["office"] = gOffice
      rec.fields["PTA"] = gPTA
      rec.fields["materialHandling"] = gMaterialHandling
      rec.fields["M5"] = gM5
      rec.Update()
      end
        • 1. Ado Xtra/date
          Chunick Level 3
          Be careful because your date may still not be formatted correctly... if this is being deployed on different pc's to your own then you will have no control over how the short date is formatted. That formatting is determined by the OS. To force your own formatting use the systemDate instead, since it returns a date object based off the system's information:

          dateObj = the systemDate
          year = dateObj.year
          month = dateObj.month
          day = dateObj.day
          sec = dateObj.seconds
          hour = sec/3600
          minute = (sec mod 3600)/60
          second = sec mod 60

          Then you can combine all this information into a formatted date string that your database will accept, remembering to use the # or % character before and after the string... it may be the % character instead of #... cannot remember which one.. give both a try and one of them will work.