Practice Numer 4

#1 - Adding items to a list box with a loop

 Public Class Form1
  
   Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
  
     Dim amount As Integer = txtValue.Text
  
     For n As Integer = 1 To amount
  
       ListBox1.Items.Add(n & "items")
  
     Next
  
   End Sub
  
 End Class  

#2 - How many years will it take to save up to a certain amount of money

 Public Class Form1
  
   Private Sub btnYears_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnYears.Click
  
     Dim payment As Integer = txtPayment.Text
  
     Dim counter As Integer = 1
  
     Dim amount As Integer
  
     Dim goal As Integer = 10000
  
     Dim years As Integer
  
     Do While amount < goal
  
       counter += 1
  
       amount = payment * counter
  
     Loop
  
     years = counter / 12
  
     MessageBox.Show(years & " Years")
  
   End Sub
  
 End Class  

#3 - Create a Times table using a loop within a loop

 Public Class Form1
  
   Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
  
     Dim y As Integer
  
     Dim x As Integer
  
     For x = 1 To 10
  
       For y = 1 To 10
  
         Dim btn As New Button
  
         btn.Location = New Point(35 * x, 35 * y)
  
         btn.Height = 35
  
         btn.Width = 35
  
         Me.Controls.Add(btn)
  
         btn.Text = (x * y)
  
       Next
  
     Next
  
   End Sub
  
 End Class  

Left/Right Outer Joins

Below are examples of left and right outer joins.

 use Yummys
  
 select *
  
 from Brand B
  
 left outer join xrefFlavorBrand xrefFB
  
 on B.BrandID = xrefFB.BrandID
  
 --Right Outer Join
  
 select *
  
 from sales S
  
 right outer join xrefDistSales xrefDS
  
 on S.SalesID = xrefDS.SalesID  

Loops

Below are some examples of code for different loops used in VB.Net

Code for a do(while first) loop

 Public Class Form1
  
   Private Sub btnLoop_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoop.Click
  
     Dim x As Integer = 5
  
     Do While x < 10
  
       x += 1
  
     Loop
  
   End Sub
  
 End Class  

Code for a do(while last) loop

 Public Class Form1
  
   Private Sub btnLoop_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoop.Click
  
     Dim x As Integer = 5
  
     Do
  
       x += 1
  
       MsgBox(x)
  
     Loop While (x < 10)
  
   End Sub
  
 End Class  

Code for a for loop

 Public Class Form1
  
   Private Sub btnLoop_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoop.Click
  
     Dim x As Integer = 1
  
     Dim y As Integer = 5
  
     For n As Integer = x To y
  
       MsgBox(n)
  
     Next
  
   End Sub
  
 End Class  

Code For a for each loop

 Public Class Form1
  
   Private Sub btnLoop_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoop.Click
  
     Dim name As String = "Ben"
  
     Dim character As Char
  
     For Each character In name
  
       MsgBox(character)
  
     Next
  
   End Sub
  
 End Class  

