Using a datagrid to update values in sql server (best practice)

Discussion in 'ASP .Net' started by Mok, Sep 7, 2005.

  1. Mok

    Mok Guest

    Hello,
    I want to pull information from a table in sql server, bind it to a
    grid and update those values. I would like to know the best way to do
    it. I used the configure data adapter wizard and it came up with a
    crazy stored proc. As in this example I would only update [start date]
    and [description]:

    CREATE PROCEDURE [mok].[NewUpdateCommand]
    (
    @Description varchar(1000),
    @Param2 datetime,
    @Original_ID int,
    @Original_Description varchar(1000),
    @Original_Start_Date datetime,
    @ID int
    )
    AS
    SET NOCOUNT OFF;
    UPDATE dbo.tblTasks SET Description = @Description, [Start Date] =
    @Param2 WHERE (ID = @Original_ID) AND (Description =
    @Original_Description OR @Original_Description IS NULL AND Description
    IS NULL) AND ([Start Date] = @Original_Start_Date OR
    @Original_Start_Date IS NULL AND [Start Date] IS NULL);
    SELECT ID, Description, [Start Date] FROM dbo.tblTasks WHERE (ID =
    @ID);
    GO

    Basically all this query does is receive start date and description. If
    one of the values wasn't modified in the datagrid, then this query
    won't modify that field. I'm thinking that since this query came out of
    a generator, it may not be the most efficient. For example, always
    sending the old values as parameters..

    My question is, what is the best way to code the stored proc and the
    application level code keeping in mind that I only want to call 1
    update stored proc? From examining the datagrid tutorials where updates
    are performed, they usually assume all the fields in the grid will be
    updated (which is not always the case).
     
    Mok, Sep 7, 2005
    #1
    1. Advertising

  2. We have the same problem..(Assuming ASP.NET 2.0)
    The trick is to write on a specific sproc for the grid you are updating
    from.
    Make sure the fields you don't want to update is set as readonly.
    Don't include the "key field in the grid columns, but make sure it is in the
    datakeys of the grid.
    Change the OldValuesParatmerFormat to "{0}" the default is "Original_{0}"

    CREATE PROCEDURE [mok].[NewUpdateCommand]
    (
    @Description varchar(1000),
    @Param2 datetime,
    @ID int
    )

    Grid should ONLY have Description and Param2 as columns and paramaters. (Do
    not specify ID as paramater, it is in the datakeys)
    ASP.NET 2.0 add the Datakey value automatically as a extra paramater.

    Use SQL Profiler and you will find
    exec NewUpdateCommand @Description='sdsdf', @Param2 = '2005/09/04', @ID='3'
    when you click update on the row you are editing...

    BTW. SQL profiler is mans best friend (after dog) to see what really happens
    when you do grid editing....

    HTH

    Gerhard

    PS. Stay away from Generated SQL code from the designer... it is not
    optimized.....use it only to see how it works once... after that... write
    your own sprocs...

    "Mok" <> wrote in message
    news:...
    > Hello,
    > I want to pull information from a table in sql server, bind it to a
    > grid and update those values. I would like to know the best way to do
    > it. I used the configure data adapter wizard and it came up with a
    > crazy stored proc. As in this example I would only update [start date]
    > and [description]:
    >
    > CREATE PROCEDURE [mok].[NewUpdateCommand]
    > (
    > @Description varchar(1000),
    > @Param2 datetime,
    > @Original_ID int,
    > @Original_Description varchar(1000),
    > @Original_Start_Date datetime,
    > @ID int
    > )
    > AS
    > SET NOCOUNT OFF;
    > UPDATE dbo.tblTasks SET Description = @Description, [Start Date] =
    > @Param2 WHERE (ID = @Original_ID) AND (Description =
    > @Original_Description OR @Original_Description IS NULL AND Description
    > IS NULL) AND ([Start Date] = @Original_Start_Date OR
    > @Original_Start_Date IS NULL AND [Start Date] IS NULL);
    > SELECT ID, Description, [Start Date] FROM dbo.tblTasks WHERE (ID =
    > @ID);
    > GO
    >
    > Basically all this query does is receive start date and description. If
    > one of the values wasn't modified in the datagrid, then this query
    > won't modify that field. I'm thinking that since this query came out of
    > a generator, it may not be the most efficient. For example, always
    > sending the old values as parameters..
    >
    > My question is, what is the best way to code the stored proc and the
    > application level code keeping in mind that I only want to call 1
    > update stored proc? From examining the datagrid tutorials where updates
    > are performed, they usually assume all the fields in the grid will be
    > updated (which is not always the case).
    >
     
    Gerhard Pretorius, Sep 7, 2005
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Robert Rossney
    Replies:
    0
    Views:
    558
    Robert Rossney
    Feb 7, 2006
  2. Mark
    Replies:
    1
    Views:
    428
    Natty Gur
    Jul 28, 2003
  3. Brian Muth
    Replies:
    1
    Views:
    542
    bruce barker
    Dec 17, 2004
  4. David Thielen
    Replies:
    3
    Views:
    1,606
    Steven Cheng [MSFT]
    Sep 1, 2008
  5. oldyork90
    Replies:
    1
    Views:
    168
    Jeremy J Starcher
    Sep 10, 2008
Loading...

Share This Page