Sage Data Objects

Official notification

2009-04-03T01:14:00.000-07:00

Official Notification

As of 17th of February 2009, the websites www.sagedataobjects.com and www.omadataobjects.com have been taken off-line in respect of a request from Sage (UK) Limited to cease offering our software without their consent.

We would advise any body interested in Sage products to go directly to the official sage website at www.sage.co.uk

C#4, Dynamic Types and Sage

2009-02-01T07:03:00.000-08:00

I was reading an article in this month's VSJ, and I was interested to read about C# 4.0's new Dynamic type. This type is C#'s answer to the weakly typed objects of languages like Basic, PHP, etc., This gives some advantages when dealing with .NET interfaces that lay above COM infrastructures, especially the Sage Data Objects library.

Therefore, with dynamics, code such as

SageDataObject50.InvoicePost InvPost;

InvPost = (SageDataObject50.InvoicePost)ws50.CreateObject("InvoicePost");

can become:

dynamic dInvoicePost = ws50.CreateObject("InvoicePost");

The cast to InvoicePost will be done at runtime, and the code will still compile, even if you don't know what the return object of this particular CreateObject method can be cast to.

The downside to this, as in every weakly typed language, is that many errors will not be spotted until runtime, and you may need to go through a process of trial-and-error to see what methods are available on a particular dynamic type.

According to VSJ this method for invoking COM objects should actually be more efficient, and certainly more elegant than using reflection.

Hopefully, I should be able to find a CTP download of C#4 to put together a code example.

Using COM reflection

2009-01-24T13:34:00.000-08:00

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

2008-11-27T10:59:00.001-08:00

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

2008-11-20T02:12:00.000-08:00

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

2008-10-23T02:29:00.000-07:00

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()

2008-10-22T01:49:00.000-07:00

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

2008-10-08T02:27:00.000-07:00

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

2008-10-02T01:26:00.000-07:00

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

2008-09-18T02:09:00.001-07:00

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)

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)

Read/Write ODBC connection for Sage

2008-09-02T13:37:00.000-07:00

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

2008-08-26T13:08:00.000-07:00

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

2008-07-30T02:05:00.000-07:00

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

2008-07-23T02:42:00.000-07:00

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

Post a purchase order in VB.NET

2008-07-19T05:55:00.000-07:00

Posting a purchase order in VB.NET is similar to posting an invoice, you're using Pop-prefixed classes rather than Invoice-Prefix classes, and using a different underlying schema, but otherwise, it's more or less the same, here is a code example:
Private Sub btnPurchaseOrderPost_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPurchaseOrderPost.Click
Dim PurchaseOrderRecord As SageDataObject50.PopRecord = ws50.CreateObject("PopRecord")
Dim PurchaseOrderPost As SageDataObject50.PopPost = ws50.CreateObject("PopPost")

'Set the type of invoice so we can find the next available number
'PurchaseOrderPost.Type = SageDataObject50.LedgerType.sdoLedgerService

'Loop for Number of Items on the Invoice
Dim PurchaseOrderItem As SageDataObject50.PopItem = PurchaseOrderPost.Items.Add()

' This is the text for the line

PurchaseOrderItem.Fields.Item("Text").Value = "Invoice Line Text"
PurchaseOrderItem.Fields.Item("Service_Item_Lines").Value = CLng(1)
PurchaseOrderItem.Fields.Item("Net_Amount").Value = CLng(100)
PurchaseOrderItem.Fields.Item("Full_Net_Amount").Value = CLng(100)
PurchaseOrderItem.Fields.Item("Tax_Amount").Value = CLng(0)
PurchaseOrderItem.Fields.Item("Nominal_Code").Value = "4000" 'Sales
PurchaseOrderItem.Fields.Item("Tax_Code").Value = "T9"
PurchaseOrderItem.Fields.Item("Tax_Rate").Value = CDec(0)