Inner Join Practice 2

 --drop table Brand
  
 --drop table Flavor
  
 --drop table xrefFlavorBrand
  
 --drop table sales
  
 --drop table distributors
  
 --drop table xrefDistFlavorBrand
  
 --drop table xrefDistSales
  
 use Yummys
  
 go
  
 create table Brand
  
 (
  
    BrandID int identity(1,1) primary key
  
    ,BrandName varchar(50)
  
    ,BrandLocation varchar(50)
  
 )
  
 insert into Brand (BrandName, BrandLocation) values ('BlueBunny', 'San Francisco')
  
 insert into Brand (BrandName, BrandLocation) values ('PurpleMonkey', 'Detroit')
  
 insert into Brand (BrandName, BrandLocation) values ('PinkHippo', 'Malibu')
  
 insert into Brand (BrandName, BrandLocation) values ('GreenRabbit', 'Tucson')
  
 insert into Brand (BrandName, BrandLocation) values ('RainbowLamb', 'Minneapolis')
  
 select * 
  
 from Brand
  
 create table flavor
  
    (
  
    Flavor_ID int identity (1,1) PRIMARY KEY,
  
    flavor_name varchar (40),
  
    descraption varchar (100),
  
    quantity int
  
    )
  
    insert into flavor
  
    (flavor_name, descraption, quantity)
  
    values
  
    ('choclate', 'classic choclate', 2)
  
    insert into flavor
  
    (flavor_name, descraption, quantity)
  
    values
  
    ('Vanilla', 'Plain Vanilla', 4)
  
    insert into flavor
  
    (flavor_name, descraption, quantity)
  
    Values
  
    ('Neopolatin', '1/3 Choclate, 1/3 Vanilla. 1/3 Strawberry', 1)
  
    insert into flavor
  
    (flavor_name, descraption, quantity)
  
    Values
  
    ('Coffee', 'Fresh Cround Coffee Flavor', 0)
  
    insert into flavor
  
    (flavor_name, descraption, quantity)
  
    Values
  
    ('Sardine', 'Self-Explanatory', 7)
  
 select *
  
 from flavor
  
 create table xrefFlavorBrand
  
 (
  
    xrefID int identity (1,1) primary key
  
    , BrandID int
  
    , FlavorID int
  
 )
  
 Insert into xrefFlavorBrand (BrandID, FlavorID) values (1,1)
  
 Insert into xrefFlavorBrand (BrandID, FlavorID) values (2,2)
  
 Insert into xrefFlavorBrand (BrandID, FlavorID) values (3,3)
  
 Insert into xrefFlavorBrand (BrandID, FlavorID) values (4,4)
  
 insert into xrefFlavorBrand (BrandID, FlavorID) values (5,5)
  
 create table sales
  
 (
  
    SalesID int identity (1,1) primary key
  
    , xrefID int
  
    , ScoopNo int
  
    , Price decimal(4,2)
  
    , SalesDate int
  
    )
  
 insert into sales
  
    (xrefID, ScoopNo, Price, SalesDate) values (1, 2, 2.00, 1/2/10)
  
 insert into sales 
  
    (xrefID, ScoopNo, Price, SalesDate) values (2, 3, 3.00, 1/3/10)
  
 insert into sales
  
    (xrefID, ScoopNo, Price, SalesDate) values (3, 2, 2.00, 1/3/10)
  
 insert into sales
  
    (xrefID, ScoopNo, Price, SalesDate) values (2,3,3.00, 1/3/10)
  
 insert into sales
  
    (xrefID, ScoopNo, Price, SalesDate) values (4,2,2.00, 1/4/10)
  
 insert into sales
  
    (xrefID, ScoopNo, Price, SalesDate) values (5, 4, 4.00, 1/4/10)
  
 insert into sales
  
    (xrefID, ScoopNo, Price, SalesDate) values (1, 5, 5.00, 1/5/10)  
  
 insert into sales
  
    (xrefID, ScoopNo, Price, SalesDate) values (4, 3, 3.00, 1/5/10)  
  
 select * from sales
  
 create table xrefDistFlavorBrand
  
 (
  
       xrefDistSalesID int identity (1,1) primary key
  
       ,BrandID int
  
       ,FlavorID int
  
       ,distributor_ID int
  
 )
  
 Insert into xrefDistFlavorBrand(BrandID,FlavorID,distributor_ID) values(1,1,1)
  
 Insert into xrefDistFlavorBrand(BrandID,FlavorID,distributor_ID) values(2,2,2)
  
 Insert into xrefDistFlavorBrand(BrandID,FlavorID,distributor_ID) values(3,3,3)
  
 insert into xrefDistFlavorBrand(BrandID,FlavorID,distributor_ID) values(4,4,4)
  
 insert into xrefDistFlavorBrand(BrandID,FlavorID,distributor_ID) values (5,5,5)
  
 select * 
  
 from xrefDistFlavorBrand
  
 --here is the second table
  
 create table xrefDistSales
  
 (
  
       xrefDistSalesID int identity (1,1) primary key
  
       ,BrandID int
  
       ,SalesID int      
  
 )
  
 insert into xrefDistSales(BrandID,SalesID) values (1,1)
  
 insert into xrefDistSales(BrandID,SalesID) values (2,2)
  
 insert into xrefDistSales(BrandID,SalesID) values (3,3)
  
 insert into xrefDistSales(BrandID,SalesID) values (4,4)
  
 insert into xrefDistSales(BrandID,SalesID) values (5,5)
  
 select *
  
 from xrefDistSales
  
 create table distributors
  
    (
  
       distributor_ID int identity (1,1) PRIMARY KEY,
  
       DistributorName varchar (30)
  
    );
  
 insert into distributors
  
 (DistributorName)
  
 Values
  
 ('ND Icecream')
  
 insert into distributors
  
 (DistributorName)
  
 Values
  
 ('GF Grocery')
  
 insert into distributors
  
 (DistributorName)
  
 Values
  
 ('Henreys Foods')
  
 insert into distributors
  
 (DistributorName)
  
 Values
  
 ('Mayville Grocery')
  
 insert into distributors
  
 (DistributorName)
  
 Values
  
 ('Red River Valley Icecream')
  
 select *
  
 from distributors
  
 --keep track of the number of tubs of ice cream on hand
  
 Select * 
  
 from flavor FL
  
 inner join xrefDistFlavorBrand xDFB
  
 on FL.Flavor_ID = xDFB.FlavorID
  
 where quantity <=1
  
 --search distributors by flavor and brand, get a list of distributors
  
 select * 
  
 from distributors D
  
 inner join xrefDistFlavorBrand xDFB
  
 on d.Distributor_ID = xDFB.Distributor_ID
  
 inner join flavor F
  
 on f.Flavor_ID = xDFB.FlavorID
  
 inner join Brand B
  
 on B.BrandID = xDFB.BrandID
  
 where flavor_name = 'choclate'
  
 and
  
 BrandName = 'BlueBunny'
  
 --Keep track of which flavors and brands were sold
  
 select * 
  
 from sales S
  
 inner join xrefDistSales xDS
  
 on s.SalesID = xDS.SalesID
  
 inner join xrefDistFlavorBrand xDFB
  
 on xDS.xrefDistSalesID = xDFB.xrefDistSalesID
  
 inner join flavor F
  
 on f.Flavor_ID = xDFB.FlavorID
  
 where f.flavor_name = 'vanilla'
  

