Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 805
  • Last Modified:

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>
0
cdX
Asked:
cdX
  • 3
1 Solution
 
Kumaraswamy RCommented:
HI

Bellow URL give some information
http://msdn.microsoft.com/en-us/library/ms972948.aspx
0
 
cdXAuthor Commented:
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

0
 
cdXAuthor Commented:
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.
0
 
cdXAuthor Commented:
I found the error.  I didn't have the dropdownlist bound to the ProjectID column.  I added this to the ddl and it worked like a charm

SelectedValue='<%# Bind("ProjectID") %>'
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now