VB.net - Pass Custom Parameter in SQL Update Command

Question

I am using a Gridview with SQLDataSource Select/Insert/Update command. It works fine for updating or changing value using the Gridview. However recently I added another plugin which use Javascript to retrieve data and I need that retrieve data to be the source data of the SQL. Any way I can do this?

Something like this:

<form id="form1" runat="server">
    <div>
    <input type="hidden" id="newname" runat="server" />
    <br />
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ID_AM" DataSourceID="SqlDataSource1">
        <Columns>
            <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" ReadOnly="True" SortExpression="ID" />
            <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
            <asp:BoundField DataField="StaffID" HeaderText="StaffID" SortExpression="StaffID" />
        </Columns>
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
         SelectCommand="SELECT [ID], [Name], [StaffID] FROM [Record]"        
         ConflictDetection="CompareAllValues" 
         UpdateCommand="UPDATE [Record] SET [Name] = @Name, [StaffID] = @StaffID WHERE [ID] = @original_ID AND (([Name] = @original_Name) OR ([Name] IS NULL AND @original_Name IS NULL)) AND (([StaffID] = @original_StaffID) OR ([StaffID] IS NULL AND @original_StaffID IS NULL))">
        <UpdateParameters>
            <asp:Parameter Name="Name" Type="String" />
            <asp:Parameter Name="StaffID" Type="String" />
            <asp:Parameter Name="original_ID" Type="Int32" />
            <asp:Parameter Name="original_Name" Type="String" />
            <asp:Parameter Name="original_StaffID" Type="String" />
        </UpdateParameters>
    </asp:SqlDataSource>
</div>
</form>

When I use the Gridview to update row, the plugin will generate a new name and store in the hidden input "newname.value". How can I put this value into the update parameter?

I am thinking a few ways to do:

• Find some way so that the SQL Parameter will somehow take that value
• Override the update command using the normal SQL update. But I want to keep the optimistic concurrency feature.

Answer 1

Thanks for your answer. Later I find a more straight forward method, seems easier and seems work for my case.

I just add the parameters on the updating event:

Protected Sub SQL_Update(source As Object, e As SqlDataSourceCommandEventArgs) Handles SqlDataSource.Updating

    dim staffid as string = newname.valie
    // presudo for other passing values

    e.Command.Parameters("@StaffID").Value = staffid
    //and for other parameters

End Sub

It can then adding those custom parameter before executing the SQL update