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="gvRes
ult_PageIn
dexChangin
g" DataSourceID="SqlDataSourc
e1" AutoGenerateColumns="False
" DataKeyNames="Terr,Mkt,Agt
no5,Fname,
Lname,DBA,
Street,Cit
y,State,Zi
p,Booth,Em
plDate,Ter
mDate,Pref
Phone,Ceas
ar,County,
ACT,Sat_Ty
pe">
<Columns>
[...]
<asp:TemplateField HeaderText="First Name" SortExpression="[FName]">
<EditItemTemplate>
<asp:TextBox ID="txtFname" Text='<%# Bind("FName") %>' runat="server"></asp:TextB
ox>
</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:TextB
ox>
</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:TextB
ox>
</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:TextB
ox>
</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="StoredP
rocedure" UpdateCommand="sp_editAgen
tInfo" UpdateCommandType="StoredP
rocedure">
<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="SelectedValu
e"
Type="String" />
<asp:ControlParameter ControlID="ddlRegion" Name="region" PropertyName="SelectedValu
e"
Type="String" />
<asp:ControlParameter ControlID="ddlStatus" Name="status" PropertyName="SelectedValu
e"
Type="String" />
<asp:ControlParameter ControlID="ddlSearchBy" Name="search" PropertyName="SelectedValu
e"
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=NUL
L,@LastNam
e=NULL,@Ca
esar=NULL,
@FName=N'J
esse',@LNa
me=N'Wisdo
m',@Ceasar
=N'Y74134'
,@County=N
'Lauderdal
e',@Terr=N
'D',@Mkt=N
'11',@Agtn
o5=N'04228
',@DBA=N' J Wisdom Ins Agy',@Street=N'206 A Cox Creek Pkwy',@City=N'Florence',@S
tate=N'AL'
,@Zip=N'35
630',@Boot
h=N'AL29A'
,@EmplDate
='1998-09-
06 00:00:00:000',@TermDate=NU
LL,@PrefPh
one=256718
3554,@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?