p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   ASP.NET 3.5 Professionals (http://p2p.wrox.com/forumdisplay.php?f=352)
-   -   Pass parameters to stored procedure from GridView (http://p2p.wrox.com/showthread.php?t=72640)

Dmitriy February 9th, 2009 09:47 AM

Pass parameters to stored procedure from GridView
 
Hi,

I have a GridVeiw control on my WEB page which is populated from the "standard" SQL 2005 membership database. This control has the DELETE column to delete selected user. I am using "dbo.aspnet_Users_DeleteUser" stored procedure to do this action. This procedure has the "signature":
Code:

ALTERPROCEDURE [dbo].[aspnet_Users_DeleteUser]
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@TablesToDeleteFrom int,
@NumTablesDeletedFrom intOUTPUT

.........
........

and returns an Integer value.

When I try to delete a row from the grid, I am getting error:

Procedure or function aspnet_Users_DeleteUser has too many arguments specified.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Procedure or function aspnet_Users_DeleteUser has too many arguments specified.
My question is: how to specify and pass parameters to the stored procedure in my case?

Thanks in advance

-Dmitriy

alliancejhall February 9th, 2009 10:35 AM

Please post more code...
 
Can you post the code you are using in your codebehind and aspx page?

Dmitriy February 9th, 2009 10:49 AM

Pass parameters to stored procedure from GridView
 
This is my Code behind:

Code:

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml.Linq;
namespace LibraryFileUpload.Restricted
{
publicpartialclassUsersList : System.Web.UI.Page
{
privatestring sErrorMessage = null;
protectedvoid Page_Load(object sender, EventArgs e)
{
}
protectedvoid usersGridView_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
try
{
Session[
"SelectedUserName"] = usersGridView.Rows[e.RowIndex].Cells[1].Text;
}
catch (Exception en)
{
sErrorMessage = en.Message;
Session[
"Message"] = "ERROR inside the usersGridView_RowDeleting() function. " + sErrorMessage + " Contact customer support,please. ";
Response.Redirect(
"ErrorForm.aspx", false);
}
}
protectedvoid LoginStatus2_LoggedOut(object sender, EventArgs e)
{
Session.Clear();
Session.Abandon();
}
protectedvoid SqlDataSource_Users_Deleting(object sender, SqlDataSourceCommandEventArgs e)
{
if (Session["SelectedUserName"] != null)
{
e.Command.Parameters[
"@ApplicationName"].Value = "/";
e.Command.Parameters[
"@UserName"].Value = (String)Session["SelectedUserName"];
e.Command.Parameters[
"@TablesToDeleteFrom"].Value = 5;
e.Command.Parameters[
"@NumTablesDeletedFrom"].Value = 0;
}
}
protectedvoid usersGridView_RowDeleted(object sender, GridViewDeletedEventArgs e)
{
if (e.Exception != null)
{
e.ExceptionHandled =
true;
sErrorMessage = e.Exception.Message;
Session[
"Message"] = "ERROR inside the usersGridView_RowDeleted() function. " + sErrorMessage + " Contact customer support,please. ";
Response.Redirect(
"ErrorForm.aspx", false);
}
}
}
}

And this is my form:

Code:

<%@PageLanguage="C#"MasterPageFile="~/Restricted/LibraryFileUpload4.Master"AutoEventWireup="true"CodeBehind="UsersList.aspx.cs"Inherits="LibraryFileUpload.Restricted.UsersList"Title="Untitled Page" %>
<asp:ContentID="Content1"ContentPlaceHolderID="head"runat="server">
<styletype="text/css">
.styleTitle
{
font-weight: bold;
text-decoration: underline;
height: 80px;
}
.style7
{
font-weight: bold;
text-decoration: underline;
height: 43px;
}
.styleWaitMessage
{
font-weight: bold;
color: Blue;
}
</style>
</
asp:Content>
<
asp:ContentID="Content2"ContentPlaceHolderID="ContentPlaceHolder1"runat="server">
<%--<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>--
%>
<tablestyle="width: 100%;">
<tr>
<tdalign="center">
<tablestyle="width: 100%;">
<trvalign="top">
<tdalign="center"class="style7">
<fontcolor="#336699"size="4">Users List Screen</font>
</td>
</tr>
<tr>
<tdalign="center">
<br/>
<asp:UpdateProgressID="UpdateProgress1"runat="server">
<ProgressTemplate>
<divclass="styleWaitMessage">
<imgsrc="../Images/spinner3-bluey.gif"/> Processing ... Wait, please.
</div>
</ProgressTemplate>
</asp:UpdateProgress>
</td>
</tr>
<trclass="style6">
<tdalign="center">
<asp:GridViewID="usersGridView"runat="server"AllowPaging="True"AllowSorting="True"BorderStyle="Double"BorderWidth="2px"CellPadding="4"CellSpacing="2"EnableSortingAndPagingCallbacks="True"Font-Bold="True"ForeColor="#333333"Width="346px"AutoGenerateColumns="False"DataSourceID="SqlDataSource_Users"onrowdeleting="usersGridView_RowDeleting"DataKeyNames="UserName"onrowdeleted="usersGridView_RowDeleted">
<FooterStyleBackColor="#5D7B9D"Font-Bold="True"ForeColor="White"/>
<RowStyleBackColor="#F7F6F3"ForeColor="#333333"/>
<Columns>
<asp:CommandFieldButtonType="Button"ShowDeleteButton="True"/>
<asp:BoundFieldDataField="UserName"HeaderText="UserName"SortExpression="UserName"/>
<asp:BoundFieldDataField="Email"HeaderText="Email"SortExpression="Email"/>
<asp:BoundFieldDataField="CreateDate"HeaderText="CreateDate"SortExpression="CreateDate"/>
</Columns>
<PagerStyleBackColor="#284775"ForeColor="White"HorizontalAlign="Center"/>
<SelectedRowStyleBackColor="#E2DED6"Font-Bold="True"ForeColor="#333333"/>
<HeaderStyleBackColor="#5D7B9D"Font-Bold="True"ForeColor="White"/>
<EditRowStyleBackColor="#999999"/>
<AlternatingRowStyleBackColor="White"ForeColor="#284775"/>
</asp:GridView>
<asp:SqlDataSourceID="SqlDataSource_Users"runat="server"ConnectionString="<%$ ConnectionStrings:MyFileUploadDBConnectionString %>"SelectCommand="SELECT aspnet_Users.UserName, aspnet_Membership.Email, aspnet_Membership.CreateDate FROM aspnet_Users INNER JOIN aspnet_Membership ON aspnet_Users.UserId = aspnet_Membership.UserId"DeleteCommandType="StoredProcedure"DeleteCommand="dbo.aspnet_Users_DeleteUser"ondeleting="SqlDataSource_Users_Deleting">
<DeleteParameters><asp:ParameterDirection="ReturnValue"Name="RETURN_VALUE"Type="Int32"DefaultValue=""/>
<asp:ParameterName="ApplicationName"Type="String"DefaultValue=""/>
<asp:ParameterDefaultValue=""Name="UserName"Type="String"></asp:Parameter>
<asp:ParameterDefaultValue=""Name="TablesToDeleteFrom"Type="Int32"/>
<asp:ParameterDefaultValue=""Direction="InputOutput"Name="NumTablesDeletedFrom"Type="Int32"/>
</DeleteParameters>
</asp:SqlDataSource>
</td>
</tr>
</table></td>
<tdalign="right"valign="top"width="50px">
<asp:LoginStatusID="LoginStatus2"runat="server"Height="31px"ToolTip="User Login/Logout"Width="46px"onloggedout="LoginStatus2_LoggedOut"/>
</td>
</tr>
</table>
<%--</ContentTemplate>
</asp:UpdatePanel> --
%>
</asp:Content>

Should I specify the returned value as well?

gbianchi February 9th, 2009 11:01 AM

Hi there..

I can see why you have the error. I don't understand why you are doing such a mess with a direct delete (or there is something I'm missing?)

Code:

<DeleteParameters><asp:ParameterDirection="ReturnValue"Name="RETURN_VALUE"Type="Int32"DefaultValue=""/>
<asp:ParameterName="ApplicationName"Type="String"DefaultValue=""/>
<asp:ParameterDefaultValue=""Name="UserName"Type="String"></asp:Parameter>
<asp:ParameterDefaultValue=""Name="TablesToDeleteFrom"Type="Int32"/>
<asp:ParameterDefaultValue=""Direction="InputOutput"Name="NumTablesDeletedFrom"Type="Int32"/>
</DeleteParameters>

Or I'm missing something (and if that happens I'm terrible sorry) or you are passing 5 parameters to the SP?

alliancejhall February 9th, 2009 11:04 AM

Noticed that too...
 
I was just about to post the same thing...

Looks like you are duplicating your output... remove this line right here and then try
<asp:ParameterDirection="ReturnValue"Name="RETURN_ VALUE"Type="Int32"DefaultValue=""/>

Dmitriy February 9th, 2009 11:17 AM

My mistake...
 
Gentlemen,
Thank you very much. I do not know how I included this returned parameter on the form? Thanks for your input again!

-Dmitriy [:(!]

Lee Dumond February 9th, 2009 11:19 AM

I am just really curious as to why one wouldn't just use the methods from the Membership classes to do this.

Dmitriy February 9th, 2009 11:27 AM

Yes,
I've tryed to use code like this:

Code:

protectedvoid usersGridView_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
try
{
Membership.DeleteUser(usersGridView.Rows[e.RowIndex].Cells[1].Text);
Response.Redirect("Administration.aspx", false);
}
catch (Exception en)
{
sErrorMessage = en.Message;
Session[
"Message"] = "ERROR inside the usersGridView_RowDeleting() function. " + sErrorMessage + " Contact customer support,please. ";
Response.Redirect(
"ErrorForm.aspx", false);
}
}



But it is giving me an error and does not update the grid?

alliancejhall February 9th, 2009 11:50 AM

Well...
 
what's the error it's giving you?

Lee Dumond February 9th, 2009 11:59 AM

Yes, what is the error?

And, by the way, GridViews don't update themselves. You have to rebind them when the underlying data changes.


All times are GMT -4. The time now is 04:25 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.