Solved

Parameter problem with Gridview updating using SQL Datasource & Stored Procedures

Posted on 2008-10-20
3
4,262 Views
Last Modified: 2013-11-08
Hi,

I am trying to use the gridview and datasource control to view and edit database information. However, I am having a problem when trying to update the information.   The datasource uses two different stored procedures, as you can see in the code.  

The problem that seems to be thrown is once I have clicked the update button, it shows the following error:
"Microsoft JScript runtime error: Sys.WebForms.PageRequestManagerServerErrorException: Procedure or function spUpdateRecipients has too many arguments specified."

The update stored procedure has three parameters:
@active int
@configNum int
@user varchar(50)

The datasource was set up using the IDE not in code, but it only has two parameters put in for some reasons.  I thought this might have been the issue, but upon adding in the third it still gives the same error...

<UpdateParameters>
                            <asp:Parameter Name="active" Type="Int32" />
                            <asp:Parameter Name="configNum" Type="Int32" />
<asp:Parameter Name="user" Type="String" />
</UpdateParameters>


If anyone could give me some ideas of how to fix this it would be great.

Thanks!
<%@ Page Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="ModifyRecipients.aspx.cs" Inherits="ReportRecipients2008.ModifyRecipients" Title="Untitled Page" %>
<%@ Register assembly="AjaxControlToolkit" namespace="AjaxControlToolkit" tagprefix="cc1" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">
 
 
<div style="width:100%">
            <asp:UpdatePanel ID="UpdatePanel1" runat="server">
                <ContentTemplate>
                    <asp:RadioButtonList ID="RadioButtonList1" runat="server" AutoPostBack="True" OnSelectedIndexChanged="RadioButtonList1_SelectedIndexChanged" RepeatDirection="Horizontal">
                        <asp:ListItem Value="0">Study Number</asp:ListItem>
                        <asp:ListItem Value="1">Protocol ID</asp:ListItem>
                    </asp:RadioButtonList>
                    <asp:MultiView ID="MultiView1" runat="server">
                        <asp:View ID="vwStudyNum" runat="server">
                            <asp:SqlDataSource ID="dsCountries" runat="server" 
                                ConnectionString="<%$ ConnectionStrings:GPMConnectionString %>" 
                                SelectCommand="SELECT DISTINCT [CountryDescription] FROM [Countries]">
                            </asp:SqlDataSource>
                            <cc1:TextBoxWatermarkExtender ID="TextBoxWatermarkExtender1" runat="server" 
                                TargetControlID="tbStudyNumber1" WatermarkText="Enter Study Number"></cc1:TextBoxWatermarkExtender>
                            <asp:DropDownList ID="ddlCountries" runat="server" AppendDataBoundItems="True" 
                                DataSourceID="dsCountries" DataTextField="CountryDescription" 
                                DataValueField="CountryDescription">
                                <asp:ListItem>All Countries / None</asp:ListItem>
                            </asp:DropDownList>                            
                            <asp:TextBox ID="tbStudyNumber1" runat="server"></asp:TextBox>
                            <asp:Button ID="btnSearch" runat="server" OnClick="btnSearch_Click" 
                                Text="Search" />
                            <br /><br />
                            <div>
                            </div>
                        </asp:View>
                        <asp:View ID="vwProtocolNum" runat="server">
                            <asp:TextBox ID="tbProtocolId2" runat="server" ReadOnly="True"></asp:TextBox>
                            <asp:Label ID="Label4" runat="server" Text="Enter protocol ID"></asp:Label>
                        </asp:View>
                    </asp:MultiView>
                    <br />
                    <asp:GridView  BorderWidth="0px" ID="grdvUserInfo" runat="server" 
                        DataSourceID="dsRecips" BackColor="White" CellPadding="4" 
                        ForeColor="Black" GridLines="Vertical" Width="500px" 
                        AutoGenerateColumns="False" onrowdatabound="grdvUserInfo_RowDataBound" 
                        DataKeyNames="Recip,Config_Num" EmptyDataText="No data returned">
                        <FooterStyle BackColor="#CCCC99" />
                        <RowStyle BackColor="#CCCCCC" ForeColor="Black" BorderColor="Black" BorderStyle="Solid" />
                        <Columns>
                            <asp:CommandField ButtonType="Image" CancelImageUrl="~/Images/cancel.png" 
                                EditImageUrl="~/Images/edit.png" HeaderText="Modify" ShowCancelButton="False" 
                                ShowEditButton="True" UpdateImageUrl="~/Images/save.png">
                                <ControlStyle Height="16px" Width="16px" />
                            </asp:CommandField>
                            <asp:BoundField DataField="Recip" HeaderText="Recip" ReadOnly="True" 
                                SortExpression="Recip" />
                            <asp:TemplateField HeaderText="Active" SortExpression="Active">
                                <EditItemTemplate>
                                    <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("Active") %>'></asp:TextBox>
                                </EditItemTemplate>
                                <ItemTemplate>
                                    <asp:Label ID="Label1" runat="server" Text='<%# Bind("Active") %>'></asp:Label>
                                </ItemTemplate>
                            </asp:TemplateField>
                            <asp:BoundField DataField="Config_Num" HeaderText="Config_Num" SortExpression="Config_Num" 
                                Visible="True" ReadOnly="True" />
                        </Columns>
                        <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
                        <SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
                        <HeaderStyle BackColor="DimGray" Font-Bold="True" ForeColor="White" />
                        <AlternatingRowStyle BackColor="#E0E0E0" />
                    </asp:GridView>
                    <asp:SqlDataSource ID="dsRecips" runat="server" 
                        ConnectionString="<%$ ConnectionStrings:ReportingConnectionString %>" 
                        SelectCommand="spGetRecipientsByJobIDCountry" 
                        SelectCommandType="StoredProcedure" UpdateCommand="spUpdateRecipients" 
                        UpdateCommandType="StoredProcedure">
                        <SelectParameters>
                            <asp:Parameter Name="country" Type="String" />
                            <asp:Parameter Name="jobId" Type="Int32" />
                        </SelectParameters>
                        <UpdateParameters>
                            <asp:Parameter Name="active" Type="Int32" />
                            <asp:Parameter Name="configNum" Type="Int32" />
                        </UpdateParameters>
                    </asp:SqlDataSource>
                </ContentTemplate>
            </asp:UpdatePanel>
            <asp:UpdateProgress ID="UpdateProgress1" runat="server" AssociatedUpdatePanelID="UpdatePanel1"
                DisplayAfter="300">
                <ProgressTemplate>                    
                    <div id="loadScreen">
                        <div id="loadScreenDetail">
                            <div id="loadScreenDetailMessage">                    
	                            <img height="15" src="Images/ajax-loader.gif" alt="loading image"/>
                                Updating, please wait...
                            </div>   
                         </div>
                    </div>
                </ProgressTemplate>
            </asp:UpdateProgress>
        </div>
 
