I've got an ASP.Net 2.0 web app, connected to a SQL Server 2005 database, that is giving me some trouble. I've got a GridView that contains a number of columns...only 4 of them are editable, so for the sake of space, I will only show the 4. Here is the GridView declaration :
<asp:GridView id="gvResult" runat="server" Width="100%" CssClass="salesdirgrid"
AllowPaging="True" CellPadding="1" AllowSorting="True"
OnPageIndexChanging="gvResult_PageIndexChanging" DataSourceID="SqlDataSource1" AutoGenerateColumns="False" DataKeyNames="Terr,Mkt,Agtno5,Fname,Lname,DBA,Street,City,State,Zip,Booth,EmplDate,TermDate,PrefPhone,Ceasar,County,ACT,Sat_Type">
<Columns>
[...]
<asp:TemplateField HeaderText="First Name" SortExpression="[FName]">
<EditItemTemplate>
<asp:TextBox ID="txtFname" Text='<%# Bind("FName") %>' runat="server"></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblFname" Text='<%# Eval("FName") %>' runat="server"></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Last Name" SortExpression="[LName]">
<EditItemTemplate>
<asp:TextBox ID="txtLname" Text='<%# Bind("LName") %>' runat="server"></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblLname" Text='<%# Eval("LName") %>' runat="server"></asp:Label>
</ItemTemplate>
</asp:TemplateField>
[...]
<asp:TemplateField HeaderText="Ceasar" SortExpression="[Ceasar]">
<EditItemTemplate>
<asp:TextBox ID="txtCeasar" Text='<%# Bind("Ceasar") %>' runat="server"></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblCeasar" Text='<%# Eval("Ceasar") %>' runat="server"></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="County" SortExpression="[County]">
<EditItemTemplate>
<asp:TextBox ID="txtCounty" Text='<%# Bind("County") %>' runat="server"></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblCounty" Text='<%# Eval("County") %>' runat="server"></asp:Label>
</ItemTemplate>
</asp:TemplateField>
[...]
</Columns>
</asp:GridView>
=================================================================
I am trying to use a SqlDataSource and Stored Procedures to work with the GridView. The Select procedure works perfectly, but it's the Update SP that's amiss. Here's the info from the SqlDataSource :
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:salesdir %>"
SelectCommand="sp_getData" SelectCommandType="StoredProcedure" UpdateCommand="sp_editAgentInfo" UpdateCommandType="StoredProcedure">
<UpdateParameters>
<asp:Parameter Name="AgtNo" Type="String" />
<asp:Parameter Name="FirstName" Type="String" />
<asp:Parameter Name="LastName" Type="String" />
<asp:Parameter Name="Caesar" Type="String" />
<asp:Parameter Name="County" Type="String" />
</UpdateParameters>
<SelectParameters>
<asp:ControlParameter ControlID="ddlUserType" Name="type" PropertyName="SelectedValue"
Type="String" />
<asp:ControlParameter ControlID="ddlRegion" Name="region" PropertyName="SelectedValue"
Type="String" />
<asp:ControlParameter ControlID="ddlStatus" Name="status" PropertyName="SelectedValue"
Type="String" />
<asp:ControlParameter ControlID="ddlSearchBy" Name="search" PropertyName="SelectedValue"
Type="String" />
<asp:ControlParameter ControlID="txtPanel" Name="input" PropertyName="Text" Type="String" ConvertEmptyStringToNull="False" />
</SelectParameters>
</asp:SqlDataSource>
==========================================================
And here is the SP, which as you can see, the parameters match the UpdateParameters :
ALTER PROCEDURE [dbo].[sp_editAgentInfo]
@AgtNo char(6),
@FirstName varchar (100),
@LastName varchar (100),
@Caesar char(6),
@County varchar(25)
AS
SET NOCOUNT ON
UPDATE tblAgtUpdate
SET FName = @FirstName, LName = @LastName, Ceasar = @Caesar, County = @County
WHERE Agtno LIKE '%' + @AgtNo
=======================================================
Now, when I got the error, I naturally searched EE here, and I found other people were getting results by using the Sql Profiler, so I tried that. I am quite confused, however, as the entry for the query came out to be :
exec sp_editAgentInfo @AgtNo=NULL,@FirstName=NULL,@LastName=NULL,@Caesar=NULL,@FName=N'Jesse',@LName=N'Wisdom',@Ceasar=N'Y74134',@County=N'Lauderdale',@Terr=N'D',@Mkt=N'11',@Agtno5=N'04228',@DBA=N' J Wisdom Ins Agy',@Street=N'206 A Cox Creek Pkwy',@City=N'Florence',@State=N'AL',@Zip=N'35630',@Booth=N'AL29A',@EmplDate='1998-09-06 00:00:00:000',@TermDate=NULL,@PrefPhone=2567183554,@ACT=NULL,@Sat_Type=NULL
Which shows first off that all the UpdateParameters came back NULL instead of the new values I typed in, then the rest of the values are the entirety of the columns in the GridView. obviously, when trying to execute the stored proecdure, it's throwing every column in the GridVew into the SP, and I understand why the error message now, I just have NO earthly idea how to fix it. HOW do I tell it only pass in those 5 values, and how do I make it actually take in the values from the Edit TextBoxes rather than NULL?
by: BriCrowePosted on 2008-03-14 at 09:16:14ID: 21126832
I'm not an asp guy but it looks like you're using the same command object for both the select and update so that you're adding two sets of parameters to the command object. That's why you've got 10 parameters being passed.