Results 1 to 12 of 12

Thread: Need Help

  1. #1
    Join Date
    Nov 2003
    Location
    New York
    Posts
    2

    Unanswered: 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

    PKG

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2003
    Location
    New York
    Posts
    2
    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

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    How about ...

    Here

    Guys .. dont you ever look outside
    dBforums Database Server Software Microsoft SQL Server
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by Enigma
    How about ...

    Here

    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..
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •