Updates from a DataGridView

[Bill Schanks]

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]]

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" <ws******@gmail .comschreef in bericht
news:a3******** *************** ***********@p25 g2000hsf.google groups.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]]

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" <ws******@gmail .comschreef in bericht
news:a3******** *************** ***********@p25 g2000hsf.google groups.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]]

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]" <MS**@posseth.c omschreef in bericht
news:OD******** ******@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" <ws******@gmail .comschreef in bericht
news:a3******** *************** ***********@p25 g2000hsf.google groups.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]

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:OD* *************@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:a3******** *************** ***********@p25 g2000hsf.google groups.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]

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]

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]

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:

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

http://devcenter.infragistics.com/Su...ArticleID=8270

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

HTH