'Populate Header Information
' see http://www.sagedataobjects.com/line50v9schema.asp
' PURCHASE_ORDER for schema
PurchaseOrderPost.Header("Order_Date").value = DateTime.Now.ToString("MM/dd/yyyy")
PurchaseOrderPost.Header("Notes_1").value = "Notes 1"
PurchaseOrderPost.Header("Notes_2").value = ""
PurchaseOrderPost.Header("Notes_3").value = ""
PurchaseOrderPost.Header("Taken_By").value = ""
PurchaseOrderPost.Header("Order_Number").value = ""
PurchaseOrderPost.Header("Payment_Ref").value = ""
PurchaseOrderPost.Header("Global_Nom_Code").value = ""
PurchaseOrderPost.Header("Global_Details").value = ""
PurchaseOrderPost.Header("Items_Net").value = CDec(100)
PurchaseOrderPost.Header("Items_Tax").value = CDec(0)

'Read the first customer
PurchaseOrderRecord.MoveFirst()
PurchaseOrderPost.Header("Account_Ref").value = CStr(PurchaseOrderRecord.Fields.Item("Account_Ref").Value)
PurchaseOrderPost.Header("Name").value = CStr(PurchaseOrderRecord.Fields.Item("Name").Value)
PurchaseOrderPost.Header("Address_1").value = CStr(PurchaseOrderRecord.Fields.Item("Address_1").Value)
PurchaseOrderPost.Header("Address_2").value = CStr(PurchaseOrderRecord.Fields.Item("Address_2").Value)
PurchaseOrderPost.Header("Address_3").value = CStr(PurchaseOrderRecord.Fields.Item("Address_3").Value)
PurchaseOrderPost.Header("Address_4").value = CStr(PurchaseOrderRecord.Fields.Item("Address_4").Value)
PurchaseOrderPost.Header("Address_5").value = CStr(PurchaseOrderRecord.Fields.Item("Address_5").Value)
PurchaseOrderPost.Header("Del_Address_1").value = CStr(PurchaseOrderRecord.Fields.Item("Del_Address_1").Value)
PurchaseOrderPost.Header("Del_Address_2").value = CStr(PurchaseOrderRecord.Fields.Item("Del_Address_2").Value)
PurchaseOrderPost.Header("Del_Address_3").value = CStr(PurchaseOrderRecord.Fields.Item("Del_Address_3").Value)
PurchaseOrderPost.Header("Del_Address_4").value = CStr(PurchaseOrderRecord.Fields.Item("Del_Address_4").Value)
PurchaseOrderPost.Header("Del_Address_5").value = CStr(PurchaseOrderRecord.Fields.Item("Del_Address_5").Value)
PurchaseOrderPost.Header("Contact_Name").value = CStr(PurchaseOrderRecord.Fields.Item("Contact_Name").Value)

PurchaseOrderPost.Header("Order_Number").value = CLng(101)

If PurchaseOrderPost.Update Then
MessageBox.Show("Purchase Order Created Successfully")
Else
MessageBox.Show("Purchase Order NOT Created")
End If

End Sub

Posting an Invoice in C#

2008-07-17T13:21:00.000-07:00

In many respects the Sage Data Objects library is much easier to use from VB.NET than C#, why?, because VB.NET deals with object casting in a much less strict way. This way, you can say:

InvItem.Fields.Item("Text").Value = "Invoice Line Text"

Rather than

object TEXT = "Text";
InvItem.Fields.Item(ref TEXT).Value = "Invoice Line Text";

Further more, since the internals of Sage hide many implementation details, you may not know what class an object is, since .NET will only report that it's a ComObject, Nothing more.

In most cases, we'd recommend that you create a stub library in VB.NET to host our component, and provide services to your C# Application, however, if that is not possible, then here is how to carry out a common task in sage, - posting an Invoice

