Accessing Lookup Tables via API

[genehughson]

Is there a way to access lookup tables via the API?

[John Morris]

Absolutely!

You need to have SoftPro Select v2.2 Service Pack 2 (SP2) in order to use this new API function. Once you have it installed, it is pretty easy to use. Currently, the lookup API supports reading, writing and deleting lookup table records. It does not allow lookup structure changes. In other words, you cannot add/remove columns using this API. It also has a limited query capability in this release. It is limited to querying against only one column at a time.

The lookup API returns ADO.NET DataTables, so it should be easy to discover the information once it's returned. You can even bind it to a grid to view it!

Lookup Sample Code

Code: Select all

//create the server connection
NetworkCredential credentials = new NetworkCredential("admin", "Passw0rd", Constants.SelectDomain);
SelectServer sps = new SelectServer("http://localhost/SelectServer", credentials);
sps.EnsureAuthenticated();

//get the lookups service
Lookups lookups = sps.GetService<Lookups>();

// The returned table is a basic ADO.NET DataTable. You can inspec the returned columns for more information
DataTable table = lookups.GetTable("Endorsements"); //use the lookup table's Display Name here

//Add a row
DataRow row = table.NewRow();
row["ColumnDisplayName"] = new LookupValue() { Value = "MyValue", IsFormula = false };

//Delete row 10
table[9].Delete();

//update row 5, column "Test" - note: do not edit the value in place, treat it like a struct, i.e. use the new keyword
table[4]["Test"] = new LookupValue() { Value = "My New Value", IsFormula = false };

//Commit the changes
lookups.AcceptChanges(table);

[czentman]

When I try to set this table to datagridview's datasource, I get the following error in the application

"System.FormatException: Formatted value of the cell has a wrong type. To replace this default dialog please handle the DataError event."

The funny thing is that it works with grid that's instantiated in the code (you gave me that code in the other thread) -
Dim grid As New DataGrid()
grid.DataSource = table
This works but I didn't want to have it instantiated in the code, I wanted it as part of the design elements of my form/page.

That's why I dragged on to my design page - a datagridview (I didn't see anything just datagrid) and I get this wierd error. Why would it work with a grid but not a gridview?

[czentman]

John, Is there something special I have to do to use the datagridview? It's a much nicer control than a plain datagrid so I'd rather use it, I just keep getting this strange error with the format exception. Any ideas?

[roteague]

I think it is the null values that are causing you the error you are getting. You need to format the columns to accept null values, but in this case, the columns are being added when the grid is being bound - kind of a catch 22.

However, you can put an empty catch handler:

Code: Select all

    Private Sub DataGridView1_DataError(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewDataErrorEventArgs) Handles DataGridView1.DataError
    End Sub

You data should display just fine.

[czentman]

Thanks. The empty error handler did the job. Big help. THank

[czentman]

The data displays correctly. I think I was able to do the deletes (except keep getting errors with deleting the last row). But I'm having alot of trouble with adds and edits. It must be getting into the data-error handler but not out of it for some reason. It allows me to only edit/add on cell. Then I cannot change another cell after that. If I remove what I entered and go to the next cell, that works fine. But I can't seem to add to more than one cell.

[czentman]

How can I format the columns to accept null values? I tried several ways and can't get it.

[roteague]

How can I format the columns to accept null values? I tried several ways and can't get it.

Try formatting the data in the GridViews RowDataBound Event Handler. Something like:

Code: Select all

Sub detailsGridView_RowDataBound(ByVal sender As Object, _
  ByVal e As GridViewRowEventArgs)
    If e.Row.RowType = DataControlRowType.DataRow Then
        'Manipulate the row data here
    ElseIf e.Row.RowType = DataControlRowType.Footer Then
    End If
End Sub

[czentman]

This didn't help me at all.