VBScript / Process SQL record set twice

Experts,

I have the following code (pasted in the code block below) that connects to SQL and processes all of the records that are returned.

Is it possible to make VBScript cycle through the record set twice, instead of just once?

*Like some sort of "move to top" and then loop through again?

Any assistance that can be provided on this would be greatly appreciated.
Const adCmdStoredProc = 4, adVarChar = 200, adParamInput = 1

Dim cn, cmd, rs

    Set cn = CreateObject("ADODB.Connection")
    Set cmd = CreateObject("ADODB.Command")

cn.ConnectionString = "Provider=SQLOLEDB;Data Source=" & SiteSQLServer & ";" & _
      "Trusted_Connection=Yes;Initial Catalog=" & SQLDatabase & ";"

    cn.Open







With cmd
    Set .ActiveConnection = cn
        .CommandText = StoredProcedure
        .CommandType = adCmdStoredProc
        .Parameters.Append .CreateParameter("@ComputerRoomNumber", adVarChar, adParamInput, 100, ComputerRoomNumber)
        .Parameters.Append .CreateParameter("@ComputerName", adVarChar, adParamInput, 100, ComputerName)

        Set rs = .Execute
End With

Set cmd = Nothing

Do While Not rs.EOF


***RECORD SET PROCESSING***


rs.MoveNext

Loop

Open in new window

LVL 5
usslindstromAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Anthony PerkinsConnect With a Mentor Commented:
The GetRows() method is what you want to use.  Assuming the value that you need is in the first column returned, do something like this:

' Here goes your rs code.
arrRS = rs.GetRows()

For I = 0 to Ubound(arrRS, 2)
   MsgBox arrRS(0, I)            -- This will display the contents of the first column for each row.
Next

' You can now repeat the code above as often as you need or place it in a function and call it that way.
0
 
Chris MangusConnect With a Mentor Database AdministratorCommented:
Right after your loop why not do this:

rs.MoveFirst

Do While Not rs.EOF

***More recordset processing

rs.MoveNext

Loop
0
 
usslindstromAuthor Commented:
I had no idea it would be something that simple.  :)

Unfortunately, for me - it looks like it's a bit of a challenge for me.

I added those lines to my script, and unfortunately the script dumps out at "rs.MoveFirst" - and is unable to continue.  - No errors or anything, is just dumps out.

Would you have any ideas why that would happen?
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
usslindstromAuthor Commented:
Here's the basic implementation of the code below.

And the log output of the script...
rs.MoveNext

            Loop

                objLogFile.WriteLine "        Initial loop processing complete."
                objLogFile.WriteLine "            Restarting the Print Spooler service..."
                    objLogFile.WriteLine "                Executing the following internal function:  PrintSpoolerRestart"

                        PrintSpoolerRestart

                objLogFile.WriteLine "                Continuing Script."
                objLogFile.WriteLine "        Processing secondary loop..."

                objLogFile.WriteLine "        BEFORE"
            rs.MoveFirst
                objLogFile.WriteLine "        AFTER"
            Do While Not rs.EOF












        Initial loop processing complete.
            Restarting the Print Spooler service...
                Executing the following internal function:  PrintSpoolerRestart
                    Stopping the Print Spooler service.
                        Printer Spooler has stopped.
                    Starting the Print Spooler service.
                        Printer Spooler has started.
                Continuing Script.
        Processing secondary loop...
        BEFORE
---------------------------------------------------------------------
Initializing Sub-Routine at 10/19/2011 8:49:01 AM.
 
Sub-Routine handle:  PrinterRemoval

Open in new window

0
 
tomHamillConnect With a Mentor Commented:
usslindstrom;

cmanqus's suggestion will work, as long as you open your RecordSet with a CursorType of Static.

One way to accomplish this is when you create the recordset object at the top of your file:

set rs = CreateObject("ADODB.RecordSet")
rs.CursorType = 3   '**** Note, 3 = Static see msdn for more info

Open in new window

Then you should be able to loop through the record set and many times as you need to.


DO WHILE RS.EOF = False
	' Do stuff
	rs.MoveNext
LOOP

rs.MoveFirst
DO WHILE RS.EOF = False
	' do more stuff
	rs.MoveNext
LOOP

Open in new window


Be sure to clean up when you're done.

SET rs = Nothing 

Open in new window







0
 
usslindstromAuthor Commented:
Sorry for my lack of understanding here...

But I included those changes that you suggested, but the script still behaves the same way.  If I include "rs.MoveFirst" in any part of the script, it breaks the loop out.

The script didn't error out by me adding these lines at the top (right above the SQL connection string):

            Set rs = CreateObject("ADODB.RecordSet")
                rs.CursorType = 3

But as soon as I add these:

            Do While Not rs.EOF
                ***DATA PROCESSING***
            rs.MoveNext
            Loop
            rs.MoveFirst
            Do While Not rs.EOF
                ***DATA PROCESSING***
            Loop

The script always dies at rs.MoveFirst.  Could it be the way I'm executing the stored procedure?  I'm not opening a data stream, if that makes sense - only executing the SP and getting results back.


