GridView - Update using stored procedure

[drakuu]

So I'm updating the records using Edit-Update Command fields in
Gridview using stored procedure. The stored procedure updates two
tables. I have there two ID columns - ProviderID and AddressID which I
need for my update statement. If I make the columns invisible or read
only in the Gridview, my update statement doesn't work. So I must make
the ProviderID and AddressID fields visible, readonly=false what makes
the two fields editable.

I'm doing it all in GridView aspx file not in code behind aspx.cs Below
I'm providing a copy of the code.

Please advice,
drakuu


--------------------- CODE -----------------------

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"

DataSourceID="SqlDataSource1" DataKeyNames="AddressId"
BackColor="White" BorderColor="#999999" BorderStyle="Solid"
BorderWidth="1px" CellPadding="3" ForeColor="Black"
GridLines="Vertical">

<Columns>

<asp:BoundField DataField="ProviderID" SortExpression="ProviderID"
ShowHeader="False" >

<ControlStyle Width="0px" />

<ItemStyle Width="0px" />

</asp:BoundField>

<asp:BoundField DataField="AddressID" SortExpression="AddressID" >

<ControlStyle Width="1px" />

</asp:BoundField>

<asp:BoundField DataField="Address" HeaderText="Address"
SortExpression="Address" />

<asp:BoundField DataField="Address2" HeaderText="Address2"
SortExpression="Address2" >

<ControlStyle Width="100px" />

</asp:BoundField>

<asp:BoundField DataField="City" HeaderText="City"
SortExpression="City" />

<asp:BoundField DataField="State" HeaderText="State"
SortExpression="State" >

<ControlStyle Width="30px" />

</asp:BoundField>

<asp:BoundField DataField="County" HeaderText="County"
SortExpression="County" >

<ControlStyle Width="75px" />

</asp:BoundField>

<asp:BoundField DataField="Zip" HeaderText="Zip" SortExpression="Zip" >

<ControlStyle Width="40px" />

</asp:BoundField>

<asp:TemplateField>

<ItemTemplate>

<asp:SqlDataSource ID="SqlDataSource4" runat="server"
ConnectionString="<%$ ConnectionStrings:TopDoctorsConnectionString %>"

SelectCommand="SELECT [AddressTypeCode],
[AddressTypeDescription] FROM [AddressType]">

</asp:SqlDataSource>

<aspropDownList ID="DropDownList2" runat="server"
DataSourceID="SqlDataSource4"

DataTextField="AddressTypeDescription"
DataValueField="AddressTypeCode" SelectedValue='<%#
Bind("AddressTypeCode") %>'>

</aspropDownList>

</ItemTemplate>

</asp:TemplateField>

<asp:CommandField ShowEditButton="True" >

<ControlStyle Font-Bold="True" ForeColor="Green" />

</asp:CommandField>

<asp:CommandField ShowDeleteButton="True" >

<ControlStyle Font-Bold="True" ForeColor="#C00000" />

</asp:CommandField>

</Columns>

<FooterStyle BackColor="#CCCCCC" />

<SelectedRowStyle BackColor="#000099" Font-Bold="True"
ForeColor="White" />

<PagerStyle BackColor="#999999" ForeColor="Black"
HorizontalAlign="Center" />

<HeaderStyle BackColor="Black" Font-Bold="True" ForeColor="White" />

<AlternatingRowStyle BackColor="#CCCCCC" />

<EmptyDataTemplate>

<aspropDownList ID="DropDownList1" runat="server">

<asp:ListItem Value="A">Administrative office</asp:ListItem>

<asp:ListItem Value="B">Billing</asp:ListItem>

<asp:ListItem Value="C">Correspondence</asp:ListItem>

<asp:ListItem Value="H">Home</asp:ListItem>

<asp:ListItem Value="P">Practice</asp:ListItem>

<asp:ListItem Value="O">Other</asp:ListItem>

</aspropDownList>

</EmptyDataTemplate>

</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:TopDoctorsConnectionString %>"

SelectCommand="SELECT DISTINCT ProviderAddress.AddressID,
ProviderAddress.ProviderID, ProviderAddress.Address,
ProviderAddress.Address2, ProviderAddress.City, ProviderAddress.State,
ProviderAddress.County, ProviderAddress.Zip,
ProviderAddressType.AddressTypeCode, AddressType.AddressTypeDescription
FROM ProviderAddress INNER JOIN ProviderAddressType ON
ProviderAddress.AddressID = ProviderAddressType.AddressID INNER JOIN
AddressType ON ProviderAddressType.AddressTypeCode =
AddressType.AddressTypeCode WHERE (ProviderAddress.ProviderID =
@ProviderId)"

UpdateCommand="p_upd_ProviderAddress"

DeleteCommand="DELETE FROM ProviderAddress WHERE (AddressID =
@AddressId AND ProviderId = @ProviderId)"
UpdateCommandType="StoredProcedure">

<UpdateParameters>

<asparameter Name="ProviderId" Type="Int32" />

<asparameter Name="Address" />

<asparameter Name="Address2" />

<asparameter Name="City" />

<asparameter Name="State" />

<asparameter Name="County" />

<asparameter Name="Zip" />

<asparameter Name="AddressTypeCode" Type="String" />

<asparameter Name="AddressId" />

</UpdateParameters>

<SelectParameters>

<asp:SessionParameter Name="ProviderId" SessionField="Pid" />

</SelectParameters>

<DeleteParameters>

<asparameter Name="AddressId" Type="Int32" />

<asp:SessionParameter Name="ProviderId" SessionField="Pid" />

</DeleteParameters>

CREATE proc dbo.p_upd_ProviderAddress
@ProviderId int, @Address varchar (100), @Address2 varchar (100),
@City varchar (50),
@State varchar (2), @County varchar (50), @Zip varchar (5), @AddressId
int, @AddressTypeCode varchar (1)
as

set nocount on

begin

update Provideraddress set ProviderId =@ProviderId , Address =
@Address,Address2 = @Address2 ,
City = @City,State = @State,County = @County,zip = @zip
where AddressID = @addressid and ProviderId = @ProviderId

update ProviderAddressType set addresstypecode = @addresstypecode
where AddressID = @addressid and ProviderId = @ProviderId


end
GO