how to edit and insert data records in SQL table using VFP data entry form

[GLABSOKENKEN1]

Hi everyone,

Can you help me I have program in visual foxpro and the requirements table is SQL not native dbf table.
can you give me sample script/ program to update insert data into sql table with using connections string, I am not yet expert in stored procedures of SQL to call program from visual foxpro form, I just new beginner in SQL scripting

See samples textbox from Visual foxpro form.

LABEL TEXTBOX
---------------------------------
ENTER PRODUCT CODE : codtext1 SEARCH Button here --> When you enter product code then click search button all records should
be displayed, if not, message is " does not exist!!" then loop to codetext1
to begin new records entry to the ff. textboxes 1 - 4, then click Save button.
PRODUCT DESCRIPTION : destext2
QUANTITY : qtytext2
UNIT PRICES : unitext3
TOTAL AMOUNT : amttext4

SAVE BUTTON CANCEL BUTTON



Thanks

Your Reply is mostly
Appreciate

 

[Koen Piller]

Hi,

Please read in this forum:

https://www.tek-tips.com/viewthread.cfm?qid=1788769

Regards,
Koen

 

[Olaf Doschke]

While it's nice Koean points to a thread I explain SQLExpress, and this translates to SQL Server in many aspects - just another connection string and you can recycle the idea of SQLExec(). While that honors me, it's not what I would recommend in case of being able to use usual ways of working on data.

The way to get from a remote backend to the same cursor/workarea behavior you have on a USEd DBF file is what all remote data access variants have in common, you query remote data into a workarea and then can make use of CURSORSETPROP statements to make that updatable and then work on it just like working on a DBF with buffering, finally submitting buffered changes with TABLEUPDATE().

SQLExec created results can be turned updatable that way, remote views are closer in a way, as they incorporate the cursorsetprops in their definition (can even be seen as code in the view designer, if you switch to "View SQL", albeit in the form of DBSetProp instead of cursorsetprop, as it's permanent settings of views and not just temporary workarea, but look up cursorsetprop and dbsetprop and you'll see an overlap in the aspects of defining the properties necessary to update data, also as it works for DBFs with data access via local views.

And that is the limitation, too, you can forget to simply USE a remote table and have access to all records, you rather have to do it the way local views are meant to be used, first query the portion of data to modify, if only needing to add new rows that simply means querying a view with WHERE .F. (or for remote backends, which don't know VFPs boolean literal types WHERE 1=0), so you get an empty result and cann add to that and submit the new data with TABLEUPDATE().

That's the way to act on remote data in short. Before you get there it's a lot of details. In fact you could read part of the VFP help about views (local and remote), that's the root topic about all you need and you can add cursoradapter as a special way of "remote views" that offer some more things and are an oop way of using views, albeit it's mainly about their SelectCmd also being an SQL query just like the query you write or interactively create for views. There is no cursoradapter designer like the visual view designer, but you don't need to "handcraft" the SelectCmd of a cursoradapter class, you can create a class based on cursoradapter and right click and pick "builder" from the context menu to get to a simple designer. Once you defined the connection properties on the first "prpperties" tab, the "Build" buttons on the "data access" tab will get active and give a simplified query designer, in fact just the field selection portion of the view designer. But you may first create a normal view and then copy over the SQL from there to the cursoradapter.

Details. I could go on, bit then this will become a book. Fortunately a book like that also exists,

https://www.hentzenwerke.com/catalog/csvfp.htm

It's older, I guess it doesn't contain cursoradapter and it's written for an old version of SQL Server, but the mechanics is still the same.

Yes, there could be done a simple demo form fir same sample database provided as bak backup file you can restore and get this going, but no, it's no good idea of learning this or working with this and just changing table and field names. You have to learn a new way of acting on data and also forget a few things you can do with DBFs.

So I guess no one here will be willing to chunk out such an example form for you, it doesn't really help.

Start reading about views, as said, about buffering data, about data access for shared access and for remote access, you finally will learn also about ways even intended for DBFs and pardon me, if you already knwo about these you will see how the remote data access just is an add on to that technique and you can reuse code to use cursoradapters instead of tables in data environments, REQUERY them like the cursors of them where view cursors, TABLEUPDATE() or TABLEREVERT() them just like they were views or DBFa and even use transactions, albeit via sending BEGIN TRANSACTION and COMMIT to the server instead of simply doing it in VFP code.

Again, details. You only get this going through this, there is no shortcut, especially if you never even used buffering and client/server techniques on DBFs that's now a steep learning curve you avoided all the time it was already available to you without a remote backend to learn about, too.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

Let's make this a little quiz or poll about your DBF knowledge: Which of these Foxpro commands, functions concepts do you know and use?


concept table/view designers
concept: views, parameterization
DBSETPROP
CURSORSETPROP
copncept: tables
CREATE TABLE
INDEX ON expression TAG tagname
conceptBC
field rules, table rules, standard values, triggers, stored procedures (all on the DBC level)
concept: referential integrity
concept: data environment
USE
APPEND BLANK
REPLACE
DELETE
LOCATE
SET ORDER
SET RELATION
SET SKIP TO
concept: SQL
INSERT-SQL
UPDATE-SQL
DELETE-SQL
concept: buffering
SET MULTILOCKS
BEGIN TRANSACTION/ENDTRANSACTION/ROLLBACK
TABLEUPDATE(),TABLEREVERT()
OLDVAL(), CURVAL(), GETFLDSTATE(), SETFLDSTATE(), GETNEXTMODIFIED()

This is not even exhaustive but has a few of the concepts, commands, and functions unavoidably to know even for decent DBF related data access.

If you know all this, then the topics of remote views, cursoradapters or SQL passthrough will "just" be additional things to learn to get to remote data access.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

And to give one example of a thing I intentionally skipped:

SCATTER/GATHER

These two commands are unnecessary with buffering. You
a) have much more workareas than in legacy foxpro, you can bind to dbfs or cursors or workareas directly and not to variables.
b) don't use private and public variables all over the place, as they are created from SCATTER and a bad concept
c) have buffering, can change a field and still have the OLDVAL() (and by the way also the CURVAL()) besides the value you or your user modified it to
d) have already tablename.fieldname access and don't need variables for that

