locked
How to update database with changes in GridView that is editable RRS feed

  • Question

  • User146952148 posted

    I have a gridview that is populated with a SqlDataSource, which runs a stored procedure. On the SqlDataSource wizard, I chose hte option to 'specify a custom SQL statement or stored proceure" which allows me to select my stored procedure just fine. On that same page where I choose my stored procdure, I have tabs to create my UPDATE, INSERT and DELETE SQL statements. Because I want to alloy the end user to update the data, I need to create the UPDATE statement. INSERT and DELETE will not be used.

    So now that you have the back story, my specific question is, how do you wite the UPDATE statement to use the data in the only the row that has changed? In a manual statement I would say something like <update MyTable set name = 'Rob' where name = 'Joe'> as an example, I don't know how to implement the ORB and JOE part though, since those are variables in my application, not set values.

    Thanks for any and all help!

    Friday, February 1, 2013 9:11 AM

Answers

  • User3866881 posted

    how do I assign the data to the variable @ID (using your example)?

    Hi,

    Just try this:

    1) Write a SQL select like select * from xxx where [somecolumnName]=@ID

    2) Then set your ControlParameter Name=ID, ControlID=Some Controls'ID and PropertyText="Some Property of your Control".

    For more you can see this:

    http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.controlparameter.aspx

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 6, 2013 9:14 PM
  • User-1716253493 posted

    righ click sqldatasource1 > properties

    in DATA group you can edit it, there is also builder

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 6, 2013 11:35 PM

All replies

  • User-1716253493 posted
    you need pk included in sp for example id. Then use it as gv datakeynames="id". The value will deferent each row. Then pass it by adding update param name="id". You only need to add the param name to pass datakeynames value. Use the param in your update command or sp.
    Friday, February 1, 2013 11:06 AM
  • User146952148 posted

    too many acronyms and shortcut words for me oned_gk, sorry. What is PK and SP, and for what example id? I don't know what GV or SP is either. I really appreciate you helping, I just don't know what you mean.

    Friday, February 1, 2013 1:28 PM
  • User-1716253493 posted

    you need primary key field included in stored procedure for example field id.

    SELECT ID, ....
    



    Then use it as gridview datakeynames="id".

     <asp:GridView ID="GridView1" runat="server" DataKeyNames="ID" ... >



    The value will deferent each row. Then pass it by adding update paramameter
    name="id".

            UpdateCommand="UPDATE YOURTABBEL SET field1=@field1 WHERE ID=@ID">
            <UpdateParameters>
                <asp:Parameter Name="field1" />
                <asp:Parameter Name="ID" Type="Int32" />
            </UpdateParameter




    You only need to add the parameter name to pass datakeynames value.
    Use the param in your update command or stored procedure.

    
    


    Friday, February 1, 2013 8:58 PM
  • User146952148 posted

    Thanks for that, I think I actually understand :) One thing though, you put your code in ASP, I am trying to put in the 'configure date source' section. See screenshot here, http://i.imgur.com/pKDpwk2.png. I'm not sure how I would do that. In my SQL code, how do I assign the data to the variable @ID (using your example)?

    Thanks again for the reply!

    Tuesday, February 5, 2013 9:13 AM
  • User3866881 posted

    how do I assign the data to the variable @ID (using your example)?

    Hi,

    Just try this:

    1) Write a SQL select like select * from xxx where [somecolumnName]=@ID

    2) Then set your ControlParameter Name=ID, ControlID=Some Controls'ID and PropertyText="Some Property of your Control".

    For more you can see this:

    http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.controlparameter.aspx

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 6, 2013 9:14 PM
  • User-1716253493 posted

    righ click sqldatasource1 > properties

    in DATA group you can edit it, there is also builder

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 6, 2013 11:35 PM