</asp:Content>

Open in new window

0
Comment
Question by:Artform04
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 13

Expert Comment

by:crazyman
ID: 22758268
a. can you post the header part of your sproc.

b.You say you added the third param yet i dont see it in the code you posted.

c.Can you post your code behind

I notice nothing is binding to your 'configNum'  and  'user' params....
only 'active'
<asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("Active") %>'></asp:TextBox>
unless your doing this in the code behind..
0
 
LVL 1

Author Comment

by:Artform04
ID: 22758519
Hey thanks for the reply.  In response to what you said...

a) here is the SPROC header:
USE [Reporting]
GO
/****** Object:  StoredProcedure [dbo].[spUpdateRecipients]    Script Date: 10/20/2008 15:50:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO


ALTER PROCEDURE [dbo].[spUpdateRecipients]
@active int,
@configNum int,
@user varchar(50)

b) In the original question, I showed you waht I put, however it didnt make any difference so I reverted it back to the IDE generated version (two params)
c) The code file is attached below.
d) As regards the binding, I converted the "Active" field to a template field as I jsut wanted to check it was using "bind" and not "eval".  The other items are boundfields which I believe are automatically bound with the datafield property e.g. DataField="Recip"

Thanks, hope the material provided can help you help me...
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 ReportRecipients2008
{
    public partial class ModifyRecipients : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {        
            if (!Page.IsPostBack)
            {
                RadioButtonList1.SelectedIndex = 0;
                MultiView1.ActiveViewIndex = RadioButtonList1.SelectedIndex;
            }
        }
 
        protected void RadioButtonList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            MultiView1.ActiveViewIndex = RadioButtonList1.SelectedIndex;
            grdvUserInfo.Visible = false;
        }
            
        protected void btnSearch_Click(object sender, EventArgs e)
        {
            //set the parameter value for the select
            dsRecips.SelectParameters["jobId"].DefaultValue = tbStudyNumber1.Text;
            if (ddlCountries.SelectedIndex == 0)
            {
                dsRecips.SelectParameters["country"].DefaultValue = "";
            }
            else 
            {
                dsRecips.SelectParameters["country"].DefaultValue = ddlCountries.SelectedValue;
            }
 
            //get the new information
            dsRecips.DataBind();
            grdvUserInfo.Visible = true;
            grdvUserInfo.DataBind();
        }
 
        protected void grdvUserInfo_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (Page.IsPostBack) {
                try
                {
                    e.Row.BorderWidth = 0;
                    if (e.Row.Cells[1].Text == "1")
                    {
                        e.Row.BackColor = System.Drawing.Color.LightGreen;
                        e.Row.Cells[1].Text = "Y";
                    }
                    else if (e.Row.Cells[1].Text == "0")
                    {
                        e.Row.Cells[1].Text = "N";
                    }
                }
                catch (Exception)
                {
                 // no problem, just trying to look at cells when there is no data so ignore
                }
            }
        }
    }
}

Open in new window

0
 
LVL 1

Accepted Solution

by:
Artform04 earned 0 total points
ID: 22765630
I have discovered the problem.  Visual Studio is automatically adding in Key's as parameters on top of the ones in the code, resulting in too many parameters.

To resolve this you need to either remove the doubled parameters in the ASPX codefile (the duplicate of the key)

OR

In the datasource.updating event, delete the problem parameter.  The code i used for this is attached...

Thanks.
protected void dsRecips_Updating(object sender, SqlDataSourceCommandEventArgs e)
        {
 
            int indexParam = 0;
            bool isFound = false;
 
            //search for the problem additional parameter which is added automatically by VS
            foreach (System.Data.SqlClient.SqlParameter xP in e.Command.Parameters)
            {
                //check if this is the trouble extra parameter
                if (xP.ParameterName == "@ConfigNum" && xP.SqlValue == null)
                {
                    //this is the problem parameter, set to found and exit the loop
                   isFound = true;
                   break;
                }
                //the param wasnt found so increment the index counter
                indexParam++;
            }
 
            //if the parameter was found, remove it from the parameters collection
            if (isFound == true)
            {
                e.Command.Parameters.Remove(e.Command.Parameters[indexParam]);
            }
        }

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question