New set of articles on ERP

New set of articles posted on :

ERP Articles

Click above link for all articles on ERP

Book Review #2 – Beginning Visual Basic 2005

Authored by Thearon Willis and Bryan Newsome, and published by Wiley Dreamtech, this book is ideal for beginners.

This book is for those who want to learn application development, and covers fundamentals of Visual Basic language. A brief coverage of .Net is also included. Project creation and adding forms and writing code to handle common programming routines are taught. Note that the datatbase used is SQL Server 2005. However, the explanation is not based on a particular domain so falls short covering end to end knowledge badly required by young programmers.

Brief coverage of web services, mobile devices and ado .net is included. XML is covered very briefly.

The book fairly detailed in its explanation and coverage is exhaustive in many chapters.

All in all a good book for one who is looking to get a detailed exposure to application development.

http://www.wileyindia.com/

Book review #1: Title: Database programming using vb

Focus: Database programming

Domain: Accounting Software

Tools used: VB 2008, and SQL Server for the windows os.

This book takes you through the steps of developing an accounting software. The first few chapters cover  topics like vb and sql server. Next, the steps for developing an accounting software are outlined.
The steps are fairly detailed and some of them are

1. Developing the Masters
2. Creating the Project
3. Writing Standard Modules
4. Creating the Accounting Database
5. Exploring the Accounting Database
6. Populating the Group Table with Chart of Accounts
7. Designing & Programming the Accounts Form
8. Programming the Account Form
9. Writing a Constructor
10. Programming the Load() event procedure
11. Saving Account details into the DataSet
12. Moving to a particular row in the DataSet
13. Achieving error free/rapid data entry by enable/disable controls
14. Validating the data entered in the Amount TextBox control
15. Developing the Transactions
16. Developing the Tran Class
17. Designing and Programming the Journal form

A separate chapter is dedicated for programming reports which may be useful for crystal reports programmers.

This book will be interesting to programmers who are starting with software development. They can pick a
few tricks quickly and the book seems to be fairly priced. These books may be useful to students who wish to do a project which may of use in their careers. Of particular interest to me was the handling of the layers with a separate layer for business logic, present layer and data layer. Even though it is a difficult concept to grasp at the first read, this concept makes designing of applications cleaner and easier to read. With the advent of web applications, multi-layered applications are in vogue. In fact, most companies are seeing if programmers are aware of this mode of programming while recruiting.

With ERP becoming a major attraction for engineering students, this book can be an inexpensive way for programmers to start development and get a feel of application development.

Being a software programmer myself, I found the book quite useful. Finally, a book you can use. Click on the title for detailed contents of the book.

Visual Basic programming and accounting software software development

PS: If you want me to review your company or a book you have authored or a software just email me —

What you should know to start Developing a Database Software Application using Visual Basic which has to work on a Client Server network

1. Basics of Visual Basic and SQL Server
2. Basics of the domain you want to develop the application for.
3. For example of Accounting domain or Inventory Domain.
4. Basics of Client / Server Application Development with Visual Basic ( visual basic ) and SQL Server or C# and SQL Server
5. Knowledge of how to install a Network Operating system.
6. Knowledge of how to install a SQL Server Installation on a Client / Server Network.
7. Knowledge of installing and setting up an Active Directory Services on a Client / Server Network
8. Basics of concept of Domain Controller in a Windows Network
9. Basics of securing Databases in Sql Server
10. Knowledge of setup and use windows authentication in Sql Server.
11. Basics of setting up and using Windows Authentication in Sql Server.
12. Knowledge of setting up and using SQL Server Authentication.
13. Knowledge of creating Logins in Sql Server.
14. Knowledge of database Roles in Sql Server.
15. Knowledge of server Roles in Sql Server.
16. Knowledge of enterprise Manager of Sql Server.
17. Knowledge of how to use the Query Analyzer of Sql Server.
18. Knowledge of creating a Database.
19. Knowledge of creating a Table using SQL Script.
20. Knowledge of create Primary Keys and Foreign Keys.
21. Knowledge of create Indexes, Clustered and Non Clustered Indexes.
22. Knowledge of how to establish Connection from Visual Basic with SQL Server over a Network.
23. Knowledge of creating a Connection Object.
24. Knowledge of creating a Command Object, its Properties and Methods.
25. Knowledge of creating a Recordset Object in a Visual Basic ( visual basic ) application.
26. Knowledge of accessing SQL Server Database using ADO.
27. Knowledge of retreiving Data using visual absic from SQL Server database over a client server Network.
28. Knowledge of create Multiple Command Objects using a Single Connection.
29. Knowledge of specify a OLEDB data provider in Visual Basic ( visual basic ) for a client server application.
30. Knowledge of pass Connection Information to SQL Server over a network using Visual Basic (VB).
31. Knowledge of insert a New Record in SQL server using Visual Basic ( VB ).
32. Knowledge of update and Delete a record using the Execute Method in Visual Basic
33. Knowledge of execute a Stored Procedure using a Command Object.
34. Knowledge of create a Input Parameter and a Output Parameter.
35. Knowledge of create Dynamic Recordsets, Keyset Recordsets, Static Recordsets, Forward Only Recordsets.
36. Knowledge of create Client Side Only Recordsets.
37. Knowledge of assign a lock for Client Side Recordsets.
38. Knowledge of navigate and Bind Controls to a Recordset.
39. Knowledge of retrieve Fields of Recordsets.
40. Knowledge of manually Populate Controls using Visual Basic programming.
41. Knowledge of add a new Record, modify and delete a Record using Visual Basic

