ADS Subquery using Visual Foxpro Tables

[former_member613967]

I am selecting a row from a Visual Foxpro Table on the server into a local datatable object, making minor adjustments to the data, and want to insert the modified row back into the source table. Following ADS guidelines for subqueries I am writing

SQLQuery = "INSERT INTO origtable (SELECT * FROM localtablename WHERE fieldname = somevalue)" - the fieldname and value reference the row in the local datatable.

When I execute using executenonquery on an adscommand object I get an error: Expected lexical element not found: <identifier> missing column name. There was a problem parsing the column list in the INSERT INTO statement

Is this form of the insert command valid for VFP tables - or must I go through the standard INSERT command syntax specifying each field with value.

[former_member613967]

Note I am writing in Microsoft Visual Basic in Visual Studio 2016 - NOT in Visual Foxpro

[michael_loop]

The problem is with the parenthesis. When the parser reads:

INSERT INTO origtable (

it expects that you will continue with

...columnlist) VALUES (values)

INSERT INTO origtable SELECT * FROM localtablename WHERE fieldname = somevalue

should work fine.

[former_member613967]

Thanks very much - that solved the columns problem, but unearthed another - that the select from table is not a physical table on a computer but is rather a datatable object, so I'm getting a file not found error on the datatable object. Perhaps there is a way to specify that the source table is a datatable object and not a file on disk?

[joachim_drr]

SQL is executed by ADS and doesn't know about your local data. You can't change that.

The only way to get local data into the table is to loop through your local data and process an "INSERT INTO ..." with concrete values for each local record.

[former_member613967]

Thanks - obviously my brain hasn't yet completed the journey from local processing to server based processing. So, given that I'm using VFP tables, would scripting in stored procedures inside ADS allow for accessing the VFP tables, the purpose being to keep data manipulation on the server rather than in local objects. I would imagine that in that scenario ADS could then insert or update directly from the working cursor as I was attempting to do, eliminating the need to specify each field individually in a SQL statement (some of the tables are rather long in field count). Doing so would have other benefits as well, such as eliminating a lot of network traffic.

[joachim_drr]

Richard,

not sure about the purpose of your issue ... is your VFP data file stored on the PC and you need to transfer it into a server database? Easiest way would be to copy the table file to the server (folder or subfolder of your database), then add it to your database and do the processing directly on the server.

check out sp_AddTableToDatabase.

Edit: oops - re-reading the other comment I see your issue ... you could store your datatable to a file on the server and then use the steps above.

[former_member613967]

The VFP tables are currently on the server as free tables. They are in use by the current VFP program set on a daily basis. I am rewriting the current VFP code in VB.net, and am using ADS because it provides access to VFP tables while also allowing access by VFP programs - really necessary and a great feature! I don't think it would work to take a table, copy it into an ADS database to work on, as that would take all the changes out of access by the current VFP program set, and I would have to copy any changes back to the original table somehow while managing changes that might have been made by the VFP programs.

My idea was to create an ADS database on the server, unrelated to the current VFP tables, then use that database to store procedures that would manipulate the VFP tables. I was hoping that doing so might provide some mechanism for keeping working cursors etc on the server rather than on the workstation, so that I could avail myself of procedures only available when the tables are located on the server and directly available to ADS (such as my original SELECT question where the localtable resided locally and was unavailable to ADS). I thought that perhaps if the 'localtable' was actually on the server the SELECT command might be made to work. Currently I am just writing long SELECT, UPDATE and INSERT command strings, individually specifying each column, to overcome the problem. It sure would be nice to be able to replace that long mess of code with a nice simple command string!

[joachim_drr]

the tables are already on the server and used by a non-ADS application? That's easy ... just open the table via ADS using compatible locking mode.