DetailsView update with subquery

Hi everybody,

I have a little Problem with an update and Detailsview.
I have 2 table (tblUser - uid,uName,rId) and (tblRole - rId, rDescription)

In my update I don't want to use the roles id, I want to use the roles Description.

Via sql I do a update the role to Admin like that

UPDATE tblUserSET rId = (SELECT rIdFROM dbo.tblRolesWHERE (rDescription ='Admin'))

But I don't get it to work for the detailsview update 

Hi,

The syntax of your update statement is ok. But what you are updating is rId which I think is identity column, we cannot update identity column.

Thanks.

|||

Hi,

no what I want to set is the riD which is the foreign key in my tblusers. This has to be possible and it is working in my mssql query browser. I'M searching for the right @. statetment for the update command in my aspx.

Thanks Philipp

|||

Try this (No warranty, untested) 

UpdateCommand="UPDATE tblUserSET rId = (SELECT rIdFROM dbo.tblRolesWHERE (rDescription = @.rDescription))  WHERE UID = @.UID" >

<UpdateParameters>

<asp:Parameter Name="UID" Type=?? />

<asp:Parameter Name="rDescription" Type="String" />

</UpdateParameters>

|||

It works!!!

Thank you very much!

greetings

Philipp