For More information on visit :

Develop a An Accounting Software and a Database Application using Visual Basic and SQL Server

Navigating a Recordset and Binding controls to a recordset using Visual Basic

The Recordset object enables your application to access data returned from an SQL query or stored procedure. Using the Recordset object, you can navigate through the records that have been returned. A Recordset has a conceptual pointer, using which Visual Basic points at any one particular record, which is the current record.

A Recordset object has certain properties and methods, which can be used to navigate and manage the recordset. The properties of a Recordset object are listed below:

Properties of Recordset:

BOF: Indicates if the pointer has moved before the first record.

EOF: Indicates if the pointer has moved past the last record.

Abosoluteposition: Sets or returns the obsolute position of the record.

Methods of Recordset object:

MoveFirst: Moves to the first record

MoveLast: Moves to the last record,

MoveNext: Moves to the next record

AddNew: Appends a blank record to the recordset

Update: Saves the modification permanently on the recordset object.

Create the form frmAccountDetails and place the controls as shown below.

User Interface, Name, Caption

Form, frmAccDetails, Account Details
Label, lblcode, Code
Label, lblname, Name
Textbox, mcode
Textbox, mname
Command Button, cmdfirst, First
Command Button, cmdprev, Previous
Command Button, cmdnext, Next
Command Button, cmdlast, Last

Declare the necessary objects as given in the following code of the General section

Option Explicit
Dim CN As Connection
Dim rs As Recordset

In the Form_Load event, initialize the Connection object and the Recordset object. Set the Provider and the ConnectionString properties of the Connection object to required values as done earlier. Set the CursorLocation, LockType, and CursorType properties of the Recordset object to adUseServer, adLockOptimistic, and adOpenDynamic respectively. Now call the Open method of the Recordset object by passing the SQL statement and the name of the Connection object as arguments. Next you need to bind the Recordset object with Text property of the text boxes by initializing the DataSource and the DataField properties. The code for the Form_Load event is given below.

Private Sub Form_Load()
Set CN = New Connection
Set rs = New Recordset
rs.CursorType = adOpenDynamic
rs.CursorLocation = adUseServer
rs.LockType = adLockOptimistic

With CN
.ConnectionString = “Integrated Security=SSPI;Initial Catalog=FinAccounting;Data Source=SYS1”
.Provider = “SQLOLEDB”
.Open
End With
rs.Open “SELECT * FROM AccountsTable”, CN

Set mcode.DataSource = rs
Set mname.DataSource = rs

mcode.DataField = “AccountCode”
mname.DataField = “AccountName”

End Sub

There are two ways to present data to the user. You can manually place the contents of the current record’s fields in appropriate controls, such as a text box, or you can bind the controls to the Recordset object.

Referencing Fields in a Recordset

In order to show a record’s value to the user, you must reference the fields of the recordset. The most efficient technique is to reference the field name directly. You can use the Fields collection, which is a more explicit code. But it is less efficient. The following two examples give the most efficient code and lesser efficient code.

Most efficient

mcode.text=rs!AccountCode
mname.text=rs!AccountName

Explicit, but less efficient

mcode.text=rs Fields(“AccountCode”).value
mname.text=rs Fields(“AccountName”).value

Manually Populating Controls

After a recordset has been created, you can manually reference fields of the recordset in order to present its values to the user. For example, if the Visual Basic form you are using has two text boxes, one to display the account’s code and a second to display the account’s name, you can manually populate the text boxes. This process must occur each time the user navigates to a new record.

Create a procedure FillControls and place the code as given below.

Sub FillControls()
mcode.text=rs!AccountCode
mname.text=rs!AccountName
End Sub

Binding controls to a Recordset

Recent addition to Visual Basic is the ability to bind controls to objects. This is similar to the technique that you use to bind controls to an ActiveX Data control using the Recordset object. You can bind any data-aware control to any field in a recordset. As the user navigates from record to record, the text box automatically shows the record’s values.

Set mcode.DataSource = rs
Set mname.DataSource = rs

mcode.DataField = “AccountCode”
mname.DataField = “AccountName”

Note: Use the binding technique as an alternative to writing a general procedure that manually populates controls on a form.

Assign the following code to the Click event of the command button, cmdFirst.

Private Sub CmdFirst_Click()
rs.MoveFirst
End Sub

Assign the following code to the Click event of the command button, cmdLast

Private Sub CmdLast_Click()
rs.MoveLast
End Sub

Write the following code in the Click event of the command button, cmdNext. The MoveNext method of the Recordset object moves the record pointer to the next record, that is, it makes the next record as the current record. This might cause a problem if the current record is the last record. In that case, the record pointer points beyond the last record. When the record pointer points beyond the last record, then the EOF property of the Recordset object is automatically set to True. To avoid such a situation, just after using the MoveNext method, you should check the value of the EOF property of the Recordset object. If the EOF contains TRUE, then the record pointer should be set to the last record by executing the MoveLast method of the Recordset object as shown below.

Private Sub CmdNext_Click()
rs.MoveNext
‘Check to see if tried to move beyond the last record
If rs.EOF = True Then
‘Moved beyond the last record
‘Set the user back to the last
rs.MoveLast
End If
End Sub

Write the following code in the Click event of the command button, cmdPrev

The MovePrevious method of the Recordset object moves the record pointer to the previous record, that is, it makes the previous record as the current record. This might cause a problem if the current record is the first record. In such a case, the record pointer points before the first record.

When the record pointer points before the first record, then the BOF property of the Recordset object is automatically set to True. To avoid such a situation, just after using the MovePrevious method, you should check the value of the BOF property of the Recordset object. If BOF returns True, then the record pointer should be set to the first record by executing the MoveFirst method of the Recordset object as shown below.

Private Sub CmdPrev_Click()
rs.MovePrevious
‘Check to see if tried to move prior to the first record
If rs.BOF = True Then
‘Moved prior to the first record
‘Set the user back to the first
rs.MoveFirst
End If
End Sub

To develop your own accounting application using visual basic, incorporating the above techniques like navigating a recordset, binding controls to a recordset, moving the pointers in a recordset, read this book titled : develop your own accounting application using visual basic

Creation of RecordSert as a Server-side Recordset and Client-side Recordset in Visual Basic

The following code is for creating a Server-side Recordset.

Option Explicit
Dim CN As Connection
Dim comobj As Command
Dim rs As Recordset

Private Sub Form_Load()
Set CN = New Connection
Set comobj = New Command
Set rs = New Recordset
rs.CursorType = adOpenStatic
rs.CursorLocation = adUseServer

With CN
.ConnectionString = “Integrated Security=SSPI;Initial Catalog=FinAccounting;Data Source=SYS1”
.Provider = “SQLOLEDB”
.Open

Option Explicit
Dim CN As Connection
Dim comobj As Command
Dim rs As Recordset

End With

With comobj
.ActiveConnection = CN
.CommandText = “SELECT * FROM AccountsTable”
End With
Set rs = comobj.Execute
End Sub

If we want to create the recordset on client side, we can use rs.CursorLocation = adUseClient.

Creation of Recordset in Visual Basic

Recordset object is created  in the same way as the Connection object and the Command object.
Type the following code in the frmAccountDetails form:

Private Sub Form_Load()
Set CN = New Connection
Set comobj = New Command
Set rs= new Recordset
With CN
.ConnectionString = “Integrated Security=SSPI;Initial Catalog=FinAccounting;Data Source=SYS1”
.Provider = “SQLOLEDB”
.Open
End With
With comobj
.ActiveConnection = CN
.CommandText = “SELECT * FROM AccountsTable”
End With
Set rs = comobj.Execute
End Sub

Types of Recordsets in Visual Basic

A resultset  returned from a database server as a result of the execution of an SQL statement can be stored in a Recordset object for further manipulation. In other words, a Recordset object can manipulate data returned by a provider. All Recordset objects consists of records (rows) and fields (columns).

There are 4 types of  recordsets

Dynamic recordsets

A dynamic recordset allows viewing  the result of additions, changes and deletion by other users. It allows all types of movements on a Recordset, such as scrolling through the Records in the forward and reverse direction.

