Invalid syntax for Insert statement

[ontytoom]

Hello Tom,

I am running into an issue when using your provider in my application.

In short, the following SQL Insert statement gets sent to VFP and is rejected as having a syntax error:

INSERT INTO 
  (SELECT 
Sname.Sn_Account, 
Sname.Sn_Name, 
Sname.Sn_Addr1, 
Sname.Sn_Addr2, 
Sname.Sn_Addr3, 
Sname.Sn_Addr4, 
Sname.Sn_Pstcode, 
Sname.Sn_Teleno, 
Sname.Sn_Faxno, 
Sname.Sn_Contact, 
Sname.Sn_Contac2, 
Sname.Sn_Region, 
Sname.Sn_Terrtry, 
Sname.Sn_Custype, 
Sname.Sn_Analsys, 
 CAST( Sname.Sn_Trnover AS n(20,2)) AS Sn_Trnover, 
Sname.Sn_Lastinv, 
Sname.Sn_Lastrec, 
 CAST( Sname.Sn_Currbal AS n(20,2)) AS Sn_Currbal, 
Sname.Sn_Crlim, 
 CAST( Sname.Sn_Ordrbal AS n(20,2)) AS Sn_Ordrbal, 
Sname.Sn_Stmntac, 
Sname.Sn_Invceac, 
Sname.Sn_Priorty, 
Sname.Sn_Stop, 
Sname.Sn_Acknow, 
Sname.Sn_Nextpay, 
Sname.Sn_Delinst, 
Sname.Sn_Memo, 
Sname.Sn_Dwar, 
Sname.Sn_Desp, 
Sname.Sn_Route, 
Sname.Sn_Tprfl, 
Sname.Sn_Cprfl, 
Sname.Sn_Ctry, 
Sname.Sn_Nrthire, 
Sname.Sn_Vrn, 
Sname.Sn_Delt, 
Sname.Sn_Ntrn, 
Sname.Sn_Mtrn, 
Sname.Sn_Dl_Flag, 
Sname.Sn_Dl_Date, 
Sname.Sn_Branch, 
Sname.Sn_Model, 
Sname.Sn_Lupdate, 
Sname.Sn_Sana, 
Sname.Sn_Rana, 
Sname.Sn_Vendor, 
Sname.Sn_Custloc, 
Sname.Sn_Bana, 
Sname.Sn_Key1, 
Sname.Sn_Key2, 
Sname.Sn_Key3, 
Sname.Sn_Key4, 
Sname.Sn_Bankac, 
Sname.Sn_Banksor, 
Sname.Sn_Email, 
Sname.Sn_Wwwpage, 
Sname.Sn_Fcreate, 
Sname.Sn_Epasswd, 
Sname.Sn_Estore, 
Sname.Sn_Luptime, 
Sname.Sn_Trndate, 
Sname.Sn_Atpycd, 
Sname.Sn_Dormant, 
Sname.Sn_Ordmail, 
Sname.Sn_Emailst, 
Sname.Sn_Emailoa, 
Sname.Sn_Project, 
Sname.Sn_Job, 
Sname.Sn_Docmail, 
Sname.Sn_Cldate, 
Sname.Sn_Cmgroup, 
Sname.Sn_Crdchck, 
Sname.Sn_Crdcrno, 
Sname.Sn_Crdnotes, 
Sname.Sn_Crdrate, 
Sname.Sn_Crdscor, 
Sname.Sn_Bic, 
Sname.Sn_Iban, 
Sname.Sn_Dltmail, 
Sname.Sn_Dl_Pubid, 
Sname.Sn_Sepayee, 
Sname.Sn_Adjsvcd, 
Sname.Sn_Ovravmt, 
Sname.Sn_Sgrp
FROM Sname Sname)(Sn_Account, Sn_Name, Sn_Addr1, Sn_Addr2, Sn_Addr3, Sn_Addr4, Sn_Pstcode, Sn_Teleno, Sn_Faxno, Sn_Contact, Sn_Contac2, Sn_Region, Sn_Terrtry, Sn_Custype, Sn_Analsys, Sn_Trnover, Sn_Lastinv, Sn_Lastrec, Sn_Currbal, Sn_Crlim, Sn_Ordrbal, Sn_Stmntac, Sn_Invceac, Sn_Priorty, Sn_Stop, Sn_Acknow, Sn_Nextpay, Sn_Delinst, Sn_Memo, Sn_Dwar, Sn_Desp, Sn_Route, Sn_Tprfl, Sn_Cprfl, Sn_Ctry, Sn_Nrthire, Sn_Vrn, Sn_Delt, Sn_Ntrn, Sn_Mtrn, Sn_Dl_Flag, Sn_Dl_Date, Sn_Branch, Sn_Model, Sn_Lupdate, Sn_Sana, Sn_Rana, Sn_Vendor, Sn_Custloc, Sn_Bana, Sn_Key1, Sn_Key2, Sn_Key3, Sn_Key4, Sn_Bankac, Sn_Banksor, Sn_Email, Sn_Wwwpage, Sn_Fcreate, Sn_Epasswd, Sn_Estore, Sn_Luptime, Sn_Trndate, Sn_Atpycd, Sn_Dormant, Sn_Ordmail, Sn_Emailst, Sn_Emailoa, Sn_Project, Sn_Job, Sn_Docmail, Sn_Cldate, Sn_Cmgroup, Sn_Crdchck, Sn_Crdcrno, Sn_Crdnotes, Sn_Crdrate, Sn_Crdscor, Sn_Bic, Sn_Iban, Sn_Dltmail, Sn_Dl_Pubid, Sn_Sepayee, Sn_Adjsvcd, Sn_Ovravmt, Sn_Sgrp)
  values (@__C2P__1, @__C2P__2, @__C2P__3, @__C2P__4, @__C2P__5, @__C2P__6, @__C2P__7, @__C2P__8, @__C2P__9, @__C2P__10, @__C2P__11, @__C2P__12, @__C2P__13, @__C2P__14, @__C2P__15, @__C2P__16, CTOT('2017-05-27T13:04:49'), CTOT('2017-05-27T13:04:49'), @__C2P__17, @__C2P__18, @__C2P__19, @__C2P__20, @__C2P__21, @__C2P__22, .f., .f., @__C2P__23, @__C2P__24, @__C2P__25, @__C2P__26, @__C2P__27, @__C2P__28, @__C2P__29, @__C2P__30, @__C2P__31, .f., @__C2P__32, @__C2P__33, @__C2P__34, @__C2P__35, @__C2P__36, CTOT('2017-05-27T13:04:49'), .f., .f., CTOT('2017-05-27T13:04:49'), @__C2P__37, @__C2P__38, @__C2P__39, @__C2P__40, @__C2P__41, @__C2P__42, @__C2P__43, @__C2P__44, @__C2P__45, @__C2P__46, @__C2P__47, @__C2P__48, @__C2P__49, CTOT('2017-05-27T00:00:00'), @__C2P__50, @__C2P__51, @__C2P__52, CTOT('2017-05-27T13:04:49'), @__C2P__53, .f., @__C2P__54, .t., .f., @__C2P__55, @__C2P__56, 0, CTOT('2017-05-27T13:04:49'), @__C2P__57, CTOT('2017-05-27T13:04:49'), @__C2P__58, @__C2P__59, @__C2P__60, @__C2P__61, @__C2P__62, @__C2P__63, .f., @__C2P__64, @__C2P__65, @__C2P__66, @__C2P__67, @__C2P__68)

