Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Using a datagrid to update values in sql server (best practice)

Reply
Thread Tools

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

 
 
Mok
Guest
Posts: n/a
 
      09-07-2005
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).

 
Reply With Quote
 
 
 
 
Gerhard Pretorius
Guest
Posts: n/a
 
      09-07-2005
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) oups.com...
> 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).
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help. Getting a An error has occurred while establishing a connectionto the server. When connecting to SQL Server 2005, this failure may be causedby the fact that under the default settings SQL Server does not allow remote aboutjav.com@gmail.com ASP .Net 0 05-03-2008 12:43 PM
Do the Self-Paced Training Kits: Microsoft SQL Server 2000 include Eval copy of SQL Server? Brian Whiting Microsoft Certification 2 12-29-2005 04:24 AM
using a datagrid to update two sql tables...I can't get it work TN Bella ASP .Net 1 06-15-2004 02:16 AM
Can't connect to SQL Server, using Windows Authentication users of SQL server? help =?Utf-8?B?UmV6YQ==?= ASP .Net 3 06-07-2004 06:42 PM
To all Gurus: How can I edit/update a DataGrid in a DataGrid (nested DataGrid)? Possible? Andreas Klemt ASP .Net Datagrid Control 0 10-08-2003 01:19 AM



Advertisments