I now have the code below and am able to only get one document to create when I know infact there are a little over 2000 in the warehouse database I am pulling from. What am I missing in my document collection? Thanks in advance!
Sub Initialize
Dim session As New NotesSession
Dim db As NotesDatabase
Dim workspace As NotesUIWorkspace
Dim codeview As NotesView
Dim con As New ODBCConnection
Dim qry As New ODBCQuery
Dim result As New ODBCResultSet
Dim Mnemonic As String
Dim doc As NotesDocument
Dim collect As NotesDocumentCollection
Set workspace = New NotesUIWorkspace
Set db = Session.CurrentDatabase
Set collect = db.UnprocessedDocuments
Call con.ConnectTo("AIS Warehouse")
Set qry.Connection = con
Set result.Query = qry
Set doc = collect.GetFirstDocument
While Not doc Is Nothing
qry.SQL = "select rtrim(code_value) as Mnemonic, description as AccessCode, " + _
"rtrim(case " + _
"when description like '__00000000000' then substring(description, 1, 2) + '%' " + _
"when description like '____000000000' then substring(description, 1, 4) + '%' " + _
"when description like '_____00000000' then substring(description, 1, 5) + '%' " + _
"when description like '______0000000' then substring(description, 1, 6) + '%' " + _
"when description like '_________0000' then substring(description, 1, 9) + '%' " + _
"when description like '__________000' then substring(description, 1, 10) + '%' " + _
"else description " + _
"end) as AccessFilter, " + _
"rtrim(cast(text1 as char(30))) as MDesc, " + _
"0 as MLevel " + _
"into #t " + _
"from caccess1 " + _
"where getdate() between begin_date and end_date " + _
"SELECT c.Mnemonic, c.AccessCode, c.AccessFilter, c.MDesc, " + _
"(SELECT COUNT(*) FROM #t p WHERE c.AccessCode <> p.AccessCode and c.AccessCode like p.AccessFilter) As MLevel, " + _
"(SELECT TOP 1 p.Mnemonic FROM #t p WHERE c.AccessCode <> p.AccessCode and c.AccessCode like p.AccessFilter ORDER BY p.AccessCode DESC) As Parent, " + _
"CASE WHEN Exists (SELECT * FROM #t p WHERE c.AccessCode <> p.AccessCode and p.AccessCode like c.AccessFilter) THEN '1' ELSE '0' END As hasChildren INTO #t2 FROM " + _
"#t c " + _
"SELECT SPACE(MLevel * 2) + MDesc + '(' + Mnemonic + ')' As Mnemonic FROM #t2 ORDER BY AccessCode "
Set result.Query = qry
Call result.Execute
If result.IsResultSetAvailable Then
Mnemonic = result.GetValue("Mnemonic")
doc.Mnemonic = Mnemonic
result.Close(DB_CLOSE)
Else
End If
Call doc.Save(False,False)
Set doc = collect.GetNextDocument(doc)
Wend
result.Close(DB_Close)
con.Disconnect
Maybe you have a look at the Designer’s help to see what NotesDatabse.UnprocessedDocuments mean. I suspect that your collection contains only a single document. You could easily check this by displaying collect.Count.
I tried to change the code to this below (to get AllDocuments). When it gets to the GetNextDocument, it loops through the code again, but the resultset isn’t available.
Sub Initialize
Dim session As New NotesSession
Dim db As NotesDatabase
Dim workspace As NotesUIWorkspace
Dim codeview As NotesView
Dim con As New ODBCConnection
Dim qry As New ODBCQuery
Dim result As New ODBCResultSet
Dim Mnemonic As String
Dim doc As NotesDocument
Dim collect As NotesDocumentCollection
Set workspace = New NotesUIWorkspace
Set db = Session.CurrentDatabase
'Set collect = db.UnprocessedDocuments
Set collect = db.AllDocuments
Call con.ConnectTo("AIS Warehouse")
Set qry.Connection = con
Set result.Query = qry
Set doc = collect.GetFirstDocument( )
While Not doc Is Nothing
qry.SQL = "select rtrim(code_value) as Mnemonic, description as AccessCode, " + _
"rtrim(case " + _
"when description like '__00000000000' then substring(description, 1, 2) + '%' " + _
"when description like '____000000000' then substring(description, 1, 4) + '%' " + _
"when description like '_____00000000' then substring(description, 1, 5) + '%' " + _
"when description like '______0000000' then substring(description, 1, 6) + '%' " + _
"when description like '_________0000' then substring(description, 1, 9) + '%' " + _
"when description like '__________000' then substring(description, 1, 10) + '%' " + _
"else description " + _
"end) as AccessFilter, " + _
"rtrim(cast(text1 as char(30))) as MDesc, " + _
"0 as MLevel " + _
"into #t " + _
"from caccess1 " + _
"where getdate() between begin_date and end_date " + _
"SELECT c.Mnemonic, c.AccessCode, c.AccessFilter, c.MDesc, " + _
"(SELECT COUNT(*) FROM #t p WHERE c.AccessCode <> p.AccessCode and c.AccessCode like p.AccessFilter) As MLevel, " + _
"(SELECT TOP 1 p.Mnemonic FROM #t p WHERE c.AccessCode <> p.AccessCode and c.AccessCode like p.AccessFilter ORDER BY p.AccessCode DESC) As Parent, " + _
"CASE WHEN Exists (SELECT * FROM #t p WHERE c.AccessCode <> p.AccessCode and p.AccessCode like c.AccessFilter) THEN '1' ELSE '0' END As hasChildren INTO #t2 FROM " + _
"#t c " + _
"SELECT SPACE(MLevel * 2) + MDesc + '(' + Mnemonic + ')' As Mnemonic FROM #t2 ORDER BY AccessCode "
Set result.Query = qry
Call result.Execute
If result.IsResultSetAvailable Then
Mnemonic = result.GetValue("Mnemonic")
doc.Mnemonic = Mnemonic
result.Close(DB_CLOSE)
Else
End If
Call doc.Save(False,False)
Set doc = collect.GetNextDocument(doc)
Wend
result.Close(DB_Close)
con.Disconnect
Subject: RE: it appears you’re closing the resultset…
I did try to comment out that line within the If statement, but get the same result - only updates to one document. I am pulling the correct mnemonic that is first though, so I know I’m hitting the correct table.
Subject: RE: make sure your collect value has more than one document
it has a count of 12 - which is how many documents i have in my notes view. what I want is to go out to the AIS warehouse and pull all of the 2000+ into new documents.
Subject: RE: make sure your collect value has more than one document
Patty… to translate your code…
You are grabbing the 12 documents out of your view.
You then get the first document…
Then you process your SELECT statement.
You then stamp the document with the Mnemonic value
Finally, you save your document and then grab the next document (out of 12)
You never actually create a new document. It almost sounds like you want to process the select statement. Then, create 2000 records from the resultset.
Subject: RE: make sure your collect value has more than one document
I am missing a Do While statement and am not quite sure where exactly to put it. I’m testing that now. I need something that says to Do While my collect is not 0. Any ideas? Thanks!
I’m trying this code below. It is updating the documents in the view, however it isn’t pulling the next mnemonic code from the AIS Warehouse table. It is putting the same code on all docs in the view.
If result.IsResultSetAvailable Then
Do Until result.IsEndofData
doc.form = "Codes"
doc.Mnemonic = result.GetValue("Mnemonic")
Call doc.Save(True,False)
Set doc = collect.GetNextDocument(doc)
Loop
Else
End If
Ok, awesome, almost there! Thank you all so much for your help! One last thing . . . when I get to the end of my resultset, I get an Object Variable not set error as the code tries to loop again. The error is on the doc.form = “Codes” line. How do I get it to realize it is the end of the result set? If I stop the debugger it is ok, but am I missing another piece?
Or, I guess the better question is how do I get the code to create documents for all the codes in my table without me having to make sure there are enough documents to begin with in my view? Does that make sense?