Keyset recordsets

The Keyset recordset is same as Dynamic recordset but prevents seeing of changes by the other users.

Static Recordset

Provides the static copy of a set of records  to find data or generate reports. Allows Scrolling through the recordset. Additions, changes or deletions by other users are not visible.

Forward only Recordset

Behaves  identical  to a static Recordset  and allows forward only scrolling. This improves performance in situations where you need to make a single pass through a recordset. We can create any of the above types of recordsets by setting the required value in the Cursor type property of the recordset object after initializing the recordset object

Executing a Stored Procedure having INPUT and OUTPUT parameter using a Command object using Visual Basic

Executing a Stored Procedure  having INPUT and OUTPUT parameter  using a Command object

Stored procedures may contain output parameters and return values. For example, the procedure GetAccountName, can return the name of the Account for an Account Code passed as a parameter. First, create the stored procedure and then write code in the frmAccountDetails form.

Create Procedure GatAccountName
@AccCode varchar(6),
@AccName varchar(30) OUTPUT
As

SELECT  ,@AccName=AccountName FROM AccountsTable WHERE AccountCode =@AccCode
Go

The following code shows how to retrieve the output parameter from a stored procedure.

Option Explicit
Dim CN As Connection
Dim comobj As Command
Dim prmByAcc As Parameter
Dim strAcc As String

Private Sub Form_Load()
Set CN = New Connection
Set comobj = New Command

With CN
.ConnectionString = “Integrated Security=SSPI;Initial Catalog=FinAccounting;Data Source=SYS1”
.Provider = “SQLOLEDB”
.Open
End With

With comobj
.ActiveConnection = CN
.CommandText = “GetAccountName”
.CommandType = adCmdStoredProc
End With

strAcc = Trim(InputBox(“Enter Customer Code.:”))
Set prmByAcc = comobj.CreateParameter(“ACode”, adBSTR, adParamInput, 6, strAcc)
comobj.Parameters.Append prmByAcc

Set prmByAcc = comobj.CreateParameter(“AName”, adBSTR, adParamOutput, 30)
comobj.Parameters.Append prmByAcc
comobj.Execute
MsgBox comobj.Parameters(“AName”)
End Sub

Executing a Stored Procedure having INPUT parameters using a Command object in Visual Basic

Executing a Stored Procedure  having INPUT parameters  using a Command object

The stored procedure returns names when we pass the account category. As you can well understand, the below procedure accepts an input parameter. So, in order to execute the stored procedure, you need to create a Parameter object by using the CreateParameter method of the Command object. The code for the stored procedure is given below. First, create the stored procedure and then write code in the frmDetails form.

Create Procedure GetAccountByCat
@AccCat varchar(1)
As
SELECT  AccountName FROM AccountsTable WHERE AccountCat =@AccCat
Go

Declaration Section

In this section, create a Command object, comobj, a string strCat, and a Parameter object, prmByCat as shown below.

Option Explicit
Dim CN As Connection
Dim comobj As Command
Dim prmByCat As Parameter
Dim strCat As String

Initialize the CN object and the comobj object. Now initialize the ConnectionString properties of the CN object. We need to use the Open method, as given in the following code.

Private Sub Form_Load()
Set CN = New Connection
Set comobj = New Command
With CN
.ConnectionString = “Integrated Security=SSPI;Initial Catalog=FinAccounting;Data Source=SYS1”
.Provider = “SQLOLEDB”
.Open
End With

Initialize the ActiveConnection, CommandText, and CommandType properties of the comobj object as shown in the following code.

With comobj
.ActiveConnection = CN
.CommandText = “GetAccountByCat”
.CommandType = adCmdStoredProc
End With

The procedure GetAccountByCat accepts an input parameter, which needs to be accepted from the user. So, the next step is accepting it from the user and storing it in the String type variable, strCat.

strCat = Trim(InputBox(“Enter Customer Category.:”))

The next step is to initialize the Parameter object, prmByCat, declared earlier. We need to call the CreateParameter method of the comobj object.

Set parameter=Command.CreateParameter (Name, Type, Direction, Size, Value), Where parameter is the Parameter object returned by the CreateParameter method and Command is the Command object. The code for calling the CreateParameter function is as follows

Set prmByCat = comobj.CreateParameter(“ACat”, adBSTR, adParamInput)

comobj.Parameters.Append prmByCat

The Parameter object has a property called Value. You need to assign the value entered by the user to this Value property. The value entered by the user is stored in the string type variable, strCat.

prmByCat.Value = strCat

The last step is to call the Execute method of the Command object.

comobj.Execute

End Sub