Hi keisha1, hi ghiehotomares,
it seems hard for you to understand English, I also don't know if you're actually both work on the same team, but your code is using the same table and field names, so that's likely.
Let's start form scratch about what you need:
1. It all start with an MSSQL Server that must be present and accessible to you. It could also be other databases, but your use of Driver=SQL Server points to MS SQL Server.
It depends on the version of the server, which drivers are available and here is an overview chart for the currently actively maintained family of "ODBC Driver X for SQL Server":
The next oldest family of ODBC drivers for MS SQL Server are "Native Client" drivers and people used drivers of this family with VFP.
Your driver "SQL Server" is the oldest one you could use.
There have been gotchas for certain field types in VFP, for example Varchar(MAX) not being fetched into a VFP Memo field type but into C(0) fields, which theoretically are impossible, you can't actually create a C(0) field with CREATE TABLE or CREATE CURSOR in VFP, but several ODBC drivers managed to do that or managed it to let VFP think it needs to declare such fields. It's nonsense it's a 0 byte field which could only ever contain an empty string or be NULL.
So, overall there are some considerations about which driver to use, once you get a connection and can make your first SQLEXECs to bring over results, that's already fine, but still not a prove it's the right driver for you. The problems drivers have is with correct mapping of data types only, so far. I haven't seen drivers fail to execute INSERT, UPDATE or DELETE, so there's no threat of TABLEUPDATE() to not work generally.
Mike Lewis surely was and is on the right track that you had order of things to do wrong, but as he said, besides pointing out what order things must be done with, he said:
Mike Lewis said:
You [highlight #FCE94F]seem[/highlight] to be setting the correct properties
and
Mike Lewis said:
[highlight #FCE94F]At first glance[/highlight], it looks like you've got all the correct settings, but in the wrong order.
I now see that the wrong Updatenamelist already was posted in the original question by ghiehotamares:
Code:
=CURSORSETPROP("UpdateNameList", "optname optname","value value","major_version major_version","minor_version","revision revision","install_failures install_failures","Id Id", "tblMScursor")
This already used all the separated single field strings, from "optname optname" up to "Id Id", he already specified the updatenmelist as a list of single field name pairs. That's wrong. It's only one cursor property, it's a list, but it's one property, sou specify a list of naim pairs within one string, not 7 strings and not 9 overall arguments.
It may be your only remaining error, but to let you see the full picture and finally perhaps get this going you have to:
1. Have SET MULTILOCKS ON. ghiehotamares has done this as the 5th step. That's just okay, just barely okay as the last chance to do this before putting a cursor into a buffered state. I'll recommend to do this one at the start, as it's a setting you need for any work with buffers, you don't need to redo it, once multilocks are on they are on for anything you do after that.
2. Connect to MSSQL (or be connected already with a valid SQL connection handle, ASQLhandles() would create an array with at least one element that contains that handle and you might have it stored into a variable or property. You can use SQLStringConnect or SQLConnect for this, depending on what you have to connect, a connection string or a DSN name.
3. Quarey data into a cursor. That needs an SQLEXEC of a SELECT SQL query. You can only make a cursor updatable towards a remote backend, when it came from the remote backend.
4. Set up that query result cursor to buffer changes. That's not done just with SET MULTILOCKS ON, that's done bu making all your CURSOSETPROPS, and the props you need to do and their best order from the most important thing to set to the last details are:
4.1 SENDUPDATES, start with "telling" VFP you want this cursor to be able to send buffered changes back to the SQL Server, details follow, but this is the major setting determining whether to enable sending updates or not, this should come first. As a human you might think this is the least important one, as setting up all things that follow, from buffering the name lists, already implicitly tell VFP you want to send updates. Well, VFP would not imply sendupdates to be .T., you explicitly set it. And no criticism here, you have that CURSORSETPROP, you just did it last and did give it the least importance. It actually feels like an annoyance to need to set this, but set it first.
4.2 BUFFERING, you do use buffermode 5, that's optimistic table buffering and okay, but notice unlike the SET MULTILOCKS ON this is something you need to repeatedly do for every SQLEXEC result cursor. It's the next most general decision to make, all further details become a mute point, if you don't first specify how to buffer.
4.3 TABLES. Yes, again now the topmost and most general property that you can tell VFP is the best thing to tell it next, which table to update. And it's not by error a plural, in theory you can give VFP "directions" how to update multiple tables that you queried with joins into one VFP cursor, the usual case is the one table.
Also notice, how it actually scips one bigger scope than which table, one step up in hierarchy is which database, that's actually also a cursor property, it's already set as that data came from an SQLEXEC and while VFP does not automatically read in all available fields, their meaning as primary or foreign key or just any other normal field of the tables you query, it remembers which database of the connection this cursor came from.
4.4 KEYFIELDLIST. The next most important feature of a table is its keyfield (or in case of multiple tables their keyfields, therefore KEYYFIELDLIST, usually just one, Id, for example.
4.5 UPDATEFIELDLIST. This is special about SQL PAssthrough, one you have multilocks and buffering set for a cursor, any field of it can change and all the changes get buffered, but SQL PAssthrough will not update any of this, unless it's a field of the UpdateFieldList. Some fields could be readonly of have other constraints not allowing to set or update them, so that's necessary. Also remember, VFP doesn't automatically get this info from the remote database. It will just fetch too much information if you just want to get the SQLEXEC result without using it as a basis for TABLEUPDATEs.
4.5 UPDATENAMELIST. That's where you clearly have an error in your CURSORSETPROPS, it's just one property, although it's a list. Well, the UPDATEFILEDLIST also already was a list and you had that right.
5. you can now act on the cursor. For example BROWSE.
Notice, this is important. While buffering already is activated by MULTILOCKS and BUFFERING, VFP needs to know everything for the later TABLEUPDATE in advance, before changes are buffered.
6. After you did changes, TABLEUPDATE will forward these changes to MS SQL Server, it does so by automatically creating a list of INSERTs, UPDATEs, and DELETEs, for which all the information about key and updatable fields etc is needed.
Take a look into the parameterization of TABLEUPDATE, there's more to it than using the first parameter as .T. for all rows (that have changed).
And take into account what Mike LEwis already said, you can and should determine whether TABLEUPDATE returned .T. for reporting success or .F. for stating there was one or several errors and then look at errors via AERROR.
You can still easily have errors in your property values, we can't see your database and whether your specificated lists are okay.
I think Mike already gave good advice about the order in which to do things and you didn't listen to him or didn't understand. Some things can be done in slightly other orders, but only start editing data once you have specified anything VFP needs to know about it as all those properties of the cursor. Also don'T forget you can't CREATE CURSOR and apply properties to make changes in it go to a database. So the SQLEXEC of a SELECT SQL query in ghiehotamares also is a non-optional step, you must get your data you want to make into an updatable cursor in a cursor that was populated from that remote database in the first place.
If you want to insert new data into a remote database only, you can and must still first query something, you can chose to not query any data itself by using WHERE 0=1, for example, so the result cursor is empty and your changes are only INSERTS, still also this empty cursor has to come from the remote backend.
You can of course also create an empty cursor and finally do INSERTS based on the cursor data, but you can't use TABLEUPDATE in that case, just direct usage of SQLEXEC of INSERTs. That should perhaps also be said to make it absolutely clear you can't just make any cursor a source of TABLEUPDATEs towards a remote backend.
Chriss
