Hi All,
I am having a problem updating using the C# datasets that is driving me mad. This should be a simple task, but I am getting some strange behaviour...I am sure I am doing something stupid but not sure what.
Basically I have three tables, TEMPLATES, TEMPLATE_EXPRESSIONS and GENERIC_EXPRESSIONS (in an Access 2003 database). The template_expressions table contains a link between templates and generic_expresions so that multiple templates can use the same generic_expressions. Built into this is a locking function on GENERIC_EXPRESSIONS, which sets a session id value to ensure multiple users can't edit at the same time.
What I wish to do is update GENERIC_EXPRESSIONS so that the locking status is set to the session id for all items for a particular template. I have created an update command on my data adapter, which looks like this:
The command is called SetTemplateLock (sessionId, templateId) and the two parameters are both defined as int32 and in the correct order when I view them.Code:UPDATE GENERIC_EXPRESSIONS
SET ITEM_LOCK = @sessionId
WHERE EXPRESSION_ID IN (SELECT EXPRESSION_ID FROM TEMPLATE_EXPRESSIONS WHERE TEMPLATE_ID = @templateId)
All seems pretty straightforward right? When I run it I use the following code:
Now I have confirmed that the values for pSessionId and pTemplateId are correct, but what happens is the following:Code:GENERIC_EXPRESSIONSTableAdapter.SetTemplateLock(pSessionId, pTemplateId);
Obviously this updates completely the wrong rows.Code:UPDATE GENERIC_EXPRESSIONS
SET ITEM_LOCK = @templateId
WHERE EXPRESSION_ID IN (SELECT EXPRESSION_ID FROM TEMPLATE_EXPRESSIONS WHERE TEMPLATE_ID = @sessionId)
I have various other queries that use a similar method (and work correctly) and the only difference with this one is that it reads data in from a sub-table.
Anyone seen any similar or can spot something stupid that I have done?
Thanks
Andrew