Solved

ADO multiple updates method

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
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…

856 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