Artform04
asked on
Parameter problem with Gridview updating using SQL Datasource & Stored Procedures
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.PageRequestMa nagerServe rErrorExce ption: 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!
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.PageRequestMa
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>
ASKER
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...
a) here is the SPROC header:
USE [Reporting]
GO
/****** Object: StoredProcedure [dbo].[spUpdateRecipients]
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
}
}
}
}
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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..