How to develop a commercial e-Statement application in vb6 step by step – Part 3 -Create E-Statement Project-System database and Tables

Building a Commercial e-Statement Application in VB 6 – Create E-Statement Project and Database

Welcome to this 3rd post of building a commercial E-Statement application. If this is your first time on this website, i recommend you take a look at the E-Statement Basics  and Setting Up You Development Environment before continuing with this section. If your are a regular visitor I am glad you are back. Please register so that you do not miss any of the articles and updates on this series.

ATTENTION: You need to have both Microsoft SQL  Server & Microsoft Visual Basic Installed!

If you have your development environment set, lets continue building this E-statement Application in VB 6.

In Summary, we are going to do the following in this article

1. Create folder structure for our application`s files
2. Create “SmartMail” project
3. Create a database and tables in SQL Server Management Studio

Create folder structure for the E-Statement Application

I like to have all my project files well structured and well organized, that way I can easily find any file I want and it will avoid us from missing very critical files when we are ready to package and deploy our application. This is not a must but from my little experience I highly recommend you do this.

• Create a main folder with your intended project name. For my case I will call the folder “SmartMail“.
• Go inside that folder and create the following sub folders:

1. Classes
2. Controls
3. Data
4. Forms
5. Modules
6. Resources
7. Reports
8. Graphics

With those folders in place this is how it will look like

Create “SmartMail” Project

So here we start doing the real staff, what we have been getting ready to do for the last two posts.

1. Open Microsoft Visual Studio 6.0 IDE from your applications, you might need to open the IDE by ‘Run as Administrator’ in Windows 7 and Windows 8 because of the UAC and to avoid any access error to the system resources
2. The following window will appear. Choose Standard EXE and click on Open

Microsoft Visual Basic 6.0 is going to create a Project1 and a Form1 automatically as shown below.

Of course no one wants to call their E-Statement Application “Project1” and Form as Form1.

So rename your Project1 to SmartMail and Form1 to frmAbout. Also change the Caption of the Form1 to About.

Your Project should look like below:

Now lets go to File > Save Project. Visual Studio will first as you to save the Form which we said we are going to name it as frmAbout.

ATTENTION: Do not just save your Forms and Project anywhere,go to the SmartMail main folder and save the frmAbout Form inside the Forms folder. See below:

Don`t worry about the other forms you are seeing, we are going to get there soon.

Again Save the SmartMail Project on the main SmartMail folder.

This same naming and saving guide should be used when saving, Modules, Classes, Controls and any other kind of files. Be extra keen when saving your files

So here is a look inside my SmartMail folder so far

Create a database in Microsoft SQL Server

To create the database login to your Microsoft SQL Management Studio using  the ‘sa’ username and your password.

The SQL Server Management Login screen will appear , provide your login credentials and click on connect

Right click on the Databases and select New Database

Enter the database name and leave the other settings the way they are and  click ok

The database will be created, expand the Databases by clicking on the [+] sign

Now that we have our database, we need to create some tables.  We will create 4 tables:

1. Users
2. AppSettings
3. Clients
4. Company

Users Table

The Users table will hold the usernames and password for each system user.To create a table go to your SmartMail database and expand using the [+] sign. Right click on the Tables and choose New Table.

Create the table columns as show below.

Make the ID column the Primary Key of your table. And also auto increment by one

The ‘CreatedDate’ column will take a Default Value of the current data and time when the user was created. To do this click on the ‘CreatedData’ column and go to the Default Value or Binding and type (getdate()) as shown below.

The above section I was showing how to create a table for those who have never used SQL Server before. For the rest of the tables I will just show the columns to be created or I will give you a query to run which will create the tables for you.

Create the Users Table using a query

USE [SmartMail]
GO
/****** Object:  Table [dbo].[Users]    Script Date: 04/26/2015 11:50:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Users](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Username] [varchar](250) NOT NULL,
    [Name] [varchar](250) NOT NULL,
    [Password] [varchar](250) NOT NULL,
    [IsAdmin] [bit] NOT NULL,
    [IsDisabled] [bit] NOT NULL,
    [CreatedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Users] ADD  CONSTRAINT [DF_Users_IsAdmin]  DEFAULT ((0)) FOR [IsAdmin]
GO
ALTER TABLE [dbo].[Users] ADD  CONSTRAINT [DF_Users_IsDisabled]  DEFAULT ((0)) FOR [IsDisabled]
GO
ALTER TABLE [dbo].[Users] ADD  CONSTRAINT [DF_Users_CreatedDate]  DEFAULT (getdate()) FOR [CreatedDate]
GO

To run the Query

Right click on the SmartMail database and choose New Query

Copy the query above and paste on the SQL Query Editor and click on the Execute button

Create the AppSettings Table using a query

The AppSettings table is going to hold all of our application`s settings. To easily create this table copy and paste the below query to your SQL Query Editor and execute

USE [SmartMail]
GO
/****** Object:  Table [dbo].[AppSettings]    Script Date: 04/26/2015 12:53:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AppSettings](
	[SettingID] [int] IDENTITY(1,1) NOT NULL,
	[SettingName] [varchar](50) NULL,
	[SettingValue] [varchar](50) NULL,
 CONSTRAINT [PK_AppSettings] PRIMARY KEY CLUSTERED 
(
	[SettingID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

Create the Clients Table using a query

The Clients table is going to hold information for our clients. We will need this information such as Client name, Client Account Number, Email Address in order for us to be able to the them the correct E-statement to their mail address

Create the Company Table using a query

The Company table holds basic information about the institution  (e.g Bank`s name and address ) which is using the E-Statement application. To create this table copy and paste the below query and execute

USE [SmartMail]
GO
/****** Object:  Table [dbo].[Company]    Script Date: 04/26/2015 14:39:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Company](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](250) NOT NULL,
	[Address] [varchar](150) NULL,
	[ContactInfo] [varchar](150) NULL,
 CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

I will end here today, I hope you enjoyed and have your application database ready. next week we will create a connection to
the database and create the Login system.
I always love to hear from my reader, so if you have any question please email me anytime.
Kindly Like and Share the posts with your friends using the buttons below.