Imports System
Imports System.Configuration
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports MySql.Data.MySqlClient
Imports System.Data
Imports System.Security.Principal
Partial Class _Default
Inherits System.Web.UI.Page
Dim conn As MySqlConnection
Dim cmd As MySqlCommand
Dim dr As MySqlDataReader
Dim User1 As String
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim enq, thisYear, sigDate As String
Dim mydate As Date
Dim newyear As Integer
'getting user identity through window authentication.
User1 = Context.User.Identity.Name
'taking off "etc/" if any.
User1 = User1.Substring(4)
Session("NTLOGON") = User1.ToUpper()
'creates current year in title of page.
mydate = Date.Today
thisYear = mydate.Year.ToString()
lblDate.Text = mydate.Year.ToString()
newyear = lblDate.Text + 1
lblDate.Text = newyear.ToString()
If Page.IsPostBack = True Then
Dim chkd, chkv, chkmb, chkms, medtrue As String
'Request.Form.GetKey(x)
chkd = chkdental.Items(0).Value.ToString()
chkv = chkvision.Items(0).Value.ToString()
chkmb = chkmedicalbase.Items(0).Value.ToString()
medtrue = chkmedicalup.Items(0).Selected
medtrue = chkmedicalup.Items(1).Selected
medtrue = chkmedicalup.Items(2).Selected
chkms = chkmedicalup.Items(0).Value.ToString()
chkms = chkmedicalup.Items(0).Value.ToString()
Else
'checks to see if user has already filled out form for current year.
Try
conn = New MySqlConnection(ConfigurationManager.ConnectionStrings("appconnection").ToString())
conn.Open()
cmd = New MySqlCommand("SELECT count(*) FROM emp_benefit_info where user_id='" & User1 & "' and year_stamp='" & thisYear & "' and locked='locked'", conn)
enq = cmd.ExecuteScalar()
If enq = 0 Then
cmd = New MySqlCommand("SELECT count(*) FROM emp_benefit_info where user_id='" & User1 & "' and year_stamp='" & thisYear & "'", conn)
enq = cmd.ExecuteScalar()
'Response.Write(enq)
If enq = 0 Then
'loading items
getmedbase()
getmedplus()
getdental()
getvision()
dep1.Style.Add("display", "none")
plus.Style.Add("display", "none")
base.Style.Add("display", "none")
sigDate = Date.Today.Month.ToString() & "/" & Date.Today.Day.ToString() & "/" & Date.Today.Year.ToString()
txtsignaturedate.Text = sigDate.ToString
Session("insert") = "true"
Else
getmedbase()
getmedplus()
getdental()
getvision()
editBenefits()
plus.Style.Add("display", "none")
base.Style.Add("display", "none")
Session("insert") = "false"
End If
conn.Open()
cmd = New MySqlCommand("SELECT name, company FROM employees where userid='" & Session("NTLOGON") & "'", conn)
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
While dr.Read()
txtname.Text = dr("name")
Session("company") = dr("company")
End While
dr.Close()
cmd.Connection.Close()
cmd.Connection.Dispose()
conn.Dispose()
Else
Response.Redirect("locked.aspx")
End If
Catch ex As Exception
lblStatus.Text = ex.Message
End Try
End If
End Sub
Protected Sub editBenefits()
Dim thisdate As Date
Dim benefitYear, itemcount As Integer
Dim itemContains, enq As String
Try
thisdate = Date.Today
benefitYear = thisdate.Year.ToString()
Catch ex As Exception
lblStatus.Text = ex.Message
End Try
conn = New MySqlConnection(ConfigurationManager.ConnectionStrings("appconnection").ToString())
conn.Open()
cmd = New MySqlCommand("SELECT company, name, cov_decline, emp_sup_life_elect, emp_sup_decline, year_stamp, dependents, medical_cov_price, dental_cov_price" & _
",vision_cov_price, emp_dep_life_recip1, cast(date_format(emp_dep_bday, '%m/%d/%Y') as char) as emp_dep_bday" & _
", cast(date_format(emp_yng_child_bday, '%m/%d/%Y') as char) as emp_yng_child_bday, marital_status, medical_cov_name, dental_cov_name" & _
", vision_cov_name, emp_dep_life_recip2, emp_flex_med_elect, emp_flex_daycare_elect, emp_signature" & _
", cast(date_format(emp_sig_date, '%m/%d/%Y') as char) as emp_sig_date, emp_flex_decline FROM emp_benefit_info" & _
" where user_id='" & Session("NTLOGON") & "' and year_stamp='" & benefitYear & "'", conn)
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
While dr.Read()
txtname.Text = dr("name")
itemcount = radmarital.Items.Count
If IsPostBack Then
Else
For i = 0 To itemcount - 1
If dr("marital_status").ToString = radmarital.Items(i).Text Then
radmarital.Items(i).Selected = True
End If
Next
End If
itemcount = chkmedicalbase.Items.Count
For i = 0 To itemcount - 1
itemContains = chkmedicalbase.Items(i).Text.ToString()
If itemContains.Contains(dr("medical_cov_name").ToString()) And itemContains.Contains(dr("medical_cov_price").ToString()) Then
chkmedicalbase.Items(i).Selected = True
Else
chkmedicalbase.Items(i).Selected = False
End If
Next
itemcount = chkmedicalup.Items.Count
For i = 0 To itemcount - 1
itemContains = chkmedicalup.Items(i).Text.ToString()
If itemContains.Contains(dr("medical_cov_name").ToString()) And itemContains.Contains(dr("medical_cov_price").ToString()) Then
chkmedicalup.Items(i).Selected = True
End If
Next
itemcount = chkdental.Items.Count
For i = 0 To itemcount - 1
itemContains = chkdental.Items(i).Text.ToString()
If itemContains.Contains(dr("dental_cov_name").ToString()) And itemContains.Contains(dr("dental_cov_price").ToString()) Then
chkdental.Items(i).Selected = True
End If
Next
itemcount = chkdental.Items.Count
For i = 0 To itemcount - 1
itemContains = chkdental.Items(i).Text.ToString()
If itemContains.Contains(dr("dental_cov_name").ToString()) And itemContains.Contains(dr("dental_cov_price").ToString()) Then
chkdental.Items(i).Selected = True
End If
Next
itemcount = chkvision.Items.Count
For i = 0 To itemcount - 1
itemContains = chkvision.Items(i).Text.ToString()
If itemContains.Contains(dr("vision_cov_name").ToString()) And itemContains.Contains(dr("vision_cov_price").ToString()) Then
chkvision.Items(i).Selected = True
End If
Next
If dr("cov_decline").ToString() = "Yes" Then
declinemed.Checked = True
Else
declinemed.Checked = False
End If
itemcount = chkdependents.Items.Count
For i = 0 To itemcount - 1
itemContains = chkdependents.Items(i).Value.ToString()
If itemContains = dr("dependents").ToString() Then
chkdependents.Items(i).Selected = True
Session("select_dependents") = True
Else
chkdependents.Items(i).Selected = False
End If
Next
Dim insur1, insur2, insur3 As String
insur1 = chkinsurdep.Items(0).Text.ToString()
insur2 = chkinsurdep.Items(1).Text.ToString()
insur3 = chkinsurdep.Items(2).Text.ToString()
Try
If insur1 = dr("emp_dep_life_recip1").ToString() Then
chkinsurdep.Items(0).Selected = True
End If
If insur2 = dr("emp_dep_life_recip2").ToString() Then
chkinsurdep.Items(1).Selected = True
End If
If insur1 <> dr("emp_dep_life_recip1").ToString() And insur2 <> dr("emp_dep_life_recip2").ToString() Then
chkinsurdep.Items(2).Selected = True
End If
Catch ex As Exception
lblStatus.Text = ex.Message
End Try
txtsupbday.Text = dr("emp_yng_child_bday")
txtsupchildbday.Text = dr("emp_dep_bday")
txtflexelectmed.Text = dr("emp_flex_med_elect")
txtflexelectdaycare.Text = dr("emp_flex_daycare_elect")
txtsignature.Text = dr("emp_signature")
txtsignaturedate.Text = dr("emp_sig_date")
itemcount = chksupelect.Items.Count
For i = 0 To itemcount - 1
itemContains = chksupelect.Items(i).Value.ToString()
If itemContains = dr("emp_sup_life_elect").ToString() Then
chksupelect.Items(i).Selected = True
Else
chksupelect.Items(i).Selected = False
End If
Next
If dr("emp_sup_decline").ToString() = "Yes" Then
chksupdecline.Checked = True
Else
chksupdecline.Checked = False
End If
If dr("emp_flex_decline").ToString() = "Yes" Then
chkflexdecline.Checked = True
Else
chkflexdecline.Checked = False
End If
End While
dr.Close()
conn.Open()
cmd = New MySqlCommand("SELECT count(*) FROM dependents where user_id='" & Session("NTLOGON") & "' and year_stamp='" & benefitYear & "'", conn)
enq = cmd.ExecuteScalar()
conn.Close()
'gets dependents from DB.
If enq > 0 Then
Dim i As Integer = 1
conn.Open()
cmd = New MySqlCommand("SELECT dep_fname, dep_relationship, cast(date_format(dep_dob, '%m/%d/%Y') as char) as dep_dob, dep_sex, dep_ss, stays_w_employee, full_time_student FROM dependents where user_id='" & Session("NTLOGON") & "' and year_stamp='" & benefitYear & "'", conn)
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
While dr.Read()
dep1.Style.Add("display", "block")
Dim dname, drelate, ddob, dsex, dss, dstay_W_employee, dfulltime As String
dname = "txtdepname" + i.ToString
drelate = "txtrelation" + i.ToString
ddob = "txtbirth" + i.ToString
dsex = "drpsex" + i.ToString
dss = "txtsocial" + i.ToString
dstay_W_employee = "drpdwelling" + i.ToString
dfulltime = "drpstudent" + i.ToString
Dim d, f, g As DropDownList
Dim a, b, c, e As TextBox
a = FindControl(dname)
b = FindControl(drelate)
c = FindControl(ddob)
d = FindControl(dsex)
e = FindControl(dss)
f = FindControl(dstay_W_employee)
g = FindControl(dfulltime)
a.Text = dr("dep_fname")
b.Text = dr("dep_relationship")
c.Text = dr("dep_dob")
d.SelectedValue = dr("dep_sex")
e.Text = dr("dep_ss")
f.SelectedValue = dr("stays_w_employee")
g.SelectedValue = dr("full_time_student")
i = i + 1
End While
dep1.Style.Add("display", "block")
Else
dep1.Style.Add("display", "none")
End If
dr.Close()
cmd.Connection.Close()
cmd.Connection.Dispose()
conn.Dispose()
End Sub
Sub ftime_Clicked(ByVal sender As Object, ByVal e As EventArgs)
Dim d1, d2, d3, d4, d5, drelate1, drelate2, drelate3, drelate4, drelate5 As String
Dim dbirth1, dbirth2, dbirth3, dbirth4, dbirth5, dsex1, dsex2, dsex3, dsex4, dsex5 As String
Dim dsocial1, dsocial2, dsocial3, dsocial4, dsocial5, ddwelling1, ddwelling2, ddwelling3, ddwelling4, ddwelling5 As String
Dim dstudent1, dstudent2, dstudent3, dstudent4, dstudent5 As String
Try
If chkdependents.SelectedValue <> "" Then
Session("sel_dependents") = True
d1 = txtdepname1.Text
d2 = txtdepname2.Text
d3 = txtdepname3.Text
d4 = txtdepname4.Text
d5 = txtdepname5.Text
drelate1 = txtrelation1.Text
drelate2 = txtrelation2.Text
drelate3 = txtrelation3.Text
drelate4 = txtrelation4.Text
drelate5 = txtrelation5.Text
dbirth1 = txtbirth1.Text
dbirth2 = txtbirth2.Text
dbirth3 = txtbirth3.Text
dbirth4 = txtbirth4.Text
dbirth5 = txtbirth5.Text
dsex1 = drpsex1.Text
dsex2 = drpsex2.Text
dsex3 = drpsex3.Text
dsex4 = drpsex4.Text
dsex5 = drpsex5.Text
dsocial1 = txtsocial1.Text
dsocial2 = txtsocial2.Text
dsocial3 = txtsocial3.Text
dsocial4 = txtsocial4.Text
dsocial5 = txtsocial5.Text
ddwelling1 = drpdwelling1.Text
ddwelling2 = drpdwelling2.Text
ddwelling3 = drpdwelling3.Text
ddwelling4 = drpdwelling4.Text
ddwelling5 = drpdwelling5.Text
dstudent1 = drpstudent1.Text
dstudent2 = drpstudent2.Text
dstudent3 = drpstudent3.Text
dstudent4 = drpstudent4.Text
dstudent5 = drpstudent5.Text
dep1.Style.Add("display", "block")
txtdepname1.Text = d1
txtdepname2.Text = d2
txtdepname3.Text = d3
txtdepname4.Text = d4
txtdepname5.Text = d5
txtrelation1.Text = drelate1
txtrelation2.Text = drelate2
txtrelation3.Text = drelate3
txtrelation4.Text = drelate4
txtrelation5.Text = drelate5
txtbirth1.Text = dbirth1
txtbirth2.Text = dbirth2
txtbirth3.Text = dbirth3
txtbirth4.Text = dbirth4
txtbirth5.Text = dbirth5
drpsex1.SelectedValue = dsex1
drpsex2.SelectedValue = dsex2
drpsex3.SelectedValue = dsex3
drpsex4.SelectedValue = dsex4
drpsex5.SelectedValue = dsex5
txtsocial1.Text = dsocial1
txtsocial2.Text = dsocial2
txtsocial3.Text = dsocial3
txtsocial4.Text = dsocial4
txtsocial5.Text = dsocial5
drpdwelling1.SelectedValue = ddwelling1
drpdwelling2.SelectedValue = ddwelling2
drpdwelling3.SelectedValue = ddwelling3
drpdwelling4.SelectedValue = ddwelling4
drpdwelling5.SelectedValue = ddwelling5
drpstudent1.SelectedValue = dstudent1
drpstudent2.SelectedValue = dstudent2
drpstudent3.SelectedValue = dstudent3
drpstudent4.SelectedValue = dstudent4
drpstudent5.SelectedValue = dstudent5
Else
Session("sel_dependents") = False
dep1.Style.Add("display", "none")
txtdepname1.Text = ""
txtdepname2.Text = ""
txtdepname3.Text = ""
txtdepname4.Text = ""
txtdepname5.Text = ""
txtrelation1.Text = ""
txtrelation2.Text = ""
txtrelation3.Text = ""
txtrelation4.Text = ""
txtrelation5.Text = ""
txtbirth1.Text = ""
txtbirth2.Text = ""
txtbirth3.Text = ""
txtbirth4.Text = ""
txtbirth5.Text = ""
drpsex1.SelectedValue = ""
drpsex2.SelectedValue = ""
drpsex3.SelectedValue = ""
drpsex4.SelectedValue = ""
drpsex5.SelectedValue = ""
txtsocial1.Text = ""
txtsocial2.Text = ""
txtsocial3.Text = ""
txtsocial4.Text = ""
txtsocial5.Text = ""
drpdwelling1.SelectedValue = ""
drpdwelling2.SelectedValue = ""
drpdwelling3.SelectedValue = ""
drpdwelling4.SelectedValue = ""
drpdwelling5.SelectedValue = ""
drpstudent1.SelectedValue = ""
drpstudent2.SelectedValue = ""
drpstudent3.SelectedValue = ""
drpstudent4.SelectedValue = ""
drpstudent5.SelectedValue = ""
End If
Catch ex As Exception
lblStatus.Text = ex.Message
End Try
End Sub
Protected Sub getmedbase()
conn = New MySqlConnection(ConfigurationManager.ConnectionStrings("appconnection").ToString())
conn.Open()
Try
cmd = New MySqlCommand("SELECT * FROM coverage_types where type='medical base' order by list_order asc", conn)
chkmedicalbase.DataSource = cmd.ExecuteReader(CommandBehavior.CloseConnection)
chkmedicalbase.ID = "chkbaseorder"
chkmedicalbase.DataTextField = "description"
chkmedicalbase.DataValueField = "description"
chkmedicalbase.DataBind()
Catch ex As Exception
lblStatus.Text = ex.Message
End Try
conn.Dispose()
End Sub
Protected Sub getmedplus()
conn = New MySqlConnection(ConfigurationManager.ConnectionStrings("appconnection").ToString())
conn.Open()
Try
cmd = New MySqlCommand("SELECT * FROM coverage_types where type='medical up' order by list_order asc", conn)
chkmedicalup.DataSource = cmd.ExecuteReader(CommandBehavior.CloseConnection)
chkmedicalup.ID = "chkuporder"
chkmedicalup.DataTextField = "description"
chkmedicalup.DataValueField = "description"
chkmedicalup.DataBind()
Catch ex As Exception
lblStatus.Text = ex.Message
End Try
conn.Dispose()
End Sub
Protected Sub getdental()
conn = New MySqlConnection(ConfigurationManager.ConnectionStrings("appconnection").ToString())
conn.Open()
Try
cmd = New MySqlCommand("SELECT * FROM coverage_types where type='dental' order by list_order asc", conn)
chkdental.DataSource = cmd.ExecuteReader(CommandBehavior.CloseConnection)
chkdental.ID = "chkdentalorder"
chkdental.DataTextField = "description"
chkdental.DataValueField = "description"
chkdental.DataBind()
Catch ex As Exception
lblStatus.Text = ex.Message
End Try
conn.Dispose()
End Sub
Protected Sub getvision()
conn = New MySqlConnection(ConfigurationManager.ConnectionStrings("appconnection").ToString())
conn.Open()
Try
cmd = New MySqlCommand("SELECT * FROM coverage_types where type='vision' order by list_order asc", conn)
chkvision.DataSource = cmd.ExecuteReader(CommandBehavior.CloseConnection)
chkvision.ID = "chkvisionorder"
chkvision.DataTextField = "description"
chkvision.DataValueField = "description"
chkvision.DataBind()
Catch ex As Exception
lblStatus.Text = ex.Message
End Try
conn.Dispose()
End Sub
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
If Session("insert") = "true" Then
insertdb()
ElseIf Session("insert") = "false" Then
upatedb()
End If
End Sub
Sub insertdb()
Dim name, medbase, medplus, dental, vision, medical_cov, med_decline, enq As String
Dim maritalstatus, logon, company, medicaldeps, life_dep_type1, life_dep_type2, life_dep_bday, life_yng_child_bday, _
life_sup_elect, life_sup_decline, flexelectmed, flexelectdaycare, signature, signaturedate, checkflexdecline, _
medbase_price, medplus_price, med_price, dental_price, vision_price As String
Dim year As Integer
Dim i, itemcount As Integer
Dim checkmeddeps As Boolean
Dim moneyindex, moneylength, textindex As Integer
Dim left, right As String
'setting values equal to nothing so that items get verified correctly
medbase = ""
medplus = ""
dental = ""
vision = ""
medicaldeps = ""
life_dep_type1 = ""
life_dep_type2 = ""
'getting values for all form elelments to insert.
itemcount = chkmedicalbase.Items.Count
For i = 0 To itemcount - 1
If chkmedicalbase.Items(i).Selected = "True" Then
medbase = chkmedicalbase.Items(i).Value.ToString()
moneylength = medbase.Length
textindex = medbase.IndexOf(" biweekly*")
moneyindex = medbase.IndexOf(" $")
left = medbase.Substring(0, moneyindex)
right = medbase.Substring(moneyindex + 2, textindex - moneyindex - 2)
medbase = left
medbase_price = right
End If
Next
itemcount = chkmedicalup.Items.Count
For i = 0 To itemcount - 1
If chkmedicalup.Items(i).Selected = "True" Then
medplus = chkmedicalup.Items(i).Value.ToString()
moneylength = medplus.Length
textindex = medplus.IndexOf(" biweekly*")
moneyindex = medplus.IndexOf(" $")
left = medplus.Substring(0, moneyindex)
right = medplus.Substring(moneyindex + 2, textindex - moneyindex - 2)
medplus = left
medplus_price = right
'Employee Only Coverage $43.38 biweekly*
End If
Next
If medbase <> "" And medplus <> "" Then
medical_cov = ""
ElseIf medbase <> "" And medplus = "" Then
medical_cov = medbase
med_price = medbase_price
ElseIf medplus <> "" And medbase = "" Then
medical_cov = medplus
med_price = medplus_price
End If
itemcount = chkdental.Items.Count
For i = 0 To itemcount - 1
If chkdental.Items(i).Selected = "True" Then
dental = chkdental.Items(i).Value.ToString()
moneylength = dental.Length
textindex = dental.IndexOf(" biweekly*")
moneyindex = dental.IndexOf(" $")
left = dental.Substring(0, moneyindex)
right = dental.Substring(moneyindex + 2, textindex - moneyindex - 2)
dental = left
dental_price = right
End If
Next
itemcount = chkvision.Items.Count
For i = 0 To itemcount - 1
If chkvision.Items(i).Selected = "True" Then
vision = chkvision.Items(i).Value.ToString()
moneylength = vision.Length
textindex = vision.IndexOf(" biweekly*")
moneyindex = vision.IndexOf(" $")
left = vision.Substring(0, moneyindex)
right = vision.Substring(moneyindex + 2, textindex - moneyindex - 2)
vision = left
vision_price = right
End If
Next
itemcount = chkdependents.Items.Count
For i = 0 To itemcount - 1
If chkdependents.Items(i).Selected = "True" Then
medicaldeps = chkdependents.Items(i).Value.ToString()
End If
Next
'checking to see if their are dependents entered in the form.
If medicaldeps <> "" Then
checkmeddeps = True
End If
itemcount = chkinsurdep.Items.Count
For i = 0 To itemcount - 1
If chkinsurdep.Items(i).Selected = "True" Then
If chkinsurdep.Items(i).Value.ToString() = "Spouse" Then
life_dep_type1 = chkinsurdep.Items(i).Value.ToString()
ElseIf chkinsurdep.Items(i).Value.ToString() = "Children" Then
life_dep_type2 = chkinsurdep.Items(i).Value.ToString()
Else
life_dep_type1 = "None"
life_dep_type2 = "None"
End If
End If
Next
year = Date.Today.Year
logon = Session("NTLOGON").ToString()
company = Session("company").ToString()
name = txtname.Text
maritalstatus = radmarital.SelectedValue.ToString()
Try
med_decline = declinemed.Checked.ToString
Catch ex As Exception
med_decline = ""
End Try
If txtsupbday.Text = "" Then
life_dep_bday = ""
Else
life_dep_bday = GetDateValue(txtsupbday.Text)
End If
If txtsupchildbday.Text = "" Then
life_yng_child_bday = ""
Else
life_yng_child_bday = GetDateValue(txtsupchildbday.Text)
End If
Try
life_sup_elect = chksupelect.SelectedItem.Value.ToString
Catch ex As Exception
life_sup_elect = ""
End Try
Try
life_sup_decline = chksupdecline.Checked.ToString
Catch ex As Exception
life_sup_decline = ""
End Try
flexelectmed = txtflexelectmed.Text
flexelectdaycare = txtflexelectdaycare.Text
signature = txtsignature.Text
If txtsignaturedate.Text = "" Then
signaturedate = ""
Else
signaturedate = GetDateValue(txtsignaturedate.Text)
End If
Try
checkflexdecline = chkflexdecline.Checked.ToString()
Catch ex As Exception
checkflexdecline = ""
End Try
If checkflexdecline = "True" Then
checkflexdecline = "Yes"
Else
checkflexdecline = "No"
End If
'converting values for drp downs b4 going into DB.
If med_decline = "True" Then
med_decline = "Yes"
Else
med_decline = "No"
End If
If life_sup_decline = "True" Then
life_sup_decline = "Yes"
Else
life_sup_decline = "No"
End If
If checkflexdecline = "True" Then
life_sup_decline = "Yes"
Else
checkflexdecline = "No"
End If
'checking if user signed up for more than one medical plan.
If medical_cov = "" Then
lblError.Text = "You cannot choose more than one medical coverage plan. Please select ONLY one medical coverage plan."
plus.Style.Add("display", "block")
base.Style.Add("display", "block")
Else
plus.Style.Add("display", "none")
base.Style.Add("display", "none")
lblError.Text = ""
Try
conn = New MySqlConnection(ConfigurationManager.ConnectionStrings("appconnection").ToString())
conn.Open()
cmd = New MySqlCommand _
("INSERT INTO emp_benefit_info(user_id,company,name,cov_decline,marital_status,emp_sup_life_elect" & _
",emp_sup_decline,year_stamp,dependents,medical_cov_price,dental_cov_price,vision_cov_price" & _
",medical_cov_name,dental_cov_name,vision_cov_name,emp_dep_life_recip1,emp_dep_life_recip2" & _
",emp_dep_bday,emp_yng_child_bday,emp_flex_med_elect,emp_flex_daycare_elect" & _
",emp_flex_decline,emp_signature, emp_sig_date) values('" & logon & "','" & company & "','" & name & "'" & _
",'" & med_decline & "','" & maritalstatus & "','" & life_sup_elect & "','" & life_sup_decline & "'" & _
",'" & year & "','" & medicaldeps & "','" & med_price & "','" & dental_price & "'" & _
",'" & vision_price & "','" & medical_cov & "','" & dental & "','" & vision & "','" & life_dep_type1 & "'" & _
",'" & life_dep_type2 & "','" & life_dep_bday & "','" & life_yng_child_bday & "'" & _
",'" & flexelectmed & "','" & flexelectdaycare & "','" & checkflexdecline & "','" & signature & "'" & _
",'" & signaturedate & "')", conn)
enq = cmd.ExecuteNonQuery()
cmd.Connection.Close()
cmd.Connection.Dispose()
'checking to see if dependents were added.
If checkmeddeps = True Then
Dim x As Integer = 1
Dim depname, deprelate, depbirth, depsex, depsocial, depdwelling, depstudent As String
'looping through each table row to get all data that was entered.
Do Until x = 5
depname = Request.Form("txtdepname" & x)
deprelate = Request.Form("txtrelation" & x)
depbirth = GetDateValue(Request.Form("txtbirth" & x))
depsex = Request.Form("drpsex" & x)
depsocial = Request.Form("txtsocial" & x)
depdwelling = Request.Form("drpdwelling" & x)
depstudent = Request.Form("drpstudent" & x)
If depname <> "" Then
conn = New MySqlConnection(ConfigurationManager.ConnectionStrings("appconnection").ToString())
conn.Open()
cmd = New MySqlCommand("INSERT INTO dependents(user_id,company,year_stamp,dep_fname,dep_relationship,dep_dob," & _
"dep_sex,dep_ss,stays_w_employee,full_time_student) " & _
"values('" & logon & "','" & company & "','" & year & "','" & depname & "','" & deprelate & "'" & _
",'" & depbirth & "','" & depsex & "','" & depsocial & "'" & _
",'" & depdwelling & "','" & depstudent & "')", conn)
enq = cmd.ExecuteNonQuery()
cmd.Connection.Close()
cmd.Connection.Dispose()
conn.Close()
conn.Dispose()
End If
x = x + 1
Loop
End If
Catch ex As Exception
lblStatus.Text = ex.Message
End Try
End If
End Sub
Sub upatedb()
Dim name, medbase, medplus, dental, vision, medical_cov, med_decline, enq As String
Dim maritalstatus, logon, company, medicaldeps, life_dep_type, life_dep_bday, life_yng_child_bday, _
life_sup_elect, life_sup_decline, flexelectmed, flexelectdaycare, signature, signaturedate, checkflexdecline As String
Dim year As Integer
Dim i, itemcount As Integer
Dim checkmeddeps As Boolean
'setting values equal to nothing so that items get verified correctly
medbase = ""
medplus = ""
dental = ""
vision = ""
medicaldeps = ""
life_dep_type = ""
'getting values for all form elelments to insert.
itemcount = chkmedicalbase.Items.Count
For i = 0 To itemcount - 1
If chkmedicalbase.Items(i).Selected = True Then
medbase = chkmedicalbase.Items(i).Value.ToString()
End If
Next
itemcount = chkmedicalup.Items.Count
For i = 0 To itemcount - 1
If chkmedicalup.Items(i).Selected = True Then
medplus = chkmedicalup.Items(i).Value.ToString()
End If
Next
If medbase <> "" And medplus <> "" Then
medical_cov = ""
ElseIf medbase <> "" And medplus = "" Then
medical_cov = medbase
ElseIf medplus <> "" And medbase = "" Then
medical_cov = medplus
End If
itemcount = chkdental.Items.Count
For i = 0 To itemcount - 1
If chkdental.Items(i).Selected = True Then
dental = chkdental.Items(i).Value.ToString()
End If
Next
itemcount = chkvision.Items.Count
For i = 0 To itemcount - 1
If chkvision.Items(i).Selected = True Then
vision = chkvision.Items(i).Value.ToString()
End If
Next
itemcount = chkdependents.Items.Count
For i = 0 To itemcount - 1
If chkdependents.Items(i).Selected = True Then
medicaldeps = chkdependents.Items(i).Value.ToString()
End If
Next
'checking to see if their are dependents entered in the form.
If medicaldeps <> "" Then
checkmeddeps = True
End If
itemcount = chkinsurdep.Items.Count
For i = 0 To itemcount - 1
If chkinsurdep.Items(i).Selected = True Then
life_dep_type = chkinsurdep.Items(i).Value.ToString()
End If
Next
year = Date.Today.Year
logon = Session("NTLOGON").ToString()
company = Session("company").ToString()
name = txtname.Text
Try
maritalstatus = radmarital.SelectedValue.ToString()
Catch ex As Exception
maritalstatus = ""
End Try
med_decline = declinemed.Checked.ToString
life_dep_bday = txtsupbday.Text
life_yng_child_bday = txtsupchildbday.Text
Try
life_sup_elect = chksupelect.SelectedItem.Value.ToString
Catch ex As Exception
life_sup_elect = ""
End Try
life_sup_decline = chksupdecline.Checked.ToString
flexelectmed = txtflexelectmed.Text
flexelectdaycare = txtflexelectdaycare.Text
signature = txtsignature.Text
signaturedate = GetDateValue(txtsignaturedate.Text)
checkflexdecline = chkflexdecline.Checked.ToString()
'converting values for drp downs b4 going into DB.
If med_decline = True Then
med_decline = "Yes"
Else
med_decline = ""
End If
If life_sup_decline = True Then
life_sup_decline = "Yes"
Else
life_sup_decline = ""
End If
If checkflexdecline = True Then
life_sup_decline = "Yes"
Else
checkflexdecline = ""
End If
'checking if user signed up for more than one medical plan.
If medical_cov = "" Then
lblError.Text = "You cannot choose more than one medical coverage plan. Please select ONLY one medical coverage plan."
plus.Style.Add("display", "block")
base.Style.Add("display", "block")
Else
plus.Style.Add("display", "none")
base.Style.Add("display", "none")
lblError.Text = ""
Try
'conn = New MySqlConnection(ConfigurationManager.ConnectionStrings("appconnection").ToString())
'conn.Open()
'cmd = New MySqlCommand("INSERT INTO emp_benefit_info(user_id,company,name,cov_decline,marital_status,emp_sup_life_elect," & _
' "emp_sup_decline,year_stamp,dependents,medical_cov,dental_cov,vision_cov,emp_dep_life_recip," & _
' "emp_life_bday,emp_yng_child_bday,emp_flex_med_elect,emp_flex_daycare_elect,emp_signature, emp_date)" & _
' " values('" & logon & "','" & company & "','" & name & "'" & _
' "'" & med_decline & "','" & maritalstatus & "','" & life_sup_elect & "','" & life_sup_decline & "'," & _
' "'" & year & "','" & medicaldeps & "','" & medical_cov & "','" & dental & "','" & vision & "'," & _
' "'" & life_dep_type & "','" & life_dep_bday & "','" & life_yng_child_bday & "')", conn)
'enq = cmd.ExecuteNonQuery()
'cmd.Connection.Close()
'cmd.Connection.Dispose()
'checking to see if dependents were added.
If checkmeddeps = True Then
Dim x As Integer
Dim depname, deprelate, depbirth, depsex, depsocial, depdwelling, depstudent As String
x = 1
'looping through each table row to get all data that was entered.
Do Until x = 5
depname = Request.Form("txtdepname" & x)
deprelate = Request.Form("txtrelation" & x)
depbirth = Request.Form("txtbirth" & x)
depsex = Request.Form("drpsex" & x)
depsocial = Request.Form("txtsocial" & x)
depdwelling = Request.Form("drpdwelling" & x)
depstudent = Request.Form("drpstudent" & x)
If depname <> "" Then
'conn = New MySqlConnection(ConfigurationManager.ConnectionStrings("appconnection").ToString())
'conn.Open()
'cmd = New MySqlCommand("INSERT INTO dependents(user_id,company,year_stamp,depfname,dep_relationship,dep_dob," & _
' "dep_sex,dep_ss,stays_w_employee,full_time_student) " & _
' "values('" & logon & "','" & company & "','" & name & "''" & med_decline & "'," & _
' "'" & med_decline & "','" & maritalstatus & "','" & life_sup_elect & "','" & life_sup_decline & "'," & _
' "'" & year & "','" & medicaldeps & "')", conn)
'enq = cmd.ExecuteNonQuery()
'cmd.Connection.Close()
'cmd.Connection.Dispose()
'conn.Close()
'conn.Dispose()
End If
x = x + 1
Loop
End If
Catch ex As Exception
lblStatus.Text = ex.Message
End Try
End If
End Sub
Public Shared Function GetDateValue(ByVal sVal As String) As String
Dim yr, mo, day As String
Dim replaceMonth, replaceDay As String
Try
If sVal.Length = 8 Then
yr = sVal.Substring(4, 4)
replaceMonth = sVal.Substring(0, 2)
mo = replaceMonth.IndexOf("/")
If mo <= "0" Then
mo = sVal.Substring(0, 2)
Else
mo = "0" & sVal.Substring(0, 1)
End If
replaceDay = sVal.Substring(3, 2)
day = replaceDay.IndexOf("/")
If day = "1" Then
day = sVal.Substring(2, 2)
Else
day = "0" & sVal.Substring(2, 1)
End If
sVal = yr & "-" & mo & "-" & day
ElseIf sVal.Length = 9 Then
yr = sVal.Substring(5, 4)
replaceMonth = sVal.Substring(0, 2)
mo = replaceMonth.IndexOf("/")
If mo <= "0" Then
mo = sVal.Substring(0, 2)
Else
mo = "0" & sVal.Substring(0, 1)
End If
If mo > 9 Then
replaceDay = sVal.Substring(3, 1)
day = replaceDay.IndexOf("/")
If day = "1" Then
day = sVal.Substring(2, 2)
Else
day = "0" & sVal.Substring(3, 1)
End If
sVal = yr & "-" & mo & "-" & day
Else
replaceDay = sVal.Substring(3, 2)
day = replaceDay.IndexOf("/")
If day = "1" Then
day = sVal.Substring(2, 2)
Else
day = "0" & sVal.Substring(2, 1)
End If
sVal = yr & "-" & mo & "-" & day
End If
ElseIf sVal.Length = 10 Then
yr = sVal.Substring(6, 4)
replaceMonth = sVal.Substring(0, 2)
mo = replaceMonth.IndexOf("/")
If mo <= "0" Then
mo = sVal.Substring(0, 2)
Else
mo = "0" & sVal.Substring(0, 1)
End If
replaceDay = sVal.Substring(3, 2)
day = replaceDay.IndexOf("/")
If day <= "0" Then
day = sVal.Substring(3, 2)
Else
day = sVal.Substring(2, 2)
End If
sVal = yr & "-" & mo & "-" & day
Else
sVal = "0000-00-00"
End If
Catch generatedExceptionName As Exception
End Try
Return sVal
End Function
End Class
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:
206:
207:
208:
209:
210:
211:
212:
213:
214:
215:
216:
217:
218:
219:
220:
221:
222:
223:
224:
225:
226:
227:
228:
229:
230:
231:
232:
233:
234:
235:
236:
237:
238:
239:
240:
241:
242:
243:
244:
245:
246:
247:
248:
249:
250:
251:
252:
253:
254:
255:
256:
257:
258:
259:
260:
261:
262:
263:
264:
265:
266:
267:
268:
269:
270:
271:
272:
273:
274:
275:
276:
277:
278:
279:
280:
281:
282:
283:
284:
285:
286:
287:
288:
289:
290:
291:
292:
293:
294:
295:
296:
297:
298:
299:
300:
301:
302:
303:
304:
305:
306:
307:
308:
309:
310:
311:
312:
313:
314:
315:
316:
317:
318:
319:
320:
321:
322:
323:
324:
325:
326:
327:
328:
329:
330:
331:
332:
333:
334:
335:
336:
337:
338:
339:
340:
341:
342:
343:
344:
345:
346:
347:
348:
349:
350:
351:
352:
353:
354:
355:
356:
357:
358:
359:
360:
361:
362:
363:
364:
365:
366:
367:
368:
369:
370:
371:
372:
373:
374:
375:
376:
377:
378:
379:
380:
381:
382:
383:
384:
385:
386:
387:
388:
389:
390:
391:
392:
393:
394:
395:
396:
397:
398:
399:
400:
401:
402:
403:
404:
405:
406:
407:
408:
409:
410:
411:
412:
413:
414:
415:
416:
417:
418:
419:
420:
421:
422:
423:
424:
425:
426:
427:
428:
429:
430:
431:
432:
433:
434:
435:
436:
437:
438:
439:
440:
441:
442:
443:
444:
445:
446:
447:
448:
449:
450:
451:
452:
453:
454:
455:
456:
457:
458:
459:
460:
461:
462:
463:
464:
465:
466:
467:
468:
469:
470:
471:
472:
473:
474:
475:
476:
477:
478:
479:
480:
481:
482:
483:
484:
485:
486:
487:
488:
489:
490:
491:
492:
493:
494:
495:
496:
497:
498:
499:
500:
501:
502:
503:
504:
505:
506:
507:
508:
509:
510:
511:
512:
513:
514:
515:
516:
517:
518:
519:
520:
521:
522:
523:
524:
525:
526:
527:
528:
529:
530:
531:
532:
533:
534:
535:
536:
537:
538:
539:
540:
541:
542:
543:
544:
545:
546:
547:
548:
549:
550:
551:
552:
553:
554:
555:
556:
557:
558:
559:
560:
561:
562:
563:
564:
565:
566:
567:
568:
569:
570:
571:
572:
573:
574:
575:
576:
577:
578:
579:
580:
581:
582:
583:
584:
585:
586:
587:
588:
589:
590:
591:
592:
593:
594:
595:
596:
597:
598:
599:
600:
601:
602:
603:
604:
605:
606:
607:
608:
609:
610:
611:
612:
613:
614:
615:
616:
617:
618:
619:
620:
621:
622:
623:
624:
625:
626:
627:
628:
629:
630:
631:
632:
633:
634:
635:
636:
637:
638:
639:
640:
641:
642:
643:
644:
645:
646:
647:
648:
649:
650:
651:
652:
653:
654:
655:
656:
657:
658:
659:
660:
661:
662:
663:
664:
665:
666:
667:
668:
669:
670:
671:
672:
673:
674:
675:
676:
677:
678:
679:
680:
681:
682:
683:
684:
685:
686:
687:
688:
689:
690:
691:
692:
693:
694:
695:
696:
697:
698:
699:
700:
701:
702:
703:
704:
705:
706:
707:
708:
709:
710:
711:
712:
713:
714:
715:
716:
717:
718:
719:
720:
721:
722:
723:
724:
725:
726:
727:
728:
729:
730:
731:
732:
733:
734:
735:
736:
737:
738:
739:
740:
741:
742:
743:
744:
745:
746:
747:
748:
749:
750:
751:
752:
753:
754:
755:
756:
757:
758:
759:
760:
761:
762:
763:
764:
765:
766:
767:
768:
769:
770:
771:
772:
773:
774:
775:
776:
777:
778:
779:
780:
781:
782:
783:
784:
785:
786:
787:
788:
789:
790:
791:
792:
793:
794:
795:
796:
797:
798:
799:
800:
801:
802:
803:
804:
805:
806:
807:
808:
809:
810:
811:
812:
813:
814:
815:
816:
817:
818:
819:
820:
821:
822:
823:
824:
825:
826:
827:
828:
829:
830:
831:
832:
833:
834:
835:
836:
837:
838:
839:
840:
841:
842:
843:
844:
845:
846:
847:
848:
849:
850:
851:
852:
853:
854:
855:
856:
857:
858:
859:
860:
861:
862:
863:
864:
865:
866:
867:
868:
869:
870:
871:
872:
873:
874:
875:
876:
877:
878:
879:
880:
881:
882:
883:
884:
885:
886:
887:
888:
889:
890:
891:
892:
893:
894:
895:
896:
897:
898:
899:
900:
901:
902:
903:
904:
905:
906:
907:
908:
909:
910:
911:
912:
913:
914:
915:
916:
917:
918:
919:
920:
921:
922:
923:
924:
925:
926:
927:
928:
929:
930:
931:
932:
933:
934:
935:
936:
937:
938:
939:
940:
941:
942:
943:
944:
945:
946:
947:
948:
949:
950:
951:
952:
953:
954:
955:
956:
957:
958:
959:
960:
961:
962:
963:
964:
965:
966:
967:
968:
969:
970:
971:
972:
973:
974:
975:
976:
977:
978:
979:
980:
981:
982:
983:
984:
985:
986:
987:
988:
989:
990:
991:
992:
993:
994:
995:
996:
997:
998:
999:
1000:
1001:
1002:
1003:
1004:
1005:
1006:
1007:
1008:
1009:
1010:
1011:
1012:
1013:
1014:
1015:
1016:
1017:
1018:
1019:
1020:
1021:
1022:
1023:
1024:
1025:
1026:
1027:
1028:
1029:
1030:
1031:
1032:
1033:
1034:
1035:
1036:
1037:
1038:
1039:
1040:
1041:
1042:
1043:
1044:
1045:
1046:
1047:
1048:
1049:
1050:
1051:
1052:
1053:
1054:
1055:
1056:
1057:
1058:
1059:
1060:
1061:
1062:
1063:
1064:
1065:
1066:
1067:
1068:
1069:
1070:
1071:
1072:
1073:
1074:
1075:
1076:
1077:
1078:
1079:
1080:
1081:
1082:
1083:
1084:
1085:
1086:
1087:
1088:
1089:
1090:
1091:
1092:
1093:
1094:
1095:
1096:
1097:
1098:
1099:
1100:
1101:
1102:
1103:
1104:
1105:
1106:
1107:
1108:
1109:
1110:
1111:
1112:
1113:
1114:
1115:
1116:
1117:
1118:
1119:
1120:
1121:
1122:
1123:
1124:
1125:
1126:
1127:
1128:
1129:
1130:
1131:
1132:
1133:
1134:
1135:
1136:
1137:
1138:
1139:
1140:
1141:
1142:
1143:
1144:
1145:
1146:
1147:
1148:
1149:
1150:
1151:
1152:
1153:
1154:
1155:
1156:
1157:
1158:
1159:
1160:
1161:
1162:
1163:
1164:
1165:
1166:
1167:
1168:
1169:
1170:
1171:
1172:
1173:
1174:
1175:
1176:
1177:
1178:
1179:
1180:
1181:
1182:
by: sevensnake77Posted on 2009-10-19 at 13:48:28ID: 25608873
you want to have the checkbox change the value in the database when checked or unchecked correct?