Subject: Sorted!
Hi Ed
Really appreciate your comments, although I managed to get it running a different way. Here’s the code if it helps anyone else. The main thing I seemed to notice was that even though the result of the “execute” statement is set to “nothing” it still couldn’t be part of the “while” process. As a result I copied the information into an array and processed that afterwards.
Sub Initialize()
On Error Goto handle_error
REM **************************************************
REM Define temporary variables
REM **************************************************
Dim vWebSession As New notessession
Dim vThisDatabase As notesdatabase
Dim vThisDocument As notesdocument
Dim newDoc As NotesDocument
Dim ses As New LCSession
Dim con As New LCConnection ("oledb")
Dim MyFldList As New LCFieldList
Dim MyFldList2 As New LCFieldList
Dim MyFld As LCField
Dim MyFld2 As LCField
Dim MyFld3 As LCField
Dim MyFld4 As LCField
Dim SQL As String
Dim MyResult As String
Dim count As Integer
Dim X As Integer
Dim Y As Integer
REM **************************************************
REM Initialize variables
REM **************************************************
Set vThisDatabase = vWebSession.CurrentDatabase
'Set vThisDocument = vWebSession.DocumentContext
ses.ClearStatus
'Set connection details
With con
.Provider = "SQLOLEDB"
.Server = "Server"
.Database = "Database"
.Metadata = "TableName"
.userid = "-----"
.password = "-----"
End With
On Error Resume Next
con.Connect
con.writeback = True
If Err = False Then
Else
Print "Connection failed with error " & Err & ": " & Error
Exit Sub
End If
REM **************************************************
REM Do stuff here
REM **************************************************
SQL = "SELECT TransactionID, UserID, projectId, questionnaireId from ProjectDetail"
con.Execute sql,myfldlist
Set myfld = myfldlist.GetField(1)
Set myfld2 = myfldlist.GetField(2)
Set myfld3 = myfldlist.GetField(3)
Set myfld4 = myfldlist.GetField(4)
'Print Open of page
Print "Successfully connected to SQLServer."
Print "<table>"
Print "<tr>"
Print "<td>Row</td>"
Print "<td>Transaction ID</td>"
Print "<td>User ID</td>"
Print "<td>Project ID</td>"
Print "<td>Questionnaire ID</td>"
Print "<td>Notes UNID</td>"
'Check for empty list
If con.Fetch(myfldlist) = 0 Then
Print "No Records Located"
Goto handle_error
End If
'Set initial Value of Y
Y = 0
'Process the list
While (con.Fetch(myfldlist) > 0)
If myfld2.text(0) = "AdminA" Then
Redim Preserve toUpdate(4, Y)
toUpdate(0,Y) = myfld.Text(0) 'Tr ID
toUpdate(1,Y) = myfld2.Text(0) 'User ID
toUpdate(2,Y) = myfld3.Text(0) 'Project ID
toUpdate(3,Y) = myfld4.Text(0) 'Questionnaire ID
toUpdate(4,Y) = Cstr(y+1)
Y = Y+1
End If
Wend
'Loop through all results located
For X = 0 To Y-1
'Create a NotesDocument
Set newDoc = vThisDatabase.CreateDocument
'Populate It
newDoc.transID = toUpdate(0,X)
newDoc.userID = toUpdate(1,X)
newDoc.projectID = toUpdate(2,X)
newDoc.quID = toUpdate(3,X)
'Save it
Call newDoc.Save(True,True)
'Write update back to SQL
con.Execute "UPDATE ProjectDetail SET NotesUNID='"+newDoc.UniversalID+"' WHERE transactionid="+toUpdate(0,X), Nothing
'Print Result
Print "<tr>"
Print "<td>" & toUpdate(4,X) & "</td>"
Print "<td>" & toUpdate(0,X) & "</td>"
Print "<td>" & toUpdate(1,X) & "</td>"
Print "<td>" & toUpdate(2,X) & "</td>"
Print "<td>" & toUpdate(3,X) & "</td>"
Print "<td>" & newDoc.UniversalID & "</td>"
Print "</tr>"
Next
'Print Table Close
Print "</table>"
'Close session
con.Disconnect
Set myfldlist = Nothing
Set myfld = Nothing
Set con = Nothing
Set ses = Nothing
Exit Sub
REM **************************************************
REM Error Handler
REM **************************************************
handle_error:
Dim stat$, errcode As Long, msg$
If ses.Status <> LCSUCCESS Then
ses.Getstatus stat, errcode, msg
If (ses.Status = LCFAIL_EXTERNAL)Then
Print "ODBC message: " & msg & " code #" & Cstr(errcode), 0, _
"error number " & Err & " line " & Erl
Else
Print "Connector message: " & Text, 0, "error number " & _
Err & " line " & Erl
End If
Else
Print Error, 0, "error number " & Err & " line " & Erl
End If
Exit Sub
End Sub