date format

[bsouthuk]

HI

Wonder if someone can help.

I have a datagrid in my app that a user can upload data from a CSV into - the data then saves and stores on my mySQL database. One of the collumns is a date column and the date needs to be in the correct mySQL date format which is 2010-02-24.

I need some control so that if a user uplaods the CSV but the date is not in the correct format for an error message to appear stating this. Could someone help with the code that I would need for this? I have so far:

Code: Select all

set the itemdel to tab
   put line 1 of the dgText of group "MobileNumbersDataGrid" into theDataA
   put item 5 of theDataA into tDate

I then need the code that basically says 'If tDate is not in format 2010-02-24 then answer "WRONG FORMAT" and exit to top

Your help would be most appreciated.

Thanks

Daniel

[BvG]

I suggest to test data before it's put into the datagrid.

Besides that, you probably want to set the itemdelimiter to "-" and check if there's three items. Useful in this case is probably also to check if each item is an integer (Note: for LC "0xAB" is an integer, as it accepts hexadecimal numbers). Alternatively you could check for each char of the items to be among the chars of "0123456789". This method will be slow tho.

For example:

Code: Select all

function isSQLDate theDate
  set the itemdel to "-"
  if the number of items in theDate <> 3 then
    return false
  end if
  repeat for each item theItem in theData
    if theItem is not an integer then
      return false
    end if
  end repeat
  return true
end isSQLDate

[bsouthuk]

thats great, works perfectly thank you!

[dunbarx]

It is possible to fool the validation. For example, "2004-333-999" would pass muster.

I think you should let LC natively do the validation, rather than a brute force method. Depending on where in the world you are, you can transform the data into something that LC itself will validate.

in the US, I would

Code: Select all

on mouseup
   put "2010-02-24" into temp
   set the itemDel to "-"
   put item 2 of temp & "/" & item 3 of temp & "/" & item 1 of temp into dateToTest
   if dateToTest is a date then answer temp && "is a valid date"
   else answer temp &&"is no good"
end mouseup

Craig Newman

[SparkOut]

Still a problem with pre 1970 dates though, especially on Windows.