3 Replies Latest reply on Mar 16, 2011 6:59 PM by philco

    Manipulating AF Tables programmatically


      I am trying to implement programmatic access to a Table in the AF library.


      I have a table called [User Settings] with columns called [User ID], [KVA Upper] and [KVA Lower]. User ID is a string that is supposed to be the ID of the connecting user and the other 2 are numeric fields.


      I have been trying to update this using:

          AFTable t = myDatabase.Tables[tableName];
          t.Table.LoadDataRow(values, true);

       The  problem is that, because there are no key fields, if I try to change any of the numeric values, it creates a new row instead of updating the existing row. To use LoadDataRow, I think there are 2 options:

      • I need the [User ID] field to be the primary key but I can't see anywhere in AF System Explorer to set that.
      • Otherwise I will need to go through the table row by row to find the one with the matching User ID and then change that row but I can't see anything in the DataTable class to enable me to do that either!

      I realise that dataTables are not OSI specific and I have been looking at http://msdn.microsoft.com/en-us/library/9186hy08 which is not helping! Can anyone give me some pointers?


      Also, to get the current connection, I have been using

          PISystem mySystem;
          mySystem = af[ServerName];
          string username = mySystem.ConnectionInfo.AccountName

      but AccountName is blank. How should I be getting the user credentials?


      --- Alistair.





        • Re: Manipulating AF Tables programmatically

          I have just found the answer to my 2nd question:


           string username = mySystem.CurrentUserName


          but I am still struggling with the first. I am thinking of programmatically setting the [User ID] columns Unique property to True...


          ... And that fails. Instead of creating another row, I get an exception: "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints." so it is still trying to create a new row.


          --- alistair.

            • Re: Manipulating AF Tables programmatically

              You should be able to find plenty of examples for working with DataTables on Microsoft's site.  For example: How to: Edit Rows in a DataTable at msdn.microsoft.com/.../tat996zc(v=vs.80).aspx.


              Code below is an example of how to modify a row:

              DataTable myTable = myAFTable.Table;
              Data.DataRow[] rows = myTable.Select("User ID='User1');
              rows[0]["MyValueColumn1"] = 123;


                • Re: Manipulating AF Tables programmatically

                  Hi Alistair,


                  I had the same issue at some point. You just need to set a primary key programmatically prior to make your changes. Here's an example: (note that in this example the key is composed of a single field).

                  Dim myPISystems As New PISystems
                  Dim myPISystem As PISystem = myPISystems.DefaultPISystem
                  Dim myDB As AFDatabase = myPISystems.Item(yourAFServerName).Databases.Item(yourAFDatabase)
                  Dim arPrimaryKey(0) As System.Data.DataColumn
                  arPrimaryKey(0) = myDB.Tables(yourTablename).Table.Columns(yourColumnname)
                  myDB.Tables(yourTablename).Table.PrimaryKey = arPrimaryKey