How do specify parameters for Update command from gridview?

Posted on 2010-01-05
Last Modified: 2013-11-26
Using Visual Studio 2008 Team, ASP.NET 3.5

I've added a gridview to an 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.

<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" />
            <asp:TemplateField ShowHeader="False">
                    <asp:ImageButton ID="btnEdit" runat="server" CausesValidation="false"
                        CommandName="" ImageUrl="~/images/pencil_add.png" Text="Edit" />
            <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">
                    <asp:DropDownList ID="Project_ddl" runat="server" DataSourceID="ProjDisplay_DS"
                        DataTextField="ProjDisplay" DataValueField="ProjectID" Width="225px">
                    <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:Label ID="Label11" runat="server" Text='<%# Bind("ProjectID") %>'
                <ItemStyle Width="225px" />
            <asp:TemplateField HeaderText="TaskCode" SortExpression="TaskCodeID">
                    <asp:SqlDataSource ID="TaskCodes_DS" runat="server"
                        ConnectionString="<%$ ConnectionStrings:PIBSConnectionString1 %>"
                        SelectCommand="SELECT [TaskCodeID], [TaskCode] FROM [TaskCodes]">
                    <asp:DropDownList ID="TaskCodeEDIT_ddl" runat="server" DataSourceID="TaskCodes_DS"
                        DataTextField="TaskCode" DataValueField="TaskCodeID"
                        SelectedValue='<%# Bind("TaskCodeID") %>' Width="150px">
                    <asp:Label ID="Label9" runat="server" Text='<%# Eval("TaskCode") %>'
                <ItemStyle Width="150px" />
            <asp:TemplateField HeaderText="PayCode" SortExpression="PayCodeID">
                    <asp:DropDownList ID="PayCode_dll" runat="server" DataSourceID="PayCodes_DS"
                        DataTextField="PayCode" DataValueField="PayCodeID"
                        SelectedValue='<%# Bind("PayCodeID") %>' Width="60px">
                    <asp:SqlDataSource ID="PayCodes_DS" runat="server"
                        ConnectionString="<%$ ConnectionStrings:PIBSConnectionString1 %>"
                        SelectCommand="SELECT * FROM [PayCodes]"></asp:SqlDataSource>                
                    <asp:Label ID="Label10" runat="server" Text='<%# Eval("PayCode") %>'
                <ItemStyle Width="60px" />
                        <asp:TemplateField HeaderText="Total Hrs" SortExpression="TotalTLineHrs">
                    <asp:Label ID="Label1" runat="server" Text='<%# Eval("TotalTLineHrs") %>'></asp:Label>
                    <asp:Label ID="lblTotal" runat="server" Text='<%#TotHrs%>'></asp:Label>
                    <asp:Label ID="Label1" runat="server" Text='<%# Bind("TotalTLineHrs") %>'></asp:Label>
                <ItemStyle HorizontalAlign="Center" Width="75px" />
            <FooterStyle Font-Bold="True" HorizontalAlign="Center" BackColor="Maroon"
                ForeColor="White" Height="22px" />
        <HeaderStyle Font-Size="Smaller" ForeColor="Maroon" />
        <AlternatingRowStyle BackColor="#EBEBEB" Width="20px" />

    <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)">
            <asp:SessionParameter Name="TID" SessionField="TimeSheetID" />
            <asp:Parameter Name="ProjectID" />
            <asp:Parameter Name="TimeSheetLineID"  />
Question by:cdX
    LVL 29

    Expert Comment

    by:Kumaraswamy R

    Bellow URL give some information

    Author Comment

    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">        
         <asp:Parameter Type="String" Name="ProjectID"></asp:Parameter>

    Open in new window


    Author Comment

    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

    Accepted Solution

    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") %>'

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now