Updates from a DataGridView

**Cor Ligthert[MVP]** · Jun 27 '08, 08:18 PM

Re: Updates from a DataGridView

Bill,

It seems that you do something terrible wrong, a dataadapter is updating
only updating (datasets, datatable, datarow(s)).

Those rows have all a rowstate which should be have not a value unchanged,
it seems to me that you are doing something where you ignore the rowstate.

Cor

"Bill Schanks" <wschanks@gmail .comschreef in bericht
news:a3f586b8-2f78-468c-8f81-c661caf61aee@p2 5g2000hsf.googl egroups.com...

VB.NET 2005
SQL Server 2000
>
I have a datagridview that users will update. When there are only a
few records the update is fine. However when they update 100-200 of
the rows the udpate can take 2+ Mins.
>
Is this normal performance, as the datagridview was not meant to
update this many records at once?
>
Here is the code I am using to save the data:
<<Snip>>
Me.Validate()
Me.Spoc_ev_resu ltsBindingSourc e.EndEdit()
Dim iUpdate As Integer = _
>
Me.Spoc_ev_resu ltsTableAdapter .Update(Me.RCT_ EV_QADataSet.sp oc_ev_results)
<<Snip>>
>
Here is the Stored Procedure that gets called:
ALTER procedure [dbo].[spoc_ev_results _update]
@ItemID int,
@Status_ID int,
@UserID int,
@Notes varchar(1000) = NULL
AS
>
UPDATE dbo.t_EV_ExcepV al_Accts
SET
Status_ID = @Status_ID,
Notes = @Notes,
LastMaintBy_ID = @UserID,
LastMaintDate = getdate()
WHERE
ID = @ItemID

**Michel Posseth [MCP]** · Jun 27 '08, 08:18 PM

Re: Updates from a DataGridView

Well this might give you some extra performance

<<Snip>>
Me.Validate()
Me.Spoc_ev_resu ltsBindingSourc e.EndEdit()

if Me.RCT_EV_QADat aSet.spoc_ev_re sults.haschange s then
Dim iUpdate As Integer = _
Me.Spoc_ev_resu ltsTableAdapter .Update(Me.RCT_ EV_QADataSet.sp oc_ev_results.g etchanges)

end if
<<Snip>>

what this small change does
Only perform the update when there are changes in the rowset , if there are
updates only send the changed records

hth

Michel

"Bill Schanks" <wschanks@gmail .comschreef in bericht
news:a3f586b8-2f78-468c-8f81-c661caf61aee@p2 5g2000hsf.googl egroups.com...

VB.NET 2005
SQL Server 2000
>
I have a datagridview that users will update. When there are only a
few records the update is fine. However when they update 100-200 of
the rows the udpate can take 2+ Mins.
>
Is this normal performance, as the datagridview was not meant to
update this many records at once?
>
Here is the code I am using to save the data:
<<Snip>>
Me.Validate()
Me.Spoc_ev_resu ltsBindingSourc e.EndEdit()
Dim iUpdate As Integer = _
>
Me.Spoc_ev_resu ltsTableAdapter .Update(Me.RCT_ EV_QADataSet.sp oc_ev_results)
<<Snip>>
>
Here is the Stored Procedure that gets called:
ALTER procedure [dbo].[spoc_ev_results _update]
@ItemID int,
@Status_ID int,
@UserID int,
@Notes varchar(1000) = NULL
AS
>
UPDATE dbo.t_EV_ExcepV al_Accts
SET
Status_ID = @Status_ID,
Notes = @Notes,
LastMaintBy_ID = @UserID,
LastMaintDate = getdate()
WHERE
ID = @ItemID

**Cor Ligthert[MVP]** · Jun 27 '08, 08:18 PM

Re: Updates from a DataGridView

Michel,

As in past too thought that the get changes was needed, was this in my idea
a mistake from me.
The dataadapter itself is normaly only handling changed records.

In this case is at least a dataset.acceptc hanges needed, as the getchanges
is a copy of the set, and the rowstates in the origianl dataset are not
changed by this.

