asked on

How do specify parameters for Update command from gridview?

Using Visual Studio 2008 Team, ASP.NET 3.5

I've added a gridview to an asp.net page that is bound to a SqlDataSource (SS2008 Ent) that I created in design view. I use template fields and set the AutoGenerateEditButton property=true. I'd like to use the integrated method of updating without code, if possible. I've added the Update statement as shown below, but how do I set parameters from values in the EditTemplate controls? Also, one of the parameters (@TimesheetLineID) is a hidden column in the gridview which represents the unique Id for the current row in the gridview. I need to reference that value for a parameter too.

Gridview:
<asp:GridView ID="GridView1" runat="server" AllowSorting="True"
AutoGenerateColumns="False" CellSpacing="3" DataKeyNames="TimeSheetLineID"
DataSourceID="TimeSheetLine_DS" AllowPaging="True" Font-Size="Smaller"
ShowFooter="True" Width="891px" AutoGenerateEditButton="True">
<RowStyle Width="20px" />
<Columns>
<asp:TemplateField ShowHeader="False">
<ItemTemplate>
<asp:ImageButton ID="btnEdit" runat="server" CausesValidation="false"
CommandName="" ImageUrl="~/images/pencil_add.png" Text="Edit" />
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="TimeSheetLineID" HeaderText="TimeSheetLineID"
InsertVisible="False" ReadOnly="True" SortExpression="TimeSheetLineID"
Visible="False" />
<asp:BoundField DataField="EID" HeaderText="EID" SortExpression="EID"
Visible="False" />
<asp:BoundField DataField="TimeSheetID" HeaderText="TimeSheetID"
SortExpression="TimeSheetID" Visible="False" />
<asp:TemplateField HeaderText="Project" SortExpression="ProjDisplay">
<EditItemTemplate>
<asp:DropDownList ID="Project_ddl" runat="server" DataSourceID="ProjDisplay_DS"
DataTextField="ProjDisplay" DataValueField="ProjectID" Width="225px">
</asp:DropDownList>
<asp:SqlDataSource ID="ProjDisplay_DS" runat="server"
ConnectionString="<%$ ConnectionStrings:PIBSConnectionString1 %>"
SelectCommand="SELECT prj.ProjectID, c.Customer + ' - ' + prj.WAN AS ProjDisplay, prj.WAN, c.Customer FROM pjm.Projects AS prj INNER JOIN sls.Customers AS c ON prj.CustomerID = c.CustomerID">
</asp:SqlDataSource>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label11" runat="server" Text='<%# Bind("ProjectID") %>'
Width="225px"></asp:Label>
</ItemTemplate>
<ItemStyle Width="225px" />
</asp:TemplateField>
<asp:TemplateField HeaderText="TaskCode" SortExpression="TaskCodeID">
<EditItemTemplate>
<asp:SqlDataSource ID="TaskCodes_DS" runat="server"
ConnectionString="<%$ ConnectionStrings:PIBSConnectionString1 %>"
SelectCommand="SELECT [TaskCodeID], [TaskCode] FROM [TaskCodes]">
</asp:SqlDataSource>
<asp:DropDownList ID="TaskCodeEDIT_ddl" runat="server" DataSourceID="TaskCodes_DS"
DataTextField="TaskCode" DataValueField="TaskCodeID"
SelectedValue='<%# Bind("TaskCodeID") %>' Width="150px">
</asp:DropDownList>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label9" runat="server" Text='<%# Eval("TaskCode") %>'
Width="150px"></asp:Label>
</ItemTemplate>
<ItemStyle Width="150px" />
</asp:TemplateField>
<asp:TemplateField HeaderText="PayCode" SortExpression="PayCodeID">
<EditItemTemplate>
<asp:DropDownList ID="PayCode_dll" runat="server" DataSourceID="PayCodes_DS"
DataTextField="PayCode" DataValueField="PayCodeID"
SelectedValue='<%# Bind("PayCodeID") %>' Width="60px">
</asp:DropDownList>
<asp:SqlDataSource ID="PayCodes_DS" runat="server"
ConnectionString="<%$ ConnectionStrings:PIBSConnectionString1 %>"
SelectCommand="SELECT * FROM [PayCodes]"></asp:SqlDataSource>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label10" runat="server" Text='<%# Eval("PayCode") %>'
Width="75px"></asp:Label>
</ItemTemplate>
<ItemStyle Width="60px" />
</asp:TemplateField>
<asp:TemplateField HeaderText="Total Hrs" SortExpression="TotalTLineHrs">
<EditItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Eval("TotalTLineHrs") %>'></asp:Label>
</EditItemTemplate>
<FooterTemplate>
<asp:Label ID="lblTotal" runat="server" Text='<%#TotHrs%>'></asp:Label>
</FooterTemplate>

