ADO multiple updates method

Posted on 2001-07-30
Last Modified: 2010-05-02
(Is it my imagination or does the search engine for this web site suck?)

   This is specifically an ADO question but I couldn't figure out what area to submit this...

   Practically all of the examples I find in ADO text books and web sites concerning UPDATE to a database table are a single row.
   My problem is that I have an array of account numbers and I must change the status field for each row to "PASSED".
   To speed things up, I have a stored procedure that all it does is change the "Status" field from NULL to whatever I send in the parameter list.  It requires two parameters: Account and Status.

   This works for me (ASP page example) for ONE update...

  Set oConn = Server.CreateObject("ADODB.Connection")
  oConn.Open sDSN
  Set oCmd = Server.CreateObject("ADODB.Command")
  Set oCmd.ActiveConnection = oConn
  oCmd.CommandText = "prProcureUpdate"
  oCmd.CommandType = adCmdStoredProc
  arrayValue(0) = 70  ' single account value
  oCmd.Parameters.Append oCmd.CreateParameter("@Account", adInteger, adParamInput, 4, arrayValue(0))
  oCmd.Parameters.Append oCmd.CreateParameter("@Status", adChar, adParamInput, 10, "PASSED")

However, I can't seem to figure out how to loop thru a large array of account numbers....

  Set oCmd = Server.CreateObject("ADODB.Command")
  Set oCmd.ActiveConnection = oConn
  oCmd.CommandText = "prProcureUpdate"
  oCmd.CommandType = adCmdStoredProc
  oCmd.Parameters.Append oCmd.CreateParameter("@Status", adChar, adParamInput, 10, "PASSED")
  For i = lbound(arrayValue) to ubound(arrayValue)
    oCmd.Parameters.Append oCmd.CreateParameter("@Account", adInteger, adParamInput, 4, arrayValue(i))

  What's the trick?

  Better yet...  what's the best method for fastest response time?  (i.e. "UpdateBatch" ?)
Question by:EasyAim
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2

Expert Comment

ID: 6336640
I donot think so that in a loop it is possible to call the update procedure.


Accepted Solution

grafe earned 50 total points
ID: 6336673
You will have to reinitialize your ADO command object inside the loop as in:

 Set oCmd.ActiveConnection = oConn
 For i = lbound(arrayValue) to ubound(arrayValue)
    oCmd.CommandText = "prProcureUpdate"
    oCmd.CommandType = adCmdStoredProc
    oCmd.Parameters.Append oCmd.CreateParameter ("@Status",  adChar, adParamInput, 10, "PASSED")
    oCmd.Parameters.Append oCmd.CreateParameter("@Account", adInteger, adParamInput, 4, arrayValue(i))
    Set Ocmd = Nothing <---- this is needed too

However, there is a faster way to do it. Doing it in a loop generates a lot of network traffic, especially significant if your web server is not the same as your database server.

If you are using SQL Server:

Instead of passing the values of the array one by one, concantenate these values into a single string, delimited any special character and should be fixed length.

Then, modify your stored procedure to accept the new string. Make a while begin/end such as:

-- @vAllAccounts in the form of 'A001~X002~X003~'

Set @vCounter = 0
Set @vCharPerItem = 5    <---- fixed length of each item + delimiter
While @vCounter <= Len(@vAllAccounts) / @vCharPerItem
      Set @vCounter = @vCounter + 1
      SELECT @vCurrentAcct = Substring(@vAllAccounts,((@vCounter-1)*@vCharPerItem)+1,@vCharPerItem-1)
      -- do whatever you want with the account

Please note that this will work only if your array strings will not exceed 8000 chars, the limit of varchar...


Author Comment

ID: 6337669
Well, you answered my main question which was - was a I missing anything blatanly.

I was thinking of building the WHERE string but I didn't want to worry about total character length either.

I'm worried processing time re-initializing everything in the loop.

I'll try both suggestions and give it a whirl...

Thanks for your response.

Expert Comment

ID: 6339679
Glad to be of help...

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month8 days, 7 hours left to enroll

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question