CODE

-----------------Create table

--create table info-- (-- id int,-- fname varchar(50),-- lname varchar(50),-- phone varchar(50),-- address varchar(50),-- city varchar(50),-- state varchar(50),-- zip varchar(50),--constraint info_pk primary key (id));
--create table Department-- (Dep_Id varchar(50),-- Dep_name varchar(50),--Constraint Department_PK primary key (Dep_Id));
--create table Course-- (Course_Id varchar(50),-- Course_No varchar(50),-- Descrip Varchar(50),-- Dep_Id Varchar(50),--Constraint Course_PK primary key(Course_Id),--Constraint Course_FK foreign key (Dep_Id) references Department(Dep_Id));

----------Insert data from other database

--use pracC--go--insert into info(fname,lname,phone,address,city,state,zip) --(--select au_fname,au_lname,phone,address,city,state,zip--from pubs.dbo.authors--)

------------Create procedure
--CREATE PROCEDURE addinfo-- @id varchar(50),-- @lname varchar(50),-- @fname varchar(50),-- @phone varchar(50),-- @add varchar(50),-- @city varchar(50),-- @state varchar(50),-- @zip varchar(50)-- --AS-- if not exists(select * from info where id = @id)-- -- begin -- insert into info-- (-- lname,-- fname,-- phone,-- address,-- city,-- state,-- zip-- )-- values-- (-- @lname,-- @fname,-- @phone,-- @add,-- @city,-- @state,-- @zip-- )-- -- end--else-- begin-- update info -- set -- lname = @lname,-- fname=@fname,-- phone = @phone,-- address = @add,-- city=@city,-- state = @state,-- zip = @zip-- where id like @id-- -- end

--create procedure exescalar

-- @id varchar(50)--as--begin-- select lname from info where id like @id--end

---ClsFunction----------

