Updating related 2 Tables
-
I could execute the following sql in SMSS, but when adding it in a sqldatasource as an update statement, it gets an error. In SSMS, and it works fine:
UPDATE S
SET S.DailyBudget = 12
FROM Account O INNER JOIN AdCampaign S
ON O.ID = S.AccountID
WHERE O.EmailAddress = 'abc@abc.com'But In Sqdatasource, it gets error:
UPDATE S SET S.DailyBudget = 12 FROM Account O INNER JOIN AdCampaign S ON O.ID = S.AccountID WHERE (O.EmailAddress = @EmailAddress)
Error: Invalid Object Name 'S'. Hint: S,O are borrowed names, and not actual table names.
-
I could execute the following sql in SMSS, but when adding it in a sqldatasource as an update statement, it gets an error. In SSMS, and it works fine:
UPDATE S
SET S.DailyBudget = 12
FROM Account O INNER JOIN AdCampaign S
ON O.ID = S.AccountID
WHERE O.EmailAddress = 'abc@abc.com'But In Sqdatasource, it gets error:
UPDATE S SET S.DailyBudget = 12 FROM Account O INNER JOIN AdCampaign S ON O.ID = S.AccountID WHERE (O.EmailAddress = @EmailAddress)
Error: Invalid Object Name 'S'. Hint: S,O are borrowed names, and not actual table names.
Hi, the hint-text gives you the information why the update failed. SqlDataSource does not provide support for abbreviations. So you have to rewrite your query to
UPDATE AdCampaign SET AdCampaign.DailyBudget = 12 FROM Account INNER JOIN AdCampaign ON Account.ID = AdCampaign.AccountID WHERE (Account.EmailAddress = @EmailAddress)
This should work. Regards, Sebastian
It's not a bug, it's a feature! Check out my CodeProject article Permission-by-aspect. Me in Softwareland.