public void CreateInvoice(DateTime InvoiceDate, int InvoiceNumber)
{
SageDataObject50.InvoicePost InvPost;

InvPost = (SageDataObject50.InvoicePost)ws50.CreateObject("InvoicePost");

InvPost.Type =(SageDataObject50.InvoiceType)SageDataObject50.LedgerType.sdoLedgerInvoice;
object Invoice_Number = "Invoice_Number";
object Invoice_Date = "Invoice_Date";

SageDataObject50.IFields ifHeaderFields = null;

Type t = InvPost.Header.GetType();

Object oFields = t.InvokeMember("Fields",
BindingFlags.GetProperty
, null, InvPost.Header, new Object[0]);

ifHeaderFields = (SageDataObject50.IFields)oFields;

ifHeaderFields.Item(ref Invoice_Number).Value = InvoiceNumber;
ifHeaderFields.Item(ref Invoice_Date).Value = InvoiceDate.ToString("MM/dd/yyyy");

t = InvPost.Items.GetType();

Object oItem = t.InvokeMember("Add",
BindingFlags.InvokeMethod,
null,
InvPost.Items,
new Object[0]);

SageDataObject50.InvoiceItem InvItem;
InvItem = (SageDataObject50.InvoiceItem)oItem;

// This is the text for the line
InvItem.Text = "Description";
object TEXT = "Text";
object SERVICE_ITEM_LINES = "Service_Item_Lines";
object NET_AMOUNT = "Net_Amount";
object FULL_NET_AMOUNT = "Full_Net_Amount";
object TAX_AMOUNT = "Tax_Amount";
object NOMINAL_CODE = "Nominal_Code";
object TAX_CODE = "Tax_Code";
object TAX_RATE = "Tax_Rate";

InvItem.Fields.Item(ref TEXT).Value = "Invoice Line Text";
InvItem.Fields.Item(ref SERVICE_ITEM_LINES).Value = (double)1;
InvItem.Fields.Item(ref NET_AMOUNT).Value = (double)100;
InvItem.Fields.Item(ref FULL_NET_AMOUNT).Value = (double)100;
InvItem.Fields.Item(ref TAX_AMOUNT).Value = (double)0;
InvItem.Fields.Item(ref NOMINAL_CODE).Value = "4000";
InvItem.Fields.Item(ref TAX_CODE).Value = "T9";
InvItem.Fields.Item(ref TAX_RATE).Value = (decimal)0;

if (!InvPost.Update())
{
MessageBox.Show("Unable to create invoice");
}
else
{
MessageBox.Show("Invoice Created");
}
}

Updating a customer's delivery address

2008-06-27T13:23:00.000-07:00

This is quite a simple piece of code, but it was asked for by one of our development community. Basically how to update a customer's delivery address from code, so here it is:

Private Sub btnUpdateDeliveryAddress_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdateDeliveryAddress.Click
Dim SalesRecord As SageDataObject50.SalesRecord = DirectCast(ws50.CreateObject("SalesRecord"), SageDataObject50.SalesRecord)
SalesRecord.MoveFirst()
SalesRecord.Edit()
SalesRecord.Fields.Item("DEL_ADDRESS_1").Value = "10 William Drive"
SalesRecord.Fields.Item("DEL_ADDRESS_2").Value = "Wallaceville"
SalesRecord.Fields.Item("DEL_ADDRESS_3").Value = "co. Durham"
SalesRecord.Fields.Item("DEL_ADDRESS_4").Value = "England"
SalesRecord.Fields.Item("DEL_ADDRESS_5").Value = "NT48 9LH"
SalesRecord.Update()
End Sub

Displaying the IFields Collection

2008-06-04T13:56:00.001-07:00

This handy utility was submitted by one of our users, to display the list of fields available within an InvoiceItem. The code could easily be adapted for any of the objects within Sage.

// This class is only to display the field information in a DataGrid or GridView

private class  FieldNameInfo
  {
  private string _name;
  private string  _desc;
  private string _typ;
  private string _len;

   public FieldNameInfo(string Name, string Desc, string Typ, string Len)
  {
   _name = Name;
   _desc = Desc;
   _typ =  Typ;
   _len = Len;
  }

   public string Name
  {
   get { return _name;  }
  }

   public string Description
  {
   get { return _desc;  }
  }

   public string Lenth
  {
   get { return _len;  }
  }

   public string Typ
  {
   get { return _typ;  }
  }

}

  private void  checkFields()
 {
  lblTable.Text = "Invoice Item";
  ArrayList  listFields = new ArrayList();

   sdo120= new  SageDataObject120.SageDataObjects();
ws120 = sdo120.GetWorkSpace();
  try
  {
   if (  ws120.Connect(ConfigurationSettings.AppSettings["SagePath"],  ConfigurationSettings.AppSettings["SageUser"],  ConfigurationSettings.AppSettings["SagePassword"], "ChkFields")  )
   {
    SageDataObject120.SopItem myRec =  (SageDataObject120.SopItem)ws120.CreateObject("InvoiceItem");
    myRec.MoveFirst();
    SageDataObject120.IFields  myFields = myRec.Fields;
    foreach (SageDataObject120.IField myFld in  myFields)
    {
     FieldNameInfo fni = new  FieldNameInfo(myFld.Name.ToString(), myFld.Description.ToString(),  myFld.Type.ToString(),  myFld.Length.ToString());
     listFields.Add(fni);
   
    }
   }
  }
  catch(Exception  ex)
  {
  } 
  finally
  {
   ws120.Disconnect();
  }
  listFieldsId.DataSource  = listFields;
  listFieldsId.DataBind();
 }

