Using COM reflection

I read with interest this month's MSDN magazine article on COM reflection. The article provides an example of using reflection to uncover hidden properties and methods of the Speech library, and also explains how the elusive System.__ComObject can give up its goodies, so to speak.

It works fine on the sample Speech API DLL on the site, however, it isn't so hot when it comes to Sage. I tried plugging in some common CLSID's from Sage's inner workings, but it gave up no further information. I then tried passing a System.__ComObject to it, with the following code;

Dim oUnknown As Object = o.GetCompanies("C:\Program Files\Sage\Accounts")
DumpCOMObject(oUnknown)

However, it crashed out when IDispatch::GetTypeInfoCount returned 0. According to MSDN reference this is done to prevent run-time inspection of the DLL, perhaps for security purposes.

So, I guess this is a bit of an anti-pattern when it comes to Sage development, but I hope that this research saves another developer some time when it comes to trying to figure out the million dollar question 'what do I cast this object to now?'

Connecting to SAGE using VBA

I'm often asked if Sage Data Objects can be used with VBA (Visual Basic for Applications), for embedding into excel spreadsheets, and the like. The answer is of course, yes. As with all development with VBA, you loose the luxury of Intellisense, so development is alot harder than when using strongly-typed languages like VB.NET.

But in a sense, it could be considered somewhat easier than languages that, one could say, are too strongly typed, such as C#. With it's refusal to support Duck-typing, that is almost vital in many instances to avoid the use of reflection.

The below example can be used to print a list of customers registered in Sage. The below code snippet should be saved as "customers.vbs", then it can be run at the command line by typing "cscript customers.vbs".


Datapath = "C:\Line50\ACCDATA\"
Set SDOEngine = CreateObject("SDOEngine.5")
Set WS = SDOEngine.Workspaces.Add("Sage")
WS.Connect Datapath, "manager", "", "Sage"
Set srCustomer = WS.CreateObject("SalesRecord")
srCustomer.MoveFirst
Do
WScript.StdOut.Write srCustomer.Fields("Account_Ref") & Vbcrlf
srCustomer.MoveNext()
Loop until srCustomer.IsEof
WS.Disconnect

Posting a stock transaction in VB.NET

In order to manage stock levels within Sage, it is often necessary to record the movement of stock in an out of the company, by way of Stock Transactions.

Stock levels are managed automatically by Sage from the result of Sales or Purchases. However, these are not the only stock movements that can happen. Stock levels can be increased (Adustment In) with deliveries and returns. Stock levels can be reduced (Adustment Out) with non Invoiced sales and losses. Stock levels can also be reset using a stock-take.

The following code creates an Adustment In (i.e. delivery) of 2 units of the first product found. At a cost price of £1.

Dim objStockPost As SageDataObject50.StockPost
objStockPost = ws50.CreateObject("StockPost")
Dim objStockRec As SageDataObject50.StockRecord
objStockRec = ws50.CreateObject("StockRecord")
objStockRec.MoveFirst()
objStockPost.Header.Fields("Stock_Code").Value = objStockRec.Fields.Item("Stock_Code").Value
Dim objStockTran As SageDataObject50.StockTran
objStockTran = objStockPost.Items.Add()
objStockTran.Fields.Item("Stock_Code").Value = objStockRec.Fields.Item("Stock_Code").Value
objStockTran.Fields.Item("Date").Value = DateTime.Now.ToString("MM/dd/yyyy")
objStockTran.Fields.Item("Type").Value = SageDataObject50.StockTransType.sdoAI
objStockTran.Fields.Item("Quantity").Value = CType(2, Double)
objStockTran.Fields.Item("Cost_Price").Value = CType(1, Double)
objStockTran.Fields.Item("Reference").Value = "Test Ref"
objStockTran.Fields.Item("Details").Value = "Test Transaction"
' Update the StockPost object
Dim bFlag As Boolean
bFlag = objStockPost.Update()
If bFlag Then
MessageBox.Show("Success")
Else
MessageBox.Show("Failure")
End If

Disconnecting from Sage

It is always important to disconnect from Sage, once your operations on it are finished, either successfully or in error. otherwise the next person to log in, will be told of an existing login, and this can cause hassle for manual operators.

Some users of the SDO have informed us of an intermittent problem, where the user can be left logged into Sage once a Disconnect() has been called. The brute-force solution to this was to delete the QUEUE.DTA file in the ACCDATA folder, but this is best avoided.

A simple but novel solution was put forward by one of our users, to simply change the GUID in the Connect() parameters to the Username, and from all accounts, this seems to work.

Quirky IsEOF()

The IsEOF() function works a little different to how you'd expect it, and without understanding this function, you may end up missing your last entered customer, or invoice.

IsEOF() is flagged true after reading the second-last record. That is to say, if you have 10 records, after reading record 9, then IsEOF() becomes true. This means you can still read record 10, but no more.

The code example in the download from the website, actually misses this point, so here's how to fix it:

SageDataObject50.SalesRecord srCustomer50 = (SageDataObject50.SalesRecord)ws50.CreateObject("SalesRecord");
srCustomer50.MoveFirst();
bool blnLast = false;
while(true)
{
strDescription = srCustomer50.Fields.Item(ref objDescription).Value.ToString();
this.lvCustomer.Items.Add(strDescription);
srCustomer50.MoveNext();
if (blnLast) break;
blnLast = srCustomer50.IsEOF();
}
break;

The code added is highlighted in brown. Note how IsEOF is flagged before the last record is read.

Type Safety with Sage

One problem that is often encountered by developers using the Sage Data Objects component is the issue of type safety. If you set the wrong type to a value of a field the software will either throw a runtime exception or, ignore the value you set in. Runtime exceptions can be seen easily, but ignored values are more problematic.

Here is an example of the problem, in Visual Basic.NET

InvPost.Header("Items_Net").value = 13

? InvPost.Header("Items_Net").value

4.7229610327708223E+275 {Double}

[Double]: 4.7229610327708223E+275 {Double}

InvPost.Header("Items_Net").value = CType(13,Double)

? InvPost.Header("Items_Net").value

13.0 {Double}

[Double]: 13.0 {Double}

Here, Items_Net is expecting a floating point value, but when assigned with an integer, it ignores the value, and does not throw an exception. Converting the type to a double, and the code works as expected.

To avoid this, you should be familiar with the types of all the fields that you use, and to do so, you should study the schema for your particular version of Sage, as explained in this link
http://sagedataobjects.blogspot.com/2008/05/exploring-sage-data-schema.html

Adding and Removing Customers with Visual Basic

Although Visual Basic 6 is quite an aged product now, but many software houses continue to use it, as much as they continue to use old versions of Sage. Here is a handy example program in Visual Basic 6 for adding and removing customers (with source code) for download here

http://www.sagedataobjects.com/sagecustomersvb.zip (3Kb)