Subject: RE: Functions and Arrays
Matt, I can only get data from my func1 or Dept50 function. Posting my code as you suggestted, can you tell me what I’m missing? Thanks.
[Options]
Option Public
Uselsx “*LSXODBC”
[Declarations]
Dim TagArray(5,1) As String
Dim db As notesdatabase
Dim DetDoc As NotesDocument
[Initialization]
Sub Initialize
Call Dept50
End Sub
[Dept50]
Sub Dept50
'===============================
’ Dim DetDoc As NotesDocument
'Set DetDoc = New notesdocument(db)
'Detdoc.form = "Details"
'===============================
Dim session As New notessession
Dim con As New ODBCConnection
Dim qry As New ODBCQuery
Dim result As New ODBCResultSet
Dim comptag As String
Dim comptime As String
Dim compvalue As String
Dim compstatus As String
Set db = session.currentdatabase
Set qry.Connection = con
'build a list of all the tags to gather
BuildTagArray
BegDateTime = Format(Datenumber ( Year(Today) , Month(Today) , Day(Today) ) , "dd-mmm-yy") + " 0:00"
EndDateTime = Format(Datenumber ( Year(Today) , Month(Today) , Day(Today) ) , "dd-mmm-yy") + " 0:59"
'connect to PI and pass username
flag = con.ConnectTo("Server1", "password")
If con.GetError <> DBstsSUCCESS Then
Messagebox con.GetExtendedErrorMessage,, con.GetError & " " & con.GetErrorMessage
Exit Sub
End If
'loop through all the tags and get the data from PI - should be one entry returned for each tag
For xx = 0 To 2
qry.SQL = | SELECT tag, time, status, value FROM picomp WHERE tag = '| + TagArray(xx,0) + |' AND time >= DATE('| + _
BegDateTime + |') AND time <= DATE('| + EndDateTime + |') |
Set result.Query = qry
If qry.GetError <> DBstsSUCCESS Then
Messagebox qry.GetExtendedErrorMessage,, qry.GetError & " " & qry.GetErrorMessage
con.Disconnect
Exit Sub
End If
result.Execute
If result.GetError <> DBstsSUCCESS Then
Messagebox result.GetExtendedErrorMessage,, result.GetError & " " & result.GetErrorMessage
con.Disconnect
Exit Sub
End If
If result.IsResultSetAvailable Then
Do
result.NextRow
comptag = result.GetValue ( "TAG" )
comptime = result.GetValue( "TIME" )
compvalue = result.GetValue( "VALUE" )
If comptag <> "" Then
If compvalue = 0 Then
compvalue = "N/A"
Else
compvalue = compvalue
End If
TagArray(xx,1) = compvalue
Else
TagArray(xx,1) = Round(Cdbl(compvalue),2)
End If
Loop Until result.IsEndOfData
result.Close(DB_CLOSE)
Else
Messagebox "No result for " + TagArray(xx,0) + " from 1122."
End If
Next xx
con.Disconnect
'go get the tags and associate them with the fields on the doc, and build the doc
BuildDetailDoc
End Sub
[Dept56]
Sub Dept56 (DetDoc As NotesDocument)
'===============================
'Dim DetDoc As NotesDocument
'Set DetDoc = New notesdocument(db)
'Detdoc.form = "Details"
'===============================
Dim session As New notessession
Dim con As New ODBCConnection
Dim qry As New ODBCQuery
Dim result As New ODBCResultSet
Dim comptag As String
Dim comptime As String
Dim compvalue As String
Dim compstatus As String
Set db = session.currentdatabase
Set qry.Connection = con
'build a list of all the tags to gather
BuildTagArrayZ
BegDateTime = Format(Datenumber ( Year(Today) , Month(Today) , Day(Today) ) , "dd-mmm-yy") + " 0:00"
EndDateTime = Format(Datenumber ( Year(Today) , Month(Today) , Day(Today) ) , "dd-mmm-yy") + " 0:59"
'connect to PI and pass username
flag = con.ConnectTo("Server2", "password")
If con.GetError <> DBstsSUCCESS Then
Messagebox con.GetExtendedErrorMessage,, con.GetError & " " & con.GetErrorMessage
Exit Sub
End If
'loop through all the tags and get the data from PI - should be one entry returned for each tag
For xx = 0 To 2
'qry.SQL = | SELECT tag, time, status, value FROM picomp WHERE tag = '| + TagArray(xx,0) + |' AND time >= DATE('| + _
'BegDateTime + |') AND time <= DATE('| + EndDateTime + |') |
qry.SQL = |SELECT tag, time, status, value FROM picomp WHERE tag = '| + TagArray(xx,0) + |' AND time >= DATE('| +_
Format(Datenumber ( Year(Today) , Month(Today) , Day(Today)) , "dd-mmm-yy") + | 0:00')|
Set result.Query = qry
If qry.GetError <> DBstsSUCCESS Then
Messagebox qry.GetExtendedErrorMessage,, qry.GetError & " " & qry.GetErrorMessage
con.Disconnect
Exit Sub
End If
result.Execute
If result.GetError <> DBstsSUCCESS Then
Messagebox result.GetExtendedErrorMessage,, result.GetError & " " & result.GetErrorMessage
con.Disconnect
Exit Sub
End If
If result.IsResultSetAvailable Then
Do
result.NextRow
comptag = result.GetValue ( "TAG" )
comptime = result.GetValue( "TIME" )
compvalue = result.GetValue( "VALUE" )
If comptag <> "" Then
If compvalue = 0 Then
compvalue = "N/A"
Else
compvalue = compvalue
End If
TagArray(xx,1) = compvalue
Else
TagArray(xx,1) = Round(Cdbl(compvalue),2)
End If
Loop Until result.IsEndOfData
result.Close(DB_CLOSE)
Else
Messagebox "No result for " + TagArray(xx,0) + " from Dept56."
End If
Next xx
con.Disconnect
'go get the tags and associate them with the fields on the doc, and build the doc
'BuildDetailDoc
'Call Dept50 (DetDoc)
success = detdoc.Computewithform(True, True)
Call detdoc.save(True,True)
End Sub
[BuildTagArray]
Sub BuildTagArray
TagArray(0,0) = "50FC106"
TagArray(1,0) = "50FI218"
TagArray(2,0) = "50FQT_PRODUCT"
End Sub
[BuildTagArrayZ]
Sub BuildTagArrayZ
TagArray(0,0) = "56F106C"
TagArray(1,0) = "56F112C"
TagArray(2,0) = "56F101"
End Sub
[BuildDetailDoc]
Sub BuildDetailDoc
Dim DetDoc As NotesDocument
Set DetDoc = New notesdocument(db)
Detdoc.form = "Details"
For yy = 0 To 5
tag = TagArray(yy,0)
value = TagArray(yy,1)
Select Case tag
Case "50FC106" : DetDoc.t50_1_lb = value
Case "50FI218" : DetDoc.t50_2_lb = value
Case "50FQT_PRODUCT" : DetDoc.AceticProduct = value
Case "56F106C" : DetDoc.t56_1_lb = value
Case "56F112C" : DetDoc.t56_2_lb = value
Case "56F101" : DetDoc.t56_3_lb = value
End Select
Next
'success = detdoc.Computewithform(True, True)
'Call detdoc.save(True,True)
Call Dept56 (DetDoc)
End Sub