Exploring the Sage Data Schema

2008-05-06T02:45:00.000-07:00

Sage ships with an ODBC driver that can be used to provide read-only access to the underlying data tables in Sage, the SDO (Sage Data Objects) can provide read-write access to these tables, but often, it's hard to know which field you need to edit. Therefore this ODBC driver can come in handy listing tables, columns, and hints for what each field is for.

After the installation of sage, a DSN should be setup, if you go to Start Contol Panel Administrative Tools ODBC Manager, then select "System DSN", you should see a DSN called, "SageLine50v9" or similar.

If you download a free utility called DSN2CSV here http://bitdaddys.com/dsn2csv.html
and, from the command prompt type:

dsn2csv "SageLine50v9" * "Manager" ""

A sample schema from Line 50 version 9 can be found here: http://www.sagedataobjects.com/line50v9schema.asp

A sample schema from Line 50 version 13 can be found here:
http://www.sagedataobjects.com/line50v13schema.asp

Connecting Sage with JAVA

2008-04-19T13:35:00.000-07:00

Connecting Sage with JAVA

It is possible to connect Sage with Java via Sage Data Objects, and a Java-COM bridge "EZ-JCom". You will need to download the SageDataObjects library from http://www.sagedataobjects.com/ and the SageForJava Library from (http://www.sagedataobjects.com/javasdo/sageforjava.zip)

After downloading and installing the SageDataObjects library, and unzipping the SageForJava Library, you will need to select the version of Sage you want to connect to, for example, version 14, Copy the File, JSDOENG140.DLL from the SDOENG140Java Folder., and place it in your Windows\System32 Folder.

Creating your Java Application

Using your preferred IDE, add a reference to the JAR file, for the selected version of sage, for example, with JCreator, Press Project > Project Settings > Required Libraries > New > Add > Add Archive. Then Select JSDOENG140.JAR

Coding:
The following code will connect to sage, and list all current customers on the screen. the code is available as part of the Java SDO download shown above.

import ezjcom.*;

public class JSage {

public static void main(String[] args)
{
SageDataObjects50.SDOEngine SDO = null;
SageDataObjects50.IWorkSpace IWS = null;
SageDataObjects50.ISalesRecord ISR = null;

try
{
// This should be the Installation location of the Sage software
String strACCData = "c:\\line50\\accdata\\";
// The default login to Sage is MANAGER
String strUsername = "MANAGER";
String strPassword = "";

// Create a new Sage Data Objects Engine object
SDO = new SageDataObjects50.SDOEngine();
SageDataObjects50.ISDOEngine ISDO = SDO.getISDOEngine();
SageDataObjects50.Workspaces WSS = ISDO.getWorkspaces();
SageDataObjects50.IWorkspaces IWSS = WSS.getIWorkspaces();

// Create a Workspace from the Engine
IWS = (SageDataObjects50.IWorkSpace)IWSS.Add("MyConnection");

// Connect to Sage
IWS.Connect(strACCData, strUsername, strPassword, "MyConnection");

// Have the Workspace get the first Sales Record
SageDataObjects50.ISDORecord ISDOR = (SageDataObjects50.ISDORecord)IWS.CreateObject("SalesRecord");

// ISDORecord is too generic, use CoerceObject to perform a 'dirty' cast.
ISR = (SageDataObjects50.ISalesRecord)ISDOR.JComCoerceObjectToAnotherType(SageDataObjects50.ISalesRecord.class);

System.out.println("Customers:");
while(true)
{
// Get the Acount_Ref field fr the customer
JComVariant jcvAccountRef = new JComVariant("Account_Ref");
SageDataObjects50.IFields ifsCustomer = ISR.getFields().getIFields();
SageDataObjects50.IField ifCustomer = ifsCustomer.Item(jcvAccountRef).getIField();
String strCustomer = ifCustomer.get_Value().getString();

// Output the Customers' Reference
System.out.println(strCustomer);

// Move to the next Customer
ISR.MoveNext();

// If end of customers, break out.
if (ISR.IsEOF()) break;
}
// Disconnect from Sage
IWS.Disconnect();
}
catch(ezjcom.JComException eException)
{
System.out.println("Exception:" + eException.toString());
}
}
}

