Getting data from lotusnotes db--Clueless!! 1

[swaybright]

I am totally clueless on how get data from a lotus notes db. I have spent days on this and am extremely frustrated.
What I want to do:
1: open a lotusnotes database
2: go to desired view(note the views are in folders)
3: search view with a search string
4: return hit set
5: see if hit set has records or has more than one record if has one record go to 6 if has no records go to 8 if has more than one record goto to error
6: open document in hit set
7: return specified field value in document (right now I'm just doing this in a message box, but later I want to put these value(s) in existing access table(s)
8: change view to a second view (there are two possible places that the document can be within the database)
9: verify single hit if not go to error
10: do step 7

I can't get past searching for a document and returning a value. Here is the code I am using. I know it is all fouled up, but like I said, I have no idea what I am doing. I am trying to piece together code I have found on this site and others with little understanding, so I can't troubleshoot it. I am getting a type mismatch error on the line indicated below. and the debugger is not returning the value for testvar that I expected; it is returning an empty string ("&quot. Please help!!!

Dim session As Object 'notes session
Dim db As Object 'notes database
Dim view As Object 'notes view
Dim searchview As Object 'notes search results
Dim doc As Object 'notes document
Dim DocCount As Long
Dim ViewStr As String
Dim testvar As string
ViewStr = "3. R&D\By Period"

Set session = CreateObject("Notes.NotesSession&quot
Set db = session.GETDATABASE("SERVERNAME", "user\chemical\millad.nsf&quot
Set view = db.GetView(ViewStr)
Set doc = view.getfirstdocument
testvar = doc.getitemvalue("tll&quot 'Here is where I get the error
MsgBox "The value in t11 is " & testvar _
& ".", vbOKOnly, "LotusNotes Value"

 

[gransbpa]

Hi Swaybright.

I see you use notes objects. You have an alternative: approach the notes views with an DSN. Therefore you'll have to download a SQL driver (I think it's NotesSQL300.exe for WIN 2000), you'll have to create an ODBC connection to the notes database using the driver. You can specify the view. If this has been stored (locally), you can access the connection by creating a new link from you access database window. You then have a virtual table which you can use for all your queries.

Good Luck!

 

[swaybright]

Thanks for the reply. Please forgive me, I understand the concept, but have no idea how to implement. However, I have made some progress on my earlier attempt. And need someone to help me fill in the missing piece. I have changed my set statements to the following

ViewStr = "3. R&D\By Period"

Set session = CreateObject("Notes.NotesSession&quot
Set db = session.GETDATABASE("Servername", "user\chemical\millad.nsf&quot
Set view = db.GetView(ViewStr)
DocCount = view.FTSearch(Searchstr, 0)
Set doc = view.GetFirstDocument
testvar = doc.getitemvalue("ServiceRequested&quot
MsgBox "The value in ServiceRequested is " & testvar(0), vbOKOnly, "LotusNotes Value"

Exit Function

I can search and bring up the correct document? in the view. Before the search, this view has several documents? (lots of entries). I can get the value for a value in one of the columns, but what I want to do is get a value in the fields of the open document(not in the columns). What command should I use to do this?

 

[ggriffit]

check out

http://www.greggriffiths.org/webdev/serverside/asp_notes/

which primarily covers access a LN db from ASP, but should extend to Acess as well.

 

[swaybright]

I see how to get the information in the columns. But that is not what I want. I want field level data.
For example. With the code above, I can return the value of any of the columns in the view. I can actually get the record set that I want from the search.
Now I have (thankfully) a single record (I know this isn't the correct terminology, but please bear with me). I want to open up that record (document?) and get the values for the fields in that record (document?)
To put it another way, the search gives me a hit set (in this case one item in the hit set). If I were manipulating Notes manually, the action would be to double click the item in the hit set and open it, then I could copy the desired field and paste it anywhere. This is what I want to do programmatically.

Any thoughts?

 

[swaybright]

ggriffit,

I've realized that I cannot get the data from the fields unless they are put into columns in a view. Is this correct? So, I tried to create a custom view, but I only have the option to make it private. So I can't search this view (why that should be the case is beyond me) Is there a way to connect using your suggestion to this private view?
If so, where is this view and corresponding data stored?

Thanks,

Shane

 

[ggriffit]

I'll ask a few guys most of the time we've just used public views on the Domino Server.

 

[swaybright]

Thanks for the help. I've done some more tinkering with my original approach because of dwarfthrower's input (thread245-644197). The following code actually works!! Note the bold statement. This is what I was missing earlier and couldn't get the syntax right. I can now pull data from any document field I want. Thanks for the sounding board.

Shane


Public Function SearchTSR(Searchstr As String)
Dim session As Object
Dim db As Object
Dim view As Object
Dim doc As Object
Dim DocCount As Long
Dim ViewStr As String
Dim testvar As Variant
Dim sSQL As String


If fIsAppRunning = False Then
MsgBox "Lotus Notes is not running" & Chr$(10) _
& "Make sure Lotus Notes is running and you have logged on."
Exit Function
End If

ViewStr = "3. R&D\By Period"

Set session = CreateObject("Notes.NotesSession&quot
Set db = session.GetDatabase("servername", "user\chemical\millad.nsf&quot
Set view = db.GetView(ViewStr)
DocCount = view.FTSearch(Searchstr, 0)

InvalidTSR:
If DocCount > 1 Then
MsgBox "The TSR number you entered resulted in more than one hit." & vbCrLf _
& "Please enter a valid TSR number.", vbCritical + vbOKOnly, "Incorrect TSR Number"
Exit Function
End If

If DocCount = 0 And ViewStr <> &quot;9. Archived\By TSR#&quot; Then
ViewStr = &quot;9. Archived\By TSR#&quot;
Set view = db.GetView(ViewStr)
DocCount = view.FTSearch(Searchstr, 0)
GoTo InvalidTSR
Else
Call view.FTSearch(Searchstr, 0)
Set doc = view.GetFirstDocument
testvar = doc.getitemvalue(&quot;t41&quot
MsgBox &quot;The value in t41 is &quot; & testvar(0) & vbCrLf & &quot;DocCount is &quot; _
& DocCount, vbOKOnly, &quot;LotusNotes Value&quot;
End If

End Function