Updating Underlying SQL Table from a Grid and a Form

[stanlyn]

Hi,

Just wondering if there is a magic bullet for writing back/updating the SQL table from the cursor that was generated from the SQL select statement. The sqlexec() commands run and I get back a cursor that is the datasource for the grid. I make a change to the grid's cursor and now its time to writ it back to the sql table. It could also be coming from textboxes on a form as well.

One way is to use sqlexec() with a sql update statement in the grid's afterrowcolchange event.

A couple years ago, I tried the sql cursor adapters and had limited success. The SQL cursor adapter just seams like a late entry into VFP and still has unresolved issues.

I know how to do the SQL passthru stuff, and that all seams like a lot of code for something as simple [replace field1 with "new"]. Just hoping for a more efficient method (in terms of time and code).

And are there any commands or functions for processing a whole grid of changes all at once, like inside a save button?

If there are no better ways, I will probably create several generic SQL pass-thru classes, one for selects, one for updates and one for deletions and pass the specifics in by parms.

Just wondering what the options are.

Thanks, Stanley

 

[Olaf Doschke]

Let me also just quote what Mike already said:

Both the connection and the string are normally persistent, that is, you only need to define them once. The definitions are stored in a DBC. This means that a DBC is a pre-requisite for using remote views.

and

You can create a temporary DBC (typically stored in the user's Temp folder), then add the connection and the view to it. At the end of the session, you can delete the DBC.

You can, you don't need to. What pays to have anyway is a per user local DBC with all view definitions, because while views are just stored queries executed at their USE, their USE locks the view definition and you avoid ERROR 1709. Mike only suggested you may create and destroy this local DBC with every application start in situations the view definitions are very dynamically depending on what tables exist, eg in a system having tables per year, per customer, etc. the list of tables and thus list of needed views are dynamic, you might have other needs and since the view DBC only is about some definitions of connection and queries, it's more a code repository than the data itself, the data is in the remote backend. You can handle the DBC as your EXE and have it static, but the DBC may get corrupt, your EXE is much less vulnerable, another reason the recreate the DBC, besides you open up information about your code/queries, if you leave a DBC. If you create it on the fly and use it exclusive, the hurdle to get at your queries is higher.

If you want more control about the security aspect don't use remote views, use SPT or CAs.

Bye, Olaf.

 

[Olaf Doschke]

One more thing you rather do SPT style now: All your CURSORSETPROPs can beceom part of the view, you don't need them at all, when you used the visual view designer and make all the necessary settings for getting an updatable view. To get this persistent properties of the view you use DBSETPROP, not CURSORSETPROP.

Bye, Olaf.

 

[stanlyn]

Hi,

Is there any wildcard/shorthand that includes all the fields to these commands?
CURSORSETPROP('KeyFieldList','PK','VwCounty')
CURSORSETPROP('UpdatableFieldList','fc_meetings','VwCounty')
CURSORSETPROP('UpdateNameList','pk county.pk, fc_meetings county.fc_meetings','VwCounty')

Thanks,
Stanley

 

[stanlyn]

Hi Olaf,

What is unclear about the key word NoData?

I see what it does, I do not understand why, because
1. I am not issuing a requery(),
2. I'm not redefining the view,
3. I'm not changing the view's parameterized value,

I define a parameterized view that pulls down 20 records and the cursor is selected,
I issue a "use in select('cursor')" that closes the cursor
Next without doing anything else, I issue "use cursor in 0 NoData" and I get an empty cursor
Next I issue a "use in select('cursor')" that closes the cursor again
And then I issue a "use cursor in 0" and now the cursor is showing the original 20 records.

No requery or definations... I do not understand this behavior... It appears to me that the created view and sql server has a 2way persistant conversation going on at all times, as that is the way I can explain it. Am I close???

Thanks,
Stanley

 

[stanlyn]

One more thing you rather do SPT style now: All your CURSORSETPROPs can beceom part of the view, you don't need them at all, when you used the visual view designer and make all the necessary settings for getting an updatable view. To get this persistent properties of the view you use DBSETPROP, not CURSORSETPROP.

Are you saying I should do this in spt instead of views, or I should use the view designer as it adds all the cursorsetprops?

Please clarify?

Stanley

 

[stanlyn]

And how do we run a named connection listed in the dbc's treeview? Or is it only shown and must be added to another command such as "create sql view" for it to run?

OK, I see the use of the word persistence by both you an Mike and you comments that they are really definitions and not data, so both front & back ends are in this continuous conversation.

Thanks,
Stanley

 

[Olaf Doschke]

1. It's your choice, as Mike said Views are limiting what you can do in comparison with SPT.

2. You don't run a connection, you define a remote view using a connection and so it uses it. You only define and store it. With SPT you just need a conncetion string and you open and close connection for whatever spt query you want to execute, even T-SQL scripts, not only queries. One more advantage of SPT.

Views give you a stored query. CAs go beyond that also giving you some events you never have with neither views nor SPT. SPT allows you to do any T-SQL, not limited to queries. So I combine SPT and CAs, but you said CAs are not for you. Most probably, because you didn't comprehend them, but to you they are broken and incomplete. Maybe now you learned with remote views, you can apply that to CAs again and switch.

I see you in a phase of trying everything out, then you can take all advantage about all the technologies and see for yourself what feels best to you. You have a lot to learn and it's totally understandable you don't get things first time. But you also judge things quite early, even if others say what's their preference. CAs fort example can't be all that bad, you jsut haven't had a first good experience with them, as you approached them wrongly.

For example your understanding about CURSORs, when you asked what and how that happens, if you use them. You have the same wrong understnading about view cursors as table. Whenever you USE sometable you have a workarea with the alias of the table name. It's not the table itself, like a file handle isn't the file, the same goes for USE viewname. You again get a workare with that name. The real thing in regard of a table is the DBF file, the real thing in regard of a view is two stages, in the end it's also a table in the remote backend, but in stage 2 it's the view definition having a name, you can use a view ALIAS something else and thereby also have different workarea names for the same view name. Since most poeple don't name their index other than fields, their view other than the table, their query result cursor (INTO CURSOR) other than the table they query from, etc, they don't understand the different things are not synonyms, they re different things. If you USE a view you don't use a cursor, you execute the view query definition and that results in a cursor in a workare having a name. You can take cursors as synonym for workarea, because the alias name really names the workarea and that's nothing else but the cursor. Even a DBF only is used via a workarea, you never have any direct byte pointer to the file, there always is the workarea level between your VFP code and the file. The only real low level file access ouwld be using FSEEK/FWRITE/FPUTS, etc., but you don't use it, that'll make things overly complicated, wouldn't it. So you even should first understand what a workarea really is. It is a representation of anything containing data. You too soon forget, that it's not the file, not the vierw, not the CA, not the remote table, but it's in memory loaded representation of that in the VFP datasession. It just feels so down to earth for DBFs, because it directly writes down to the DBF, especially not using Buffering, but even with DBFs workareas are an intermediate level on the way to the file, they are not the file. In the same manner the workare named after the view name is not the view. The view is just the definition of its query and a name of that, which is used to name the workarea, too. And if you instanciate a CA the cursor it creates also is merely the VFP side workarea name. All these things are very comparably uable, once you are after the stage of retrieving the data into the workarea, then you can bind that to VFP controls, you can SCAN through it, you can access a current rows, you can SEEK, LOCATE, SKIP, GOTO, you have recnos, etc., etc., in case of DBFs that is due to its file structure, in case of anything else that's due to in memory DBFs or TEMP file DBFs cursors are on the file level, so they all compare to DBFs, as they all are DBFs.

Bye, Olaf.

 

[Mike Lewis]

Are you saying I should do this in spt instead of views, or I should use the view designer as it adds all the cursorsetprops?

That's not what I was saying, and I don't think it's what Olaf was saying.

Your original question was about how to send the updates (to a local cursor) back to the remote database. We discussed what you need to do to make that happen. But then I suggested the possibility of using remote views as an alternative approach. This was an easier option in this particular case, especially given that you want to bind the data to a grid.

You still have the choice between remote views and SPT. The point is that remote views are generally easier to work with, but are limited in what you can do with them. For example, you can't use remote views to create new tables (on the server), to import bulk data, or to run stored procedures (all of which are possible with SPT). As far as I can see, those limitations aren't relevant in your case.

Whether or not to use the view designer is a separate question. As we have explained, the definitions of the remote views and the connections live in a DBC. If those definitions are static, you can create them at design time, and distribute them (that is, distribute the DBC) with your executables. The reason I showed you how to create the definitions in code was because I thought you wanted to do that on the fly - after the application was installed.

I also mentioned the possibility of creating a temporary DBC at the start of a session, and deleting it at the end of the session. That would be relevant if the view definitions were arbitrary, and dependent on factors that change each time the application is run (I mentioned the example of my generic import routine). But you also have the option of programmatically creating the DBC, connection and view definitions - and of doing that once only, and storing them on the user's system.

Regarding the CURSORSETPROPs. The point there was that the view designer gives you a shortcut for generating those function calls. You can use that even if you don't intend to use the view designer for anything else. The idea is that you create the view with the designer, go to the View SQL menu option, copy the relevant bits of code from there, then throw away the view designer. You can then use that code at the point that you programmatically create the view. But don't get hung up on this; it's a shortcut, not a strategy.

I know this is all a lot to absorb if you haven't done it before, but I'm sure it will all fall into place.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Once more, view properties are rather once set with DBSETPROP, CURSORSETPROP only sets workarea properties. You need to redo CURSORSETPROPS everytime, you only need to do DBSETPROPs once, like you also only need to CREATE a view once. The CREATE view command does not include all necessary clauses to set the query, updatability, whertype, updatablefields, keyfields etc. all in one command, but if you use the view desinger all those things are set and stored in the DBC. Using a view you design there or you CREATE and then also do all the necessary additional DBSETPROPs will just need to be used to work. That and the ability to REQUERY them easily makes them more grid compatible than SPT. And while am at that differences/advantages, CAs also allow you to do REQUERY and are similarly easy to use with grids, they are harder to handle, if you are not used to OOP, that's mainly the difference, and not having a visual view/sql designer as the view designer is.

Bye, Olaf.

 

[stanlyn]

OK guys, I really appreciate you breaking it all down. I am keeping up with the conversation and pretty much understand what is being said.

1. Lets talk SPT some more about updating bound grid data back to the remote table using spt. Is it as simple of having code in the grid's beforerowcolchange event that uses the key from the current record and do a sql update field1 to new value where remotekey equals localkey?

2. Adding a new record would be issuing an "append blank" or "sql insert into the local readwrite cursor, then if sql server is responsible for generating the identity key, I should insert into the remote table a placeholder record with enough data to satisify any constraints, then issue another sql command to get the last identity number, and finally issue a sql command and update the placeholder record with real data that was added to the cursor.

3. Deleting a record would be "delete from table where remotekey equals localkey"?

4. What is the proper order of sequence to display grid data while keeping custom grid headers and other grid properties from changing and to make sure the grid does not show blank when the recordsource and type changes?

Thanks,
Stanley

 

[stanlyn]

Hi Olaf,

but you said CAs are not for you. Most probably, because you didn't comprehend them, but to you they are broken and incomplete. Maybe now you learned with remote views, you can apply that to CAs again and switch.

Yes, you are exactly right... Hopefully you are right in that I'm learning from your teaching on how all this stuff works! Thank you!

Back a few years ago when I was playing with these, I was able to make spt work, but it was a lot of work. When I was playing with remote views I was in the dbf mindset where I was always accessing the entire table all the time and remote views were too slow because of that. Therefore, I continued using dbfs and times have changed...

Back then I also looked at the CAs and trying to get that to work was like trying to understand the code that some VFP wizard creates. And for that reason, I stay away from wizard generated code.

Too many choices is also not helping either...

Thanks,
Stanley

 

[stanlyn]

Hi Mike,

The idea is that you create the view with the designer, go to the View SQL menu option, copy the relevant bits of code from there, then throw away the view designer.

Ok, I get what you are saying here as I do this when creating a complex query in other tools...

Olaf mentioned eview and did I download and ran it and was not particulary impressed. Looked to similar to vfp's designer. How is it better? Maybe if I only know what all the options did and how to use them. I'm sure that would help. I will revisit it and look at it with this new knowledge.

Thanks,
Stanley

 

[Olaf Doschke]

Regarding 1-4:

In simple terma, once you do all the CURSORSETPROPs you had used on a view (where you should have used dbsetprop to make these properties permanent view properties), the SPT cursor is like a view, you use TABLEUPDATE() to store changes to it (this answers 1). HEre CAs simply have all these properties as class properties, so you also only set them once.
You use INSERT or APPEND BLANK to create new records (this answers 2) beforehand and you also simply delete the rows you want to delete remotely in the cursor (this answers 3). The only thing not working like views with SPT is REQUERY(), here a CA has an advantage, you redo the SQLEXEC and thus have the grid goes blank effect, unless you do the usual stuff, eg set grids recordsource blank temporarily or use a techinque with two cursors (these are two possibilities to question 4). Again CA is in advantage about this aspect, as you can REQUERY and avoid the grid problems. Why am I saying I use CA and SPT? Perhaps it will get clearer now? CA has another goood advanatage, you can detach and attach the cursor from the CA object, for which I have uses, which to put it short help with loading partial data and still only maintain one CA cursor and keep all buffer states correctly.

Regarding CAs and wizard code:
Wizards and FFC framework are a different story than the native CA builder, you get a self contained CA class not routing back to any FFC framework library. No this time the "wizard" code, the builder code is not that bad, it's not most elegant, but you can also use CA without the builder or with better builders, as bernard bout made some.

Regarding eView:
eView is not a new designer, it mainly helps you figure out all code needed to create the same view you do with the designer. Once you have that, you can make a more complex query than the visual designer allows. Also having view definitions in full code (not just the create view, also all necessary dbsetprop) you can easily do what Mike suggested earlier and create your views on the fly in pure code, no files to keep and copy, just have code in your EXE capable to create a view DBC.

Bye, Olaf.

 

[Mike Lewis]

Stanley,

I think Olaf has answered your latest questions. Let me just pick up one point:

1. Lets talk SPT some more about updating bound grid data back to the remote table using spt. Is it as simple of having code in the grid's beforerowcolchange event that uses the key from the current record and do a sql update field1 to new value where remotekey equals localkey?

First, I wouldn't do that in the AfterRowCol change. Remember, the last syllable of AfterRowCol change is Col. In other words, the event will fire when the user changes the row or the column. If the user is editing the entire row, you will be sending data to the back-end each time they move to the next column. Not only does that mean a lot of network traffic, but also that you won't be able to do things like record-level validation, as that relies on having the data from several fields in place.

My preference would be to provide a separate Save button on the form. Let the user make as many edits as they like to the grid (that is, the local cursor), but don't send any of them to the back end until they hit Save. That also has the advantage that you can provide a Cancel Changes button, so that they can discard the changes and start again if they wish. The Save button would to a TABLEUPDATE() on the local cursor. The Cancel button would do a TABLEREVERT().

But regardless of that, you basically have two ways of updating the back end when using SPT. One way is to detect the fields that have changed, and then generate UPDATE statements to update the equivalent fields on the server. You send the UPDATEs via a separate SPT call. You would also need to provide code to deal with INSERTs and DELETEs in a similar way.

But a simpler method is to make the local cursor itself updatable. To do that, you set the same properties that we discussed earlier: Tables, KeyFieldList, UpdateNameList, SendUpdates. Use CURSORSETPROP() to set those properties.

As Olaf pointed out, the local cursor acts very much like a remote view in this respect. In fact, you can think of a remote view as the same thing as a local SPT cursor, but which comes equipped with an interactive design tool, and whose definition is stored persistently in a DBC.

Finally, don't forget that you need the same error handling with SPT as yo do with remote views. Check the reply from SQLEXEC(), and call AERROR() if it is negative.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[stanlyn]

Sorry guys for not replying sooner. I'm in the middle of changing a mobo in our SCO Unix server that went down Saturday evening. The bad mobo finally gave up after 17+ years of 24/7 operation with several people hammering on it daily... The challenge has been finding hardware compatible with 14 year old software, which was they last time I upgraded the os and software. At the time there were no USB, PCI-e, sata, or ssd. Floppy drive and SCSI are both a must... Anyway, I've still git to find a video solution that is compatible with X-11r5.

Back to the discussion... Mike, how is the error handling done when using buffering=5 and you are saving a grid/cursor full of changes at once thru your save button? Does some of the changes get saved while the errored ones do not? What is the process on knowing what happened and which row it occurred on? This is simple when dealing with on a row by row basis, but on a whole cursor all at once?????

So, a spt generated cursor can be a remote view just by setting cursorsetprops?? And a remote view cursor can be a spt cursor by removing those cursorsetprops? Does any dbsetprops need to be set also? I noticed you did not mention that.

And Olaf, I need to try and play with theses mythologies before responding. Thanks for all the detailed explanations as it helps greatly with the why questions. I hope to back at this late Tuesday as soon as I get the Unix server finished.

Thanks,
Stanley

 

[Olaf Doschke]

Still many misconceptions.

It will all get clearer, if you don't only try to understand things theoretically, but simply do and try things, you would actually avoid some questions making an uninformed impression, if you'd take the time to experiment instead of writing questions. If I tell you about DBSETPROP I point you to using it, and then you should. It would avoid asking your question about it. This thread, any thread, is not meant to be fully self contained, you only get things, if you start up your foxpro and do things while reading here, refer to help topics, too, etc. You can't expect us to write a whole book for you to just need to read and know everything. You seem to want to get a deep understanding of things before making a first step. You don't learn skiing in theory lessions and then are a star on the ski-run in your first run. I know you did something and already came back with your code, but you could really answer some of your questions yourself.

>So, a spt generated cursor can be a remote view just by setting cursorsetprops??
That's much too much said. An SPT can be made updatble. It's still not a view, as it's not stored in a database, which is an essential attribute of a view, and that matters, because it means a REQUERY doesn't work, the SPT query is not an attribute of the spt cursor and so REQUERY() doesn't know what to reexecute, that query of the view is stored in the DBC, in SPT cursors the similar thing is the SQLEXECUTE line of code, but that's not accessible for a REQUERY. I already said that ("The only thing not working like views with SPT is REQUERY()") and that's a big minus in regard of using SPT cursors as view replacement. SPTs strength are in other matters. To be very precise once more: A SPT cursor handled with CURSORSETPROPS can act on the remote backend via TABLEUPDATE, REQUERY isn't possible though, that's still an exclusive of the (remote) view and CA.

If you're not concerened about requery, almost anything can be made updatable targetting a remote database table, despite opening a DBF in a workarea, but it would be very unusal to use a DBF file to update a remote backend. I think you could set properties of a cursor you create by CREATE CURSOR to use a connection handle and update a remote backend. I never did that, because instead of thinking about what field structure to create via CREATE CURSOR you can simply use WHERE .F. ( or in T-SQL WHERE 1=0) to create an empty result cursor from the backend, if you merely want to insert new data into the backend.

>And a remote view cursor can be a spt cursor by removing those cursorsetprops?
In what direction are you thinking. I was telling you to do DBSETPROPS on your naked half view you created by CREATE SQL VIEW, because while CURSORSETPROPS is setting the same properties, it's not permanent. You can say, that doesn't matter, as you also always redo the CREATE SQL VIEW, but you don't need to. Once you create your remote view and set its permanent properties via DBSETPROP instead of CURSORSETPROP, you then and for all times in future only need to USE view to get the data the query retrieves and have the view cursor updatable without any further CURSORSETPROP or DBSETPROP.

Again: Mike just suggested you may create views at runtime, you don't have to, but even if you do, it's more to point to set the view properties instead of the view cursor properties. You still haven't comprehended the difference, it seems. The view cursor compares to the view as an object compares to a class or a running form compares to a SCX file. Do you create an empty form and then populate it with controls at runtime? No, you design a form with all controls and even code inside and then only DO FORM. In the same manner a view with all its necessary properties stored in a DBC is ready to use, in the same manner you also only INDEX a newly created table, once it's done it's done, the table and index is created. So even if you create views at runtime to have an advantage in dynamically changing queries, you will want to reuse the views in that application session and not create a view before any single use. Do you go home from work and then build your home every evening? It woudl have the advantage you could have a new home everyday, but it's not very practical, is it?

I'll leave the question about how to handle updates of multiple records for Mike, just one thing: It's the same as for buffered DBFs, this is not dewpending on remote or not remote backend, this is just in the topic of table vs row buffering. Look at all parameters of TABLEUPDATE and you'd know how to do this and how you can define how TABLEUPDATE continues or stops at a single row error. It's all already there and you simply need to read it.

Bye, Olaf.

 

[Mike Lewis]

how is the error handling done when using buffering=5 and you are saving a grid/cursor full of changes at once thru your save button? Does some of the changes get saved while the errored ones do not? What is the process on knowing what happened and which row it occurred on?

As Olaf said, this is exactly the same as with an ordinary buffered DBF. You achieve it by an appropriate setting of the first parameter to TABLEUPDATE(), and then using functions such as GETNEXTMODIFIED(), GETFLDSTATE(), CURVAL() and OLDVAL() to determine what has changed.

I also endorse what Olaf said about our replies in this thread not being a fully self-contained lesson on how to access remote data. As you have seen, there is a lot to learn on this topic, but the best way to learn it is to roll up your sleeves and start doing it. Take it one step at a time, and be prepared to try different approaches.

You should also take some time to read up on the subject in the VFP Hep file. In particular, these two topics: Working with Views, and Enhancing Applications Using SQL Pass-Through.

Once you have done that, you should be able to make an informed choice between remote views and SPT. By all means, come back with follow-up questions - I'm not trying to discourage that. But you also need to try things out for yourself.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Just to point out one more thing: You can also decide for row buffering with a remote backend, also implictly doing the tableupdate of single row changes when leaving the row, it just causes more traffic towards the MSSQL backend and with table buffering you have more control about that. You can decide to do single row updates with table buffering, too, it's also just a matter of parameterizing TABLEUPDATE() for the current row update, only.

If you are not well versed with VFPs buffering mechanisms, as they also work for DBFs, then you're bound to learn this on top of learning the remote database access technologies, yes.

Bye, Olaf.

 

[torturedmind]

I dunno if this could help but I made this function to open an Oracle table/view. The other functions came from the FAQ section of this generous forum. You may want to change some things to suit your environment/back-end DB.

Usage:

OpenRemoteDat( ;
	nConnHandle, ;
	"SomeSchema", ;
	"SomeTable", ;
	"SELECT " + ;
		"a.* " + ;
		"FROM SomeTable a " + ;
		"WHERE a.mrk_del = 0 " + ;
		"ORDER BY a.n3key", ;
	"SomeView", ;
	"")

********************
FUNCTION OpenRemoteDat
LPARAMETERS ;
	dbconnect, ;		&& CONNECTION HANDLE (NUMERIC)
	dbschema, ;		&& USER/SCHEMA NAME
	dbtable, ;		&& TABLE NAME
	dbsql, ;		&& SQL STRING TO BE PERFORMED
	cursorname, ;		&& OUTPUT NAME OF CURSOR/VIEW
	dbkeyfield		&& PRIMARY INDEX FIELD

LOCAL UpdatableFields, UpdateNames, ufl, failed

IF SQLEXEC(dbconnect, dbsql, cursorname) > 0
	IF NOT EMPTY(dbkeyfield)
		CURSORSETPROP("Tables", dbschema + "." + dbtable, cursorname)

		SELECT (cursorname)
		UpdatableFields = ""
		UpdateNames = ""

		*****************************************************************************
		***** IMPORTANT: dbKeyField MUST NOT BE INCLUDED IN THE UpdatableFields *****
		***** BUT SHOULD BE INLCUDED IN THE UpdateNameList.                     *****
		*****************************************************************************

		FOR ufl = 1 TO FCOUNT(cursorname)
			PUBLIC pub_dbtable, pub_dbcolumn
			pub_dbtable = ALLTRIM(UPPER(dbtable))
			pub_dbcolumn = ALLTRIM(UPPER(FIELD(ufl, cursorname)))

			SQLEXEC(dbconnect, ;
				"SELECT " + ;
					"CAST(1 AS NUMBER(1, 0)) ok, " + ;
					"table_name, " + ;
					"column_name " + ;
					"FROM user_tab_columns " + ;
					"WHERE table_name = ?pub_dbtable " + ;
					"AND column_name = ?pub_dbcolumn")

			RELEASE pub_dbtable, pub_dbcolumn

			IF SQLResult.ok = 1
				SELECT (cursorname)

				***** UPDATE ONLY FIELDS THAT BELONG TO THE TABLE *****
				IF NOT UPPER(FIELD(ufl, cursorname)) = UPPER(dbkeyfield)
					UpdatableFields = UPPER(UpdatableFields + ;
						IIF(EMPTY(UpdatableFields), "", ", ") + ;
							FIELD(ufl, cursorname))
				ENDIF

				UpdateNames = UPPER(UpdateNames + ;
					IIF(EMPTY(UpdateNames), "", ", ") + ;
						FIELD(ufl, cursorname) + " " + ;
						dbschema + "." + dbtable + "." + FIELD(ufl, cursorname))
				*********************************************************************
			ENDIF

			USE IN SQLResult
		ENDFOR

		CURSORSETPROP("KeyFieldList", dbkeyfield, cursorname)
		CURSORSETPROP("UpdatableFieldList", UpdatableFields, cursorname)
		CURSORSETPROP("UpdateNameList", UpdateNames, cursorname)
		CURSORSETPROP("Buffering", 3, cursorname)
		CURSORSETPROP("SendUpdates", .T., cursorname)
	ENDIF

	SELECT (cursorname)
ELSE
	MESSAGEBOX("Unable to execute the SQL statement.", 48, "System message")
	err_mem()
ENDIF
************************************************************

*WAIT CLEAR
_SCREEN.MOUSEPOINTER = 0
********************

This will allow you to see if the error is from VFP or from your back-end DB.

********************
FUNCTION err_mem
AERROR(err_memvars)
LIST MEMORY ;
	TO FILE (ADDBS(spcl_fldr3()) + "err_memvars.txt") ;
	NOCONSOLE
myshell(ADDBS(spcl_fldr3()) + "err_memvars.txt")
RELEASE err_memvars EXTENDED

CANCEL
********************

********************
FUNCTION spcl_fldr3
	LPARAMETERS what_folder

	IF PCOUNT() = 0
		what_folder = "MyDocuments"
	ENDIF

	LOCAL cFolderPath, cSpecialFolder
	cFolderPath = ""
	cSpecialFolder = what_folder

	WSHShell = CREATEOBJECT("WScript.Shell")

	IF TYPE("WSHShell") = "O"
		cFolderPath = WSHShell.SpecialFolders(cSpecialFolder)
	ENDIF

	RETURN cFolderPath
ENDFUNC
********************

********************
FUNCTION myshell
	LPARAMETERS lcfilename, fmode
	LOCAL retparam, msgshell
	retparam = 32
	msgshell = ""

	DECLARE INTEGER ShellExecute ;
		IN SHELL32.dll ;
		INTEGER nWinHandle,;
		STRING cOperation,;
		STRING cFileName,;
		STRING cParameters,;
		STRING cDirectory,;
		INTEGER nShowWindow

	IF NOT FILE(lcfilename) OR EMPTY(lcfilename) OR PCOUNT() = 0
		retparam = -1
	ELSE
		lcworkdir = SYS(2023)

		IF TYPE("fmode") = "L" ;
				OR TYPE("fmode") = "U"
			fmode = 2
		ENDIF

		IF fmode = 1	&& BROWSE FILE IN FOLDER
			retparam = ShellExecute( 0, "", GETENV("SystemRoot") + "\explorer.exe",  "/n, /select, " + FULLPATH(lcfilename), "", 1)
		ELSE			&& OPEN FILE IN ITS DEFAULT APPLICATION
			retparam = ShellExecute( 0, "Open", lcfilename, "", lcworkdir, 1)
		ENDIF
	ENDIF

	DO CASE
		CASE retparam = 2
			msgshell = "Bad file association."
		CASE retparam = 29
			msgshell = "Failure to load application."
		CASE retparam = 30
			msgshell = "Application is busy."
		CASE retparam = 31
			msgshell = "File not associated to an application."
		CASE retparam = -1
			msgshell = "Unable to continue. File or path not found."
	ENDCASE

	IF retparam < 32
		MESSAGEBOX(msgshell, 48, "System message")
	ENDIF

	RETURN (retparam >= 32)
ENDFUNC
********************

kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."