Licensing

Code written using the SageDataObjects library is subject to a 14 day trial license, and will cease to operate after those 14 days. More details on this can be read at www.sagedataobjects.com

The Java-Com bridge is subject to a seperate license, which can be obtained at www.ezjcom.com

Sage on your website

2008-04-17T12:34:00.000-07:00

Connecting Sage with your website

Sage Data Objects can run within an ASP.NET web page, which will allow Internet visitors interact with your Sage accountancy software.

Sage requires high levels of permissions to run from a ASP.NET website, which may lead to some security implications. Your web-server should be dedicated, and you will need either physical or remote desktop access to it.

Configuring the Administrator Account

If your Administrator account has a blank password – that is, you are not asked to enter one, when logging onto the machine. Then you will need to configure the account to allow log-ons from non console applications.

Press Start > Run > gpedit.msc

Press Computer Configuration > Windows Settings > Security Settings > Local Policies > Security Options

Click on “Account: Limit local account use of blank password to console log on only”
Select Disable

Configuring IISAdmin Service
IIS will need to be able to interact with the desktop, in order to process Sage instructions. This is achieved thus:

Press Start > Control Panel > Administrative Tools > Services
Right Click on IISAdmin, Select properties

Press Log on
Check “Allow service interact with desktop”

Restart the service

Configuring Machine.config
The ASP.NET worker process (aspnet_wp.exe) will have to run under the Administrator account also, to configure this, you will need to edit the machine.config file

Open C:\windows\Microsoft.net\Framework\v1.1.4322\Config\Machine.config in notepad

Edit the ProcessModel section to add the following
username=”Administrator”
password=””
Then Restart IIS.

Creating a Web Project

Create a new Visual Basic .NET web application project in Visual Studio,
Right Click on References > Add Reference
Select SageDataObjectComponent

Add a Button to the page, named btnConnect, and a DataGrid named dgCustomers

Click the button, and add this code:

Dim sdo50 As SageDataObject50.SageDataObjects
Dim ws50 As SageDataObject50.WorkSpace
sdo50 = New SageDataObject50.SageDataObjects
ws50 = sdo50.GetWorkSpace()
Dim strACCData As String = ConfigurationSettings.AppSettings("ACCDATA")
Dim strUsername As String = ConfigurationSettings.AppSettings("USERNAME")
Dim strPassword As String = ConfigurationSettings.AppSettings("PASSWORD")
ws50.Connect(strACCData, strUsername, strPassword, Guid.NewGuid().ToString())
Dim alCustomers As ArrayList = New ArrayList
Dim srCustomer50 As SageDataObject50.SalesRecord = _ DirectCast(ws50.CreateObject("SalesRecord"), SageDataObject50.SalesRecord)
srCustomer50.MoveFirst()
While True
alCustomers.Add(srCustomer50.Fields.Item("Account_Ref").Value())
srCustomer50.MoveNext()
If srCustomer50.IsEOF() Then Exit While
End While
dgCustomers.DataSource = alCustomers
dgCustomers.DataBind()
ws50.Disconnect()

Your Web.config will need the following values

<appsettings>
<add key="ACCDATA" value="C:\LINE50\ACCDATA\"></add>
<add key="USERNAME" value="MANAGER"></add>
<add key="PASSWORD" value=""></add>
</appsettings>