SQL Programming
InsertDataFromCursor ignoring Date fields
Gravatar is a globally recognized avatar based on your email address. InsertDataFromCursor ignoring Date fields
  Bob
  All
  Jul 28, 2019 @ 02:11pm

This code seems to totally ignore date fields when trying to post to an Azure SQL data table It gives an error operand type clash int is incompatible with date [1526:206] Is there something I need to do differently with date fields?

    Create cursor ClientMaster(ID int,ClientName C(60),Address1 C(30),Address2 C(30),City C(25),State C(2),Postal C(10),Country C(30),;
        Phone C(12),Fax C(12),www C(60),SerialNum C(5),Activation C(5),Network C(10),Password C(8),Expire D,MaintFee N(7,2),;
        Billed D,MaintPaid D,LastShip D,RelDate D,Purchased D,Price N(7,2),Options C(10))
    SELECT clientmaster
    APPEND BLANK
    replace ClientName WITH "NewClient",Address1 WITH "123 Someplace",Address2 WITH "Apt 3",City WITH "Brentwood",State WITH "TN",Postal WITH "37027",Country WITH "USA",;
    Phone WITH "6151231234",Fax WITH "6151234567",www WITH "ABC",SerialNum WITH "99997",Activation WITH "IIIIG",Network WITH "NONE",Password WITH "Fubar",Expire with DATE(),MaintFee WITH 699.00,;
    Billed WITH DATE(),MaintPaid WITH DATE(),LastShip WITH DATE(),RelDate WITH DATE(),Purchased WITH DATE(),Price WITH 1234.00,Options WITH "A"
    COPY TO bobtest
    GO top
    
    loSql.Connect("Server=tcp:ccstest.database.windows.net,1433;Database=CCS_Clients;Uid=bloree;Pwd=#########;Encrypt=yes;TrustServerCertificate=yes;Connection Timeout=30;")

    loSQL.InsertDataFromCursor("ClientMaster")
    
    Messagebox("Error: " + loSql.cErrorMsg)
    MESSAGEBOX("Finished")
Gravatar is a globally recognized avatar based on your email address. re: InsertDataFromCursor ignoring Date fields
  Michael Hogan (Ideate Hosting)
  Bob
  Jul 29, 2019 @ 09:11pm

I haven't played with Azure SQL - but I do know that there's no such thing as a DATE() field in MSSQL. Try converting it to DATETIME(). You can probably use Time DTOT(DATE())

When you CREATE CURSOR - that would be a field type of T

Gravatar is a globally recognized avatar based on your email address. re: InsertDataFromCursor ignoring Date fields
  Rick Strahl
  Bob
  Jul 30, 2019 @ 05:07am

Bob,

This has to do with the sql driver used. Different drivers support different date formats. I can’t remember which is which newer or older but generally you need to use datetime values instead of date as Michael points out.

Rick

Gravatar is a globally recognized avatar based on your email address. re: InsertDataFromCursor ignoring Date fields
  Rick Strahl
  Bob
  Jul 30, 2019 @ 05:07am

Bob,

This has to do with the sql driver used. Different drivers support different date formats. I can’t remember which is which newer or older but generally you need to use datetime values instead of date as Michael points out.

Rick

Gravatar is a globally recognized avatar based on your email address. re: InsertDataFromCursor ignoring Date fields
  Bob
  Rick Strahl
  Jul 30, 2019 @ 10:46am

I modified the sql table and the VFP cursor to be datetime fields. The command still didn't work. I took all the datetime fields out of the cursor and the the update worked fine, except of course all of the date fields in the new SQL record were null. Could it have anything to do with the SQL datetime fields looking like this 2019-12-31 00:00:00.000 ?? I will play with it some more and report back if I get something to work.

Gravatar is a globally recognized avatar based on your email address. re: InsertDataFromCursor ignoring Date fields
  Rick Strahl
  Bob
  Jul 30, 2019 @ 10:58am

I don’t have access to my computer right now as I’m traveling but check the foxpro wiki - I think there’s a bunch of info on sql and odbc issues with drivers and data types you have to watch out for.

Rick

Gravatar is a globally recognized avatar based on your email address. re: InsertDataFromCursor ignoring Date fields
  Jeff L
  Rick Strahl
  Jul 30, 2019 @ 03:34pm

Here's a Native 10 connection string to Azure SQL

Driver={SQL Server Native Client 10.0}; Server=tcp:[serverName].database.windows.net;Database=myDataBase; Uid=[LoginForDb]@[serverName];Pwd=myPassword;Encrypt=yes;

If you use this then what happens?

© 1996-2024