FCapo
asked on
Connecting (reading and writing) to the simply accounting 2010 database from visual studio 2010
Hi,
I'm currently developing a program for my company and I need to be able to write directly to the clients table in my simply accounting database, I'm not sure who could help me in achieving this but I need to speak to someone who can guide me through the process.
I'm using visual studio 2010 to connect to the database. The database is a ".SAI" file.
I've tried adding a new datasource using Microsoft ODBC Data Source (ODBC) wizard and selected my database name from the drop-down list. The problem I'm having is I cannot add or update records in the database using this method, I can only read records but not write to them. An error is always given anytime I try to update the records stating that there's an error in the query.
I've also tried using the MySQL connection wizard to add the database but it simply cannot find it no matter what I enter. I know that simply accounting uses MySQL for their database but I can't find any instruction on how to connect to it.
I need to be able to : ADD - DELETE - UPDATE the information in the "tcustomr" table of the simply accounting database. The only fields I specifically need in the table are:
lId
sName
sCntcName
sStreet1
sStreet2
sCity
sProvState
sPostalZip
sEmail
I've tried downloading the SDK but I can't find any tutorials on how exactly I can query and save records to the database. It is extremely difficult to understand the process through the SDK.
I would really appreciate it if someone can help me figure out how to connect to my database, read data from the "tcustomr" table and update or delete records from the table using visual studio 2010 (vb.net)
Essentially I need the exact code to use in visual studio in order to be able to do this
Thank you for any help you can offer me,
FCapo
I'm currently developing a program for my company and I need to be able to write directly to the clients table in my simply accounting database, I'm not sure who could help me in achieving this but I need to speak to someone who can guide me through the process.
I'm using visual studio 2010 to connect to the database. The database is a ".SAI" file.
I've tried adding a new datasource using Microsoft ODBC Data Source (ODBC) wizard and selected my database name from the drop-down list. The problem I'm having is I cannot add or update records in the database using this method, I can only read records but not write to them. An error is always given anytime I try to update the records stating that there's an error in the query.
I've also tried using the MySQL connection wizard to add the database but it simply cannot find it no matter what I enter. I know that simply accounting uses MySQL for their database but I can't find any instruction on how to connect to it.
I need to be able to : ADD - DELETE - UPDATE the information in the "tcustomr" table of the simply accounting database. The only fields I specifically need in the table are:
lId
sName
sCntcName
sStreet1
sStreet2
sCity
sProvState
sPostalZip
sEmail
I've tried downloading the SDK but I can't find any tutorials on how exactly I can query and save records to the database. It is extremely difficult to understand the process through the SDK.
I would really appreciate it if someone can help me figure out how to connect to my database, read data from the "tcustomr" table and update or delete records from the table using visual studio 2010 (vb.net)
Essentially I need the exact code to use in visual studio in order to be able to do this
Thank you for any help you can offer me,
FCapo
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Short of me downloading the SDK (some 14 MB!), can you give us a sample of how they do it in the example code? We probably will be able to "translate" to your language.
ASKER
Yes of course I've included the code for the form below, the vb.net example does not show how to write to the database, it's the C# one that does, I've also included the C# as an attachment.
The problem is I've never used C# before so it doesn't give me much to see how its done in that programming language.
Thank you cSharp.txt
The problem is I've never used C# before so it doesn't give me much to see how its done in that programming language.
Thank you cSharp.txt
Option Strict Off
Option Explicit On
Imports System.Runtime.InteropServices
Imports System.Text
Friend Class frmSample
Inherits System.Windows.Forms.Form
Private Sub cmdOK_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmdOK.Click
Dim lNum As Integer
Dim lId As Integer
Dim iRet As Short
Dim pRecord(1999) As Byte
Dim iDBLink As Short
Dim iTBLink As Short
Dim iFileVer As Short
Dim iCtyCode As Short
Dim iDBCode As Short
Dim bIsAcctCopy As Boolean
Dim szUserName As String
Dim szPassword As String
Dim szFileName As String
Dim szCompName As String
Dim sFindData As WIN32_FIND_DATA
szFileName = Trim(txtFileName.Text)
szUserName = Trim(txtUserName.Text)
szPassword = Trim(txtPassword.Text)
sFindData = New WIN32_FIND_DATA
If szFileName = "" Or FindFirstFile(szFileName, sFindData) = -1 Then
DisplayError(("The file does not exsit. Please select another file."))
Exit Sub
End If
Dim szHost As String
Dim szPort As String
Dim szFileDir As String
Dim lLength As Long
Dim dbClient As New Simply.ConnectionManagerServiceClient.ConnectionManagerServiceClient
Dim dbError As Simply.ConnectionManagerService.ConnectionManagerError
szFileDir = LCase$(szFileName)
lLength = Len(szFileDir)
If (Strings.Right(szFileDir, 4) = ".sai") Then
szFileDir = Strings.Left(szFileDir, lLength - 4) + Replace(Strings.Right(szFileDir, 4), ".sai", ".saj")
End If
szHost = ""
szPort = ""
dbError = dbClient.GetConnectionInfo(szFileDir, szHost, szPort)
If dbError = Simply.ConnectionManagerService.ConnectionManagerError.No_Error Then
MsgBox(iDBLink)
If wSDBOpenDatabase(iDBLink, FLG_SHARED, "Sample", szUserName, szPassword, szHost, "", szPort) = DBS_SUCCESS Then
If wSDBGetDBVer(iDBLink, iFileVer, iCtyCode, iDBCode, bIsAcctCopy) = DBS_SUCCESS Then
If (iFileVer < 17001) Then
DisplayError(("The SDK will only work with databases created in Simply Accounting 2010 and higher. Please select another file."))
Call wSDBCloseDatabase(iDBLink)
Call vSDBUnloadDAO()
Exit Sub
End If
End If
If wSDBOpenTable(iDBLink, "tCustomr", FLG_READONLY, iTBLink) = DBS_SUCCESS Then
'Read record #1 in the compnay info record
lId = 3
Call SetBytes(pRecord, System.BitConverter.GetBytes(lId), 0, 4)
iRet = wSDBGetRecord(iDBLink, iTBLink, FLG_KEY0, pRecord)
szCompName = ""
If (iFileVer > 9000) Then
Call Byte2String(pRecord, szCompName, 34, 52)
Else
Call Byte2String(pRecord, szCompName, 28, 52)
End If
lblCompanyName.Text = Trim(szCompName)
End If
Call wSDBCloseTable(iDBLink, iTBLink)
'Get the number of records per module
lNum = GetNumRecModule(iDBLink, "tAccount")
lblNumAccounts.Text = Trim(Str(lNum))
lNum = GetNumRecModule(iDBLink, "tVendor")
lblNumVendors.Text = Trim(Str(lNum))
lNum = GetNumRecModule(iDBLink, "tCustomr")
lblNumCustomers.Text = Trim(Str(lNum))
lNum = GetNumRecModule(iDBLink, "tEmp")
lblNumEmployees.Text = Trim(Str(lNum))
lNum = GetNumRecModule(iDBLink, "tInvent")
lblNumInventory.Text = Trim(Str(lNum))
lNum = GetNumRecModule(iDBLink, "tProject")
lblNumProjects.Text = Trim(Str(lNum))
Call wSDBCloseDatabase(iDBLink)
Else
DisplayError(("Cannot open the database. Please select another file."))
End If
Call vSDBUnloadDAO()
Else
DisplayError(("Cannot connect to database server."))
End If
End Sub
Private Sub Byte2String(ByRef ArrayOfBytes() As Byte, ByRef StringEqv As String, ByVal start As Short, ByVal strSize As Short)
Dim i As Short
StringEqv = ""
For i = 0 To strSize - 1
If ArrayOfBytes(start + i) = 0 Then
Exit For
Else
StringEqv = StringEqv + Convert.ToChar(ArrayOfBytes(start + i))
End If
Next i
End Sub
Private Sub SetBytes(ByRef byteCopyto() As Byte, ByRef byteCopyFrom() As Byte, ByVal nStart As Short, ByVal nSize As Short)
Dim n As Short
For n = 0 To nSize - 1
'Make sure we don't copy something we don't have.
If n < byteCopyFrom.Length - 1 Then
If byteCopyFrom(n) = 0 Then
Exit For
Else
byteCopyto(nStart + n) = byteCopyFrom(n)
End If
End If
Next n
End Sub
Private Sub DisplayError(ByRef szErrorString As String)
Call MsgBox(szErrorString, MsgBoxStyle.ApplicationModal + MsgBoxStyle.Exclamation + MsgBoxStyle.OkOnly, "Sample - Error")
lblCompanyName.Text = ""
lblNumAccounts.Text = ""
lblNumVendors.Text = ""
lblNumCustomers.Text = ""
lblNumInventory.Text = ""
lblNumEmployees.Text = ""
lblNumProjects.Text = ""
txtUserName.Text = ""
txtPassword.Text = ""
txtFileName.Text = ""
txtFileName.Focus()
End Sub
Private Function GetNumRecModule(ByRef iDBLink As Short, ByRef szTableName As String) As Integer
Dim iTBLink As Short
Dim lNum As Integer
lNum = 0
If wSDBOpenTable(iDBLink, szTableName, FLG_READONLY, iTBLink) = DBS_SUCCESS Then
Call wSDBGetNumRecs(iDBLink, iTBLink, lNum)
Call wSDBCloseTable(iDBLink, iTBLink)
End If
GetNumRecModule = lNum
End Function
Private Sub cmdClose_Click()
Me.Close()
End Sub
End Class
Thanks for the points! Never got around to responding, however, just browsed through the code and having converted the C# code to VB.NET, looks straight foward! Are you still having issues here?
Imports System.IO
Imports System.Windows.Forms
Imports Simply.Domain.Utility
Imports SimplySDK
Imports SimplySDK.Support
Imports SimplySDK.GeneralModule
Imports SimplySDK.PayableModule
Imports SimplySDK.ReceivableModule
Imports SimplySDK.PayrollModule
Imports SimplySDK.InventoryModule
Imports SimplySDK.ProjectModule
Imports System.Data
Namespace SDKExample
#Region "class ConsoleAlert"
''' <summary>
''' This class overrides the default behaviour of alerts in the SDK.
''' The default behaviour of the SDK throws exceptions.
''' Here, Messages are displayed via the Console.
''' </summary>
Public Class ConsoleAlert
Inherits SDKAlert
''' <summary>
''' AskAlert expects a return value of AlertResult.YES or AlertResult.NO
''' </summary>
Public Overrides Function AskAlert(message As SimplyMessage) As AlertResult
Console.WriteLine(message.Message)
Dim answer As String = Console.ReadLine().ToLower()
If answer(0) = "y"c Then
Return AlertResult.YES
Else
Return AlertResult.NO
End If
End Function
''' <summary>
''' AskSaveAlert pops up when a ledger is unsaved and an action is performed
''' which attempts to close the current ledger.
''' AskAlert expects a return value of AlertResult.YES or AlertResult.NO
''' </summary>
Public Overrides Function AskSaveAlert() As AlertResult
Console.WriteLine("Save ledger record before continue?")
Dim answer As String = Console.ReadLine().ToLower()
If answer(0) = "y"c Then
Return AlertResult.YES
Else
Return AlertResult.NO
End If
End Function
''' <summary>
''' YNCAlert expects a return value of AlertResult.YES or AlertResult.NO or AlertResult.CANCEL
''' </summary>
Public Overrides Function YNCAlert(message As SimplyMessage) As AlertResult
Console.WriteLine(message.Message)
Dim answer As String = Console.ReadLine().ToLower()
If answer(0) = "y"c Then
Return AlertResult.YES
ElseIf answer(0) = "n"c Then
Return AlertResult.NO
Else
Return AlertResult.CANCEL
End If
End Function
''' <summary>
''' StopAlert displays generic error messages
''' </summary>
Public Overrides Sub StopAlert(message As SimplyMessage)
Console.WriteLine(message.Message)
End Sub
''' <summary>
''' StopAlertNotShow displays confirmations
''' </summary>
Public Overrides Function StopAlertNotShow(message As SimplyMessage) As Boolean
Console.WriteLine(message.Message)
Return False
End Function
End Class
#End Region
#Region "class MessageBoxAlert"
''' <summary>
''' This class overrides the default behaviour of alerts in the SDK.
''' The default behaviour of the SDK throws exceptions.
''' Here, Messages are displayed via the MessageBoxes.
''' </summary>
Public Class MessageBoxAlert
Inherits SDKAlert
''' <summary>
''' AskAlert expects a return value of AlertResult.YES or AlertResult.NO
''' </summary>
Public Overrides Function AskAlert(message As SimplyMessage) As AlertResult
Dim res As DialogResult = MessageBox.Show(message.Message, "", MessageBoxButtons.YesNo)
If res = DialogResult.Yes Then
Return AlertResult.YES
Else
Return AlertResult.NO
End If
End Function
''' <summary>
''' AskSaveAlert pops up when a ledger is unsaved and an action is performed
''' which attempts to close the current ledger.
''' AskAlert expects a return value of AlertResult.YES or AlertResult.NO
''' </summary>
Public Overrides Function AskSaveAlert() As AlertResult
Dim res As DialogResult = MessageBox.Show("Save ledger record before continue?", "", MessageBoxButtons.YesNo)
If res = DialogResult.Yes Then
Return AlertResult.YES
Else
Return AlertResult.NO
End If
End Function
''' <summary>
''' YNCAlert expects a return value of AlertResult.YES or AlertResult.NO or AlertResult.CANCEL
''' </summary>
Public Overrides Function YNCAlert(message As SimplyMessage) As AlertResult
Dim res As DialogResult = MessageBox.Show(message.Message, "", MessageBoxButtons.YesNoCancel)
If res = DialogResult.Yes Then
Return AlertResult.YES
ElseIf res = DialogResult.No Then
Return AlertResult.NO
Else
Return AlertResult.CANCEL
End If
End Function
''' <summary>
''' StopAlert displays generic error messages
''' </summary>
Public Overrides Sub StopAlert(message As SimplyMessage)
MessageBox.Show(message.Message)
End Sub
''' <summary>
''' StopAlertNotShow displays confirmations
''' </summary>
Public Overrides Function StopAlertNotShow(message As SimplyMessage) As Boolean
MessageBox.Show(message.Message)
Return False
End Function
End Class
#End Region
#Region "class NoAlert"
''' <summary>
''' This class simulates the default behaviour of alerts in the SDK.
''' The default behaviour of the SDK throws exceptions.
''' It is intentionally empty, since no methods are overridden.
''' </summary>
Public Class NoAlert
Inherits SDKAlert
End Class
#End Region
#Region "class SDKExample"
''' <summary>
''' Sample SDK program
''' </summary>
Public Class SDKExample
#Region "Misc Account Number Setup for SDKExample"
Private Const SDK_ACCTBANK As Integer = 1054
Private Const SDK_ACCTINVASSSET As Integer = 1521
Private Const SDK_ACCTINVREV As Integer = 4021
Private Const SDK_ACCTINVCOGS As Integer = 5011
Private Shared m_ActNumLength As Short = 4
''' <summary>
''' Sets m_ActNumLength according to database setting
''' </summary>
Private Shared Sub GetAccountNumberLen()
m_ActNumLength = CShort((New SDKDatabaseUtility()).RunScalerQuery("SELECT nActNumLen FROM tCompOth"))
End Sub
''' <summary>
''' Generates Account Number according to m_ActNumLength
''' </summary>
Private Shared Function MakeAccountNumber(number As Integer) As Integer
Return MakeAccountNumber(number, m_ActNumLength)
End Function
''' <summary>
''' Generates Account Number according to m_ActNumLength
''' </summary>
Private Shared Function MakeAccountNumber(number As Integer, maxLen As Short) As Integer
Dim zeros As String = String.Empty
Dim num_str As String = If((number <= 0), "1", InlineAssignHelper(num_str, Convert.ToString(number)))
For i As Integer = num_str.Length To Math.Min(CShort(8), maxLen) - 1
zeros = zeros & "0"
Next
Return Convert.ToInt32(num_str & zeros)
End Function
''' <summary>
''' Checks if an account exists
''' </summary>
Private Shared Function AccountExists(number As Integer) As Boolean
number = MakeAccountNumber(number)
If CInt((New SDKDatabaseUtility()).RunScalerQuery("SELECT COUNT(lId) FROM tAccount WHERE lId = " & number.ToString())) > 0 Then
Return True
End If
Return False
End Function
#End Region
#Region "AddLedgersExample"
''' <summary>
''' Add Ledgers Example
''' </summary>
Public Shared Sub AddLedgersExample()
Dim input As String
Console.WriteLine()
Console.WriteLine("-----------------------------------")
Console.WriteLine("Adding Ledger Records Example")
Console.WriteLine("-----------------------------------")
Console.WriteLine()
' accounts
Console.Write("Enter a new Account Number: ")
input = Console.ReadLine()
Dim acctled As AccountLedger = SDKInstanceManager.Instance.OpenAccountLedger()
Try
acctled.InitializeNew()
acctled.Name = "Test Account"
acctled.NameAlt = "Test AccountF"
acctled.Number = Convert.ToInt32(input)
acctled.Type = "G"
If acctled.Save() Then
Console.WriteLine("Account added successfully")
End If
Catch e As InvalidEntryException
Console.WriteLine(e.Message)
End Try
SDKInstanceManager.Instance.CloseAccountLedger()
' vendors
Console.WriteLine()
Console.Write("Enter a new Vendor name: ")
input = Console.ReadLine()
Dim venled As VendorLedger = SDKInstanceManager.Instance.OpenVendorLedger()
venled.InitializeNew()
venled.Name = input
venled.Contact = "Richard Zhanen"
venled.Street1 = "7077-76th Avenue"
venled.City = "New Westminister"
venled.Province = "British Columbia"
venled.PostalCode = "V8Y 3K7"
venled.Country = "Canada"
venled.Phone1 = "604-552-5149"
venled.Phone2 = "604-555-7204"
venled.Fax = "604-555-7211"
venled.Email = "richard@hotmail.com"
venled.WebSite = "www.richard.com"
If venled.Save() Then
Console.WriteLine("Vendor added successfully")
End If
SDKInstanceManager.Instance.CloseVendorLedger()
' customers
Console.WriteLine()
Console.Write("Enter a new Customer name: ")
input = Console.ReadLine()
Dim custled As CustomerLedger = SDKInstanceManager.Instance.OpenCustomerLedger()
custled.InitializeNew()
custled.Name = input
custled.Contact = "Darryl Wolfe"
custled.Street1 = "552 Columbia Street"
custled.City = "Seattle"
custled.Province = "Washington"
custled.PostalCode = "98505"
custled.Country = "USA"
custled.Phone1 = "206-445-7007"
custled.Phone2 = "206-283-6009"
custled.Fax = "206-555-7012"
custled.Email = "darryl@hotmail.com"
custled.WebSite = "www.darryl.com"
If custled.Save() Then
Console.WriteLine("Customer added successfully")
End If
SDKInstanceManager.Instance.CloseCustomerLedger()
' employees
Console.WriteLine()
Console.Write("Enter a new Employee name: ")
input = Console.ReadLine()
Dim empled As EmployeeLedgerBase = SDKInstanceManager.Instance.OpenEmployeeLedger()
empled.InitializeNew()
empled.Name = input
If TypeOf empled Is EmployeeLedgerUS Then
empled.Street1 = "656 Pitt Street"
empled.City = "Los Angeles"
empled.Province = "CALIFORNIA"
empled.PostalCode = "91101"
empled.Phone1 = "604-555-7888"
empled.Phone2 = "604-555-7999"
empled.SINSSN = "706921129"
empled.BirthDate = New DateTime(1976, 1, 1)
empled.HireDate = New DateTime(2001, 1, 1)
empled.TaxTable = "CALIFORNIA"
TryCast(empled, EmployeeLedgerUS).StateStatus = "Single"
empled.PayPeriods = 24
ElseIf TypeOf empled Is EmployeeLedger Then
empled.Street1 = "656 Pitt Street"
empled.City = "Burnaby"
empled.Province = "British Columbia"
empled.PostalCode = "V7T 3H9"
empled.Phone1 = "604-555-7888"
empled.Phone2 = "604-555-7999"
empled.SINSSN = "706921129"
empled.BirthDate = New DateTime(1976, 1, 1)
empled.HireDate = New DateTime(2001, 1, 1)
empled.TaxTable = "British Columbia"
empled.PayPeriods = 24
End If
If empled.Save() Then
Console.WriteLine("Employee added successfully")
End If
SDKInstanceManager.Instance.CloseEmployeeLedger()
' inventory
Console.WriteLine()
Console.Write("Enter a new Inventory number: ")
input = Console.ReadLine()
Dim invled As InventoryLedger = SDKInstanceManager.Instance.OpenInventoryLedger()
invled.InitializeNew()
invled.Number = input
invled.Name = "Service1"
invled.NameAlt = "Service1"
invled.IsServiceType = True
invled.IsActivityType = False
invled.StockingUnit = "Hour"
invled.StockingUnitAlt = "Hour"
invled.RegularPrice = 10.49
invled.PreferredPrice = 10.49
If invled.Save() Then
Console.WriteLine("Inventory added successfully")
End If
SDKInstanceManager.Instance.CloseInventoryLedger()
' projects
Console.WriteLine()
Console.Write("Enter a new Project name: ")
input = Console.ReadLine()
Dim projled As ProjectLedger = SDKInstanceManager.Instance.OpenProjectLedger()
projled.InitializeNew()
projled.Name = input
projled.NameAlt = "Basement Renovation F"
projled.StartDate = New DateTime(2001, 2, 2)
If projled.Save() Then
Console.WriteLine("Project added successfully")
End If
SDKInstanceManager.Instance.CloseProjectLedger()
End Sub
#End Region
#Region "LoadLedgersExample"
''' <summary>
''' Load Ledgers Example
''' </summary>
Public Shared Sub LoadLedgersExample()
Dim input As String
Console.WriteLine()
Console.WriteLine("-----------------------------------")
Console.WriteLine("Loading Ledger Records Example")
Console.WriteLine("-----------------------------------")
Console.WriteLine()
' accounts
Console.Write("Enter an Account Number: ")
input = Console.ReadLine()
Console.WriteLine()
Dim acctled As AccountLedger = SDKInstanceManager.Instance.OpenAccountLedger()
Try
If acctled.LoadByAccountNumber(Convert.ToInt32(input)) Then
Console.WriteLine("Number: {0}", acctled.Number)
Console.WriteLine("Name: {0}", acctled.Name)
Console.WriteLine("Name (Alt): {0}", acctled.NameAlt)
Console.WriteLine("Type: {0}", acctled.Type)
Console.WriteLine("Class: {0}", acctled.[Class])
Console.WriteLine("CurrencyCode: {0}", acctled.CurrencyCode)
Else
Console.WriteLine("Account not found: " & input)
End If
Catch e As InvalidEntryException
Console.WriteLine(e.Message + ": " & input)
End Try
Console.WriteLine()
SDKInstanceManager.Instance.CloseAccountLedger()
' vendors
Console.Write("Enter a Vendor Name: ")
input = Console.ReadLine()
Console.WriteLine()
Dim venled As VendorLedger = SDKInstanceManager.Instance.OpenVendorLedger()
Try
If venled.LoadByName(input) Then
Console.WriteLine("Name: {0}", venled.Name)
Console.WriteLine("Contact: {0}", venled.Contact)
Console.WriteLine("Street1: {0}", venled.Street1)
Console.WriteLine("Street2: {0}", venled.Street2)
Console.WriteLine("City: {0}", venled.City)
Console.WriteLine("Province: {0}", venled.Province)
Console.WriteLine("PostalCode: {0}", venled.PostalCode)
Console.WriteLine("Country: {0}", venled.Country)
Console.WriteLine("Phone1: {0}", venled.Phone1)
Console.WriteLine("Phone2: {0}", venled.Phone2)
Console.WriteLine("Fax: {0}", venled.Fax)
Console.WriteLine("Email: {0}", venled.Email)
Console.WriteLine("WebSite: {0}", venled.WebSite)
Console.WriteLine("CurrencyCode: {0}", venled.CurrencyCode)
Console.WriteLine("TaxId: {0}", venled.TaxId)
Else
Console.WriteLine("Vendor not found: " & input)
End If
Catch e As InvalidEntryException
Console.WriteLine(e.Message + ": " & input)
End Try
Console.WriteLine()
SDKInstanceManager.Instance.CloseVendorLedger()
' customers
Console.Write("Enter a Customer Name: ")
input = Console.ReadLine()
Console.WriteLine()
Dim cusled As CustomerLedger = SDKInstanceManager.Instance.OpenCustomerLedger()
Try
If cusled.LoadByName(input) Then
Console.WriteLine("Name: {0}", cusled.Name)
Console.WriteLine("Contact: {0}", cusled.Contact)
Console.WriteLine("Street1: {0}", cusled.Street1)
Console.WriteLine("Street2: {0}", cusled.Street2)
Console.WriteLine("City: {0}", cusled.City)
Console.WriteLine("Province: {0}", cusled.Province)
Console.WriteLine("PostalCode: {0}", cusled.PostalCode)
Console.WriteLine("Country: {0}", cusled.Country)
Console.WriteLine("Phone1: {0}", cusled.Phone1)
Console.WriteLine("Phone2: {0}", cusled.Phone2)
Console.WriteLine("Fax: {0}", cusled.Fax)
Console.WriteLine("Email: {0}", cusled.Email)
Console.WriteLine("WebSite: {0}", cusled.WebSite)
Console.WriteLine("CurrencyCode: {0}", cusled.CurrencyCode)
Console.WriteLine("PriceList: {0}", cusled.PriceList)
Else
Console.WriteLine("Customer not found: " & input)
End If
Catch e As InvalidEntryException
Console.WriteLine(e.Message + ": " & input)
End Try
Console.WriteLine()
SDKInstanceManager.Instance.CloseCustomerLedger()
' employees
Console.Write("Enter an Employee Name: ")
input = Console.ReadLine()
Console.WriteLine()
Dim empled As EmployeeLedgerBase = SDKInstanceManager.Instance.OpenEmployeeLedger()
Try
If empled.LoadByName(input) Then
Console.WriteLine("Name: {0}", empled.Name)
Console.WriteLine("Street1: {0}", empled.Street1)
Console.WriteLine("Street2: {0}", empled.Street2)
Console.WriteLine("City: {0}", empled.City)
Console.WriteLine("State: {0}", empled.Province)
Console.WriteLine("Zip Code: {0}", empled.PostalCode)
Console.WriteLine("Phone1: {0}", empled.Phone1)
Console.WriteLine("Phone2: {0}", empled.Phone2)
Console.WriteLine("SSN: {0}", empled.SINSSN)
Console.WriteLine("BirthDate: {0:d}", empled.BirthDate)
Console.WriteLine("HireDate: {0:d}", empled.HireDate)
Console.WriteLine("PayPeriods: {0}", empled.PayPeriods)
Console.WriteLine("TaxTable: {0}", empled.TaxTable)
If TypeOf empled Is EmployeeLedgerUS Then
Console.WriteLine("Federal Status:{0}", TryCast(empled, EmployeeLedgerUS).FederalStatus)
Console.WriteLine("State Status: {0}", TryCast(empled, EmployeeLedgerUS).StateStatus)
Console.WriteLine("Federal Allowances:{0}", TryCast(empled, EmployeeLedgerUS).FederalAllowances)
Console.WriteLine("State Allowances:{0}", TryCast(empled, EmployeeLedgerUS).StateAllowances)
End If
Else
Console.WriteLine("Employee not found: " & input)
End If
Catch e As InvalidEntryException
Console.WriteLine(e.Message + ": " & input)
End Try
Console.WriteLine()
SDKInstanceManager.Instance.CloseEmployeeLedger()
' inventory
Console.Write("Enter an Inventory Part Code: ")
input = Console.ReadLine()
Console.WriteLine()
Dim invled As InventoryLedger = SDKInstanceManager.Instance.OpenInventoryLedger()
Try
If invled.LoadByPartCode(input) Then
Console.WriteLine("Part Code: {0}", invled.Number)
Console.WriteLine("Name: {0}", invled.Name)
Console.WriteLine("Name (Alt): {0}", invled.NameAlt)
Console.WriteLine("IsServiceType: {0}", invled.IsServiceType)
Console.WriteLine("IsActivityType: {0}", invled.IsActivityType)
Console.WriteLine("StockingUnit: {0}", invled.StockingUnit)
Console.WriteLine("StockingUnit (Alt): {0}", invled.StockingUnitAlt)
Console.WriteLine("RegularPrice: {0}", invled.RegularPrice)
Console.WriteLine("PreferredPrice: {0}", invled.PreferredPrice)
Console.WriteLine("AssetAccount: {0:d}", invled.AssetAccount)
Console.WriteLine("RevenueAccount: {0:d}", invled.RevenueAccount)
Console.WriteLine("ExpenseAccount: {0}", invled.ExpenseAccount)
Console.WriteLine("VarianceAccount: {0}", invled.VarianceAccount)
Else
Console.WriteLine("Inventory not found: " & input)
End If
Catch e As InvalidEntryException
Console.WriteLine(e.Message + ": " & input)
End Try
Console.WriteLine()
SDKInstanceManager.Instance.CloseInventoryLedger()
' inventory
Console.Write("Enter a Project Name: ")
input = Console.ReadLine()
Console.WriteLine()
Dim projled As ProjectLedger = SDKInstanceManager.Instance.OpenProjectLedger()
Try
If projled.LoadByName(input) Then
Console.WriteLine("Name: {0}", projled.Name)
Console.WriteLine("Name (Alt): {0}", projled.NameAlt)
Console.WriteLine("StartDate: {0:d}", projled.StartDate)
Else
Console.WriteLine("Project not found: " & input)
End If
Catch e As InvalidEntryException
Console.WriteLine(e.Message + ": " & input)
End Try
Console.WriteLine()
SDKInstanceManager.Instance.CloseProjectLedger()
End Sub
#End Region
#Region "ErrorCheckingExample"
''' <summary>
''' Error Checking Example
''' Must step through for this example
''' </summary>
Public Shared Sub ErrorCheckingExample()
Console.WriteLine()
Console.WriteLine("-----------------------------------")
Console.WriteLine("Error Checking Example")
Console.WriteLine("-----------------------------------")
Console.WriteLine()
' SimplyNoAccessException examples
' Account Ledger
Dim acctled As AccountLedger = SDKInstanceManager.Instance.OpenAccountLedger()
acctled.InitializeNew()
acctled.Number = MakeAccountNumber(1051)
acctled.Name = "Petty Cash"
acctled.Type = "H"
' Note: Setting account type to Group Heading
Try
' Trying to access an object that does not currently exist
Console.WriteLine("Attempting set the class of a Header account...")
' This is the Account Class under the Class Options tab
acctled.[Class] = "Bank"
Catch e As SimplyNoAccessException
' Cannot do this here because the current account type is a Group Heading
' Group Headings cannot specify an account class (not available at all)
Console.WriteLine(e.Message)
Console.WriteLine()
End Try
SDKInstanceManager.Instance.CloseAccountLedger()
' Inventory & Services Ledger
Dim invled As InventoryLedger = SDKInstanceManager.Instance.OpenInventoryLedger()
invled.InitializeNew()
invled.IsServiceType = False
' set the Type to Inventory
Try
' Trying to select the Activity (Time & Billing) checkbox
Console.WriteLine("Attempting set to an activity when the item is not a service...")
invled.IsActivityType = True
Catch e As SimplyNoAccessException
' Cannot do this because the Activity (Time & Billing) checkbox doesn't
' exist when the Type is Inventory
Console.WriteLine(e.Message)
Console.WriteLine()
End Try
SDKInstanceManager.Instance.CloseInventoryLedger()
' InvalidEntryException examples
' Account Ledger
acctled = SDKInstanceManager.Instance.OpenAccountLedger()
acctled.InitializeNew()
acctled.Number = MakeAccountNumber(1501)
Try
' Set the Account Class to a class that doesn't exist
Console.WriteLine("Attempting set an account class that does not exist [Fake Class]...")
acctled.[Class] = "Fake Class"
Catch e As InvalidEntryException
' Cannot do this because this account class doesn't exist
Console.WriteLine(e.Message)
Console.WriteLine()
End Try
SDKInstanceManager.Instance.CloseAccountLedger()
' Customer Ledger
Dim custled As CustomerLedger = SDKInstanceManager.Instance.OpenCustomerLedger()
custled.InitializeNew()
custled.Name = "test customer"
Try
' Selecting a price list that does not exist
Console.WriteLine("Attempting set an price list to an item that does not exist [Fake Price List]...")
custled.PriceList = "Fake Price List"
Catch e As InvalidEntryException
' Cannot do this because the item being selected is not in the price list
Console.WriteLine(e.Message)
Console.WriteLine()
End Try
SDKInstanceManager.Instance.CloseCustomerLedger()
End Sub
#End Region
#Region "AlertMessagesExample"
''' <summary>
''' Alert Message example
''' </summary>
Public Shared Sub AlertMessagesExample()
Console.WriteLine()
Console.WriteLine("-----------------------------------")
Console.WriteLine("Alert Messages Example")
Console.WriteLine("-----------------------------------")
Console.WriteLine()
Dim custled As CustomerLedger = SDKInstanceManager.Instance.OpenCustomerLedger()
' Create a new customer
custled.Name = "Danny12345"
custled.Save()
' Create the same customer to show the handling of alerts (ConsoleAlert)
custled.Save()
' Create the same customer to show the handling of alerts (MessagBoxAlert)
SDKInstanceManager.Instance.SetAlertImplementation(New MessageBoxAlert())
custled.Save()
' Set to have no alerts handled to show the catching of SimlyErrorMessageException
SDKInstanceManager.Instance.SetAlertImplementation(New NoAlert())
custled.Name = "Danny12345"
Try
custled.Save()
Catch e As SimplyErrorMessageException
' Able to catch the default exception if no alerts
If e.MessageId = SimplyMessageId.E71 Then
Console.WriteLine("DUPLICATE CUSTOMER")
Else
Console.WriteLine(e.Message)
End If
End Try
SDKInstanceManager.Instance.CloseCustomerLedger()
' demostrate catching of AlertNotImplementedException
Dim invled As InventoryLedger = SDKInstanceManager.Instance.OpenInventoryLedger()
invled.InitializeNew()
invled.IsServiceType = False
Try
' Setting an account that does not have the Asset class type will prompt the user
invled.AssetAccount = MakeAccountNumber(1100).ToString()
Catch e As AlertNotImplementedException
' Alerts are still off, and this message prompts the user
' However, the program is unable to continue since this type of exception requires user input
Console.WriteLine(e.Message)
End Try
SDKInstanceManager.Instance.CloseInventoryLedger()
SDKInstanceManager.Instance.SetAlertImplementation(New ConsoleAlert())
End Sub
#End Region
#Region "DatabaseUtilityExamples"
''' <summary>
''' Database Utility examples
''' </summary>
Public Shared Sub DatabaseUtilityExamples()
Console.WriteLine()
Console.WriteLine("-----------------------------------")
Console.WriteLine("Usage of SDKDatabaseUtility Example")
Console.WriteLine("-----------------------------------")
Console.WriteLine()
Dim util As New SDKDatabaseUtility()
' Example of getting a string
' Get all names from the vendor table
Dim count As Integer = util.RunSelectQuery("SELECT sName FROM tVendor")
Dim name As String
For index As Integer = 0 To count - 1
name = util.GetStringFromLastSelectQuery(index, 0)
Console.WriteLine("RunSelectQuery(""SELECT sName FROM tVendor""): " & name)
Next
Console.WriteLine()
' Example of getting an integer
' Get the asset account numbers that are not blank from the inventory table
count = util.RunSelectQuery("SELECT lAcNAsset FROM tInvent WHERE lAcNAsset != 0")
Dim assetAccount As Integer
For index As Integer = 0 To count - 1
assetAccount = util.GetIntFromLastSelectQuery(index, 0)
Console.WriteLine("RunSelectQuery(""SELECT lAcNAsset FROM tInvent WHERE lAcNAsset != 0""): " & assetAccount.ToString())
Next
Console.WriteLine()
' Example of getting a short
' Get all pay periods from the Employee table
count = util.RunSelectQuery("SELECT nAcctClass FROM tAccount")
Dim acctclass As Short
For index As Integer = 0 To count - 1
acctclass = util.GetShortFromLastSelectQuery(index, 0)
Console.WriteLine("RunSelectQuery(""SELECT nAcctClass FROM tAccount""): " & acctclass.ToString())
Next
Console.WriteLine()
' Example of getting a date time
' Get all start dates from the project table
count = util.RunSelectQuery("SELECT dtStart FROM tProject")
Dim startDate As System.Nullable(Of DateTime)
For index As Integer = 0 To count - 1
startDate = util.GetDateTimeFromLastSelectQuery(index, 0)
Console.WriteLine("RunSelectQuery(""SELECT dtStart FROM tProject""): " & startDate.ToString())
Next
Console.WriteLine()
' Example of getting a double
' Get all prices that are greater than 50 from the the inventory price table
count = util.RunSelectQuery("SELECT dPrice FROM tInvPrc WHERE dPrice > 50")
Dim price As Double
For index As Integer = 0 To count - 1
price = util.GetDoubleFromLastSelectQuery(index, 0)
Console.WriteLine("RunSelectQuery(""SELECT dPrice FROM tInvPrc WHERE dPrice > 50""): " & price.ToString())
Next
Console.WriteLine()
' Example of getting a data set
' Get Vendor dataset
Dim x As Integer = util.RunSelectQuery("SELECT sName FROM tVendor")
Dim s As DataSet = util.GetDataSetFromLastSelectQuery()
name = DirectCast(s.Tables(0).Rows(0)(0), String)
Console.WriteLine("RunSelectQuery(""SELECT sName FROM tVendor""): " & name)
Console.WriteLine()
' Example of scaler query
' Get count of records in vendor table where name is greater than 'M'
Dim numRecords As Long = CLng(util.RunScalerQuery("SELECT count(*) FROM tVendor WHERE sName > 'M'"))
Console.WriteLine("RunScalerQuery(""SELECT count(*) FROM tVendor WHERE sName > 'M'""): " & numRecords.ToString() & " records")
Console.WriteLine()
' Example of non query
' Update all record to "Canada" in vendor table
Dim ret As Integer = util.RunNonQuery("UPDATE tVendor SET sCountry = 'Canada'")
Console.WriteLine("RunNonQuery(""UPDATE tVendor SET sCountry = 'Canada'""): " & ret & "records affected")
Console.WriteLine()
End Sub
#End Region
#Region "Process Journal"
''' <summary>
''' Add ledger records required for journal examples
''' </summary>
Public Shared Sub SetupJournalsExample()
Dim acctled As AccountLedger = SDKInstanceManager.Instance.OpenAccountLedger()
' Create 4 expense accounts
Dim accountNum As Integer = 5611
For i As Integer = 1 To 4
If Not acctled.LoadByAccountNumber(MakeAccountNumber(accountNum)) Then
acctled.InitializeNew()
acctled.Name = "Expense " & i
acctled.Number = MakeAccountNumber(accountNum)
acctled.Type = "G"
acctled.Save()
End If
accountNum += 1
Next
' Create bank account
If Not acctled.LoadByAccountNumber(MakeAccountNumber(SDK_ACCTBANK)) Then
acctled.InitializeNew()
acctled.Name = "Checking Account"
acctled.Number = MakeAccountNumber(SDK_ACCTBANK)
acctled.Type = "A"
acctled.[Class] = "Bank"
acctled.Save()
End If
' Create inventory asset
If Not acctled.LoadByAccountNumber(MakeAccountNumber(SDK_ACCTINVASSSET)) Then
acctled.InitializeNew()
acctled.Name = "Nails Asset"
acctled.Number = MakeAccountNumber(SDK_ACCTINVASSSET)
acctled.Type = "G"
acctled.[Class] = "Inventory"
acctled.Save()
End If
' Create inventory revenue
If Not acctled.LoadByAccountNumber(MakeAccountNumber(SDK_ACCTINVREV)) Then
acctled.InitializeNew()
acctled.Name = "Nails revenue"
acctled.Number = MakeAccountNumber(SDK_ACCTINVREV)
acctled.Type = "G"
acctled.[Class] = "Operating Revenue"
acctled.Save()
End If
' Create inventory COGS
If Not acctled.LoadByAccountNumber(MakeAccountNumber(SDK_ACCTINVCOGS)) Then
acctled.InitializeNew()
acctled.Name = "Nails Cogs"
acctled.Number = MakeAccountNumber(SDK_ACCTINVCOGS)
acctled.Type = "G"
acctled.[Class] = "Cost of Goods Sold"
acctled.Save()
End If
SDKInstanceManager.Instance.CloseAccountLedger()
' Create vendor
Dim vendled As VendorLedger = SDKInstanceManager.Instance.OpenVendorLedger()
vendled.InitializeNew()
vendled.Name = "Sage Vendor"
vendled.Save()
SDKInstanceManager.Instance.CloseVendorLedger()
' Create customer
Dim custled As CustomerLedger = SDKInstanceManager.Instance.OpenCustomerLedger()
custled.InitializeNew()
custled.Name = "Sage Customer"
custled.Save()
SDKInstanceManager.Instance.CloseCustomerLedger()
' Create inventory
Dim invled As InventoryLedger = SDKInstanceManager.Instance.OpenInventoryLedger()
If Not invled.LoadByPartCode("N-3347") Then
invled.InitializeNew()
invled.Number = "N-3347"
invled.Name = "Nails"
invled.IsServiceType = False
invled.IsActivityType = False
invled.StockingUnit = "Box"
invled.RegularPrice = 1.49
invled.AssetAccount = MakeAccountNumber(SDK_ACCTINVASSSET).ToString()
invled.RevenueAccount = MakeAccountNumber(SDK_ACCTINVREV).ToString()
invled.ExpenseAccount = MakeAccountNumber(SDK_ACCTINVCOGS).ToString()
invled.Save()
End If
' Create service
If Not invled.LoadByPartCode("CR-10") Then
invled.InitializeNew()
invled.Number = "CR-10"
invled.Name = "Computer Repair"
invled.IsServiceType = True
invled.IsActivityType = False
invled.StockingUnit = "Hour"
invled.Save()
End If
SDKInstanceManager.Instance.CloseInventoryLedger()
' create project
Dim projled As ProjectLedger = SDKInstanceManager.Instance.OpenProjectLedger()
projled.InitializeNew()
projled.Name = "Sage Project"
projled.Save()
SDKInstanceManager.Instance.CloseProjectLedger()
End Sub
''' <summary>
''' Process General Journal Example
''' </summary>
Public Shared Sub ProcessGeneralJournalExample()
Console.WriteLine()
Console.WriteLine("-----------------------------------")
Console.WriteLine("Processing General Journal Example")
Console.WriteLine("-----------------------------------")
Console.WriteLine()
SetupJournalsExample()
Console.WriteLine()
' General Journal Example
Dim genJourn As GeneralJournal = SDKInstanceManager.Instance.OpenGeneralJournal()
genJourn.Source = "GJ 1"
genJourn.Comment = "Test General Journal"
genJourn.SetAccount(MakeAccountNumber(5611).ToString(), 1)
genJourn.SetDebit(100.0, 1)
genJourn.SetAccount(MakeAccountNumber(5612).ToString(), 2)
genJourn.SetDebit(200.0, 2)
genJourn.SetAccount(MakeAccountNumber(5613).ToString(), 3)
genJourn.SetDebit(300.0, 3)
genJourn.SetAccount(MakeAccountNumber(5614).ToString(), 4)
genJourn.SetDebit(400.0, 4)
genJourn.SetAccount(MakeAccountNumber(SDK_ACCTBANK).ToString(), 5)
genJourn.SetCredit(1000.0, 5)
genJourn.SetComment("Comment 1", 1)
If genJourn.Post() Then
Console.WriteLine("Purchase invoice 'GJ 1' posting succeeded")
Else
Console.WriteLine("Purchase invoice 'GJ 1' posting failed")
End If
Console.WriteLine()
SDKInstanceManager.Instance.CloseGeneralJournal()
End Sub
''' <summary>
''' Process Invoices Example
''' </summary>
Public Shared Sub ProcessInvoicesExample()
Console.WriteLine()
Console.WriteLine("-----------------------------------")
Console.WriteLine("Processing Invoices Example")
Console.WriteLine("-----------------------------------")
Console.WriteLine()
SetupJournalsExample()
Console.WriteLine()
' Purchase Invoice Example
Dim purJourn As PurchasesJournal = SDKInstanceManager.Instance.OpenPurchasesJournal()
Dim purInvoiceNum As String = "PJ" & (New Random().[Next](9999).ToString())
' Pay Later
purJourn.SelectTransType(0)
' invoice
purJourn.SelectPaidByType("Pay Later")
purJourn.InvoiceNumber = purInvoiceNum
purJourn.SelectAPARLedger("Sage Vendor")
purJourn.SetItemNumber("N-3347", 1)
purJourn.SetQuantity(100, 1)
purJourn.SetUnit("Box", 1)
purJourn.SetPrice(0.99, 1)
purJourn.SetDescription("Item #2", 2)
purJourn.SetQuantity(100, 2)
purJourn.SetUnit("Each", 2)
purJourn.SetPrice(0.49, 2)
purJourn.SetLineAccount(MakeAccountNumber(5611).ToString(), 2)
purJourn.SetFreightAmount(15.0)
purJourn.SetTermDiscPercent(5.0)
purJourn.SetTermDiscDay(10)
purJourn.SetTermDiscNetDay(21)
Dim addInfo As AdditionalInfo = purJourn.DoAdditionalInfo()
addInfo.SetAdditionalComment("Comment")
addInfo.SetAdditionalDate("Mar 15, 2015")
addInfo.Save()
If purJourn.Post() Then
Console.WriteLine("Purchase invoice '" & purInvoiceNum & "' posting succeeded")
Else
Console.WriteLine("Purchase invoice '" & purInvoiceNum & "' posting failed")
End If
Console.WriteLine()
' Lookup posted invoice
purJourn.Undo()
If purJourn.LoadInvoiceForLookup("Sage Vendor", purInvoiceNum) Then
Console.WriteLine("Lookup purchase invoice succeeded")
Console.WriteLine((purJourn.GetAPARLedgerName() & ":" & vbTab) + purJourn.InvoiceNumber)
For lineNum As Integer = 1 To purJourn.NumberOfJournalLines
Console.WriteLine("Line {0}:" & vbTab & "{1}" & vbTab & "{2:f}" & vbTab & "{3}" & vbTab & "{4}" & vbTab & "{5:f}" & vbTab & "{6:f}" & vbTab & "{7}", lineNum.ToString(), purJourn.GetItemNumber(lineNum), purJourn.GetQuantity(lineNum), purJourn.GetUnit(lineNum), purJourn.GetDescription(lineNum), _
purJourn.GetPrice(lineNum), purJourn.GetLineAmount(lineNum), purJourn.GetAccountNumber(lineNum))
Next
Console.WriteLine("Total: " & purJourn.GetTotalAmount().ToString())
Console.WriteLine()
' Adjust currently loaded invoice
If purJourn.AdjustInvoiceFromLookup() Then
For lineNum As Integer = 1 To purJourn.NumberOfJournalLines
If purJourn.GetQuantity(lineNum) > 2 Then
purJourn.SetQuantity(purJourn.GetQuantity(lineNum) - 1.0, lineNum)
End If
Next
If purJourn.Post() Then
Console.WriteLine("Purchase invoice '" & purInvoiceNum & "' adjustment succeeded")
Else
Console.WriteLine("Purchase invoice '" & purInvoiceNum & "' adjustment failed")
End If
Console.WriteLine()
End If
End If
SDKInstanceManager.Instance.ClosePurchasesJournal()
' Sales Invoice Example
Dim salJourn As SalesJournal = SDKInstanceManager.Instance.OpenSalesJournal()
' Cash
salJourn.SelectTransType(0)
' invoice
salJourn.SelectPaidByType("Cash")
salJourn.InvoiceNumber = "SJ 1"
salJourn.SetShipDate(salJourn.GetJournalDate())
salJourn.SelectAPARLedger("Sage Customer")
salJourn.SelectShiptoAddressName("<Mailing Address>")
salJourn.SetShipToAddressLine("DOS Ltd", 1)
salJourn.SetShipToAddressLine("Tom Vu", 2)
salJourn.SetShipToAddressLine("7789 Rich Street", 3)
salJourn.SetShipToAddressLine("Richmond, BC", 4)
salJourn.SetShipToAddressLine("V3K 2Q2", 5)
salJourn.SetItemNumber("CR-10", 1)
salJourn.SetQuantity(1, 1)
salJourn.SetUnit("Hour", 1)
salJourn.SetDescription("Computer Repair - No Warranty", 1)
salJourn.SetPrice(90.0, 1)
salJourn.SetLineAmount(90.0, 1)
salJourn.SetLineAccount(MakeAccountNumber(SDK_ACCTINVREV).ToString(), 1)
salJourn.SetDescription("Item #2", 2)
salJourn.SetQuantity(2, 2)
salJourn.SetUnit("Each", 2)
salJourn.SetPrice(0.99, 2)
salJourn.SetLineAccount(MakeAccountNumber(SDK_ACCTINVREV).ToString(), 2)
' allocate projects
Dim projAlloc As ProjectAllocation = salJourn.AllocateLine(2)
projAlloc.SetProject("Sage Project", 1)
Try
projAlloc.SetPercent(50.0, 1)
Catch generatedExceptionName As SimplyNoAccessException
' can't allocate by percent, allocation by amount
projAlloc.SetAmount(0.99, 1)
End Try
projAlloc.Save()
salJourn.SetFreightAmount(0.0)
salJourn.SetDiscountRate(5.0)
salJourn.SetComment("Paid by cash")
' additional information
addInfo = salJourn.DoAdditionalInfo()
addInfo.SetAdditionalComment("Comment")
addInfo.SetAdditionalDate("Mar 15, 2015")
addInfo.Save()
If salJourn.Post() Then
Console.WriteLine("Sales invoice 'SJ 1' posting succeeded")
Else
Console.WriteLine("Sales invoice 'SJ 1' posting failed")
End If
Console.WriteLine()
' Lookup posted invoice
salJourn.Undo()
If salJourn.LoadInvoiceForLookup("Sage Customer", "SJ 1") Then
Console.WriteLine("Lookup sales invoice succeeded")
Console.WriteLine((salJourn.GetAPARLedgerName() & ":" & vbTab) + salJourn.InvoiceNumber)
For lineNum As Integer = 1 To salJourn.NumberOfJournalLines
Console.WriteLine("Line {0}:" & vbTab & "{1}" & vbTab & "{2:f}" & vbTab & "{3}" & vbTab & "{4}" & vbTab & "{5:f}" & vbTab & "{6:f}" & vbTab & "{7}", lineNum.ToString(), salJourn.GetItemNumber(lineNum), salJourn.GetQuantity(lineNum), salJourn.GetUnit(lineNum), salJourn.GetDescription(lineNum), _
salJourn.GetPrice(lineNum), salJourn.GetLineAmount(lineNum), salJourn.GetAccountNumber(lineNum))
Next
' Show taxes
Dim taxtotal As TaxSummaryInfo = salJourn.GetTotalTaxAmountInfo()
For lineNum As Integer = 1 To taxtotal.GetCount()
Console.WriteLine("{0}:" & vbTab & vbTab & "{1:f}", taxtotal.GetTaxNameByRow(lineNum), taxtotal.GetTaxAmountByRow(lineNum))
Next
Console.WriteLine("Total Taxes:" & vbTab & "{0:f}", salJourn.GetTaxTotalAmount())
Console.WriteLine("Total: " & salJourn.GetTotalAmount().ToString())
Console.WriteLine()
' Adjust currently loaded invoice
If salJourn.AdjustInvoiceFromLookup() Then
salJourn.SetQuantity(salJourn.GetQuantity(1) + 1.0, 1)
If salJourn.Post() Then
Console.WriteLine("Sales invoice 'SJ 1' adjustment succeeded")
Else
Console.WriteLine("Sales invoice 'SJ 1' adjustment failed")
End If
Console.WriteLine()
End If
End If
' Reverse posted invoice
salJourn.Undo()
If salJourn.LoadInvoiceForLookup("Sage Customer", "SJ 1") Then
If salJourn.ReverseInvoice() Then
Console.WriteLine("Sales invoice 'SJ 1' reversal succeeded")
Else
Console.WriteLine("Sales invoice 'SJ 1' reversal failed")
End If
End If
SDKInstanceManager.Instance.CloseSalesJournal()
End Sub
''' <summary>
''' Process Orders and Quotes Example
''' </summary>
Public Shared Sub ProcessOrdersQuotesExample()
Console.WriteLine()
Console.WriteLine("-----------------------------------")
Console.WriteLine("Processing Journals Example")
Console.WriteLine("-----------------------------------")
Console.WriteLine()
SetupJournalsExample()
' Purchase Order Example
Dim purJourn As PurchasesJournal = SDKInstanceManager.Instance.OpenPurchasesJournal()
Try
' order
purJourn.SelectTransType(1)
Catch generatedExceptionName As SimplyNoAccessException
Console.WriteLine("Purchase Orders are currently not available in this database")
SDKInstanceManager.Instance.ClosePurchasesJournal()
Return
End Try
purJourn.SelectAPARLedger("Sage Vendor")
purJourn.OrderQuoteNum = "PurOrder1"
purJourn.SetItemNumber("N-3347", 1)
purJourn.SetOrdered(50, 1)
purJourn.SetUnit("Box", 1)
purJourn.SetPrice(0.99, 1)
If purJourn.Post() Then
Console.WriteLine("Purchase order 'PurOrder1' posting succeeded")
Else
Console.WriteLine("Purchase order 'PurOrder1' posting failed")
End If
Console.WriteLine()
' Lookup posted order
purJourn.Undo()
purJourn.SelectTransType(1)
If purJourn.LoadOrderQuoteRecord("PurOrder1") Then
Console.WriteLine("Lookup purchase order succeeded")
Console.WriteLine((purJourn.GetAPARLedgerName() & ":" & vbTab) + purJourn.OrderQuoteNum)
For lineNum As Integer = 1 To purJourn.NumberOfJournalLines
Console.WriteLine("Line {0}:" & vbTab & "{1}" & vbTab & "{2:f}" & vbTab & "{3:f}" & vbTab & "{4}" & vbTab & "{5}" & vbTab & "{6:f}" & vbTab & "{7:f}" & vbTab & "{8}", lineNum.ToString(), purJourn.GetItemNumber(lineNum), purJourn.GetOrdered(lineNum), purJourn.GetBackOrdered(lineNum), purJourn.GetUnit(lineNum), _
purJourn.GetDescription(lineNum), purJourn.GetPrice(lineNum), purJourn.GetLineAmount(lineNum), purJourn.GetAccountNumber(lineNum))
Next
Console.WriteLine("Total: " & purJourn.GetTotalAmount().ToString())
Console.WriteLine()
' Adjust currently loaded order
purJourn.AdjustOrderQuoteRecord()
purJourn.SetOrdered(40, 1)
If purJourn.Post() Then
Console.WriteLine("Purchase order 'PurOrder1' adjustment succeeded")
Else
Console.WriteLine("Purchase order 'PurOrder1' adjustment failed")
End If
Console.WriteLine()
End If
' remove order
purJourn.Undo()
purJourn.SelectTransType(1)
If purJourn.LoadOrderQuoteRecord("PurOrder1") Then
If purJourn.RemoveOrderQuote("PurOrder1") Then
Console.WriteLine("Purchase order 'PurOrder1' removal succeeded")
Else
Console.WriteLine("Purchase order 'PurOrder1' removal failed")
End If
Console.WriteLine()
End If
SDKInstanceManager.Instance.ClosePurchasesJournal()
' Sales Quote Example
Dim salJourn As SalesJournal = SDKInstanceManager.Instance.OpenSalesJournal()
Try
' quote
salJourn.SelectTransType(2)
Catch generatedExceptionName As SimplyNoAccessException
Console.WriteLine("Sales Quotes are currently not available in this database")
SDKInstanceManager.Instance.ClosePurchasesJournal()
Return
End Try
salJourn.SelectAPARLedger("Sage Customer")
salJourn.OrderQuoteNum = "QuoteToOrd1"
salJourn.SetShipDate(salJourn.GetJournalDate())
salJourn.SetItemNumber("CR-10", 1)
salJourn.SetOrdered(3, 1)
salJourn.SetUnit("Hour", 1)
salJourn.SetPrice(90.0, 1)
salJourn.SetLineAccount(MakeAccountNumber(SDK_ACCTINVREV).ToString(), 1)
If salJourn.Post() Then
Console.WriteLine("Sales quote 'QuoteToOrd1' posting succeeded")
Else
Console.WriteLine("Sales quote 'QuoteToOrd1' posting failed")
End If
Console.WriteLine()
' Convert Quote to an order
salJourn.Undo()
salJourn.SelectTransType(2)
If salJourn.LoadOrderQuoteRecord("QuoteToOrd1") Then
Try
salJourn.ConvertQuoteToOrder()
Catch generatedExceptionName As SimplyNoAccessException
Console.WriteLine("Sales Orders are currently not available in this database")
SDKInstanceManager.Instance.ClosePurchasesJournal()
Return
End Try
If salJourn.Post() Then
Console.WriteLine("Sales order 'QuoteToOrd1' posting ysucceeded")
Else
Console.WriteLine("Sales order 'QuoteToOrd1' posting failed")
End If
Console.WriteLine()
End If
' Remove order that was just converted
salJourn.Undo()
salJourn.SelectTransType(1)
If salJourn.LoadOrderQuoteRecord("QuoteToOrd1") Then
If salJourn.RemoveOrderQuote("QuoteToOrd1") Then
Console.WriteLine("Sales order 'QuoteToOrd1' removal succeeded")
Else
Console.WriteLine("Sales order 'QuoteToOrd1' removal failed")
End If
Console.WriteLine()
End If
SDKInstanceManager.Instance.CloseSalesJournal()
End Sub
#End Region
#Region "Main"
Public Shared Sub Main(args As String())
Dim saiFile As String = String.Empty
Dim sajFile As String = String.Empty
Dim username As String = String.Empty
Dim password As String = String.Empty
SDKInstanceManager.Instance.SetAlertImplementation(New ConsoleAlert())
Console.WriteLine("Enter file name of a sample Simply Accounting database")
Console.Write("> ")
saiFile = Console.ReadLine()
saiFile = saiFile.ToUpper()
If Not saiFile.EndsWith(".SAI") Then
Console.WriteLine("Not a valid Simply Accounting file.")
Return
End If
If Not File.Exists(saiFile) Then
Console.WriteLine("Simply Accounting file (.SAI) does not exist.")
Return
End If
sajFile = saiFile.Replace(".SAI", ".SAJ")
If Not Directory.Exists(sajFile) Then
Console.WriteLine("Simply Accounting folder (.SAJ) does not exist.")
Return
End If
Console.WriteLine(vbLf & "Enter username")
Console.Write("> ")
username = Console.ReadLine()
Console.WriteLine(vbLf & "Enter password")
Console.Write("> ")
password = Console.ReadLine()
Console.WriteLine(vbLf & "Opening database..." & vbLf)
' Please note, that this sample program may update data in your database, so please do not use live data.
' =====================================================
' We recommend using Premium sample data.
' =====================================================
'
' If you use data that does not have account numbers turned on or if the number of digits used for
' account numbers is not 4, this sample program will not work properly.
' If so, you will need to modify this sample program according to your test database.
If SDKInstanceManager.Instance.OpenDatabase(saiFile, username, password, False, "Simply SDK Sample Program", "SASDK", _
1) Then
Try
GetAccountNumberLen()
'Uncomment the examples you want to run
'LoadLedgersExample();
'ErrorCheckingExample();
'AlertMessagesExample();
'DatabaseUtilityExamples();
'ProcessGeneralJournalExample();
'ProcessInvoicesExample();
'ProcessOrdersQuotesExample();
AddLedgersExample()
Finally
SDKInstanceManager.Instance.CloseDatabase()
End Try
End If
End Sub
Private Shared Function InlineAssignHelper(Of T)(ByRef target As T, value As T) As T
target = value
Return value
End Function
#End Region
End Class
#End Region
End Namespace
ASKER
I found a different way around it, actually in the end I didn't end up using this specific code as it was beyond what I needed, but thank you for responding!
All is well that ends well!
FCapo, could you please elaborate on your solution to this problem?
I cant even get the sample included in SDK to open SimplyAccounting company database.
wSDBOpenDatabase(iDBLink, FLG_SHARED, "Sample", szUserName, szPassword, szHost, "", szPort) returns error code 130
Thanks
I cant even get the sample included in SDK to open SimplyAccounting company database.
wSDBOpenDatabase(iDBLink, FLG_SHARED, "Sample", szUserName, szPassword, szHost, "", szPort) returns error code 130
Thanks
I am looking for the same code as above BUT in Visual Basic 6, as I have an Old Application written in VB6 and I am trying to open database (wSDBOpenDatabase) with VB6 code. I am integrating Simply Accounting. (Trial version of 2012). Any help would be highly appreciated.
ASKER
I'm not sure if someone here can guide me through the process of using the SDK or has a faster solution without actually having to use the SDK?
Thank you