NotesSQL & Multiple-step operation generated errors

I am using version 3.02g of the Notes ODBC drivers to connect from Visual Basic to a Notes app.

Reading the data from the DB is all fine, I can step through the recordsets and look at the columns of data so I know I have my DSN’s set up OK, however updating any record produces the error “Multiple-step operation generated errors. Check each status value”

I have tried all possibilities that I can think of here - anyone have any ideas on this? Further info - If I try & link the tables (views) into an access database, I can read fine, but trying to update I see an error about “No INSERT/ALTER…on the view” This is native within access and therefore nothing to do with my shabby code, so I am suspecting the ODBC driver itself.

Here’s the code anyway.

Dim NotesConn As New ADODB.Connection

Set NotesConn = CreateObject(“ADODB.Connection”)

NotesConn.ConnectionString = “DSN=myDSN”

NotesConn.Open

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

rs.ActiveConnection = NotesConn

rs.CursorLocation = adUseClient

rs.CursorType = adOpenKeyset

rs.LockType = adLockOptimistic

rs.Open “Select * from $Users”, NotesConn ’ ($Users is my view name)

rs.MoveFirst

rs!UserNameField = “Fred” *** errors on this line ***

'rs.Fields(“UserNameField”).Value = “Fred” (this didnt work either)

rs.Update

NotesConn.Close

Set rs = Nothing

Set NotesConn = Nothing

And the field UserNameField in Notes is just text field - no lookups - not computed etc.

Subject: NotesSQL & Multiple-step operation generated errors

Just as the error states, you can not update / insert view data, it’s just like in Notes, you can’t modify the data there, you have to open up the document to modify it. Views are representations of documents and include formulas and constant data that don’t necessarily match the document 100%. So, read the documentation, you need to update the document (Form), so, you can either select the document based on the NoteUNID (see example in documentation) or just select all documents based on the Form used and update that documents that way.

Ron

Subject: RE: NotesSQL & Multiple-step operation generated errors

Hi Ron

Thanks for this - useful info which I had missed in the documentation. The bad news is that in all my previous attempts I had tried this by looking at the document - and I have tested again since your post.

The code fails at the rs.Update line - with the error “Insuficient base table information for updating or refreshing.”

I have tried with the recordset with an Addnew command, and editing an old doc. Both fail at the Update line.

There is only one field on my Notes form as well which is the plain text one - so I am not missing any data or similar.

The other point is that when I link either the view or the from in access - I again get errors about cant insert row. No code involved here.

Andrew

Subject: RE: NotesSQL & Multiple-step operation generated errors

Let me answer the Access one first, this has been answered here before, you have to set up a primary key, if you don’t, Access won’t let you insert / update. Set the primary key as the NoteUNID, which is the document Unique ID, NotesSQL exposes that through that field. Make that field (and other internal fields) visible by turning on the option in the DSN named Return Notes implicit fields. This is disabled by default.

It’s extreemly possible the problem in VB is the exact same problem, look to see how to enable the primary key (there’s probably some vb api command to do that) and set it to NoteUNID.

This is also inside the doc, though it may be hard to find…

http://www-12.lotus.com/ldd/doc/notessql/3.0.2f/notessql.nsf/66208c256b4136a2852563c000646f8c/7005c9ff90581a4385256dd7004d81f5?OpenDocument

Ron