June 14th, 2011, 04:28 AM
C# Datasets (.NET2)
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.
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:
Obviously this updates completely the wrong rows.
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?
June 14th, 2011, 07:10 AM
(RESOLVED) C# Datasets (.NET2)
Well I have managed to resolve the problem and I think I know why it is occuring. It would appear that the statement within the brackets is parsed first, i.e.
(SELECT EXPRESSION_ID FROM TEMPLATE_EXPRESSIONS WHERE TEMPLATE_ID = @templateId)
This being the case templateId would be the first parameter and sessonId the second. I have changed the parameters round and lo and behold it works!
Click Here to Expand Forum to Full Width