Following problem: I have a table UserDetails. This table contains a key to another table (Suppliers) To update the UserDetail, I use a stored procedure which examines whether the details for this user have already been inserted, if not insert them, otherwise
update the existing record:
ALTER PROCEDURE [dbo].[AddOrUpdateUserDetails]
(
@UserId uniqueidentifier,
@Email nvarchar(256),
@Voornaam nvarchar(50),
@LeverancierID uniqueidentifier
)
AS
-- Does this UserId already have a record in UserDetails? If so, UPDATE, else INSERT
IF EXISTS(SELECT 1 FROM JanAntwerpen.UserDetails WHERE UserId = @UserId)
UPDATE JanAntwerpen.UserDetails SET
UserId = @UserId,
Voornaam = @Voornaam,
LeverancierID = @LeverancierID
WHERE UserId = @UserId
ELSE
INSERT INTO JanAntwerpen.UserDetails(UserId, Voornaam, LeverancierID )
VALUES(@UserId, @Voornaam, @LeverancierID)
My page contains a detailsview displaying this users information as follows:
The way the data are downloaded from the server is via a sqlDataSource:
<asp:SqlDataSource ID="dsUserDetails" runat="server"
ConnectionString="<%$ ConnectionStrings:MembersDB %>"
SelectCommand="SELECT dbo.aspnet_Users.UserId, dbo.aspnet_Users.UserName, UserDetails.Voornaam, dbo.aspnet_Membership.Email, dbo.Leverancier.LeverancierID FROM dbo.Leverancier RIGHT OUTER JOIN UserDetails ON dbo.Leverancier.LeverancierID = UserDetails.LeverancierID RIGHT OUTER JOIN dbo.aspnet_Users INNER JOIN dbo.aspnet_Membership ON dbo.aspnet_Users.UserId = dbo.aspnet_Membership.UserId ON UserDetails.UserId = dbo.aspnet_Users.UserId WHERE (dbo.aspnet_Users.UserName = @UserName)"
UpdateCommand="AddOrUpdateUserDetails" UpdateCommandType="StoredProcedure"
onselecting="dsUserDetails_Selecting">
<SelectParameters>
<asp:Parameter Name="UserName" />
</SelectParameters>
<UpdateParameters>
<asp:Parameter Name="UserId"/>
<asp:Parameter Name="Email" Type="String" />
<asp:Parameter Name="Voornaam" Type="String" />
<asp:Parameter Name="LeverancierID" />
</UpdateParameters>
</asp:SqlDataSource>
This works fine, but it is not convenient to show the LeverancierID as it is an ID, instead I want to show the LeverancierName from that same table (Suppliers) which makes more sense. When I show this data, the update command (which handles the insert command
as well) gives errors as the LeverancierID (supplierID) is not provided, but the LeverancierName instead.
I was thinking it might be possible using DataKeyNames, but I don't get it to work!
Yes，I think you can add the column into the DetailsView—
<asp:DetailsView ID="dvYourProfile" runat="server"
AutoGenerateRows="False" DataSourceID="dsUserDetails" DefaultMode="Edit"
BackColor="Gainsboro" onitemupdated="dvYourProfile_ItemUpdated"
Font-Size="Medium" Width="350px" GridLines="Horizontal" DataKeyNames="LeverancierID">
And then you can show your LeverancierName as usual on the DetailsView。
JanAntwerpen
Member
42 Points
27 Posts
sproc not updating
Feb 28, 2012 11:09 AM|LINK
Hello!
Following problem: I have a table UserDetails. This table contains a key to another table (Suppliers) To update the UserDetail, I use a stored procedure which examines whether the details for this user have already been inserted, if not insert them, otherwise update the existing record:
ALTER PROCEDURE [dbo].[AddOrUpdateUserDetails] ( @UserId uniqueidentifier, @Email nvarchar(256), @Voornaam nvarchar(50), @LeverancierID uniqueidentifier ) AS -- Does this UserId already have a record in UserDetails? If so, UPDATE, else INSERT IF EXISTS(SELECT 1 FROM JanAntwerpen.UserDetails WHERE UserId = @UserId) UPDATE JanAntwerpen.UserDetails SET UserId = @UserId, Voornaam = @Voornaam, LeverancierID = @LeverancierID WHERE UserId = @UserId ELSE INSERT INTO JanAntwerpen.UserDetails(UserId, Voornaam, LeverancierID ) VALUES(@UserId, @Voornaam, @LeverancierID)My page contains a detailsview displaying this users information as follows:
<asp:DetailsView ID="dvYourProfile" runat="server" AutoGenerateRows="False" DataSourceID="dsUserDetails" DefaultMode="Edit" BackColor="Gainsboro" onitemupdated="dvYourProfile_ItemUpdated" Font-Size="Medium" Width="350px" GridLines="Horizontal"> <Fields> <asp:TemplateField HeaderText="UserId" SortExpression="UserId"> <ItemTemplate> <asp:Label ID="Label2" runat="server" Text='<%# Bind("UserId") %>'></asp:Label> </ItemTemplate> <EditItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Bind("UserId") %>'></asp:Label> </EditItemTemplate> <InsertItemTemplate> <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("UserId") %>'></asp:TextBox> </InsertItemTemplate> </asp:TemplateField> <asp:BoundField DataField="UserName" HeaderText="UserName" SortExpression="UserName" ReadOnly="True" /> <asp:BoundField DataField="Voornaam" HeaderText="Voornaam" SortExpression="Voornaam" /> <asp:TemplateField HeaderText="Email" SortExpression="Email"> <ItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Bind("Email") %>'></asp:Label> </ItemTemplate> <EditItemTemplate> <asp:TextBox ID="txtEmail" runat="server" Text='<%# Bind("Email") %>'></asp:TextBox> </EditItemTemplate> <InsertItemTemplate> <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("Email") %>'></asp:TextBox> </InsertItemTemplate> </asp:TemplateField> <asp:BoundField DataField="LeverancierID" HeaderText="LeverancierID" SortExpression="LeverancierID" /> <asp:TemplateField ShowHeader="False"> <ItemTemplate> <asp:Button ID="Button1" runat="server" CausesValidation="False" CommandName="Edit" Text="Edit" /> </ItemTemplate> <EditItemTemplate> <asp:Button ID="Button1" runat="server" CausesValidation="False" Text="Update" ValidationGroup="passs" onclick="Button1_Click" /> <asp:Button ID="Button2" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel" /> </EditItemTemplate> </asp:TemplateField> <asp:TemplateField ShowHeader=false> </asp:TemplateField> </Fields></asp:DetailsView>
The way the data are downloaded from the server is via a sqlDataSource:
<asp:SqlDataSource ID="dsUserDetails" runat="server" ConnectionString="<%$ ConnectionStrings:MembersDB %>" SelectCommand="SELECT dbo.aspnet_Users.UserId, dbo.aspnet_Users.UserName, UserDetails.Voornaam, dbo.aspnet_Membership.Email, dbo.Leverancier.LeverancierID FROM dbo.Leverancier RIGHT OUTER JOIN UserDetails ON dbo.Leverancier.LeverancierID = UserDetails.LeverancierID RIGHT OUTER JOIN dbo.aspnet_Users INNER JOIN dbo.aspnet_Membership ON dbo.aspnet_Users.UserId = dbo.aspnet_Membership.UserId ON UserDetails.UserId = dbo.aspnet_Users.UserId WHERE (dbo.aspnet_Users.UserName = @UserName)" UpdateCommand="AddOrUpdateUserDetails" UpdateCommandType="StoredProcedure" onselecting="dsUserDetails_Selecting"> <SelectParameters> <asp:Parameter Name="UserName" /> </SelectParameters> <UpdateParameters> <asp:Parameter Name="UserId"/> <asp:Parameter Name="Email" Type="String" /> <asp:Parameter Name="Voornaam" Type="String" /> <asp:Parameter Name="LeverancierID" /> </UpdateParameters> </asp:SqlDataSource>This works fine, but it is not convenient to show the LeverancierID as it is an ID, instead I want to show the LeverancierName from that same table (Suppliers) which makes more sense. When I show this data, the update command (which handles the insert command as well) gives errors as the LeverancierID (supplierID) is not provided, but the LeverancierName instead.
I was thinking it might be possible using DataKeyNames, but I don't get it to work!
Any advice is welcome!
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: sproc not updating
Mar 01, 2012 12:48 AM|LINK
Yes，I think you can add the column into the DetailsView—
<asp:DetailsView ID="dvYourProfile" runat="server" AutoGenerateRows="False" DataSourceID="dsUserDetails" DefaultMode="Edit" BackColor="Gainsboro" onitemupdated="dvYourProfile_ItemUpdated" Font-Size="Medium" Width="350px" GridLines="Horizontal" DataKeyNames="LeverancierID"> And then you can show your LeverancierName as usual on the DetailsView。Reguards！Kindly correct me if I take you wrongly……