05-31-2010 07:26 AM
As you may be tired of reading I am building an external VB.Net/ASP.Net application that will talk with a custom SQL DB but which must also read and write an ACT! DB. I am pretty comfortable with creating new Contact records and I am confident that these techniques will work for Companies and Employees as well. I now need help on two related tasks, searching for a list of records matching some criteria other than the ID and using the results to bind to a VB data-aware control such as a combo box or data grid view control. I have been pouring over the SDK Code Reference and also experimenting with any properties I can discover in an instantiated framework object in VB. One work-around is to use the ACT Read-Only ODBC connectivity for all searching but since creating and updating must take place through the framework I'd just as soon keep all actions in the same environment.
I'm basically looking for the ACT Framework equivalent of what I would have done this way in VB using SQL
Dim conSQL as New Connection
Set connection properties and open
Dim comSQL as new Command
Set command properties including stored procedure name or SQL Text
Set command parameters to search values or concatenate values into SQL string
Dim drSQL as DataReader
drSQL = comSQL.ExecuteReader
cboContacts.DataSource = drSQL
cboContacts.DisplayMember = "Display Column Name"
cboContacts.ValueMember = "ID Column Name"
A related task I will need to be able to perform is, after having selected a specific record from a list, retrieve that record into a Contact object for updating. I've seen how to fetch a record by its ID so I'm pretty sure I can do this. My current stubmling block is searching for one or more records based on some criteria other than the ID, including complex criteria.
Thanks for all of the help I've received so far.
06-01-2010 07:44 AM
So far as just retrieving a list of contacts in this case based on criteria other than ID, you could do something like
afw.Contacts.GetContacts and use the IFilterCriteria parameter to narrow the search based on whatever criteria you create.
What you have is almost identical to how you'd connect via the OLE, their may be an SQL equivalent but I've always stuck with the OLE.
The IFilterCriteria may resolve your second problem as well, you can retrieve a subset of contacts into a contact list.
06-01-2010 08:12 AM
Thanks much. You guys are up bright and early out there in Arizona.
Do you by any chance have a code sample that does this?
Also, I have seen the IFilterCriteria in my browsing. I am a little concerned about the size of this result set and how it appears in application memory. My client has started migrating contacts from their old Outlook Contact Manager and are well in excess of 20,000 and climbing. I would be very reluctant to have multiple web application sessions each creating memory copies of 25,000 contacts and then applying a filter. Is the filter applied before the contasts are fetched (as in an SQL WHERE clause)? Do any indexes on the database benefit this process?
Also, is the contact list an object that can be bound to VB control .DataSource properties or can it be transformed into such an object?
06-01-2010 12:21 PM
I believe iFilter will filter an array so your concerns about scaling/performance are valid. Use the lookup call instead of filter - this will offload the filtering/data return to the db server. I'm pretty sure I've seen a VB lookup example posted on this forum so do a quick search.
As far as binding - you should be able to do control and data binding with ACT! objects. Are you looking to update the data in ACT! from this control or read only? If read only then I suggest using the OLEDB Provider - it's setup as a data source is straight forward.