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)

Reading and Updating Departments in Sage

In Sage, products are often assigned to Departments, for example, a food wholesaler might have a department for frozen foods, and one for fresh foods.

Reading and updating departments in Sage via Sage Data Objects is easy, and here's how.

Reading departments:

Dim IDepartment As SageDataObject50.Department

Dim strDescription As String

IDepartment = DirectCast(ws50.CreateObject("DepartmentData"), SageDataObject50.Department)

Dim i As Int16

For i = 0 To 100

IDepartment.Read(i)

strDescription = IDepartment.Fields.Item("NAME").Value.ToString()

Me.lvDepartments.Items.Add(strDescription)

Next

To update them, you just use the write(int) method.

Dim IDepartment As SageDataObject50.Department

Dim strDescription As String

IDepartment = DirectCast(ws50.CreateObject("DepartmentData"), SageDataObject50.Department)

Dim i As Int16

For i = 0 To 100

IDepartment.Fields.Item("NAME").Value = "RESET"

IDepartment.Write(i)

Next

Read/Write ODBC connection for Sage

Sage ships with a read only ODBC connection, which is useful for doing linked-server joins with SQL server, or similar databases. Or, a quick way to import into Excel, for instance. However, the main limitation of this, is that it is only read-only, you cannot change any data within Sage.

A company we were speaking to offer a ODBC SDK on a 15 day trial basis, and we'd be interested in hearing from anyone who can integrate our component with this SDK to create a read/write ODBC connection with Sage.

Here's the link to the ODBC SDK
http://www.datadirect.com/downloads/registration/sdk_eval/index.ssp

Schema for Sage Line 50 Financial Controller

We're working through the schema of the Sage Line 50 Financial controller version, and thought that as a reference for Sage developers to publish our schema online

http://www.sagedataobjects.com/line50financialcontroller.asp

Third party integration

If you recieve an error "Sage Data Objects is not registered, please contact the supplier of this application" within a day or so of trying the trail version, don't panic, simply activate third party integration as follows:

1. Open Sage and click the Tools | Activation | Enable Third Party Integration menu option.

3. A screen will appear with a phone number to call; request the SDO Serial Number and Activation Key from the Sage customer services agent, enter the details in the textboxes provided and click the Register button.

Note: when calling the phone number the Sage customer services agent will usually ask you for you Sage Line 50 Serial Number (see the Help | About menu for this), your company name, first line of your address and / or postcode. Attaining the SDO Serial Number and Activation Key is a completely free service (apart from the price of the call) and will typically take no long than 5 minutes.

Read Purchase orders from Sage in VB.NET

Reading purchase orders from Sage is very similar to reading invoices (sales orders) from sage, but once again, the objects you use are prefixed with "pop" (purchase order processing), and you use a different set of schemas.

Here's the code:

Private Sub btnPurchases_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPurchases.Click
Dim strOrderNumber As String
Dim prPurchase As SageDataObject50.PopRecord = DirectCast(ws50.CreateObject("PopRecord"), SageDataObject50.PopRecord)
prPurchase.MoveFirst()

While Not prPurchase.IsEOF()
' For A Full list of available fields go to:
' http://www.sagedataobjects.com/line50v9schema.asp
' and see the PURCHASE_ORDER Table
strOrderNumber = prPurchase.Fields.Item("ORDER_NUMBER").Value.ToString()
Dim popItem As SageDataObject50.PopItem
popItem = CType(prPurchase.Link, SageDataObject50.PopItem)
popItem.MoveFirst()
Do
' For A Full list of available fields go to:
' http://www.sagedataobjects.com/line50v9schema.asp
' and see the POP_ITEM Table
Dim strLineItemText As String
strLineItemText = popItem.Fields.Item("TEXT").Value.ToString()
Loop While popItem.MoveNext()
Me.lvPurchases.Items.Add(strOrderNumber)
prPurchase.MoveNext()
End While
End Sub