Need Help

I have a web app in asp.net which has 8 textboxes

the user searches the database and the search result is shown in a datagrid

the user has the option to select any records displayed using a checkbox

and they can enter values into the textboxes (or leave it empty) and when they say edit

the app updates the database the selected rows with values from the 8 textboxes id any of the text boxes are empty or "" then the existing value in that column has to be retained if the text box is not empty then then the new value is updated

the way i am doing it now is putting the update statement in a for loop and checking if the text box is not null and then updating the DB for that row

the problem is that if the user selects 5000 rows then the app has to do 5000 updates.

and it is a big performance problem

I want to do this update in the SQL 2000 by using a Stored Proc or do I need to use a Cursor

can somebody help me with small code snippet ..

Thanks

PKGCan you show us some code? Like what the update looks like?

In your case though, I might use a work table...and do the updates on the server...|||Originally posted by Brett Kaiser
Can you show us some code? Like what the update looks like?

In your case though, I might use a work table...and do the updates on the server...

here is a sample update statement

UPDATE BIRECIN SET GLDEBIT = '789945562.524', GLCREDIT ='74859612.586' WHERE EntryClass = 'AADM' and AccCode ='MDH' and RevArea='CCU '

then..


UPDATE BIRECIN SET GLDEBIT = '789945562.524', GLCREDIT ='74859612.586' WHERE EntryClass = 'AAFM' and AccCode ='MDH' and RevArea='CCI '

this update statement is inside a for loop and gets executed for n number of rows|||Use the isnull function

Update YourTable
Set YourExistingValue = isnull(@.YourNewValue, YourExistingValue)
From YourTable
Where Keyfield = @.KeyField

If @.YourNewValue is null, the old value is retained.

If you can't submit null values, but have to submit zero-length strings instead, this should work:
Set YourExistingValue = isnull(null(@.YourNewValue, ''), YourExistingValue)

blindman|||Yeah, ok, but he wants to update an entire record set back to the database...

How would you do that...

He says he's looping like 5,000 times...

How do you get the data?|||I get it. The table he is updating with the check boxes isn't in SQL server. It's a dataset in his asp page?

Criminy. That's an asp problem, and out of my scope.

blindman|||Is there some way in asp to write the records (or just the checked ones) back into a temporary table that he could then join to his raw data tables?

blindman|||Yeah, that's what I was thinking, so he could do a set based op instead..

like bcp the data or is there an asp method...

There's got to be asp forums someplace...

We got the db section covered|||How about ...

Here (http://www.dbforums.com/forumdisplay.php?forumid=192)

Guys .. dont you ever look outside
dBforums Database Server Software Microsoft SQL Server|||Originally posted by Enigma
How about ...

Here (http://www.dbforums.com/forumdisplay.php?forumid=192)

Guys .. dont you ever look outside
dBforums Database Server Software Microsoft SQL Server

Sure do:

http://www.sqlteam.com/default.asp

Front end stuff...No thanks...see what a vb developer is making?

Enough to muddle through...not enough to be tapped to code...

The horror...the sheer horror...

Besides...I make all of the developers call stored procedures...they just present what I give them..|||The way the world should be. We cook the food. They can arrange it on the plate. Bon apetit', and my complements to the chef.

blindman|||Originally posted by blindman
The way the world should be. We cook the food. They can arrange it on the plate. Bon apetit', and my complements to the chef.

blindman

That's a GREAT analogy...