Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ADO multiple updates method

Posted on 2001-07-30
4
Medium Priority
?
228 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
[X]
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
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 150 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…
Suggested Courses

715 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