Imports System.WindowsImports System.Text.RegularExpressions
Public Class ClsFunction Inherits ClsConnection
Sub listview(ByVal red As SqlClient.SqlDataReader, ByVal lv As ListView) lv.Items.Clear() While red.Read Dim i As Integer = 1 Dim lvi As ListViewItem = New ListViewItem(red(0).ToString()) While i <= red.FieldCount - 1 lvi.SubItems.Add(red(i).ToString()) i = i + 1 End While lv.Items.Add(lvi) End While If lv.Items.Count > 0 Then lv.Items(0).Selected = True End If End Sub
Function reader(ByVal str As String) Return sqldatareader(str) End Function
Sub fillcombo(ByVal cbx As ComboBox, ByVal red As SqlClient.SqlDataReader, ByVal column As String) While red.Read cbx.Items.Add(red(column).ToString()) End While End Sub Sub clearfields(ByVal cnt As Control) For Each c As Control In cnt.Controls If TypeOf c Is TextBox Then c.Text = "" End If 'If TypeOf c Is ComboBox Then ' c.Text = -1 'End If Next End Sub
Sub validate(ByVal txtphone As TextBox) Dim valid As Boolean = True If Regex.Match(txtphone.Text, "^([a-zA-Z]+[a-zA-Z]+\\s[a-zA-Z]+)*$").Success Then MessageBox.Show("Please enter a valid last name", "Message", MessageBoxButtons.OK, MessageBoxIcon.Error) txtphone.Focus() valid = False Return End If 'bool valid = true;
' If (!Regex.Match(txtFname.Text, "^[A-Za-z]+$").Success) Then ' { ' valid = false; ' MessageBox.Show("Please enter a valid first name", "Message", MessageBoxButtons.OK, MessageBoxIcon.Error); ' txtFname.Focus(); ' return; ' }
' If (!Regex.Match(txtLname.Text, "^([a-zA-Z]+[a-zA-Z]+\\s[a-zA-Z]+)*$").Success) Then ' { ' MessageBox.Show("Please enter a valid last name", "Message", MessageBoxButtons.OK, MessageBoxIcon.Error); ' txtLname.Focus(); ' valid = false; ' return; ' }
' If (!Regex.Match(txtAdres.Text, "^([0-9]+\\s+([a-zA-Z]+[a-zA-Z]+\\s[a-zA-z]+))*$").Success) Then ' { ' MessageBox.Show("Please enter a valid address", "Message", MessageBoxButtons.OK, MessageBoxIcon.Error); ' txtAdres.Focus(); ' valid = false; ' return; ' }
' If (!Regex.Match(txtSurb.Text, "^([a-zA-Z]+[a-zA-Z]+\\s[a-zA-Z]+)*$").Success) Then ' { ' MessageBox.Show("Please enter a valid suburb", "Message", MessageBoxButtons.OK, MessageBoxIcon.Error); ' txtSurb.Focus(); ' valid = false; ' return; ' }
' If (!Regex.Match(txtPcode.Text, "^([0-9]+)$").Success) Then ' { ' MessageBox.Show("Please enter a valid post code", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information); ' txtSurb.Focus(); ' valid = false; ' return; ' }
' If (!Regex.Match(txtState.Text, "^([a-zA-Z]+[a-zA-Z]+\\s[a-zA-Z]+)*$").Success) Then ' { ' MessageBox.Show("Please enter a valid state", "Message", MessageBoxButtons.OK, MessageBoxIcon.Error); ' txtState.Focus(); ' valid = false; ' return; ' }
' If (!Regex.Match(txtCountry.Text, "^([a-zA-Z]+[a-zA-Z]+\\s[a-zA-Z]+)*$").Success) Then ' { ' MessageBox.Show("Please enter a valid country", "Message", MessageBoxButtons.OK, MessageBoxIcon.Error); ' txtCountry.Focus(); ' valid = false; ' return; ' }
' If (!Regex.Match(txtHometel.Text, "^([0-9]+[-().\\s]+)*$").Success) Then ' { ' MessageBox.Show("Please enter a valid phone number", "Message", MessageBoxButtons.OK, MessageBoxIcon.Error); ' txtHometel.Focus(); ' valid = false; ' return; ' }
' If (!Regex.Match(txtOfficetel.Text, "^([0-9]+[-().\\s]+)*$").Success) Then ' { ' MessageBox.Show("Please enter a valid phone number", "Message", MessageBoxButtons.OK, MessageBoxIcon.Error); ' txtOfficetel.Focus(); ' valid = false; ' return; ' }
' If (!Regex.Match(txtMob.Text, "^([0-9]+[-().\\s]+)*$").Success) Then ' { ' MessageBox.Show("Please enter a valid phone number", "Message", MessageBoxButtons.OK, MessageBoxIcon.Error); ' txtMob.Focus(); ' valid = false; ' return; ' }
' If (!Regex.Match(txtMail.Text, "^(([\\w-]+)@([\\w-]+\\.)+[A-Za-z]{2,3})*$").Success) Then ' { ' MessageBox.Show("Please enter a valid email address name", "Message", MessageBoxButtons.OK, MessageBoxIcon.Error); ' txtMail.Focus(); ' valid = false; ' return; ' }
End Sub
Public Function number(ByVal Key As String) As Boolean If (Key >= 48 And Key <= 57) Or Key = 8 Or Key = 46 Or Key = 32 Then number = False Else number = True End If End Function Public Function character(ByVal Key As String) As Boolean If (Key >= 48 And Key <= 57) Then character = True Else character = False End If End FunctionEnd Class

-------ClsConnection------------
Public Class ClsConnection Public con As New SqlClient.SqlConnection Sub connection() con = New SqlClient.SqlConnection("Data Source=MIKEL;Initial Catalog=pracC;Integrated Security=True") ' con = New SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=practice;Integrated Security=True")
End Sub
Function sqldatareader(ByVal str As String) Try connection() Dim cm As New SqlClient.SqlCommand(str, con) Dim red As SqlClient.SqlDataReader con.Open() red = cm.ExecuteReader() Return red Catch ex As Exception System.Windows.Forms.MessageBox.Show(ex.Message, "Error") End Try End Function
Sub execommand(ByVal str As String) Try connection() Dim cm As New SqlClient.SqlCommand(Str, con) con.Open() cm.ExecuteNonQuery() Finally If con.State = ConnectionState.Open Then con.Close() End If End Try
End SubEnd Class

----ClsProcedure------
Imports System.WindowsPublic Class ClsProcedure Inherits ClsConnection Public selectinfo As String Sub insertinfo(ByVal tfname As String, ByVal tlname As String, ByVal tphone As String, ByVal taddress As String, ByVal tcity As String, ByVal tstate As String, ByVal tzip As String, ByVal str As String) Try If str = "" Then execommand("insert into info values('" & tfname & "','" & tlname & "','" & tphone & "','" & taddress & "','" & tcity & "', '" & tstate & "','" & tzip & "')") MessageBox.Show("saved!!!") Else execommand("update info set lname = '" & tlname & "', fname = '" & tfname & "', phone = '" & tphone & "', address='" & taddress & "', state = '" & tstate & "', city = '" & tcity & "', zip = '" & tzip & "' where id like '" & str & "'") MessageBox.Show("Updated!!!") End If Catch ex As Exception MessageBox.Show(ex.Message, "error") End Try End Sub Sub deleteinfo(ByVal id As String) Try execommand("delete from info where id like '" & id & "'") MessageBox.Show("deleted!!!") Catch ex As Exception MessageBox.Show(ex.Message, "error") End Try End Sub Sub selectid(ByVal str As String) If str = "" Then selectinfo = "select * from info order by lname" Else selectinfo = "select * from info where id like '" & str & "'" End If End Sub
End Class

-----FORM CODE----------

Public Class Form1 Dim func As New ClsFunction Dim pro As New ClsProcedure Dim red As SqlClient.SqlDataReader Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load loads() Timer1.Start() End Sub
Private Sub Timer1_Tick_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick Me.ToolStripStatusLabel1.Text = "Date: " + Date.Today & vbCrLf & " Time: " + TimeOfDay End Sub
Sub loads() pro.selectid("") red = func.reader(pro.selectinfo) func.listview(red, Me.ListView1)
red = func.reader(pro.selectinfo) func.fillcombo(Me.ComboBox1, red, "lname") End Sub

Private Sub ButSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButSave.Click If MessageBox.Show("Add...?", "add", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) = Windows.Forms.DialogResult.OK Then pro.insertinfo(Me.TextFirstName.Text, Me.TextLastName.Text, Me.TextPhone.Text, Me.TextAddress.Text, Me.TextCity.Text, Me.TextState.Text, Me.TextZip.Text, Me.TextBox8.Text) ButClear.PerformClick() loads() End If
End Sub
Private Sub Butdel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Butdel.Click pro.deleteinfo(Me.ListView1.SelectedItems.Item(0).Text) loads() End Sub
Private Sub ButClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButClear.Click func.clearfields(Me.GroupBox1) End Sub
Private Sub ListView1_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListView1.DoubleClick pro.selectid(Me.ListView1.SelectedItems.Item(0).Text) red = func.reader(pro.selectinfo) While red.Read Me.TextBox8.Text = red("id").ToString() Me.TextFirstName.Text = red("fname").ToString() Me.TextLastName.Text = red("lname").ToString() Me.TextAddress.Text = red("address").ToString() Me.TextCity.Text = red("city").ToString() Me.TextPhone.Text = red("phone").ToString() Me.TextState.Text = red("state").ToString() Me.TextZip.Text = red("zip").ToString() End While End Sub
Private Sub TextPhone_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TextPhone.KeyPress e.Handled = func.number(Asc(e.KeyChar)) End Sub
Private Sub TextLastName_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TextLastName.KeyPress e.Handled = func.character(Asc(e.KeyChar)) End SubEnd Class

----Select statemnt for login---
Select FName from user_t where UserName like '" & Me.TextBox1.Text & "' and Pass like '" & Me.TextBox2.Text & "' and UserType like '" & Me.ComboBox1.Text & "'