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
- Create folder structure for our application`s files
- Create “SmartMail” project
- 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:
- Classes
- Controls
- Data
- Forms
- Modules
- Resources
- Reports
- 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.
- 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
- 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:
- Users
- AppSettings
- Clients
- 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.
[…] our previous post on this series on How to Create an e-Statement Application, we created the Smart Mail database in Microsoft SQL Server and its tables. Today we are going to create Modules,Menus and ODBC Data […]