Updating fails for certain tables

Jul 26, 2012 at 12:38 PM
Hello,

I have 2 tables, Pe_Ret and Pe_Med, in a DBC file. Pe_Med is linked to Pe_Ret through a foreign key. I have no problems inserting or updating entries in Pe_Ret. However, I can't update anything in Pe_Med without getting a TargetInvocationException; the inner exception is a VfpException. Here's the message of the inner exception:

Syntax error.

UPDATE t0
SET t0.Activesubstance = ?, t0.Appid = ?, t0.Byprotocol = .f., t0.Concentration = ?, [...],
t0.Druginvoiceno = NULL, t0.Fractionno = 0,
t0.Idreteta = 57, to.Issuedtype = ?, [...],
t0.Testpatienttype = ?, t0.Vatpercent = @__Param__5__
FROM Pe_Med t0
WHERE (Id = 200)

And the stack trace:

at VfpClient.VfpCommand.ExecuteNonQuery()
at IQToolkit.Data.DbEntityProvider.Executor.ExecuteCommand(QueryCommand query, Object[] paramValues)
at lambda_method(Closure )
at IQToolkit.Data.EntityProvider.Execute(Expression expression)
at LinqToVfp.VfpQueryProvider.Execute(Expression expression)
at IQToolkit.Updatable.InsertOrUpdate[T,S](IUpdatable`1 collection, T instance, Expression`1 updateCheck, Expression`1 resultSelector)

I snipped a few things from the inner exception's message, but nothing significant. Id is the primary key in Pe_Med, Idreteta is the foreign key and Issuedtype was the value I was trying to update. I have no idea why the question marks appear and I'm equally baffled by the @__Param__5__ that's placed for the last field in the table, a field that's not being updated.

Can anyone tell me what's causing the update to fail? Am I doing something wrong? I get the same exception both from my code in Visual Studio, and from LINQPad. The LINQPad code that's causing the exception is:

// get an element to update
var med = Pe_Med.Where(x => x.Id == 200)
    .Select(x => x)
    .OrderBy(x => x.Id).First();  // If I don't use OrderBy I get a VfpException (SQL: TOP requires an ORDER BY)
med.Issuedtype = "3";  // try to modify something in the entry
Pe_Med.InsertOrUpdate(med); // here's where I get the described exception

Thank you,

Alex

Coordinator
Jul 26, 2012 at 2:11 PM

Short Answer:

That is certainly a bug in the code.

 

Long Answer:

LINQ to VFP builds and update statement that includes all fields and during that process it determines which field values can be added to the update statement as constant values. For the field values that can’t/shouldn’t be constant values an OleDbParameter is used and a parameter name such as @__Param__5__ is included in the sql statement. Unfortunately  though, OleDbCommand (which holds a collection of OleDbParameters) doesn’t actually work with named parameters. It expects question marks as parameter place holders and processes them in the order they are listed in the collection. So there is a process that converts the named parameters into question marks. In your case @__Param__5__ isn’t getting converted for some reason.

 

 

Could you provide me a sample project that I could use to debug the issue? … trbrothers@hotmail.com

Coordinator
Jul 27, 2012 at 11:18 AM

I posted a fix for this issue.

Jul 27, 2012 at 11:25 AM
Thank you! I tried the new version and it works.

Alex



On Fri, Jul 27, 2012 at 1:18 PM, TomBrothers <notifications@codeplex.com> wrote:

From: TomBrothers

I posted a fix for this issue.

Read the full discussion online.

To add a post to this discussion, reply to this email (LinqToVfp@discussions.codeplex.com)

To start a new discussion for this project, email LinqToVfp@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com