Thank you guys for being patient, this is all relatively new for me, and I'm trying to get a better understanding of Record Sets and ADO objects.
0
 
Anthony PerkinsCommented:
No, it will not work that way.  The Execute method produces a Forward-Only cursor, you cannot use MoveFirst.

You have a couple of options:
1. Use the Recorset's Open method (performance is not good).
2. Leave the Execute and instead load an array using the GetRows method. Then cycle through the array.

>>The script didn't error<<
Sure it does.  It just happens that you are using On Error Resume Next.
0
 
usslindstromAuthor Commented:
AcPerkins - long time no see.  :)

Actually - the Stored Procedure execution method is the one you coded for me back in August.  *VERY much appreciated on the help back then.  I'm going to have to shadow you around at some point and just copy your ideas down.  :)

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_27281702.html

There's the link to my question on executing Stored Procedures from VBScript.  Of which, your solution worked flawlessly.  But here's the new problem I'm facing.

-----------------

This script is for printer installation against computers.  This is handled in three basic steps.

1.  Write the registry values to create the printer port (TCP/IP).
2.  Restart the Print Spooler service so it can reload/re-read the registry.
3.  Install the printer using the PrintUI command set.

Up to this point, I've been linear in this process.  So, SQL returns a value on which printer is needed to install - the whole process goes through - where it's then off to record #2.  Rinse and repeat.

So you can see the dilema here on if a unit has 6 printers to be installed.  It restarts the print spooler service prior to each of them, and a unit is stuck waiting a pretty decent amount of time for those to happen (roughly 6 seconds or so for each service cycle).

I'm trying to change the script to process the record set twice, where it can write ALL of the registry keys, restart the print spooler, then come back and install printers.

--------------------------

I'm open to whatever suggestions you guys have, and your "array" idea might be perfect for this solution.  *That is, if you guys wouldn't mind pointing me in the direction of how I could do that.

As always, thanks for the help.
Const adCmdStoredProc = 4, adVarChar = 200, adParamInput = 1
Dim cn, cmd, rs

Set cn = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")

cn.ConnectionString = "Your SQL Server Connection String Goes here"
cn.CursorLocation = adUseClient
cn.Open

With cmd
    Set .ActiveConnection = cn
    .CommandText = "usp_YourStoredProcedureNameGoesHere"
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("@ComputerName", adVarChar, adParamInput, 100, "YourComputerNameGoesHere")
    Set rs = .Execute
End With
Set cmd = Nothing

' Here goes your rs code.




rs.Close
Set rs = Nothing

cn.Close
Set cn = Nothing

Open in new window

0
 
tomHamillCommented:
acPerkins  "No, it will not work that way.  The Execute method produces a Forward-Only cursor, you cannot use MoveFirst."

I do remember hearing something like that in the distant past, but iis it possible that it is no longer true?

I created a simple Access DB with a table name "ValueTable" (creative, eh? LOL).  The table has 2 columns (RecordNumber and Value). I added four records to the table.  I than ran the following script which, as you can see, populates the RecordSet using the execute method and use .MoveFirst to loop through the record set twice.  The script runs with out error.

So there are a few difference between what I'm doing and what usslindstrom want to do (SQL vs Access, StoredProc vs SQL Statement) but I'm still managing to to populate a recordset using the execute method and loop through that recordset twice.  That must mean I'm not creating a ForwardOnly cursor, no?

DIM cn
DIM cmd
DIM rs


SET cn = CreateObject("ADODB.Connection")
SET cmd = CreateObject("ADODB.Command")

SET rs = CreateObject("ADODB.RecordSet")
rs.CursorType = 3

cn.ConnectionString = "" & _
	"Driver={Microsoft Access Driver (*.mdb)};" & _
	"Dbq=TestDB.mdb;Uid=Admin;Pwd=;"
cn.Open


sql = "SELECT * FROM ValueTable ORDER BY VALUE"

WITH cmd
 	.ActiveConnection = cn
	.CommandText = sql
	
	set rs = .Execute
end WITH


DO WHILE rs.EOF = False
	msgbox(rs("RecordNumber"))
	rs.MoveNext
LOOP

msgbox("moveFirst")
rs.MoveFirst

DO WHILE rs.EOF = False
	msgbox(rs("Value"))
	rs.MoveNext
LOOP


msgbox("END")

Open in new window

0
 
Anthony PerkinsCommented:
>>I do remember hearing something like that in the distant past, but iis it possible that it is no longer true?<<
I am not sure about MS Access, I have not used it in over 12 years.  It is true if you are using SQL Server Stored Procedures.  This make sense with SQL Server, for performance reasons you would not want to go back and forth to the server.  However, you can easily verify this by using the Recordset's Supports method as in something like the following:

If rs.Supports(adBookmark) Then
   MsgBox "Yay, I can go back!"
Else
   MsgBox "Sorry, there is no turning back now!"
End If