SCATTER and GATHER and even INSERT-SQL were extended to work with record objects, the only reason for that is not to cope with legacy code still working with scatter gather, also not to work with a syntax object.property being tablename.fieldname, that already existed, but you can only use tablename.fieldname in the current datasession, while a record object can be passed to other data sessions (i.e. other forms) and even to COM objects, not only VFP made OLE servers.

So SCATTER/GATHER is obsolete with the introduction of buffering, so I guess since about VFP5.

Edit: Another thing I completely skipped is Rushmore query optimization mainly necessary for the initial data fetching of (hopefully) just a few (10-100) records to work with in some form, and when switdching to remote backend this is knowing the remote backend ways of query optimizations, its index types and SQL engine, not VFP anymore. So also obsolete knowlegde for remote access, albeit usable, if you have reasons to reuse some data processing/crunching code after exporting some remote data to dbfs or cursors. But likely once data is in MSSQL you're better off processing data the SQL Server way, eg going for datawarehousing with OLAP cubes and MDX, admittedly a thing I only did once at all.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

Just one thing I often see:

People from the legacy FoxPro camp don't know forms, just legacy screen PRGs, @SAY, @GET READ and in regard of data access know how to create DBFs (maybe even just work on existing ones) and USE, APPEND, REPLACE and DELETE.

That's like saying you know HTML when you know a HTML file skeleton plus <H1>, <H2>, <P>, <IMG> and <A> tag. You can already write texts and illustrate them and hyperlink them.

They may have some legacy FoxPro knowledge that I don't even have because it's completely outdated and the VFP help only has a stub text saying "for backward compatibility, use this and that instead".

The bad news actually is, while you can still work with DBFs with just these few xBase commands, it's in no way sufficient for scalable multiple user environments, even VFP itself is more complex, forget about that. If you're of that camp it's even better you go for a C# frontend or anything more modern like a web application based on JavaScript only with Node.JS and a MySQL backend or Mongo instead of relearning a fully new set of VFP knowledge.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

All that said, don't feel bad, even if you're fitting this stereotypical description of a legacy FoxPro developer or hobbyist. We're still here to help, but with detail questions, not with wholesome concepts and ready to use forms. They also don't really help to discover all the stuff you need to know, learning by example is even not possible with VFP, learning details, yes, but learning a whole simple application, even just a single form one: no. There are too many concepts in the game at once.

If you say all you need is such forms with two or three fields searching data and adding to it, that's a typical simply business application of some searchable data, but you could move to MS Access or HTML or C# or anything not deprecated and learn the stuff you need for that from scratch and have a much better perspective, even as a hobbyist. That's why I said all this.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name