SQL Insert Date

[Johnny Harris]

Thanks for the help Joseph,

I changed my array from type string to object and it seems to be working now. I’m not sure if that is the best approach to parameters with .net or not.
It may come back to bite me.

Thanks for all the help. I was able to set the date format and separator as you have it below with .net, but it didn’t help.
It’s handy to know that those database properties can be set in code.

Thanks for all the info. I’m a frequent Xojo user also.

Johnny

From: Joseph Morgan 
Sent: Saturday, October 12, 2013 6:10 PM
To: Valentina Developers 
Subject: Re: SQL Insert Date

I should add that there is another way to open Vdatabase in Xojo  

the method to setup the db for the second way to access the db looks like this:

  VDatabase.DateFormat = EVDateFormat.kMDY
  mVDatabase.DateSep = "-"
  mVDatabase.TimeSep = ":"
 
Joseph


On Oct 12, 2013, at 6:08 PM, Joseph Morgan <joseph at checkos.com> wrote:


  After looking at my set-up I set up the database right after opening it (not necessarily after connecting to the server which happens earlier). Sorry I don't know how exactly the opening of the db happens in .net

  I have a variable "mVdatabase" after opening the next line is my setup method "SetupVDB" 

  mVDatabase.Open( f )
  SetupVDB//we can do any initial setups on the database before we use it


  the setup method has the following lines of code:

  dim setupstring as String = "SET PROPERTY DateTimeFormat,DateSeparator,TimeSeparator OF DATABASE  TO 'kYMD','-',':';"
  mVRBdatabase.SQLExecute(setupstring)
  mVRBdatabase.Commit


  if you search for "kYMD" on the wiki you should find the other constants that are for other formats.

  Any time I have a field that uses a date in XOJO, I submit it as a string so it's likely to work similarly in .net

  I hope this helps you!
  Joseph


  On Oct 12, 2013, at 5:39 PM, "Johnny Harris" <johnny at southshore.com> wrote:


    Hi Joseph,

    I found in the docs:

    SET PROPERTY DateTimeFormat and Set Property DateSeparator

    I tried executing both queries just after connecting and still get the same results.

    Is that what you were referring to? If not maybe an example of your setup string would help.

    It seems to me that the date value I’m trying to insert using parameters is being converted to a string type when I add
    it to the parameters array, which is a string array. I’m guessing since the database is receiving it as a string it thinks it is invalid.

    I may be way off base also.

    Thanks For Helping.

    Johnny

    From: Joseph Morgan 
    Sent: Saturday, October 12, 2013 5:13 PM
    To: Valentina Developers 
    Subject: Re: SQL Insert Date

    It's been my experience that each time you open/connect to vserver, you have to set up date format. I send an SQL execute right after connect that tells the server to use the correct date format. Let me know if you need me to send you the setup string that I use. 

    Joseph 

    On Oct 12, 2013, at 5:08 PM, "Johnny Harris" <johnny at southshore.com> wrote:


      Update...

      I just tried the same query without parameter binding and it worked OK.

      So I guess my question now is how to insert into a date field using parameter binding?

      Regards,
      Johnny Harris

      From: Johnny Harris 
      Sent: Saturday, October 12, 2013 5:05 PM
      To: Valentina Developers 
      Subject: SQL Insert Date

      Hello everyone,

      I’m trying to insert a record and one of the fields is a date field.

      I get the following error message:

      Date Value “10/12/2013” is invalid. Illegal symbol at “-1” position."

      Would anyone happen to have any pointers for inserting dates?
      I’m guessing the field is in the wrong format but the format for
      the database is set to “MMDDYYYY” and the separator is “/”.

      I’m using .net and parameter binding.

      Regards,
      Johnny Harris

--------------------------------------------------------------------------
      _______________________________________________
      Valentina mailing list
      Valentina at lists.macserve.net
      http://lists.macserve.net/mailman/listinfo/valentina

      _______________________________________________
      Valentina mailing list
      Valentina at lists.macserve.net
      http://lists.macserve.net/mailman/listinfo/valentina



----------------------------------------------------------------------------
    _______________________________________________
    Valentina mailing list
    Valentina at lists.macserve.net
    http://lists.macserve.net/mailman/listinfo/valentina

    _______________________________________________
    Valentina mailing list
    Valentina at lists.macserve.net
    http://lists.macserve.net/mailman/listinfo/valentina


  _______________________________________________
  Valentina mailing list
  Valentina at lists.macserve.net
  http://lists.macserve.net/mailman/listinfo/valentina




--------------------------------------------------------------------------------
_______________________________________________
Valentina mailing list
Valentina at lists.macserve.net
http://lists.macserve.net/mailman/listinfo/valentina
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macserve.net/pipermail/valentina/attachments/20131012/c015f843/attachment-0001.html>