UpdateCommand for Asp GridView using a join between two tables

Question

I have two tables that I join sims and transactionlog and display in a Asp GridView using a SQLDatasource

Then I have an ASP GridView with the following select and update statements:

SelectCommand="SELECT * FROM sims s INNER JOIN TransactionLog AS tl ON s.id = tl.SimsId"     
UpdateCommand="UPDATE SET s.Notes=@Notes FROM sims s INNER JOIN TransactionLog AS tl ON s.id = tl.SimsId WHERE s.id=@id and tl.Id=@Id1"

Right now I only have the Notes column not set to readonly, but would like to add the others in later as soon as I can get the update statement to work for the notes column first.

When I try to update the notes column the error I'm getting is the following: Incorrect syntax near the keyword 'SET'.]

Mmm, hope my question is stated clearly enough.

Gave Mike the credits since he helped me out the most. In the end I just settled for a stored procedure and it worked. — n4rzul
– n4rzul, Commented Mar 31, 2011 at 10:42

Mikael Östberg · Accepted Answer · 2011-03-16 11:36:00Z

1

This is how you write and SQL UPDATE statement with the FROM clause.

drop table t2
drop table t1
go

create table t1 (
Id int not null identity(1,1) primary key,
Name varchar(50) null
)

create table t2 (
t1Id int not null foreign key references t1(id),
Name varchar(50) null
)

insert into t1(name) values('T1Test1')
insert into t1(name) values('T1Test2')
insert into t2(t1Id, name) values(1, 'T2Test1')
insert into t2(t1Id, name) values(2, 'T2Test2')

-- Have a look at the data
select * from t1 inner join t2 on t1.Id = t2.t1Id

update t2 
set t2.Name = 'T2Test1_changed'
from t2 inner join t1 on t2.t1Id = t1.Id
where t1.Id = 1

-- See the changes
select * from t1 inner join t2 on t1.Id = t2.t1Id

This means that your statement should look like this:

UPDATE sims
SET s.Notes = @Notes 
FROM sims s INNER JOIN TransactionLog tl ON s.id = tl.SimsId 
WHERE s.id = @id and tl.Id = @Id1

I do however doubt the WHERE clause. Maybe there's no need to use both IDs there, but only you know that.

Try you queries in SQL Management Studio before applying them in you application for a better debugging experience.

Here is the MSDN docs on the UPDATE statement: http://msdn.microsoft.com/en-us/library/ms177523.aspx

edited Mar 16, 2011 at 11:36

answered Mar 16, 2011 at 9:54

Mikael Östberg

17.2k6 gold badges67 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

n4rzul Over a year ago

So if I wanted to update columns on both sides of the join I'm screwed? That doesn't sound right?

n4rzul Over a year ago

Mike, this article uses an inner join in a update statement just fine. The problem with it and why its not quite right in my case is that it inly updates fields in the "left" table and not both. I tried getting Ben Nadels to work for fields in the left and right table, but cant get it to work. Heres the article: bennadel.com/blog/…

Mikael Östberg Over a year ago

You are right! You can in face use the FROM clause in an UPDATE statement, but it seems like you only can update one table. I've add an example.

n4rzul Over a year ago

Yeah, I got the update 1 table part, problem is I need to update both tables in a single update statement :(. Thanks for the stellar effort Mike it has been much appreciated.

Mikael Östberg Over a year ago

Perhaps you can enter two statements in the UpdateCommand and separate them with a ;. That should probably work. But there is no way you can update two tables with one UPDATE statement.

|

Collectives™ on Stack Overflow

UpdateCommand for Asp GridView using a join between two tables

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related