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)

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