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

DetailsView Control (Please Help!)

Can someone please tell me what I am doing wrong...

I have a simple webform that has contains a GridView and a DetailsView. Once the GridView is populated the user simply selects a record from the list. The DetailsView is them populated with all of the data from the selected record.

The DetailsView is bound to a SQL DataSource/CustomerData. I have made sure that the DV control has the Edit/Update Command listed. The user can click the Edit button and successfully edit the fields but when the user clicks the "Update" button I am getting the following message.

Updating is not supported by data source 'CustomerData' unless UpdateCommand is specified

Why isnt the control handling the Update.

Many Thanks!!!!

T

You still need to have your SqlDataSource for the detailview configured to handle the editing...

This sample is doing the same tihng as you described. Chaeck your code against it to see what is the difference.

http://www.asp.net/QuickStart/util/srcview.aspx?path=~/aspnet/samples/data/GridViewMasterDetailsInsert.src

If you still have question, please post back here.

|||

Limno,

First thank you for responding... I did do some more research after posting and found that I was not calling the Update Command. I subsequently have added that... I am not getting any errors but... Its not updating the database. I looked at the link you supplied and it appears that I have everything setup correctly... Obviously not!!!!

Any guidance would be much appreciated.

Here is my code:

<asp:DetailsViewID="DVCustomerInfo"runat="server"Height="22px"Width="215px"CellPadding="4"Font-Size="0.65em"ForeColor="#333333"GridLines="None"HeaderText="Customer Info."AllowPaging="True"AutoGenerateRows="False"DataKeyNames="WorkSheetID,Origin,DealerName,DateCreated,SalesRep,BkupSalesRep,FirstName,MiddleName,LastName,Address,City,State,Zip,HomePhone,WorkPhone,CellPhone,Email,County,SalesTax,SS,DOB"DataSourceID="ApplicantSQLDS">

<FooterStyleBackColor="#1C5E55"Font-Bold="True"ForeColor="White"/><CommandRowStyleBackColor="#C5BBAF"Font-Bold="True"/><EditRowStyleBackColor="#7C6F57"/><RowStyleBackColor="#E3EAEB"/><PagerStyleBackColor="#666666"ForeColor="White"HorizontalAlign="Center"/><FieldHeaderStyleBackColor="#D0D0D0"Font-Bold="True"/><HeaderStyleBackColor="Black"Font-Bold="True"ForeColor="White"/><AlternatingRowStyleBackColor="White"/><Fields><asp:BoundFieldDataField="WorkSheetID"HeaderText="WorkSheetID"ReadOnly="True"SortExpression="WorkSheetID"/><asp:BoundFieldDataField="Origin"HeaderText="Origin"ReadOnly="True"SortExpression="Origin"/><asp:BoundFieldDataField="DealerName"HeaderText="DealerName"ReadOnly="True"SortExpression="DealerName"/><asp:BoundFieldDataField="DateCreated"HeaderText="DateCreated"ReadOnly="True"SortExpression="DateCreated"/><asp:BoundFieldDataField="SalesRep"HeaderText="SalesRep"ReadOnly="True"SortExpression="SalesRep"/><asp:BoundFieldDataField="BkupSalesRep"HeaderText="BkupSalesRep"SortExpression="BkupSalesRep"/><asp:BoundFieldDataField="FirstName"HeaderText="FirstName"ReadOnly="True"SortExpression="FirstName"/><asp:BoundFieldDataField="MiddleName"HeaderText="MiddleName"ReadOnly="True"SortExpression="MiddleName"/><asp:BoundFieldDataField="LastName"HeaderText="LastName"ReadOnly="True"SortExpression="LastName"/><asp:BoundFieldDataField="Address"HeaderText="Address"SortExpression="Address"/><asp:BoundFieldDataField="City"HeaderText="City"SortExpression="City"/><asp:BoundFieldDataField="State"HeaderText="State"SortExpression="State"/><asp:BoundFieldDataField="Zip"HeaderText="Zip"SortExpression="Zip"/><asp:BoundFieldDataField="HomePhone"HeaderText="HomePhone"SortExpression="HomePhone"/><asp:BoundFieldDataField="WorkPhone"HeaderText="WorkPhone"SortExpression="WorkPhone"/><asp:BoundFieldDataField="CellPhone"HeaderText="CellPhone"SortExpression="CellPhone"/><asp:BoundFieldDataField="Email"HeaderText="Email"SortExpression="Email"/><asp:BoundFieldDataField="County"HeaderText="County"SortExpression="County"/><asp:BoundFieldDataField="SalesTax"HeaderText="SalesTax"SortExpression="SalesTax"/><asp:BoundFieldDataField="SS"HeaderText="SS"SortExpression="SS"/><asp:BoundFieldDataField="DOB"HeaderText="DOB"SortExpression="DOB"/><asp:CommandFieldButtonType="Button"CausesValidation="False"ShowEditButton="True"/></Fields></asp:DetailsView><asp:SqlDataSourceID="ApplicantSQLDS"runat="server"ConnectionString="<%$ ConnectionStrings:CustomerInfoConnection %>"SelectCommand="SELECT [WorkSheetID], [Origin], [DealerName], [DateCreated], [SalesRep], [BkupSalesRep], [FirstName], [MiddleName], [LastName], [Address], [City], [State], [Zip], [HomePhone], [WorkPhone], [CellPhone],, [County], [SalesTax], [SS], [DOB] FROM [CustomerTable] WHERE ([WorkSheetID] = @.WorkSheetID)"UpdateCommand="UPDATE [CustomerTable] SET [BkupSalesRep] = @.BkupSalesRep, [Address] = @.Address, [City] = @.City, [State] = @.State, [Zip] = @.Zip, [HomePhone] = @.HomePhone, [WorkPhone] = @.WorkPhone, [CellPhone] = @.CellPhone, = @.Email, [County] = @.County, [SalesTax] = @.SalesTax, [SS] = @.SS, [DOB] = @.DOB WHERE [WorkSheetID] = @.WorkSheetID"><UpdateParameters><asp:ParameterName="BkupSalesRep"Type="String"/><asp:ParameterName="Address"Type="String"/><asp:ParameterName="City"Type="String"/><asp:ParameterName="State"Type="String"/><asp:ParameterName="Zip"Type="String"/><asp:ParameterName="HomePhone"Type="String"/><asp:ParameterName="WorkPhone"Type="String"/><asp:ParameterName="CellPhone"Type="String"/><asp:ParameterName="Email"Type="String"/><asp:ParameterName="County"Type="String"/><asp:ParameterName="SalesTax"Type="String"/><asp:ParameterName="SS"Type="String"/><asp:ParameterName="DOB"Type="String"/></UpdateParameters><SelectParameters><asp:ControlParameterControlID="GridView1"Name="WorkSheetID"PropertyName="SelectedValue"Type="String"/></SelectParameters></asp:SqlDataSource>

|||

Hi:

Is theWorkSheetID column your primary key in your database table? You need primary key to do update.

You may not need put all columns inDataKeyNames. If WorkSheetID is the primary key of the table, you can set

DataKeyNames="WorkSheetID".

|||

I found the problem...I must say its always interesting to learn soemthing new...

Anyway... I found that I had my DataKeyNames set incorrectly... I had it set to all of the fields rather then the 1 primary key WorksheetID.

I am successfully updating... Many thanks again for replying.

T

detailsview control - how to edit record in Sql Server Express

I have null values in some of the fields & when I use EDIT to make changes these changes are not recorded + I receive no error.

Check the condition in the update statement. If it is correct..pls provide the complete code...so that we can give you some suggestion accordingly