Solved

ADO multiple updates method

Posted on 2001-07-30
4
219 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

776 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