Professional Web Applications Themes

Multiple Updates to SQL Server - ASP Database

Hello, I was just wondering if anyone knows a better way to update multiple rows of a database then the one I am using. Here is what I currently do: (using ASP to update SQL Server 2000) I basically have a survey with 50 questions as an HTML form: <form action="save.asp" method="post"> <input type="text" name="Q1"> <input type="text" name="Q2"> <input type="text" name="Q3"> .... <input type="text" name="Q49"> <input type="text" name="Q50"> <input type="submit" value="Save"> </form> Then I have the save page which simply loops thru the values and inserts them into my table, like so: x=1 While x < 51 RS.Open "UPDATE Answers ...

  1. #1

    Default Multiple Updates to SQL Server

    Hello, I was just wondering if anyone knows a better way to update
    multiple rows of a database then the one I am using. Here is what I
    currently do:

    (using ASP to update SQL Server 2000)

    I basically have a survey with 50 questions as an HTML form:

    <form action="save.asp" method="post">
    <input type="text" name="Q1">
    <input type="text" name="Q2">
    <input type="text" name="Q3">
    ....
    <input type="text" name="Q49">
    <input type="text" name="Q50">
    <input type="submit" value="Save">
    </form>

    Then I have the save page which simply loops thru the values and
    inserts them into my table, like so:

    x=1

    While x < 51

    RS.Open "UPDATE Answers SET Answer=" & request.form("Q" & x) & " WHERE
    QuestionID=" & x,con,1,3

    x=x+1
    Wend

    Is there a faster way to do this? Maybe I can create a giant block of
    SQL with all 50 updates in it, and then do con.Execute("LOTS OF
    SQL")...is that possible? Does anyone know the best way to do
    something like this...Inserting or Updating numerous rows quickly.

    Regards,
    Dan Meehan
    Dan Guest

  2. #2

    Default Re: Multiple Updates to SQL Server

    Well for one thing you definitely don't need a recordset - that's just
    wasted. you can just use the connection's Execute() method with no return
    values

    it's also posible to build a string containing multiple update statements
    separated by semicolons, and just execute it once. up to you whether your
    app is suited to this approach or not. If you want to get obsessive about
    performance you could also investigate locking options and NOCOUNT, though
    personally I think that's pushing things in most cases.


    --
    Jason Brown
    Microsoft GTSC, IIS

    This posting is provided AS IS with no warranties, and confers no rights.


    "Dan Meehan" <net> wrote in message
    news:google.com... 


    Jason Guest

  3. #3

    Default Re: Multiple Updates to SQL Server

    Ok Jason, so now I changed my code so that the loop creates a string
    like this:

    MySQLString = "UPDATE Table SET Value=1 WHERE ID=1;UPDATE Table SET
    Value=2 WHERE ID=2;UPDATE Table SET Value=3 WHERE ID=3;UPDATE Table
    SET Value=4 WHERE ID=4;"

    and then I just do

    con.Execute(MySQLString)

    Is that it? When I do it this way I should be saving both the
    resources of creating a Recordset object, and the overhead of
    connecting to the Server 50 times, right? Can you think of any other
    ways to improve this? I know you had mentioned the locking options
    and NOCOUNT? What are those generally used for?

    -Dan








    "Jason Brown" <microsoft.com> wrote in message news:<phx.gbl>... [/ref]
    Dan Guest

  4. #4

    Default Re: Multiple Updates to SQL Server

    Dan Meehan wrote: 

    Better yet:
    con.Execute(MySQLString,,129)

    129 is the combination of two constants: adCmdText (1) and
    adExecuteNoRecords (128)

    You should always specify the CommandType (adCmdText) so ADO does not have
    to waste time guessing what it is. And you should tell ADO that you are not
    expecting any records to be returned so it does not waste time and resources
    creating a recordset to receive results that aren't coming.


    Another technique you may wish to consider is sending a delimited string to
    a stored procedure which ps the string and uses a loop to execute the
    updates. Something like this:

    dim sParms
    for x = 1 to 50
    if len(sParms) = 0 then
    sParms = x & "~"
    else
    sParms = sParms & "|" & x & "~"
    end if
    sParms = sParms & request.form("Q" & x)
    next
    'send string to procedure like this:
    con.UpdAnswers sParms

    The procedure would look something like this:

    CREATE PROCEDURE UpdAnswers
    Parms varchar(8000) AS
    SET NOCOUNT ON
    DECLARE ID int, ans int,
    pipepos int, tildepos int

    BEGIN TRAN
    SET pipepos = charindex('|',Parms)
    WHILE pipepos > 0
    BEGIN
    SET tildepos=charindex('~',parms)
    SET ID = Left(Parms,tildepos-1)
    SET ans = substring(Parms, tildepos+1,
    pipepos - tildepos - 2)
    UPDATE Table SET value = ans WHERE ID = ID
    IF error !=0
    BEGIN
    ROLLBACK TRAN
    RAISERROR 20001 'Error updating table'
    RETURN
    END
    SET Parm=substring(Parm,pipepos+1,8000)
    SET pipepos = charindex('|',Parms)
    END
    COMMIT TRAN
    go

     

    SET NOCOUNT ON is used to prevent SQL Server from returning informational
    messages (x number of records were affected ...)

    Don't bother with the locking options. Let SQL Server handle this for you.

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Guest

Similar Threads

  1. Multiple connections to same server
    By kristo2 in forum Macromedia Contribute Connection Administrtion
    Replies: 2
    Last Post: April 2nd, 07:58 AM
  2. CF File Access Broken /w Win Server 2003 Updates
    By binxsta in forum Coldfusion Server Administration
    Replies: 0
    Last Post: December 20th, 07:26 PM
  3. Multiple UPDATEs in one CFQUERY?
    By Marcus_in_Leeds in forum Coldfusion Database Access
    Replies: 7
    Last Post: November 17th, 03:31 PM
  4. asynchronous client updates (server polling?)
    By inajamaica in forum Macromedia Flex General Discussion
    Replies: 8
    Last Post: May 31st, 11:07 PM
  5. Win2003 Server Slow Response after Updates
    By Matt in forum Windows Server
    Replies: 0
    Last Post: June 10th, 07:14 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139