Pass parameters to stored procedure from GridView

Dmitriy · #1 (**permalink**) February 9th, 2009, 09:47 AM

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 · #2 (**permalink**) February 9th, 2009, 10:35 AM

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

Dmitriy · #3 (**permalink**) February 9th, 2009, 10:49 AM

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 · #4 (**permalink**) 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 · #5 (**permalink**) February 9th, 2009, 11:04 AM

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 · #6 (**permalink**) February 9th, 2009, 11:17 AM

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 · #7 (**permalink**) 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 · #8 (**permalink**) 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 · #9 (**permalink**) February 9th, 2009, 11:50 AM

what's the error it's giving you?

Lee Dumond · #10 (**permalink**) 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.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
how to pass value to stored procedure?	hertendreef	SQL Server 2005	3	February 23rd, 2007 11:43 AM
How to pass a parameter to a stored procedure?	MaxGay2	VB.NET 2002/2003 Basics	1	November 8th, 2006 02:48 PM
stored procedure with parameters	Danmalam	VB Databases Basics	0	February 27th, 2005 03:52 PM
How will I pass stored procedure in an asp	shoakat	SQL Server 2000	1	July 15th, 2004 10:28 PM
SQL Pass though parameters to stored procedures	blinton25	SQL Server 2000	3	March 29th, 2004 02:49 PM