Inserting Data into Lotus notes via c#

Question

I am tasked with inserting a record into a Lotus notes database via c#. In the past I have been able to do this simply by connecting to the database (myDatabase,nsf), then passing the

sql = Insert into MyForm (field1, field2) Values ('value1', value2')

using (var queryUpdate = new OdbcCommand(sql, db))
{
    try
    {
        result = queryUpdate.ExecuteNonQuery();
    }
}

Works fine. The problem now is, the main form (MainForm) has the actual data I need to insert into on a subform (Client Data). Accessing it using the above shows no such fields on MyForm. When I attempt to insert via:

sql = Insert into 'Client Data' (field1, field2) Values ('value1', value2')

or

sql = Insert into [Client Data] (field1, field2) Values ('value1', value2')

or

sql = Insert into Client_Data (field1, field2) Values ('value1', value2')

Always says table doesn't exist.

Does anyone know how to insert to a subform? I tried a view but this is not possible and docs state I should use insert on the actual document.

Any help or direction anyone can point me to is greatly appreciated. Thanks in advance for any and all help.

Geo...

Can't you write something in Notes to do this? Why does it have to be C#? — Rick S, Mar 03 '14 at 21:54
We are actually pushing data from another app into Notes. This is required because the new app will be replacing the Notes form but in the meantime, we need to "dup data" in both new app and notes. Other Notes apps use this info. — George, Mar 03 '14 at 21:58
Technically you could probably still do it from Notes. Have your C# app write a txt file of the data you want to import to Notes and have a Notes job pick it up and add the data. I've read data from Notes in the past, but never wrote data to it. Hope you find a workable solution. — Rick S, Mar 03 '14 at 22:02
Yeah problem there is, we are querying Notes to see if the record already exists and if so, update if needed. If not, insert. Thanks I hope someone is able to help on this...been searching for an answer for a while. — George, Mar 03 '14 at 22:05

score 1 · Answer 1 · edited May 23 '17 at 11:49

AFAIK, NotesSQL wants to map a flat form to a SQL table, so it expects a form to contain all relevant fields. It can't deal with subforms.

So you have a couple of choices...

1) Create a hidden form ClientDataFlat with all the fields that you need, use NotesSQL to insert your data into ClientDataFlat, and have an agent run in the database to rewrite the form field in all new documents.

FIELD Form := @If(form="ClientDataFlat";"Client Data";form);

Or...

2) Instead of using NotesSQL you can use the Domino interop classes from C#. The code will be pretty straightforward, because you are working directly with documents instead of through the fiction of tables that are bound to forms. There is a slight problem if you have to run your code on Win64 because IBM does not support the Notes COM classes (which are underneath the covers of the interop classes) on 64 bits, but they do mostly work and should be fine for this purpose after you get past the problems with registration.

score 0 · Answer 2 · answered Mar 03 '14 at 21:55

0

Check your database connection string. You may need to set the default database to the Lotus Notes database.

answered Mar 03 '14 at 21:55

Black Frog

11,595
1
35
66

score 0 · Answer 3 · answered Mar 04 '14 at 14:09

0

I assume that C# can access COM? Why not simply use COM to both access documents/data in the Notes database and to update/replace values in the fields in Notes?

We do that at work from Visual FoxPro, works like a charm.

answered Mar 04 '14 at 14:09

Karl-Henry Martinsson

2,770
15
25

Inserting Data into Lotus notes via c#

3 Answers3