Assignment Three - Question Four

Create a form with three buttons and a listbox. Upon loading put three items in the listbox so it is not empty. Make button one selct item one, button two select item two, and button three select item three.

 Public Class Form1
  
   Dim WithEvents lstItems As New ListBox
  
   Dim strone As String = "Item One"
  
   Dim strtwo As String = "Item Two"
  
   Dim strthree As String = "Item Three"
  
   Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
  
     lstItems.Location = New Point(82, 5)
  
     lstItems.Items.Add(strone)
  
     lstItems.Items.Add(strtwo)
  
     lstItems.Items.Add(strthree)
  
     Me.Controls.Add(lstItems)
  
   End Sub
  
   Private Sub btnOne_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOne.Click
  
     lstItems.SelectedIndex = 0
  
   End Sub
  
   Private Sub btnTwo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTwo.Click
  
     lstItems.SelectedIndex = 1
  
   End Sub
  
   Private Sub btnThree_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnThree.Click
  
     lstItems.SelectedIndex = 2
  
   End Sub
  
 End Class  

Assignment Three - Question Three

When button is clicked, it will create 3 labels and 3 textboxes associated with those labels. When textboxes are hovered over, change their background color. When it is not being hovered over, change the bacground color back to white.

 Public Class Form1
  
   Dim WithEvents txtone, txttwo, txtthree As New TextBox
  
   Dim WithEvents lblone, lbltwo, lblthree As New Label
  
   Private Sub btnMultiply_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMultiply.Click
  
     txtone.Height = 75
  
     txtone.Width = 100
  
     txtone.Text = "I Am Textbox One"
  
     txtone.Location = New Point(4, 1)
  
     txtone.BackColor = Color.White
  
     txttwo.Height = 75
  
     txttwo.Width = 100
  
     txttwo.Text = "I Am Textbox Two"
  
     txttwo.Location = New Point(225, 1)
  
     txttwo.BackColor = Color.White
  
     txtthree.Height = 75
  
     txtthree.Width = 100
  
     txtthree.Text = "I Am Textbox Three"
  
     txtthree.Location = New Point(450, 1)
  
     txtthree.BackColor = Color.White
  
     lblone.Height = 100
  
     lblone.Width = 100
  
     lblone.Text = "I Am Label One"
  
     lblone.Location = New Point(4, 85)
  
     lbltwo.Height = 100
  
     lbltwo.Width = 100
  
     lbltwo.Text = "I Am Label Two"
  
     lbltwo.Location = New Point(225, 85)
  
     lblthree.Height = 100
  
     lblthree.Width = 100
  
     lblthree.Text = "I Am Label Three"
  
     lblthree.Location = New Point(450, 85)
  
     Me.Controls.Add(txtone)
  
     Me.Controls.Add(txttwo)
  
     Me.Controls.Add(txtthree)
  
     Me.Controls.Add(lblone)
  
     Me.Controls.Add(lbltwo)
  
     Me.Controls.Add(lblthree)
  
   End Sub
  
   Private Sub txtone_mouseenter(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtone.MouseEnter
  
     txtone.BackColor = Color.Red
  
   End Sub
  
   Private Sub txtone_mouseleave(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtone.MouseLeave
  
     txtone.BackColor = Color.White
  
   End Sub
  
   Private Sub txttwo_mouseenter(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txttwo.MouseEnter
  
     txttwo.BackColor = Color.Red
  
   End Sub
  
   Private Sub txttwo_mouseleave(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txttwo.MouseLeave
  
     txttwo.BackColor = Color.White
  
   End Sub
  
   Private Sub txtthree_mouseenter(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtthree.MouseEnter
  
     txtthree.BackColor = Color.Red
  
   End Sub
  
   Private Sub txtthree_mouseleave(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtthree.MouseLeave
  
     txtthree.BackColor = Color.White
  
   End Sub
  
 End Class  

Assignment Three - Question Two

Form with 4 buttons
1 - enable/disable textbox
2 - change background color in textbox between two colors
3 - Put in and remove text in textbox
4 - Change border style between fixed 3d and none

 Public Class Form1
  
   Private Sub btnEnable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEnable.Click
  
     If txtBox.Enabled = True Then
  
       txtBox.Enabled = False
  
     ElseIf txtBox.Enabled = False Then
  
       txtBox.Enabled = True
  
     End If
  
   End Sub
  
   Private Sub btnBackground_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBackground.Click
  
     If txtBox.BackColor = Color.White Then
  
       txtBox.BackColor = Color.Red
  
     ElseIf txtBox.BackColor = Color.Red Then
  
       txtBox.BackColor = Color.White
  
     End If
  
   End Sub
  
   Private Sub btnHide_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnHide.Click
  
     If txtBox.Text = "Now You See Me" Then
  
       txtBox.Text = ""
  
     ElseIf txtBox.Text = "" Then
  
       txtBox.Text = "Now You See Me"
  
     End If
  
   End Sub
  
   Private Sub btnBoarder_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBoarder.Click
  
     If txtBox.BorderStyle = BorderStyle.Fixed3D Then
  
       txtBox.BorderStyle = BorderStyle.None
  
     ElseIf txtBox.BorderStyle = BorderStyle.None Then
  
       txtBox.BorderStyle = BorderStyle.Fixed3D
  
     End If
  
   End Sub
  
 End Class  

Assignment 3 - Question One

When button is hovered over move button to a random location

 Public Class Form1
  
   Private Sub btn1_MouseHover(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn1.MouseHover
  
     Dim rnd1 As New Random
  
     Dim x As Integer = rnd1.Next(100)
  
     Dim y As Integer = rnd1.Next(100)
  
     btn1.Location = New Point(x, y)
  
   End Sub
  
 End Class  

Assignment

 use Yummys
  
 go
  
 --drop table Brand
  
 --drop table Flavor
  
 --drop table xrefFlavorBrand
  
 create table Brand
  
 (
  
    BrandID int identity(1,1) primary key
  
    ,BrandName varchar(50)
  
    ,BrandLocation varchar(50)
  
 )
  
 insert into Brand (BrandName, BrandLocation) values ('BlueBunny', 'San Francisco')
  
 insert into Brand (BrandName, BrandLocation) values ('PurpleMonkey', 'Detroit')
  
 insert into Brand (BrandName, BrandLocation) values ('PinkHippo', 'Malibu')
  
 insert into Brand (BrandName, BrandLocation) values ('GreenRabbit', 'Tucson')
  
 insert into Brand (BrandName, BrandLocation) values ('RainbowLamb', 'Minneapolis')
  
 select * 
  
 from Brand
  
 create table flavor
  
    (
  
    Flavor_ID int identity (1,1) PRIMARY KEY,
  
    flavor_name varchar (40),
  
    descraption varchar (100)
  
    )
  
    insert into flavor
  
    (flavor_name, descraption)
  
    values
  
    ('choclate', 'classic choclate')
  
    insert into flavor
  
    (flavor_name, descraption)
  
    values
  
    ('Vanilla', 'Plain Vanilla')
  
    insert into flavor
  
    (flavor_name, descraption)
  
    Values
  
    ('Neopolatin', '1/3 Choclate, 1/3 Vanilla. 1/3 Strawberry')
  
    insert into flavor
  
    (flavor_name, descraption)
  
    Values
  
    ('Coffee', 'Fresh Cround Coffee Flavor')
  
    insert into flavor
  
    (flavor_name, descraption)
  
    Values
  
    ('Sardine', 'Self-Explanatory')
  
 select *
  
 from flavor
  
 create table xrefFlavorBrand
  
 (
  
    xrefID int identity (1,1) primary key
  
    , BrandID int
  
    , FlavorID int
  
 )
  
 Insert into xrefFlavorBrand (BrandID, FlavorID) values (1,1)
  
 Insert into xrefFlavorBrand (BrandID, FlavorID) values (2,2)
  
 Insert into xrefFlavorBrand (BrandID, FlavorID) values (3,3)
  
 Insert into xrefFlavorBrand (BrandID, FlavorID) values (4,4)
  
 insert into xrefFlavorBrand (BrandID, FlavorID) values (5,5)
  
 select *
  
 from xrefFlavorBrand
  
 select FL.*, xFB.BrandID, B.BrandName
  
 from flavor FL 
  
 inner join xrefFlavorBrand xFB
  
 on FL.Flavor_ID = xFB.xrefID
  
 inner join Brand B
  
 on xFB.BrandID = B.BrandID
  
 where FL.flavor_name = 'Coffee'
  

Adding a button, label, listbox, and text box programmatically

Here I added a button, label, listbox, and text box programmatically. I also changed some properties along the way.

 Public Class Form1
  
   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  
     Dim txtBox As New TextBox
  
     Dim lblLabel As New Label
  
     Dim btnButton As New Button
  
     Dim lstList As New ListBox
  
     txtBox.Height = 7
  
     txtBox.Width = 150
  
     txtBox.Text = "Hello"
  
     txtBox.Location = New Point(50, 1)
  
     lblLabel.Text = "Hola"
  
     lblLabel.Location = New Point(50, 150)
  
     btnButton.Height = 75
  
     btnButton.Width = 75
  
     btnButton.Text = "Bonjour"
  
     btnButton.Location = New Point(50, 50)
  
     lstList.Height = 70
  
     lstList.Width = 50
  
     lstList.Text = "Hi"
  
     lstList.Location = New Point(50, 175)
  
     Me.Controls.Add(txtBox)
  
     Me.Controls.Add(lblLabel)
  
     Me.Controls.Add(btnButton)
  
     Me.Controls.Add(lstList)
  
   End Sub
  
 End Class  

Using If and Case statements

1. Shipping Costs

 Public Class Form1
  
   Private Sub btnTotal_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTotal.Click
  
     Dim itemscost As String
  
     Dim totalcost As Integer
  
     itemscost = txtItemsCost.Text
  
     If itemscost >= 50 Then
  
       totalcost = itemscost
  
     ElseIf itemscost < 50 Then
  
       totalcost = itemscost + 5
  
     End If
  
     MessageBox.Show(totalcost)
  
   End Sub
  
 End Class  

2. Temperature Control App

 Public Class Form1
  
   Private Sub btnTotal_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTotal.Click
  
     Dim temp As Integer = txtTemp.Text
  
     Dim AC As Integer = 76
  
     Dim heat As Integer = 72
  
     If temp < heat Then
  
       MessageBox.Show("Heat Turned On")
  
     ElseIf temp > AC Then
  
       MessageBox.Show("AC Turned on")
  
     ElseIf temp <> heat And AC Then
  
       MessageBox.Show("Temperature Perfect")
  
     End If
  
   End Sub
  
 End Class  

3. Sock Size by Age

 Public Class Form1
  
   Private Sub btnTotal_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSize.Click
  
     Dim size As String
  
     Dim age As String = txtAge.Text
  
     Select Case age
  
       Case 0 To 2
  
         size = "XS"
  
       Case 3 To 4
  
         size = "S"
  
       Case 5 To 8
  
         size = "M"
  
       Case 9 To 12
  
         size = "L"
  
       Case 13 To 20
  
         size = "XL"
  
     End Select
  
     MsgBox(size)
  
   End Sub
  
 End Class