Question & Answer: Needing help with the coding on my Visual Basic assignment……

Needing help with the coding on my Visual Basic assignment.

Programming Assignment #7

The database Microland.accdb is maintained by the Microland Computer Warehouse, a mail-order computer-supply company. The illustrations below show data in the Customers table and the Orders Table in the database. The table identifies each customer by an ID number and gives, in addition to the name and address, the total amount of purchases during the current year. The Order table provides the customer id and the item ordered.

The database that contains the table for this assignment is contained within your Instructions folder and should be placed in your project’s bindebug folder.

**To receive any points for this assignment you must do the following:

For this assignment you will open the attached Microsoft Access file and add your name and your instructors name in the customers table using the following information:

CustID Name Street City AmtPurchase

13 Your Name Your Street Your City 100.50

14 Your teachers Name Your Street Your City 95.25

You must also add the following lines to the Orders table

CustID itemID Quantity

13 HW921 3

14 SW109 2

Write a Visual Basic program that will:

⦁ On the form_load event, display in a listbox the customers’ names that are listed in the Customers table.

⦁ When the user selects a customer from the listbox,the customer’s name, street, city, and purchases should be displayed in the textboxes to the right of the buttons as illustrated below.

⦁ The customer and the order table are joined and the orders for the customer are displayed in the atagrid. The datagrid should have headers for the data as shown below.

⦁ The purchases should be formatted to be currency

⦁ A Display Total Purchases should be included to sum up all of the total purchases for all names listed in the lstOutput. This information should be displayed in a message box.

⦁ A Clear button should be included to clear all of the textboxes and datagrid. [Note that it should not clear the listbox containing the customers’ names.]

⦁ A Close button should be included to exit the application.

As in all of your assignments, make sure that you have used appropriate programming techniques (i.e. naming of controls and variables, form has a title, tab order of controls is appropriate, textboxes used for display purposes are not writeable, internal documentation/comments are clear, formatting of any currency fields, etc.)

Design your form based on the diagram below.

Microland Michael Smith Brittany Jones Warren Pease John Smith Mary Johnson Barbara Todd Carl Thomas Ben Rob Joan Garcia Albert Hu Sarah Davis Ashley Ross Michael Smith temlD PL208 HW913 SW101 quantty Address: 2 ParkSt Dallas, TX 75201 $234.50 Close Fom Clear Information Display Total Purchases

Microland Michael Smith Brittany Jones Warren Pease John Smith Mary Johnson Barbara Todd Carl Thomas Ben Rob Joan Garcia Albert Hu Sarah Davis Ashley Ross Michael Smith temlD PL208 HW913 SW101 quantty Address: 2 ParkSt Dallas, TX 75201 $234.50 Close Fom Clear Information Display Total Purchases

Expert Answer

 

‘VB Code for the project for the given window

——————————————————————-

Imports System.Data.OleDb
Public Class Form1

Dim con As New OleDbConnection(“Provider=microsoft.Jet.oledb.4.0DataSource=D:accdb.mdb;”)

Dim cmd As New OleDbCommand
Dim rdr As OleDbDataReader

Private Sub listCustomers_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles listCustomers.SelectedIndexChanged

‘Populate text fields and datagridview
Dim custid As Integer
Dim ds As New DataSet
Dim da As New OleDbDataAdapter
custid = listCustomers.SelectedValue

con.Open()

cmd.Connection = con
cmd.CommandText = “SELECT CustName,Street,City,AmtPurchase FROM Customers”
rdr = cmd.ExecuteReader

If rdr.HasRows Then
Do While rdr.Read()
txtName.Text = rdr(0)
txtAddr.Text = rdr(1)
txtCity.Text = rdr(2)
txtPurchase.Text = “$” & Math.Round(rdr(3), 2)
Loop
End If
rdr.Close()

cmd.CommandText = “SELECT ItemID,Quantity FROM Orders where CustID=” & custid

da.SelectCommand = cmd
da.Fill(ds, “Orders”)
dtgOrders.DataSource = ds.Tables(“Orders”)

con.Close()

End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
‘To display customers

LoadCustomers()
End Sub

Private Sub LoadCustomers()

listCustomers.Items.Clear()
con.Open()

cmd.Connection = con
cmd.CommandText = “SELECT CustID,CustName FROM Customers”
rdr = cmd.ExecuteReader

If rdr.HasRows Then
Do While rdr.Read()
listCustomers.Items.Add(New ListItem(rdr(1),rdr(0))
Loop
End If
rdr.Close()
con.Close()

End Sub

Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click

‘To exit from application
Application.Exit()

End Sub

Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
txtName.Text = “”
txtAddr.Text = “”
txtCity.Text = “”
txtPurchase.Text = “”
End Sub

Private Sub btnTotalPurchase_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTotalPurchase.Click
Dim Total As Long = 0

con.Open()

cmd.Connection = con
cmd.CommandText = “SELECT Sum(AmtPurchase) FROM Customers”
Total = cmd.ExecuteScalar
con.Close()

MsgBox(“Total Amount purchased : $” & Math.Round(Total, 2))

End Sub

End Class

Still stressed from student homework?
Get quality assistance from academic writers!