Cor

"Michel Posseth [MCP]" <MSDN@posseth.c omschreef in bericht
news:OD5Fa$20IH A.1240@TK2MSFTN GP02.phx.gbl...

Well this might give you some extra performance
>
<<Snip>>
Me.Validate()
Me.Spoc_ev_resu ltsBindingSourc e.EndEdit()
>
>
>
if Me.RCT_EV_QADat aSet.spoc_ev_re sults.haschange s then
Dim iUpdate As Integer = _
Me.Spoc_ev_resu ltsTableAdapter .Update(Me.RCT_ EV_QADataSet.sp oc_ev_results.g etchanges)
>
end if
<<Snip>>
>
what this small change does
Only perform the update when there are changes in the rowset , if there
are updates only send the changed records
>
hth
>
Michel
>
>
"Bill Schanks" <wschanks@gmail .comschreef in bericht
news:a3f586b8-2f78-468c-8f81-c661caf61aee@p2 5g2000hsf.googl egroups.com...

>VB.NET 2005
>SQL Server 2000
>>
>I have a datagridview that users will update. When there are only a
>few records the update is fine. However when they update 100-200 of
>the rows the udpate can take 2+ Mins.
>>
>Is this normal performance, as the datagridview was not meant to
>update this many records at once?
>>
>Here is the code I am using to save the data:
><<Snip>>
>Me.Validate( )
>Me.Spoc_ev_res ultsBindingSour ce.EndEdit()
>Dim iUpdate As Integer = _
>>
>Me.Spoc_ev_res ultsTableAdapte r.Update(Me.RCT _EV_QADataSet.s poc_ev_results)
><<Snip>>
>>
>Here is the Stored Procedure that gets called:
>ALTER procedure [dbo].[spoc_ev_results _update]
>@ItemID int,
>@Status_ID int,
>@UserID int,
>@Notes varchar(1000) = NULL
>AS
>>
>UPDATE dbo.t_EV_ExcepV al_Accts
>SET
>Status_ID = @Status_ID,
>Notes = @Notes,
>LastMaintBy_ ID = @UserID,
>LastMaintDat e = getdate()
>WHERE
>ID = @ItemID

>
>

**Bill Schanks** · Jun 27 '08, 08:18 PM

Re: Updates from a DataGridView

Ok, it's only updating the rows that have changed. My problem is when
the user changes a lot of the rows (100-200 of them), then it takes a
long time to update.

The datagridview could have 800 records on it, but if they only change
2 of them everything is fine. My question is: Is it normal for it take
2+ Mins to update 200 rows?

On Jun 21, 9:22 am, "Cor Ligthert[MVP]" <notmyfirstn... @planet.nl>
wrote:

Michel,
>
As in past too thought that the get changes was needed, was this in my idea
a mistake from me.
The dataadapter itself is normaly only handling changed records.
>
In this case is at least a dataset.acceptc hanges needed, as the getchanges
is a copy of the set, and the rowstates in the origianl dataset are not
changed by this.
>
Cor
>
"Michel Posseth [MCP]" <M...@posseth.c omschreef in berichtnews:OD5 Fa$20IHA.1240@T K2MSFTNGP02.phx .gbl...
>

Well this might give you some extra performance

>

<<Snip>>
Me.Validate()
Me.Spoc_ev_resu ltsBindingSourc e.EndEdit()

>

if Me.RCT_EV_QADat aSet.spoc_ev_re sults.haschange s then
Dim iUpdate As Integer = _
Me.Spoc_ev_resu ltsTableAdapter .Update(Me.RCT_ EV_QADataSet.sp oc_ev_results.g etchanges)

>

end if
<<Snip>>

>

what this small change does
Only perform the update when there are changes in the rowset , if there
are updates only send the changed records

>

hth

>

Michel

>

"Bill Schanks" <wscha...@gmail .comschreef in bericht
news:a3f586b8-2f78-468c-8f81-c661caf61aee@p2 5g2000hsf.googl egroups.com...

VB.NET 2005
SQL Server 2000

