Option Strict
On
Imports System.Data.SqlClient
Public Class
frmEmployeeInput
Inherits System.Windows.Forms.Form
Private conNovelty
As SqlConnection
Private Sub frmEmployeeInput_Load(ByVal sender As
System.Object, ByVal e As System.EventArgs) _
Handles MyBase.Load
conNovelty
= New SqlConnection("workstation
id=INSPIRON;packet size=4096;" & _
"integrated security=SSPI;data
source=INSPIRON;" & _
"persist security
info=False;initial
catalog=Novelty")
End Sub
Private Sub btnSave_Click(ByVal
sender As System.Object,
ByVal e As System.EventArgs) _
Handles btnSave.Click
Dim cmd As New SqlCommand
Dim trans As SqlTransaction
Dim strDepartmentName As String
Dim intNewDeptName As Object
Try
conNovelty.Open()
If txtFirstName.Text <> "" Then
If
txtLastName.Text <> "" Then
If
IsNumeric(txtDepartmentID.Text) Then
If InStr(txtDepartmentID.Text,
",") = 0 _
And InStr(txtDepartmentID.Text,
"$") = 0 _
And InStr(txtDepartmentID.Text,
".") = 0 Then
If IsNumeric(txtSalary.Text) Then
Try
trans = conNovelty.BeginTransaction
cmd.Connection = conNovelty
cmd.CommandType = CommandType.Text
cmd.Transaction = trans
cmd.CommandText = "INSERT INTO tblEmployee"
& _
"(FirstName,
LastName, DepartmentID,
Salary) " & _
"VALUES ('" & txtFirstName.Text
& "', '" & _
txtLastName.Text & "', '" &
_
CInt(txtDepartmentID.Text) & _
"', CONVERT(money,'" & _
CDbl(txtSalary.Text) &
"'))"
cmd.ExecuteNonQuery()
trans.Commit()
MsgBox("All
data submitted by you was successfully recorded to " & _
"the database.", MsgBoxStyle.Information,
"FYI:")
Catch ex As Exception
If Err.Number = 5 Then
'Foreign Key violation from
database
strDepartmentName = InputBox _
("Enter
Department Name", "Department Name")
If strDepartmentName
<> "" Then
cmd.CommandText = "SELECT ID FROM tblDepartment
WHERE " & _
"DepartmentName = '" & _
strDepartmentName & "'"
intNewDeptName = cmd.ExecuteScalar()
If Not intNewDeptName Is Nothing Then
MsgBox("The Department Name of '" & strDepartmentName & _
"' you're trying to add already exists in this database. " & _
vbCrLf &
"The Department ID number of " & _
txtDepartmentID.Text & _
" you're trying to enter is ignored.
" & _
vbCrLf &
"The original Department ID number of " & _
CInt(intNewDeptName) & " is
used instead.", _
MsgBoxStyle.Information,
"Attention!")
cmd.CommandText = "INSERT INTO tblEmployee" & _
"(FirstName, LastName,
DepartmentID, " & _
"Salary) VALUES ('" & txtFirstName.Text
& _
"', '" & txtLastName.Text &
"', '" & _
CInt(intNewDeptName) & "', "
& _
txtSalary.Text & ")"
cmd.ExecuteNonQuery()
trans.Commit()
MsgBox("All data submitted by you was successfully "
& _
"recorded to the database.", MsgBoxStyle.Information, _
"FYI:")
Else
cmd.CommandText = "INSERT INTO tblDepartment" & _
"(DepartmentName) VALUES('"
& _
strDepartmentName
& "')"
cmd.ExecuteNonQuery()
cmd.CommandText
= "SELECT ID FROM tblDepartment WHERE "
& _
"DepartmentName = '" & _
strDepartmentName & "'"
intNewDeptName = cmd.ExecuteScalar()
MsgBox("The Department ID number of " & _
txtDepartmentID.Text & "
you're trying to enter " & _
"cannot be used for this transaction.
" & vbCrLf & _
"The next available Department ID number of
" & _
CInt(intNewDeptName) & " is
used instead.", _
MsgBoxStyle.Information,
"Attention!")
cmd.CommandText = "INSERT INTO tblEmployee" & _
"(FirstName, LastName,
DepartmentID, " & _
"Salary) VALUES ('" & txtFirstName.Text
& _
"',
'" & txtLastName.Text & "', '"
& _
CInt(intNewDeptName) & "',
" & _
txtSalary.Text & ")"
cmd.ExecuteNonQuery()
trans.Commit()
MsgBox("All data submitted by you was successfully "
& _
"recorded to the database.", _
MsgBoxStyle.Information, "FYI:")
End If
Else
trans.Rollback()
MsgBox("None
of the data you're trying to add was not " & _
"recorded to the database!", _
MsgBoxStyle.Information, "FYI:")
End If
Else
trans.Rollback()
MsgBox("The data you're trying to add was not recorded to
" & _
"the database!", MsgBoxStyle.Information,
"FYI:")
End If
End Try
Else
MsgBox("This
field was either left blank, or is non-numeric. " & _
"Please correct.", MsgBoxStyle.Exclamation, "Field: Salary")
txtSalary.Clear()
txtSalary.Focus()
End If
Else
MsgBox("This field has
one or more of the following illegal " & _
"characters
[$], [,] or [.]" & vbCrLf _
& "Please
correct.", MsgBoxStyle.Exclamation,
"Field: DEPARTMENT ID")
txtDepartmentID.Clear()
txtDepartmentID.Focus()
End
If
Else
MsgBox("This field was
either left blank, or is non-numeric. Please correct.",
_
MsgBoxStyle.Exclamation, "Field: DEPARTMENT ID")
txtDepartmentID.Clear()
txtDepartmentID.Focus()
End
If
Else
MsgBox("This field was
left blank. Please fill in.", MsgBoxStyle.Exclamation,
_
"Field: LAST
NAME")
txtLastName.Focus()
txtLastName.Focus()
End
If
Else
MsgBox("This field was
left blank. Please fill in.", MsgBoxStyle.Exclamation,
_
"Field: FIRST
NAME")
txtFirstName.Clear()
txtFirstName.Focus()
End
If
conNovelty.Close()
Catch
ex As Exception
MsgBox(Err.Description
& " No changes applied to the database.", _
MsgBoxStyle.Critical,
"Attention!")
End Try
End Sub
End Class