Accessing Lookup Tables via API
Moderator: Phil Barton
-
- Posts: 1
- Joined: Fri Oct 03, 2008 2:41 pm
Accessing Lookup Tables via API
Is there a way to access lookup tables via the API?
-
- Posts: 411
- Joined: Thu Sep 11, 2008 11:35 am
- Location: Raleigh, NC, USA
- Contact:
Re: Accessing Lookup Tables via API
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
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);
John Morris
Sr. Software Architect
SoftPro
Sr. Software Architect
SoftPro
Re: Accessing Lookup Tables via API
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?
"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?
Re: Accessing Lookup Tables via API
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?
Re: Accessing Lookup Tables via API
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:
You data should display just fine.
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
Robert
Re: Accessing Lookup Tables via API
Thanks. The empty error handler did the job. Big help. THank
Re: Accessing Lookup Tables via API
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.
Re: Accessing Lookup Tables via API
How can I format the columns to accept null values? I tried several ways and can't get it.
Re: Accessing Lookup Tables via API
Try formatting the data in the GridViews RowDataBound Event Handler. Something like:czentman wrote:How can I format the columns to accept null values? I tried several ways and can't get it.
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
Robert
Re: Accessing Lookup Tables via API
This didn't help me at all.