Here is what the MSDN documentation has to say about the Connection's Execute method:
The returned Recordset object is always a read-only, forward-only cursor. If you need a Recordset object with more functionality, first create a Recordset object with the desired property settings, then use the Recordset object's Open Method (ADO Recordset) method to execute the query and return the desired cursor type.

I cannot find similar documentation regarding the Command object and I suspect that is because it is dependant on the Provider.
0
 
usslindstromAuthor Commented:
acPerkins...  Would you happen to know how I would convert received data into variables with the array method you're showing me?


------------------------------------------
' Here goes your rs code.
arrRS = rs.GetRows()

For I = 0 to Ubound(arrRS, 2)
   MsgBox arrRS(0, I)            -- This will display the contents of the first column for each row.
Next
------------------------------------------




Right now, I'm creating variables by the recordset column values - if that makes sense.

strPrinterName = rs.Fields("PrinterName").Value


I'd imagine I'd have to convert the above to something like this:

strPrinterName = arrRS(0, I)      <---  Or whatever column the value was returned in.



Is this right?

0
 
Anthony PerkinsConnect With a Mentor Commented:
They are interchangeable.  In other words, in order to access the data from a recordset you know you have two methods, you can do it by name (rs.Fields("PrinterName").Value) or by ordinal position (rs.Fields(0).Value assuming that "PrinterName" is the first column) it does not make any difference as to the result (although admitedly the first is more readable).

Once you have loaded an array using the GetRows() method you can retrieve the values in much the same way. So if you wanted to see the value for the first column in all the rows you would use this:
For I = 0 to Ubound(arrRS, 2)
   MsgBox arrRS(0, I)            -- This will display the contents of the first column for each row.
Next

If you wanted to see the value for the second column for all the rows:
For I = 0 to Ubound(arrRS, 2)
   MsgBox arrRS(1, I)            -- This will display the contents of the second column for each row.
Next

Or if you wanted to see both:
For I = 0 to Ubound(arrRS, 2)
   MsgBox arrRS(0, I) & vbCr & arrRS(1, I)   -- This will display the contents of the first two columns for each row.
Next

I trust this is clear now.  Play with it and you will see what I mean and how easy it is to use (much easier than the cumbersome recordset)
0
 
usslindstromAuthor Commented:
So far so good.  Thank you for your changes acPerkins.

I've made a horrible loop though, in that the 2nd run through the array - the script returns the first record set over and over.

Here's my basics, in the code block below:

The process handles it fine the first time through, but the 2nd one just keeps returning the same record over and over.  I'd imagine I need a way to reset the array after the first processing, but not sure.
With cmd
    Set .ActiveConnection = cn
        .CommandText = StoredProcedure
        .CommandType = adCmdStoredProc
        .Parameters.Append .CreateParameter("@ComputerRoomNumber", adVarChar, adParamInput, 100, ComputerRoomNumber)
        .Parameters.Append .CreateParameter("@ComputerName", adVarChar, adParamInput, 100, ComputerName)

        Set rs = .Execute
End With

Set cmd = Nothing

arrRS = rs.GetRows()

    rs.Close

For I = 0 to Ubound(arrRS, 2)

Next

For I = 0 to Ubound(arrRS, 2)

Next

Open in new window

0
 
usslindstromAuthor Commented:
Disregard my last comment.  The loop is in the first rocess of "For I = 0 to Ubound(arrRS, 2) as well.

Loops all around me.  :(
0
 
usslindstromAuthor Commented:
Yup.  Nasty little loop...

The following version of code below yields me these results:


For I = 0 to Ubound(arrRS, 2)

                    strPrinterName = arrRS(0,1)
                    strProtocol = arrRS(4,1)
                    strPortNumber = arrRS(5,1)
                    strSNMPEnabled = arrRS(6,1)
                    strExtendedAttributes = arrRS(7,1)
                    strQueue = arrRS(8,1)
                    strDoubleSpool = arrRS(9,1)

                objLogFile.WriteLine "    Unparsed data:  " & strPrinterName & " / " & strProtocol & " / " & strPortNumber & " / " & strSNMPEnabled & " / " & strExtendedAttributes & " / " & strQueue & " / " & strDoubleSpool
NEXT








    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n
    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n
    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n
    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n
    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n
    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n
    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n
    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n
    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n
    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n
    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n
    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n
    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n
    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n
    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n
    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n
    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n
    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n
    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n
    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n
    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n
    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n
    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n
    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n
    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n
    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n
    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n
    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n
    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n
    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n
    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n
    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n
    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n
    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n
    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n
    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n
    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n
    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n
    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n
    Unparsed data:  DPDO-102-383BA / oemsetup.inf / Kyocera Mita FS-3830N / FS-3830n


.
.
.

Open in new window

0
 
usslindstromAuthor Commented:
**DISREGARD ALL**  There was a typo in my code.  It's been removed and all is well.  :)
0
 
usslindstromAuthor Commented:
OUTSTANDING Assistance!

Thank you very much for the code / and explinations on what it was doing guys.
0
All Courses

From novice to tech pro — start learning today.