Solved

ADO multiple updates method

Posted on 2001-07-30
4
216 Views
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")
  oCmd.Execute

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))
    oCmd.Execute
  Next


  What's the trick?

  Better yet...  what's the best method for fastest response time?  (i.e. "UpdateBatch" ?)
   
0
Comment
Question by:EasyAim
  • 2
4 Comments
 
LVL 1

Expert Comment

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

bye
loveneesh
0
 

Accepted Solution

by:
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))
    oCmd.Execute
    Set Ocmd = Nothing <---- this is needed too
 Next


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
   Begin
      Set @vCounter = @vCounter + 1
      SELECT @vCurrentAcct = Substring(@vAllAccounts,((@vCounter-1)*@vCharPerItem)+1,@vCharPerItem-1)
      --
      -- do whatever you want with the account
   End


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

Ciao...
0
 

Author Comment

by:EasyAim
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.
0
 

Expert Comment

by:grafe
ID: 6339679
Glad to be of help...
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
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…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

708 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now