Visual basic

I will attach the file for the tutorial it’s divided in two parts.

Use the attached handout to complete Parts 1-4 of the Catalog Manager project. When you are done, save your project folder as a .zip file and upload here. I want you to do in two days.

Complete Part 5 and Part 6 of the Catalog Manager assignment from Week 1. I want you to do it in four days.

you will need to finish the first part an turn in zip file and complete the other in the same project in four days.

Desktop Application: Catalog Manager
In this assignment you will:
⇒
⇒
⇒
⇒
⇒
⇒
Learn to connect to the course database ChairsDB and review its contents
Create an application for managing the CATALOG table in the database
Learn to use System.Data and System.Data.SQLClient libraries to create database connections
Work with File Dialog controls
Learn to dynamically add and edit data in the remote database
Learn to represent graphics in database format
Goreva INFS 3121/6121
1
Creating the Catalog Manager
Part I. Creating the Menu Form
In this part we will review the basic controls and commands of VB and review the VB.NET environment. You will:
⇒ set up .NET environment
⇒ define project properties, such as location and name
⇒ create new projects
⇒ compile and run your projects
1. Create a new Windows application (File  New Project). Make sure that the language is Visual Basic and that
Windows is selected from the Visual Basic list as the application type. Select Windows Forms Application in the
middle section. Give your project a meaningful name (I named mine CatalogManager) and choose a good
location. Note: you do not have to save it on the network drive because it is a local application and it will not be
hosted on the server.
2. The new form you just created will contain information about your project (similar to a Help or a Q/A page). We
will change its name and caption to “About” and populate it with the necessary functionality.
Change the following properties of the form:
A. Name is a unique identifier of a form (and any other object). It consists of letters
and numbers only and should not have spaces and other symbols. In other words, a name
is a unique ID, by which VB refers to the object. Change the name of your form to
frmAbout (see the picture).
B. File Name is the name of the file that will be created for your form. Make sure
that VB renamed your file name to frmAbout.vb.
C. Text is the text which is displayed on the title bar of the form. Create a
meaningful caption text for your form, e.g. About Catalog Manager.
You do not have to populate the About form; if you have time, you can add the description of your project on this
form when you are done with the rest of the assignment.
Goreva INFS 3121/6121
2
Creating the Catalog Manager
Part 2: Creating Project Interface
The goal of this part is to create the main (menu) form of the desktop application.
⇒ Add main menu and a toolbar to the form frmMenu
⇒ Learn the commands to open a form
In this step you will add a new form with the menu that will allow you to access the rest of the pages in your Catalog
project. The form will contain the other pages; therefore, we will make it an MDI container.
1. Add a new Windows form to your project. Name the form frmMenu. Change IsMdiContainer property of the new
form to true. You may also change the icon to make your form look more professional. Next, we will add two menus
to the form: a Menu Strip (includes menu items in the text form) and a Tool Strip (includes the icons and the names
of the tools).
a. Adding a Menu Strip. You can find the Menu Strip control in the Menus and Toolbars section or the Toolbox.
Create the following menus and submenus:
File (name this menu menuFile)
Exit (name = menuExit)
Change Catalog (name = menuChange)
New Product (name = menuAdd)
Edit Products (name = menuEdit)
Reports (name = menuReports)
Products (name = menuProducts)
Help (name = menuHelp)
About (name = menuAbout)
b. Adding a Tool Strip. Add a new folder to your project; this folder will contain the images, so
we will name it Img. Copy the images Add.png, Edit.png, and Help.png to this folder. Make
sure you copy them physically, on the hard drive, and then add as existing items in .NET
Solution Explorer.
Add a Tool Strip control to your form. You can find it on the Menus and Toolbars list of the
Toolbox. Add the following items to your tool strip (note: to add the images, you will need to click the Import
Button on the Select Resource menu and choose all three images as the resource):
(Name)
tbsAdd
tbsEdit
tbsHelp
Text
Add
Edit
Help
DisplayStype
ImageAndText
ImageAndText
ImageAndText
Image
Add.png
Edit.png
Help.png
Your page will look similar to the figure on the left.
Make sure that the Menu form is the starting form in your
project: go to the top menu Project  CatalogManager
Properties and change the startup form from frm About to
Goreva INFS 3121/6121
3
Creating the Catalog Manager
frmMenu. You can now run your project and make sure the menu form opens first.
2. Write the code to open the form “About” from the main form
On the main form, double click the item About (menuAbout) of the menu strip. First, we will declare a new instance
of the form frmAbout, make sure that the new instance displays within the MDI container, which is the main form,
and finally, show the form. Write the code to open the form About:
Private Sub menuAbout_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
Handles menuAbout.Click
Dim About As New frmAbout
About.MdiParent = frmMenu.ActiveForm
About.Show()
End Sub
Rebuild and run your project. Check if the form About opens when you click Help  About on the toolbar or the
menu:
3. Add two blank Windows forms to your project. Name them frmAdd and frmEdit. Add the functionality to open
these forms from the menu strip and the toolbar strip (you will need to re-write and slightly modify the code for
opening the Add form). Add the functionality to close the application when user clicks File  Exit (hint: use the
command Application.Exit).
Goreva INFS 3121/6121
4
Creating the Catalog Manager
Part 3: Adding Basic Functionality
The goal of this part is to prepare the entire project interface. You will do the following:
⇒ Populate the forms “Add Product” and “Edit Product” with controls
⇒ Review how to create procedures and call them from any place in the code
⇒ Link all project forms, add code to the buttons on the main toolbar and the main menu
In the current version of the project, in order to open a form (e.g. frmAbout) from
the main menu and the toolbar, you need to repeat the same code twice for the
menu item _click event and the toolbar _click event. To avoid this, we will create a
procedure for opening each form and call this procedure each time we need it.
We will place this procedure in a module – a .vb file were we will place the code
that can be accesses from anywhere in the project.
1. Add a module to your application:
Right click on your project name in the solution explorer and select Add 
Module.
Name your module mdlMain. We will use it to store the code that is needed by
multiple components of the project.
2. Add a new procedure to the module:
A procedure is a code which can be reused many times. The general syntax of a public (accessible from all locations)
procedure in VB.NET is as follows:
Public Sub ProcedureName (Arguments)
Procedure Code
Procedure Code
Procedure Code
End Sub
We will add three procedures: OpenAddForm, OpenEditForm, and OpenAboutForm. Open your module and add each
procedure:
Public Sub OpenAddForm()
End Sub
Public Sub OpenEditForm()
End Sub
Public Sub OpenAboutForm()
End Sub
Notice: when you hit Enter after each of the first lines, VB adds the ending line End Sub for you.
Goreva INFS 3121/6121
5
Creating the Catalog Manager
Next, we will cut the code from the menu events and paste it in the module. Find the place in the code that opens
the form About from the main form frmMenu (highlighted code):
Private Sub AboutToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles AboutToolStripMenuItem.Click
Dim About As New frmAbout
About.MdiParent = frmMenu.ActiveForm
About.Show()
End Sub
Cut this code and paste it between the first and the last lines of the procedure OpenAboutForm (module):
Public Sub OpenAboutForm()
Dim About As New frmAbout
About.MdiParent = frmMenu.ActiveForm
About.Show()
End Sub
Next, we will call this procedure from the menu strip and from the tool strip. Find the button that calls the form
About
a) on the menu strip
and b) on the tool strip:
Double click each of the options to get to the code and type the line to call your procedure:
Private Sub menuAbout_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
Handles menuAbout.Click
OpenAboutForm()
End Sub
Private Sub tsMain_ItemClicked(ByVal sender As System.Object, ByVal e As
System.Windows.Forms.ToolStripItemClickedEventArgs) Handles tsMain.ItemClicked
OpenAboutForm()
End Sub
Repeat the same operation for the remaining forms frmAdd and frmEdit.
3. To populate the forms frmAdd and frmEdit…
Add the following controls from the Common Controls toolbar to the form frmAdd (as in the picture below):
Form
Text Box
Text Box
Goreva INFS 3121/6121
Name
frmAdd
txtProductType
txtProductName
Text
Add New Item
–
6
Other Properties
Size: 650, 400
Creating the Catalog Manager
Text Box
Text Box
Picture Box
Button
Button
Button
txtProductPrice
txtProductDescription
imgPicture
btnLoadPicture
btnAdd
btnClose
–
Multiline = true, Scrollbars = Vertical
Size Mode = StretchImage
Load Picture
Add Item to Catalog
Close without Saving
Add labels next to the text boxes and the image as shown in the figure. The labels do not have any functionality other
than clarify the contents of the text boxes to the user.
Select all controls on the Add form, copy them and paste to the Edit form. On the top of the Edit form add three
more controls:
Form
Combo Box
Button
Text Box
Name
frmEdit
cbxProduct
btnLoad
txtProductID
Text
Edit Existing Items
Load
–
Other Properties
Size: 650, 400
Enabled = false
Add labels next to the new text boxes. This is what your form frmEdit will look like:
Goreva INFS 3121/6121
7
Creating the Catalog Manager
Part 4: Working with OFD Controls and Files
The goal of this part is to add functionality to the forms frmAdd and frmEdit to populate the image boxes with the
images from the files.
Open the form frmAdd and select the OpenFileDialog control from the Dialogs toolbar. Double click on the control to
place it on the form and change its name to ofdPicture. Next, we will add the code to open the dialog boxes and
display pictures in the image boxes on the forms frmAdd and frmEdit when user clicks the button “Load Picture”.
1. To store the image data, we will need
a) The variable TempImage to temporarily store the images in the buffer and
b) A string variable strFileName to record the names of the files where we will put the images.
Open the form frmAdd and add the following variable declarations on top just after the form class declaration Public
Class frmAdd:
Public Class frmAdd
Dim TempImage As Image
Dim strFileName As String = “”
2. Next, we will retrieve an image from the file and place it in the picture box, following these steps:
a) Show the file dialog
b) Record the path to the image (the one that the user selects in the open file dialog) into the variable
strFileName
c) Check if the file path is not empty
d) Convert the file strFileName into a bitmap (image) type and record this image in the variable TempImage
e) Put the image stored in TempImage in the picture box on the form
Goreva INFS 3121/6121
8
Creating the Catalog Manager
Now we will put this to code. Open the form frmAdd and add the code to the button btnLoadPicture_Click event:
Private Sub btnLoadPicture_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnLoadPicture.Click
ofdPicture.ShowDialog()
Try
strFileName = ofdPicture.FileName
tempImage = New Bitmap(strFileName)
imgPicture.Image = tempImage
Catch
End Try
End Sub
We will use the Try…Catch method to catch an error when the file selected in ofdPicture cannot be converted to an
image (e.g. user selects an Excel file by mistake). This method will not let the program shut down; instead, it will only
display an error message.
Rebuild and run your application. Click the button btnLoadPicture on the form frmAdd and check if the Open File
Dialog procedure works by adding a picture to your picture box:
Repeat the same code to add functionality to Load Picture button on the form frmEdit.
Goreva INFS 3121/6121
9
Creating the Catalog Manager
Part 5: Adding New Records to the Database
In this part you will create the functionality of the application that adds a new record to the database. It includes:
⇒ Writing code for adding text/numeric fields to the database
⇒ Working with the image files and with file streams
⇒ Learn to program arrays using buffer arrays as an example
1. This section is for information only; you do not need to write any code.
Open your database and review the contents of the Tables and Programmability
folders. Tables should include CATALOG, and Programmability should include two
stored procedures – SaveProductCatalog and EditProductCatalog. The CATALOG
table includes all product information, and the two procedures allow you to add
items to the catalog and change items that are already in the table.
Here is the code for SaveProductCatalog:
CREATE procedure [dbo].[SaveProductCatalog](
@Type varchar(20),
@Name varchar(50),
@Price money,
@Description varchar(600),
@Picture image
)
AS
insert into CATALOG
values
(@Type, @Name, @Price, @Description, @Picture )
GO
Notice that the procedure takes 5 parameters passed from the outside, and adds a new product with the field values
equal to the passed parameters. The CATALOG table includes 6 fields, but we do not pass the Product ID because it is
an auto-incremented field and its values are set up by the database.
The code of the procedure EditProductCatalog is slightly different:
CREATE procedure [dbo].[EditProductCatalog](
@SearchID int,
@Type varchar(20),
@Name varchar(50),
@Price money,
@Description varchar(600),
@Picture image
)
AS
UPDATE CATALOG
SET
PROD_TYPE = @Type,
PROD_NAME = @Name,
PROD_PRICE = @Price,
PROD_DESC = @Description,
PROD_PIC = @Picture
WHERE PROD_ID = @SearchID
GO
Goreva INFS 3121/6121
10
Creating the Catalog Manager
This time the ID of the product (I named it @SearchID) needs to be passed, because the database has to know which
product to change. The remaining five parameters are the new values that need to be recorded in the product fields.
2. Open the code for the form frmAdd and import two namespaces on the top: System.Data and
System.Data.SQLClient:
You will need these namespaces to connect to the database. You will also need a connection string that will store the
parameters of the database connection. This string should be accessible from all places in your project; so we will add
it to the module mdlMain that you created earlier:
Notice: you will need to modify the connection string to connect to your database. In the connection line
“Server=infsweb01.rmu.edu;uid=goreva;pwd=@g123456;Database=ChairsDB” replace the user id and the password
with your own.
Next, switch to the code for frmAdd write the code that adds a new record to the database. This code works when
the user clicks the “Add” button; therefore, you need to add it under btnAdd_Click event (hint: double click the
button in the Design view, and the code will be generated for you). Type the Try/Catch structure inside the event:
We will need this code to catch the invalid records and give user a message explaining why they cannot be added to
the database. The rest of the code (Steps 1-2) will go between the first two lines Try … Catch.
Step 1: Add the declarations of the SQL Connection variable and the SQL Command variable that will provide
connection to the database and run the stored procedure as an SQL Command:
Goreva INFS 3121/6121
11
Creating the Catalog Manager
Step 2: We will need a block of code to transform the image in the picture box and save it into file streams:
Step 3: Add the code to pass one-by-one the values of parameters (we will retrieve them from the controls on the
form) to the SQL procedure. Remember, we need to pass 5 parameters: @Type, @Name, @Price, @Description, and
@Picture.
In order to pass a parameter to the SQL procedure SaveProductCatalog we need to write four commands.
• First, declare a variable of the SqlParameter type and associate it with the matching parameter name and
type in the stored procedure (e.g. declare a variable named parType and associate it with a varchar
parameter named @Type in the stored procedure).
• Second, set the direction of the parameter as Input, because we intend to record a value in the SQL
parameter.
• Third, assign the value to the parameter, e.g. parType takes its value from the text box txtProductType.
• Fourth, add the new parameter to the parameters list of the SQL command:
Dim parType As New SqlParameter(“@Type”, SqlDbType.VarChar)
parType.Direction = ParameterDirection.Input
parType.Value = txtProductType.Text
Cmd.Parameters.Add(parType)
Similarly to parType, add the remaining four parameters to the SQL procedure:
Goreva INFS 3121/6121
12
Creating the Catalog Manager
After you added all parameters, execute the SQL command and close the connection. It makes sense to display a
message that the new product was added to the database:
Cmd.ExecuteNonQuery() ‘Execute command
Cnn.Close() ‘Close connection
‘Display a message that a new record has been successfully added to the DB
MessageBox.Show(“The product has been added to the Catalog Table in the SQL Server Database”)
Step 4: Just before the end of the Try clause and the beginning of Catch, add the code to clear the form after an item
has been added to the database:
Goreva INFS 3121/6121
13
Creating the Catalog Manager
Now you can start adding items to your table in the catalog.
Compile and run; make sure you test your program on one or
two catalog items before you start adding the rest.
Part 6: Editing Products in the Database
In this assignment you will:
⇒ Add a procedure to display information from the database on the form
⇒ Add a procedure for making changes to the database through the form
Add the links to the Data namespaces on the top of the form frmEdit (same as you did for frmAdd):
Imports System.Data.SqlClient
Imports System.IO
1. Write the code to load product names in the combo box on the form frmEdit.
Place the following code under the frmEdit_Load event. This code will retrieve products names from the database
and place them in the combo box so the user can select a product they would like to change.
Make sure you read the comments over the lines of code; they might give you a better understanding of what each
line stands for. Rebuild and run the code. When you open the form frmEdit, you should see that the combo box
contains the names of the products in the database.
Goreva INFS 3121/6121
14
Creating the Catalog Manager
2. Write a procedure to temporarily save pictures into the files. This function will convert the binary data (and this is
how we store the images in the database) into the image .bmp files and save them in the current project directory.
We will need them to be able to a) display the images in the picture boxes and b) save them back in the database.
3. Write another procedure to display the data from the database. We will call this procedure from the button
btnLoad_Click event, which means that it will load a product data each time the user selects a product in the
dropdown box and clicks the button Load. Start by the declarations of the variables:
Goreva INFS 3121/6121
15
Creating the Catalog Manager
Next, open the database connection and read the information about the product with the given ID (tempID) that was
passed to the procedure. Display the product information in the corresponding text boxes on the form.
4. Find the btnLoad_Click event and write the code to call the procedure DisplayData, created in the previous step.
This code will pass the ID of the selected product to the procedure DisplayData and call this procedure.
Private Sub btnLoad_Click(sender As Object, e As EventArgs) Handles btnLoad.Click
btnSave.Enabled = True ‘We load the product, so now it is allowed to save it
Dim tempID As Integer = cbxProduct.SelectedValue ‘Save the ID of the product
imgPicture.Image = Nothing ‘Just in case, clear the picture boxes
Call DisplayData(tempID) ‘Display product data based on its ID (tempID)
End Sub
5. To save changes in the database:
You already have the code to add a new product to your database (frmAdd). Now we will slightly modify it to save
the changes to an existing product in the database. Copy the code which adds a new product from the button
btnAdd_Click event (the form frmAdd) to the button btnSave_Click event on the form frmEdit. Change the code to
the following (you already know the meaning of each line):
Goreva INFS 3121/6121
16
Creating the Catalog Manager
Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
Try
‘Copy the code from the Try/Catch method from the Add form here.
Catch ex As Exception
MsgBox(ex.Message.ToString)
End Try
End Sub
Make the following changes to the code to adapt it to the Edit page:
1. Dim Cmd As New SqlCommand(“EditProductCatalog”, Cnn) – replace SaveProductCatalog with
EditProductCatalog
2. Add another parameter parSearchID. Remember that the SQL command EditProductCatalog changes a
product with a given ID, and this ID is passed with the parameter @SearchID.
Build and run your application, make sure that it works correctly:
Goreva INFS 3121/6121
17
Creating the Catalog Manager
?