I´m having problem to delete/update using stored procedure with gridview. Actually I made an update and delete work but just by itself not togheter. I made update ONLY works If I dont pass any fields from gridview to datakeynames and the delete show the following error:
Procedure 'sp03_Delete_Usuario' expects parameter '@AN001_CDUSUARIO', which was not supplied.
But the delete ONLY works if I pass all the fields form gridview to datakeynames. So the update stops to work. The code is:
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Usuario.aspx.vb" Inherits="Usuario" %> <!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:Label ID="Label1" runat="server" Font-Bold="True" Font-Size="X-Large" Text="Cadastro de Usuários"></asp:Label><br /> <br /> <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" BackColor="White" BorderColor="#999999" BorderStyle="None" BorderWidth="1px" CellPadding="3" DataSourceID="SqlDataSourceUsuarios" GridLines="Vertical"> <FooterStyle BackColor="#CCCCCC" ForeColor="Black" /> <Columns> <asp:CommandField CancelText="Cancelar" DeleteText="Deletar" EditText="Editar" ShowDeleteButton="True" ShowEditButton="True" /> <asp:BoundField DataField="AS001_NOME" HeaderText="Nome" SortExpression="AS001_NOME" /> <asp:BoundField DataField="AS001_RAMAL" HeaderText="Ramal" SortExpression="AS001_RAMAL" /> <asp:BoundField DataField="AS001_EMAIL" HeaderText="E-mail" SortExpression="AS001_EMAIL" /> <asp:BoundField DataField="AS001_LOGIN" HeaderText="Login" SortExpression="AS001_LOGIN" /> <asp:BoundField DataField="AS001_SENHA" HeaderText="Senha" SortExpression="AS001_SENHA" /> <asp:BoundField DataField="AD001_DTNASCIMENTO" HeaderText="Data Nascimento" SortExpression="AD001_DTNASCIMENTO" /> <asp:BoundField DataField="AS001_ADMINISTRADOR" HeaderText="Administrador" SortExpression="AS001_ADMINISTRADOR" /> <asp:BoundField DataField="AN001_CDUSUARIO" HeaderText="AN001_CDUSUARIO" InsertVisible="False" SortExpression="AN001_CDUSUARIO" /> <asp:BoundField DataField="AS003_CARGO" HeaderText="Cargo" SortExpression="AS003_CARGO" /> <asp:TemplateField HeaderText="Setor" SortExpression="AS004_NOME"> <EditItemTemplate> <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSourceSetor" DataTextField="AS004_NOME" DataValueField="AN005_CDSETOR" SelectedValue='<%# Bind("AN005_CDSETOR") %>' Width="152px"> </asp:DropDownList><asp:SqlDataSource ID="SqlDataSourceSetor" runat="server" ConnectionString="<%$ ConnectionStrings:BdAtConnectionString %>" SelectCommand="SELECT [AN005_CDSETOR], [AS004_NOME] FROM [T005_SETOR]"></asp:SqlDataSource> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Bind("AS004_NOME") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> </Columns> <RowStyle BackColor="#EEEEEE" ForeColor="Black" /> <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" /> <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" /> <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" /> <AlternatingRowStyle BackColor="Gainsboro" /> </asp:GridView> <asp:SqlDataSource ID="SqlDataSourceUsuarios" runat="server" ConnectionString="<%$ ConnectionStrings:BdAtConnectionString %>" SelectCommand="SELECT T001_USUARIO.AN001_CDUSUARIO, T001_USUARIO.AS001_NOME, T001_USUARIO.AS001_RAMAL, T001_USUARIO.AS001_EMAIL, T001_USUARIO.AS001_LOGIN, T001_USUARIO.AS001_SENHA, T001_USUARIO.AD001_DTCADASTRO, T001_USUARIO.AD001_DTEXCLUSAO, T001_USUARIO.AD001_DTNASCIMENTO, T001_USUARIO.AS001_ADMINISTRADOR, T003_FUNCIONARIO.AS003_CARGO, T005_SETOR.AN005_CDSETOR, T005_SETOR.AS004_NOME FROM T001_USUARIO INNER JOIN T003_FUNCIONARIO ON T001_USUARIO.AN001_CDUSUARIO = T003_FUNCIONARIO.AN001_CDUSUARIO INNER JOIN T005_SETOR ON T003_FUNCIONARIO.AN005_CDSETOR = T005_SETOR.AN005_CDSETOR AND T001_USUARIO.AD001_DTEXCLUSAO IS NULL" UpdateCommand="sp02_Update_Usuario" UpdateCommandType="StoredProcedure" InsertCommand="sp01_Insert_Usuario" InsertCommandType="StoredProcedure" DeleteCommand="sp03_Delete_Usuario" DeleteCommandType="StoredProcedure"> <InsertParameters> <asp:Parameter Name="AS001_NOME" Type="String" /> <asp:Parameter Name="AS001_RAMAL" Type="String" /> <asp:Parameter Name="AS001_EMAIL" Type="String" /> <asp:Parameter Name="AS001_LOGIN" Type="String" /> <asp:Parameter Name="AS001_SENHA" Type="String" /> <asp:Parameter Name="AD001_DTCADASTRO" Type="DateTime" /> <asp:Parameter Name="AD001_DTEXCLUSAO" Type="DateTime" /> <asp:Parameter Name="AD001_DTNASCIMENTO" Type="DateTime" /> <asp:Parameter Name="AS001_ADMINISTRADOR" Type="String" /> <asp:Parameter Name="AS003_CARGO" Type="String" /> <asp:Parameter Name="AN005_CDSETOR" Type="Int32" /> </InsertParameters> <DeleteParameters> <%-- <asp:ControlParameter ControlID="GridView1" Name="AN001_CDUSUARIO" PropertyName ="SelectedValue" /> <asp:ControlParameter ControlID="GridView1" Name="AS001_NOME" PropertyName="SelectedValue" /> <asp:ControlParameter ControlID="GridView1" Name="AS001_RAMAL" PropertyName="SelectedValue" /> <asp:ControlParameter ControlID="GridView1" Name="AS001_EMAIL" PropertyName="SelectedValue" /> <asp:ControlParameter ControlID="GridView1" Name="AS001_LOGIN" PropertyName="SelectedValue" /> <asp:ControlParameter ControlID="GridView1" Name="AS001_SENHA" PropertyName="SelectedValue" /> <asp:ControlParameter ControlID="GridView1" Name="AD001_DTNASCIMENTO" PropertyName="SelectedValue" /> <asp:ControlParameter ControlID="GridView1" Name="AS001_ADMINISTRADOR" PropertyName="SelectedValue" /> <asp:ControlParameter ControlID="GridView1" Name="AS003_CARGO" PropertyName="SelectedValue" /> <asp:ControlParameter ControlID="GridView1" Name="AN005_CDSETOR" PropertyName="SelectedValue" /> --%> <asp:Parameter Name="AN001_CDUSUARIO" Type="Int32" /> <asp:Parameter Name="AS001_NOME" Type="String" /> <asp:Parameter Name="AS001_RAMAL" Type="String" /> <asp:Parameter Name="AS001_EMAIL" Type="String" /> <asp:Parameter Name="AS001_LOGIN" Type="String" /> <asp:Parameter Name="AS001_SENHA" Type="String" /> <asp:Parameter Name="AD001_DTNASCIMENTO" Type="DateTime" /> <asp:Parameter Name="AS001_ADMINISTRADOR" Type="String" /> <asp:Parameter Name="AS003_CARGO" Type="String" /> <asp:Parameter Name="AN005_CDSETOR" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="AN001_CDUSUARIO" Type="Int32" /> <asp:Parameter Name="AS001_NOME" Type="String" /> <asp:Parameter Name="AS001_RAMAL" Type="String" /> <asp:Parameter Name="AS001_EMAIL" Type="String" /> <asp:Parameter Name="AS001_LOGIN" Type="String" /> <asp:Parameter Name="AS001_SENHA" Type="String" /> <asp:Parameter Name="AD001_DTNASCIMENTO" Type="DateTime" /> <asp:Parameter Name="AS001_ADMINISTRADOR" Type="String" /> <asp:Parameter Name="AS003_CARGO" Type="String" /> <asp:Parameter Name="AN005_CDSETOR" Type="Int32" /> </UpdateParameters> </asp:SqlDataSource> <br /> <asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" BackColor="White" BorderColor="#999999" BorderStyle="Solid" BorderWidth="1px" CellPadding="3" DataSourceID="SqlDataSourceUsuarios" ForeColor="Black" GridLines="Vertical" Height="50px" Width="125px" DefaultMode="Insert" DataKeyNames="AN001_CDUSUARIO,AS001_NOME,AS001_RAMAL,AS001_EMAIL,AS001_LOGIN,AS001_SENHA,AD001_DTCADASTRO,AD001_DTEXCLUSAO,AD001_DTNASCIMENTO,AS001_ADMINISTRADOR,AS003_CARGO,AN005_CDSETOR,AS004_NOME"> <FooterStyle BackColor="#CCCCCC" /> <EditRowStyle BackColor="#000099" Font-Bold="True" ForeColor="White" /> <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" /> <Fields> <asp:BoundField DataField="AS001_NOME" HeaderText="Nome" SortExpression="AS001_NOME" /> <asp:BoundField DataField="AS001_RAMAL" HeaderText="Ramal" SortExpression="AS001_RAMAL" /> <asp:BoundField DataField="AS001_EMAIL" HeaderText="E-mail" SortExpression="AS001_EMAIL" /> <asp:BoundField DataField="AS001_LOGIN" HeaderText="Login" SortExpression="AS001_LOGIN" /> <asp:BoundField DataField="AS001_SENHA" HeaderText="Senha" SortExpression="AS001_SENHA" /> <asp:BoundField DataField="AD001_DTNASCIMENTO" HeaderText="Data Nascimento" SortExpression="AD001_DTNASCIMENTO" /> <asp:BoundField DataField="AS001_ADMINISTRADOR" HeaderText="Administrador" SortExpression="AS001_ADMINISTRADOR" /> <asp:BoundField DataField="AS003_CARGO" HeaderText="Cargo" SortExpression="AS003_CARGO" /> <asp:TemplateField HeaderText="Setor" SortExpression="AS004_NOME"> <EditItemTemplate> <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("AS004_NOME") %>'></asp:TextBox> </EditItemTemplate> <InsertItemTemplate> <asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="SqlDataSourceSetorInsert" DataTextField="AS004_NOME" DataValueField="AN005_CDSETOR" SelectedValue='<%# Bind("AN005_CDSETOR") %>' Width="208px"> </asp:DropDownList><asp:SqlDataSource ID="SqlDataSourceSetorInsert" runat="server" ConnectionString="<%$ ConnectionStrings:BdAtConnectionString %>" SelectCommand="SELECT [AN005_CDSETOR], [AS004_NOME] FROM [T005_SETOR]"> </asp:SqlDataSource> </InsertItemTemplate> <ItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Bind("AS004_NOME") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:CommandField ShowInsertButton="True" /> </Fields> <HeaderStyle BackColor="Black" Font-Bold="True" ForeColor="White" /> <AlternatingRowStyle BackColor="#CCCCCC" /> </asp:DetailsView> <br /> <br /> <asp:Button ID="Button2" runat="server" Text="Voltar" Width="120px" /> <asp:Button ID="Button1" runat="server" Text="Button" /> <asp:Button ID="Button3" runat="server" Text="Button" /></div> </form> </body> </html>
Procedures:
CREATE PROCEDURE dbo.sp02_Update_Usuario
(@AN001_CDUSUARIO INTEGER,
@AS001_NOME VARCHAR(50),
@AS001_RAMAL VARCHAR(5),
@AS001_EMAIL VARCHAR(50),
@AS001_LOGIN VARCHAR(20),
@AS001_SENHA VARCHAR(20),
@AD001_DTNASCIMENTO DATETIME,
@AS001_ADMINISTRADOR VARCHAR(1),
@AS003_CARGO VARCHAR(50),
@AN005_CDSETOR INTEGER )AS
BEGIN
UPDATE T001_USUARIO SET AS001_NOME = @AS001_NOME, AS001_RAMAL = @AS001_RAMAL, AS001_EMAIL = @AS001_EMAIL, AS001_LOGIN = @AS001_LOGIN,
AS001_SENHA = @AS001_SENHA, AD001_DTNASCIMENTO = @AD001_DTNASCIMENTO,
AS001_ADMINISTRADOR = @AS001_ADMINISTRADOR WHERE AN001_CDUSUARIO = @AN001_CDUSUARIO;
UPDATE T003_FUNCIONARIO SET AS003_CARGO = @AS003_CARGO, AN005_CDSETOR = @AN005_CDSETOR WHERE AN001_CDUSUARIO = @AN001_CDUSUARIO;
END
GO
CREATE PROCEDURE dbo.sp03_Delete_Usuario
(@AN001_CDUSUARIO INTEGER,
@AS001_NOME VARCHAR(50),
@AS001_RAMAL VARCHAR(5),
@AS001_EMAIL VARCHAR(50),
@AS001_LOGIN VARCHAR(20),
@AS001_SENHA VARCHAR(20),
@AD001_DTNASCIMENTO DATETIME,
@AS001_ADMINISTRADOR VARCHAR(1),
@AS003_CARGO VARCHAR(50),
@AN005_CDSETOR INTEGER )AS
BEGIN
UPDATE T001_USUARIO SET AD001_DTEXCLUSAO = GETDATE() WHERE AN001_CDUSUARIO = @AN001_CDUSUARIO;
END
GO
![]() |
0 |
![]() |
O solved.. I had to dynamically pass the parameters for delete and for update like this:
Protected Sub LinkButton1_Click(ByVal sender As Object, ByVal e As System.EventArgs)Dim parametros As String() = {"AN001_CDUSUARIO", "AS001_NOME", "AS001_RAMAL", "AS001_EMAIL", "AS001_LOGIN", "AS001_SENHA", "AD001_DTNASCIMENTO", "AS001_ADMINISTRADOR", "AS003_CARGO", "AN005_CDSETOR"}
GridView1.DataKeyNames = parametros
End SubProtected Sub LinkButton1_Click1(ByVal sender As Object, ByVal e As System.EventArgs)Dim parametros As String() = {}GridView1.DataKeyNames = parametros
End Sub
thanks
![]() |
0 |
![]() |