The resulting error is:

System.Data.Entity.Infrastructure.DbUpdateException : An error occurred while updating the entries. See the inner exception for details. ----> System.Data.Entity.Core.UpdateException : An error occurred while updating the entries. See the inner exception for details. ----> VfpClient.VfpException : Syntax error. ----> System.Data.OleDb.OleDbException : Syntax error.

I found a StackOverflow post saying that named parameters with @ sumbol are not supported by VFP: https://stackoverflow.com/questions/16386352/sql-parametrized-syntax-error-with-vfp-oledb

I am not confident where the error occurs, so I opened this issue in hopes to shed more light on the query composition process. If you might have any workarounds in mind, I will gladly try them out. Also, please let me know if you need any additional info from me to research this issue.

Many thanks for all your work and for your kind support!

NOTE: I also posted on StackOverflow in case someone there might be of help: https://stackoverflow.com/questions/44219602/visual-foxpro-syntax-error-in-insert-statement-when-using-entity-framework-and

[tombrothers]

The problem isn’t with the @ symbol. It is true that the VFP oledb provider, using OleDbCommand, doesn’t handle name parameters. But I wrote a wrapper class to allow named parameters.

As you’ve probably noticed, the insert statement is attempting to insert into a select statement instead of a table as you would normally expect. This comment on a previous issue explains why this happens in the EDMX along with possible solutions.

[ontytoom]

Many thanks for quick response and for the info.

Indeed, when viewing the EDMX file via text editor, I see messages like this all over the place:

Errors Found During Generation: warning 6002: The table/view 'Sname' does not have a primary key defined. The key has been inferred and the definition was created as a read-only table/view.

Unfortunately, I cannot change the schema of the VFP database. We are integrating with an off the shelf package and are not at liberty to tinker with its schema. None of the tables have primary keys defined. The schema is exceptionally complex (close to 380 tables, with the widest table having 250 columns). I could possibly go code-first if I knew the schema, but given its complexity it would be much more preferable to have the entities generated for me.

I will review all the links you provided in detail, and will post any follow-up questions.

[tombrothers]

No need to changed the database schema. Just use code-first. This link shows you how to get started. It will generate all the models for you. You'll just have to fix the primary key mapping.