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")
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
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
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
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.
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
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?