Roger_Hanggi
asked on
Why does Update and Delete not work?
I am trying my first test in ASP.Net, but have a problem with the delete and update commands. The Select and Insert routines work as expected.
1. When I click the Edit link, it opens the editable view as planned. But when I click the Update link, it closes the editable form without complaining, but does not update the data.
2. When I click the Delete link, an error screen appears with the message "Must declare the scalar variable "@id". But the id parameter is declared in the <DeleteParameters> section.
Any suggestions on either problem? The page code is listed below.
Thanks.
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TestConn %>"
DeleteCommand="DELETE Members WHERE id=@id"
InsertCommand="INSERT INTO Members([First_Name], [Last_Name], [Role_Id]) VALUES (@First_Name,@Last_Name,@R ole_Id)"
SelectCommand="SELECT Members.id, Members.First_Name, Members.Last_Name, Roles.Role, Members.Role_Id FROM Members INNER JOIN Roles ON Members.Role_Id = Roles.id ORDER BY Members.id"
UpdateCommand="UPDATE Members SET First_Name=@First_Name, Last_Name=@Last_Name, Role_Id=@Role_ID WHERE id=@id">
<DeleteParameters>
<asp:Parameter Name="id" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="First_Name" />
<asp:Parameter Name="Last_Name" />
<asp:Parameter Name="Role_ID" />
<asp:Parameter Name="id" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="First_Name" />
<asp:Parameter Name="Last_Name" />
<asp:Parameter Name="Role_Id" />
</InsertParameters>
</asp:SqlDataSource>
</div>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:TestConn %>"
SelectCommand="SELECT [id], [Role] FROM [Roles]"></asp:SqlDataSour ce>
<asp:DetailsView ID="DetailsView1" runat="server" AllowPaging="True" AutoGenerateRows="False"
DataSourceID="SqlDataSourc e1" Height="50px" Width="125px">
<Fields>
<asp:BoundField DataField="id" HeaderText="id" InsertVisible="False" ReadOnly="True"
SortExpression="id" />
<asp:BoundField DataField="First_Name" HeaderText="First_Name" SortExpression="First_Name " />
<asp:BoundField DataField="Last_Name" HeaderText="Last_Name" SortExpression="Last_Name" />
<asp:BoundField DataField="Role" HeaderText="Role" SortExpression="Role" />
<asp:TemplateField HeaderText="Role Type" SortExpression="Role_Id">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSourc e2"
DataTextField="Role" DataValueField="id" SelectedValue='<%# Bind("Role_Id") %>'>
</asp:DropDownList>
</EditItemTemplate>
<InsertItemTemplate>
<asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="SqlDataSourc e2"
DataTextField="Role" DataValueField="id" SelectedValue='<%# Bind("Role_Id") %>'>
</asp:DropDownList>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Eval("Role") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="True" />
</Fields>
</asp:DetailsView>
</form>
</body>
</html>
1. When I click the Edit link, it opens the editable view as planned. But when I click the Update link, it closes the editable form without complaining, but does not update the data.
2. When I click the Delete link, an error screen appears with the message "Must declare the scalar variable "@id". But the id parameter is declared in the <DeleteParameters> section.
Any suggestions on either problem? The page code is listed below.
Thanks.
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb"
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TestConn
DeleteCommand="DELETE Members WHERE id=@id"
InsertCommand="INSERT INTO Members([First_Name], [Last_Name], [Role_Id]) VALUES (@First_Name,@Last_Name,@R
SelectCommand="SELECT Members.id, Members.First_Name, Members.Last_Name, Roles.Role, Members.Role_Id FROM Members INNER JOIN Roles ON Members.Role_Id = Roles.id ORDER BY Members.id"
UpdateCommand="UPDATE Members SET First_Name=@First_Name, Last_Name=@Last_Name, Role_Id=@Role_ID WHERE id=@id">
<DeleteParameters>
<asp:Parameter Name="id" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="First_Name" />
<asp:Parameter Name="Last_Name" />
<asp:Parameter Name="Role_ID" />
<asp:Parameter Name="id" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="First_Name" />
<asp:Parameter Name="Last_Name" />
<asp:Parameter Name="Role_Id" />
</InsertParameters>
</asp:SqlDataSource>
</div>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:TestConn
SelectCommand="SELECT [id], [Role] FROM [Roles]"></asp:SqlDataSour
<asp:DetailsView ID="DetailsView1" runat="server" AllowPaging="True" AutoGenerateRows="False"
DataSourceID="SqlDataSourc
<Fields>
<asp:BoundField DataField="id" HeaderText="id" InsertVisible="False" ReadOnly="True"
SortExpression="id" />
<asp:BoundField DataField="First_Name" HeaderText="First_Name" SortExpression="First_Name
<asp:BoundField DataField="Last_Name" HeaderText="Last_Name" SortExpression="Last_Name"
<asp:BoundField DataField="Role" HeaderText="Role" SortExpression="Role" />
<asp:TemplateField HeaderText="Role Type" SortExpression="Role_Id">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSourc
DataTextField="Role" DataValueField="id" SelectedValue='<%# Bind("Role_Id") %>'>
</asp:DropDownList>
</EditItemTemplate>
<InsertItemTemplate>
<asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="SqlDataSourc
DataTextField="Role" DataValueField="id" SelectedValue='<%# Bind("Role_Id") %>'>
</asp:DropDownList>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Eval("Role") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="True" />
</Fields>
</asp:DetailsView>
</form>
</body>
</html>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I found the answer in searching through MS documents. I had to set the ConflictDetection property for the SQLDataSource1 to CompareAllValues, and also the OldValuesParameterFormatSt ring property to old_{0}. Then I could change the command strings for the update and delete commands to:
DeleteCommand="DELETE Members WHERE id=@old_id"
UpdateCommand="UPDATE Members SET First_Name=@First_Name, ... WHERE id=@old_id"
Now it works. But TheLearnedOne is correct that I could delete the parameters for all the commands.
DeleteCommand="DELETE Members WHERE id=@old_id"
UpdateCommand="UPDATE Members SET First_Name=@First_Name, ... WHERE id=@old_id"
Now it works. But TheLearnedOne is correct that I could delete the parameters for all the commands.
ASKER