<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("TotalTLineHrs") %>'></asp:Label>
</ItemTemplate>
<ItemStyle HorizontalAlign="Center" Width="75px" />
</asp:TemplateField>

</Columns>
<FooterStyle Font-Bold="True" HorizontalAlign="Center" BackColor="Maroon"
ForeColor="White" Height="22px" />
<HeaderStyle Font-Size="Smaller" ForeColor="Maroon" />
<AlternatingRowStyle BackColor="#EBEBEB" Width="20px" />
</asp:GridView>

SQLDataSource:
<asp:SqlDataSource ID="TimeSheetLine_DS" runat="server"
ConnectionString="<%$ ConnectionStrings:MyConnStr %>"
SelectCommand="SELECT TSL.TimeSheetLineID, TSL.EID, TSL.TimeSheetID, TSL.ProjectID, TSL.TaskCodeID, TSL.PayCodeID, TSL.TotalTLineHrs, PC.PayCode, TC.TaskCode, C.Customer + ' - ' + PRJ.WAN AS ProjDisplay FROM TimeSheetLines AS TSL INNER JOIN PayCodes AS PC ON TSL.PayCodeID = PC.PayCodeID INNER JOIN pjm.Projects AS PRJ ON TSL.ProjectID = PRJ.ProjectID INNER JOIN TaskCodes AS TC ON TSL.TaskCodeID = TC.TaskCodeID INNER JOIN sls.Customers AS C ON PRJ.CustomerID = C.CustomerID AND PRJ.CustomerID = C.CustomerID AND PRJ.CustomerID = C.CustomerID WHERE (TSL.TimeSheetID = @TID)"
UpdateCommand="UPDATE TimeSheetLines SET ProjectID = @ProjectID WHERE (TimeSheetLineID = @TimeSheetLineID)">
<SelectParameters>
<asp:SessionParameter Name="TID" SessionField="TimeSheetID" />
</SelectParameters>
<UpdateParameters>
<asp:Parameter Name="ProjectID" />
<asp:Parameter Name="TimeSheetLineID" />
</UpdateParameters>
</asp:SqlDataSource>

Kumaraswamy R

HI

Bellow URL give some information
http://msdn.microsoft.com/en-us/library/ms972948.aspx

cdX

ASKER

I made changes to the update statement and parameters per the link above, but now I get this error:

Must declare the scalar variable "@original_TimeSheetLineID".

I guess it is not automatically retrieving the parameter for the TimeSheetLineID using the "original" prefix. Below is my code that I have changed for the Update statement and parameters now:

UpdateCommand="UPDATE [TimeSheetLines] SET [ProjectID] = @ProjectID WHERE [TimeSheetLineID] = @original_TimeSheetLineID">        
<UpdateParameters>
     <asp:Parameter Type="String" Name="ProjectID"></asp:Parameter>
</UpdateParameters>

Open in new window

cdX

ASKER

Might I also add that the example is in ASP.NET 2.0 and I'm using 3.5, if that makes any difference. I did notice a difference in the dialog box for creating the SQLDataSource. There is not a section for methods AND no checkbox for OptimisticConcurrency as mentioned in the link http://msdn.microsoft.com/en-us/library/ms972948.aspx.

ASKER CERTIFIED SOLUTION

cdX

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial