ADO Update question

[freavis]

I have a script that updates records in a DB and I want to know how many rows were updated when the script is done. Where and what do I need to add to this script to accomplish that?

Dim db

db = InputBox("Enter the database name you want to update.&quot


'Create Connection
set con = CreateObject("ADODB.Connection&quot
strCon = "driver={SQL SERVER};server=COPPER;uid=hsi;pwd=mypw;database=" & db & ""
con.Open strCon

'Create Command
set doit = CreateObject("ADODB.Command&quot
set doit.ActiveConnection = con

sql = "UPDATE hsi.useraccount " & _
"SET userpref2 = 8415465 " & _
"WHERE userpref2 = 8413413 "

set rs = CreateObject("ADODB.Recordset&quot

rs.Open sql, con

wscript.echo "Users updated for " & db &""

set con = nothing
set doit = nothing

wscript.quit

 

[dilettante]

Well you may be working too hard, for example you don't need a Connection, a Command, and a Recordset here. It can all be done with one ADO Command object.

You also don't want to use the Open method, use Execute instead. Execute takes three parameters, the first being RecordsAffected, the second Parameters, and the third Options. Here we only need the first and third ones.

Hmmm...

Set up a test "database"

I don't have a SQL Server database handy, and no Access on this machine either. Here's a quick example using Jet to update a named range of cells in an Excel workbook. The Excel/Jet combo is just simulating a database to be updated via ADO:

* I created a workbook called MyWorkBook.xls

* I have one worksheet called MyWorkSheet

* I set up MyWorkSheet as follows. The {x} symbols below represent the row and column IDs of the table cells. We can ignore these for our purposes.

            {A}    {B}
     {1} Resident  Age
     {2} Fred       32
     {3} Wilma      29
     {4} Barney     32
     {5} Betty      27

* I created a named cell range covering A1 - B5 and called it Bedrock.

This gives us a simulated database MyWorkBook.xls with a "table" called Bedrock having two columns named Resident and Age.

Write an update script

I want a script that will find every row with Age = 32 and change the Age to 34, then show me the number of rows that were updated.

I'll call it ADOCmd.wsf because by writing a Windows Script File instead of a naked .vbs file I can use the <reference> and <object> elements. The first will give me access to the ADO constants w/o having to define them myself. The second will let me declare the Command object without messing about with a Dim and a CreateObject( ) and a Set ... = Nothing and all that jazz.

You can do this the regular way (as a .vbs) but you'll need to look up and define the ADO constants then.

Run the script

We run the script and out comes our message:

     Updated 2 rows

Then we can open the workbook MyWorkbook.xls in Excel and we see:

            {A}    {B}
     {1} Resident  Age
     {2} Fred       34
     {3} Wilma      29
     {4} Barney     34
     {5} Betty      27

Just as we wanted, expected, and saw reported: 2 rows were updated.

Listing of ADOCmd.wsf

Here is the listing:

<job id=&quot;ADOCmd&quot;>
  <reference object=&quot;ADODB.Command&quot; />
  <object id=&quot;objCmd&quot; progid=&quot;ADODB.Command&quot; />
  <script language=&quot;VBScript&quot;>
    Option Explicit
    Dim lngRecordsAffected

    objCmd.ActiveConnection = &quot;Provider = Microsoft.Jet.OLEDB.4.0;&quot; _
                            & &quot;Data Source = MyWorkBook.xls;&quot; _
                            & &quot;Extended Properties = Excel 8.0;&quot;
    objCmd.CommandText = &quot;UPDATE Bedrock SET Age = 34 WHERE Age = 32&quot;
    objCmd.Execute lngRecordsAffected, Null, adCmdText And adExecuteNoRecords

    WScript.Echo &quot;Updated &quot; & CStr(lngRecordsAffected) & &quot; rows&quot;
  </script>
</job>

Note the use of Null for the second Execute method parameter (whch we don't need in this case, thus I pass Null). Note also the Options mask passed as the third parameter. Here I've said &quot;the CommandText property is not a file name or URL, etc. but is indeed the text of a command to execute&quot; and also &quot;don't return any records from this Execute call.&quot;


Hope this is clear and gives you what you need. It should work just fine with your SQL Server databases. Execute is documented at:

http://msdn.microsoft.com/library/d...ry/en-us/ado270/htm/mdamth02_4.asp?frame=true