I'm attempting to attach to a SQL server, run a query, and return an integer value as the result of the query.

What works:
I successfully connect to the database:
$objConnect = CreateObject("ADODB.Connection")
$objConnect.Open("$CONNECTION_STRING")

I then run my query with a SQL statement:
$objRec = $objConnect.execute("SELECT COUNT(*) AS REC_COUNT FROM tablename WHERE field = 'N'")

What doesn't work:
$objRec should contain the integer result of the query, right? Wrong! it appears as a longish number (15239064 or some such) - not what I expected. An equivalent VB script returns a value of 4 or 5 for the same query.

Does anyone have insights on how I should get my integer value?

Thanks in advance!
MarkR

You would be better off creating a recordset object first, running your SQL query without the COUNT function embedded, to simply return the results to the recordset object, then using the RecordCount() method to get an integer value representing the number of records. Here's an example from a script I wrote to write logon data to an Access database.

code:

---

$DATABASE = "\\corpfs01\rollout$$\logon.mdb"
$DSN="Driver={Microsoft Access Driver (*.mdb)}; DBQ=$DATABASE"
;Create SQL statements to check/write/update database
$CHECK_ENTRY_USERS = "SELECT * FROM TBL_USERS WHERE USERNAME='@USERID';"
$CHECK_ENTRY_COMPUTERS = "SELECT * FROM TBL_COMPUTERS WHERE WORKSTATION='@WKSTA';"
$Connection = CreateObject("ADODB.Connection")
$Command = CreateObject("ADODB.Command")
$Recordset = CreateObject("ADODB.Recordset")
;Check for connection object
if $Connection
	
	;Open connection to database
	$Connection.ConnectionString = $DSN
	$Connection.Open()
	;Check for existing records for the current workstation/user
	;Add new record or update existing as required
	;Check for User details first
	$Command.ActiveConnection = $Connection
	$Recordset.CursorType = 3
	$Recordset.LockType = 3
	$Recordset.ActiveCommand = $Command
	$Command.CommandText = $CHECK_ENTRY_USERS
	$Recordset.Open($Command)
	
	;Create new record if none exists to update
	If $Recordset.RecordCount < 1
		$Recordset.AddNew
	Endif
  

---

MS documentation on ADO can be found here with examples in VB and VBS for nearly all methods and properties.

HTH

The VB script automaticaly launch this command when you called the object:
$objRec("field 1")
that's why you got the value

You'll need to do this explicitly with kixtart :
$objRec("REC_COUNT")