>

I have a datagridview that users will update. When there are only a
few records the update is fine. However when they update 100-200 of
the rows the udpate can take 2+ Mins.

>

Is this normal performance, as the datagridview was not meant to
update this many records at once?

>

Here is the code I am using to save the data:
<<Snip>>
Me.Validate()
Me.Spoc_ev_resu ltsBindingSourc e.EndEdit()
Dim iUpdate As Integer = _

>

Me.Spoc_ev_resu ltsTableAdapter .Update(Me.RCT_ EV_QADataSet.sp oc_ev_results)
<<Snip>>

>

Here is the Stored Procedure that gets called:
ALTER procedure [dbo].[spoc_ev_results _update]
@ItemID int,
@Status_ID int,
@UserID int,
@Notes varchar(1000) = NULL
AS

>

UPDATE dbo.t_EV_ExcepV al_Accts
SET
Status_ID = @Status_ID,
Notes = @Notes,
LastMaintBy_ID = @UserID,
LastMaintDate = getdate()
WHERE
ID = @ItemID

**Steve Gerrard** · Jun 27 '08, 08:18 PM

Re: Updates from a DataGridView

Bill Schanks wrote:

Ok, it's only updating the rows that have changed. My problem is when
the user changes a lot of the rows (100-200 of them), then it takes a
long time to update.
>
The datagridview could have 800 records on it, but if they only change
2 of them everything is fine. My question is: Is it normal for it take
2+ Mins to update 200 rows?
>

I would say no. I would also say that the datagridview (or any other control you
might use) has nothing to do with it. The update process goes from the datatable
to the backend database, regardless of how the data in the table got changed.

You are presumably updating one row at a time, with a separate round trip for
each row. That may be the source of slowness, depending on the network and the
database server and the connection. Anything you can do to send all of the
updates at the same time would help.

**Bill Schanks** · Jun 27 '08, 08:18 PM

Re: Updates from a DataGridView

Yea, I think the slowless is from the multiple round trips. How would
I go about doing one round trip? Say I have 100 Updates on the grid.
How would I update multiple rows with different values with one update
statement?

On Jun 21, 8:02 pm, "Steve Gerrard" <mynameh...@com cast.netwrote:

Bill Schanks wrote:

Ok, it's only updating the rows that have changed. My problem is when
the user changes a lot of the rows (100-200 of them), then it takes a
long time to update.

>

The datagridview could have 800 records on it, but if they only change
2 of them everything is fine. My question is: Is it normal for it take
2+ Mins to update 200 rows?

>
I would say no. I would also say that the datagridview (or any other control you
might use) has nothing to do with it. The update process goes from the datatable
to the backend database, regardless of how the data in the table got changed.
>
You are presumably updating one row at a time, with a separate round trip for
each row. That may be the source of slowness, depending on the network and the
database server and the connection. Anything you can do to send all of the
updates at the same time would help.

**Jason Keats** · Jun 27 '08, 08:18 PM

Re: Updates from a DataGridView

Bill Schanks wrote:

Yea, I think the slowness is from the multiple round trips. How would
I go about doing one round trip? Say I have 100 Updates on the grid.
How would I update multiple rows with different values with one update
statement?

Here's some reading:

Performing Batch Operations Using DataAdapters - ADO.NET

http://msdn.microsoft.com/en-us/library/aadf8fk2.aspx

Learn more about batching operations using DataAdapters, instead of sending one operation at a time, to improve performance.

Support Page has been Retired | Support | Help

http://devcenter.infragistics.com/Support/KnowledgeBaseArticle.Aspx?ArticleID=8270

ASP.NET Core, an open-source web development framework | .NET

http://www.asp.net/learn/data-access/tutorial-63-cs.aspx

Build web apps and services that run on Windows, Linux, and macOS using C#, HTML, CSS, and JavaScript. Get started for free on Windows, Linux, or macOS.

HTH

Updates from a DataGridView

Updates from a DataGridView

Comment

Comment

Comment

Comment

Comment

Comment

Comment