' My First ADO Script
' prints out number of records and fields results from executing a SQL Query

' create and initialize variables
Dim objConnection 	: Set objConnection = CreateObject("ADODB.Connection")
Dim objRecordset 	: Set objRecordset = CreateObject("ADODB.Recordset") 
Dim strSql 			: strSQL = "Select top(10) * From Customers order by lname, fname"
Dim strConnectionString 
strConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" &_
							"Persist Security Info=False;Initial Catalog=BatteryCo;Data Source=.\WinCC" 

' 1. Connect to sql server database
objConnection.ConnectionString = strConnectionString
objConnection.Open

' 2. execute SQL Command and store results in RecordSet object
objRecordset.Open strSQL, objConnection, 3 

' 3. View results
HMIRuntime.Trace "The [BatteryCo] database [Customers] table has " & _
					 objRecordset.RecordCount & " records and " & _
					 	objRecordset.Fields.Count & " Fields." & vbCrlf & vbCrlf
					 	
If (objRecordset.RecordCount > 0) Then ' make sure we have at least 1 record returned. 	
	objRecordset.MoveFirst 	' Move to first record in recordset
	Dim rIndex, fIndex		' field and record index variables 
	
	' print column names; the column collection begins at 0
	For fIndex = 0 To objRecordset.Fields.Count - 1
		HMIRuntime.Trace CStr(objRecordset.Fields(fIndex).Name) & ","
	Next
	
	HMIRuntime.Trace vbCrlf ' move to next line in debug window
	' loop through each record returned. The record index begins at 1
	For rIndex = 0 To objRecordset.RecordCount - 1
		' loop through each column and print value
		For fIndex = 0 To objRecordset.Fields.Count - 1
			HMIRuntime.Trace CStr(objRecordset.Fields(fIndex).Value) & ","
		Next
	HMIRuntime.Trace vbCrlf ' move to next line in debug window
	objRecordset.MoveNext   ' go to next record. 
	Next
	
Else
	HMIRuntime.Trace "Selection returned no fields" & vbNewLine 
End If					 	

' 4. Close and delete ADO Objects. 
objRecordset.Close
objConnection.Close
Set objRecordset = Nothing
Set objConnection = Nothing