Ado.net aplication

Hello,
I post a program in vb using ado.net wich is a nice application.


public Class Form1
Dim conn As New OleDbConnection("Provider=....")
Dim k As Integer
Private Sub Iesire_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Iesire.Click
Me.Close()
End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Try
conn.Open()
Dim comand As New OleDbCommand("Select DenumireS from Sectii order by denumireS", conn)
comand.CommandType = CommandType.Text
Dim reader As OleDbDataReader = comand.ExecuteReader 'se creeaza un DataReader pt inreg din BD
If reader.HasRows Then
Do While reader.Read 'se muta cursorul pe prima inregistrare
Sectii.Items.Add(reader.GetString(0).ToString)
Loop
Sectii.SelectedIndex = 0
End If
Catch ex As Exception
MsgBox(ex.ToString())
End Try
End Sub

Public Sub getstud(ByVal crit As String, ByVal sens As String)
Dim stud As New OleDbCommand()
Nume.Items.Clear()
Grupa.Items.Clear()
stud.Connection = conn
stud.CommandType = CommandType.Text
stud.CommandText = "select nume,grupa from Studenti where cods = (select cods from sectii where denumires = " & "'" & Sectii.SelectedItem.ToString & "'" & ")order by " & crit & " " & sens

Dim stud1 As OleDbDataReader = stud.ExecuteReader
If stud1.HasRows Then
Do While stud1.Read
Nume.Items.Add(stud1.GetString(0).ToString)
If Grupa.Items.Contains(stud1.GetString(0).ToString) = False Then
Grupa.Items.Add(stud1.GetString(1).ToString)
End If
Loop
If Nume.Items.Count > 0 Then
Nume.SelectedIndex = 0
End If
End If
End Sub

Private Sub Sectii_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Sectii.SelectedIndexChanged
getstud("nume", "asc")
End Sub

' OR...........
Private Sub loadAngajati(ByVal prct As String, ByVal query As String)
lista.Items.Clear()
connectToDB()
Dim cmd As New OleDbCommand
cmd.Connection = conn
cmd.CommandType = CommandType.Text
cmd.CommandText = query
cmd.Parameters.Add(New OleDbParameter("nm", prct))
MsgBox(prct)
Dim reader As OleDbDataReader = cmd.ExecuteReader
If reader.HasRows Then
Do While reader.Read
lista.Items.Add(reader.GetString(0))
Loop
End If
End Sub
Private Sub combo_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles combo.SelectedIndexChanged
Dim query As String = "SELECT nume FROM Angajati WHERE proiect_id = (SELECT proiect_id FROM Proiecte WHERE nume = ?)"
Call loadAngajati(combo.SelectedItem, query)
End Sub
'..................

Private Sub Nume_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Nume.SelectedIndexChanged
Dim com1 As New OleDbCommand()
com1.Connection = conn
com1.CommandType = CommandType.Text
com1.CommandText = ("select grupa ,nrmatricol,datan from Studenti where nume = " & "'" & Nume.SelectedItem.ToString & "' and cods = (select cods from sectii where denumires = " & "'" & Sectii.SelectedItem.ToString & "'" & ")")
Dim com2 As OleDbDataReader = com1.ExecuteReader
If com2.HasRows Then
Do While com2.Read
Grupa.SelectedItem = com2.GetString(0).ToString
NumeS.Text = Nume.SelectedItem.ToString
GrupaS.Text = com2.GetString(0).ToString
NrMatr.Text = com2.GetString(1).ToString
Try
Data.Value = com2.GetDateTime(2)
Catch ex As Exception
Data.Value = DateTime.Now
End Try
Loop
End If
End Sub

Private Sub Adauga_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Adauga.Click
Dim i, j As Integer
i = 0
Dim com1 As New OleDbCommand()
com1.Connection = conn
com1.CommandType = CommandType.Text
com1.CommandText = "select * from studenti where nrmatricol ='" & NrMatr.Text & "'"
Dim com2 As OleDbDataReader = com1.ExecuteReader
If com2.HasRows Then
Do While com2.Read
If com2.GetString(1).ToString = NrMatr.Text Then
MsgBox("NUMAR MATRICOL EXISTENT")

i = 1
End If
If com2.GetString(2) = NumeS.Text Then
MsgBox("NUME EXISTENT")
i = 1
End If
Loop
End If
If Integer.TryParse(NrMatr.Text, j) = False Then
MsgBox("NUMAR MATRICOL INVALID!!!!")
i = 1
NrMatr.Clear()
End If
If NumeS.Text = "" Then
MsgBox("NUME INVALID")
i = 1
NumeS.Clear()
End If
If Integer.TryParse(GrupaS.Text, j) = False Then
MsgBox("GRUPA INVALIDA")
i = 1
GrupaS.Clear()

End If
Dim c As New OleDbCommand()
c.Connection = conn
c.CommandType = CommandType.Text
c.CommandText = "select cods from sectii where denumires = '" & Sectii.SelectedItem.ToString & "'"
Dim d As OleDbDataReader = c.ExecuteReader()
d.Read()

Dim com3 As New OleDbCommand()
com3.Connection = conn
com3.CommandType = CommandType.Text
k = Integer.Parse(d.GetValue(0).ToString)
If i = 0 Then
'com3.CommandText = "insert into studenti(nrmatricol,nume,grupa,datan,cods) values( " & "'" & NrMatr.Text.ToString & "','" & NumeStud.Text & "','" & GrupaStud.Text & "','" & NastereStud.Value.ToString.Split(" ")(0) & "','" & k & "')"
com3.CommandText = "insert into studenti(nrmatricol,nume,grupa,datan,cods) values( @param1,'" & NumeS.Text & "','" & GrupaS.Text & "','" & Data.Value.ToString.Split(" ")(0) & "','" & k & "')"

Dim p As New OleDbParameter
p.Value = NrMatr.Text
p.Direction = ParameterDirection.Input
p.OleDbType = OleDbType.VarChar
p.ParameterName = "param1"
com3.Parameters.Add(p)
com3.ExecuteReader()
getstud("nume", "asc")
End If
End Sub

Private Sub Sterge_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Sterge.Click
Dim com1 As New OleDbCommand()
com1.Connection = conn
com1.CommandType = CommandType.Text
com1.CommandText = "delete from studenti where nrmatricol= '" & NrMatr.Text & "'"
com1.ExecuteReader()
getstud("nume", "asc")
End Sub


Private Sub Modifica_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Modifica.Click
Dim i, y As Integer
i = 0
If Nume.Text = "" Then
MsgBox("Nume invalid!")
i = 1


End If
If Integer.TryParse(GrupaS.Text, y) = False Then
MsgBox("Grupa invalida !")
i = 1
End If

If i = 0 Then
Dim com1 As New OleDbCommand()
com1.Connection = conn
com1.CommandType = CommandType.Text
com1.CommandText = "update studenti set nume = " & "'" & NumeS.Text.ToString & "'" & " where nrmatricol = " & "'" & NrMatr.Text.ToString & "'"
com1.ExecuteReader()

Dim com2 As New OleDbCommand()
com2.Connection = conn
com2.CommandType = CommandType.Text
com2.CommandText = "update studenti set grupa = " & "'" & GrupaS.Text.ToString & "'" & " where nrmatricol = " & "'" & NrMatr.Text.ToString & "'"
com2.ExecuteReader()

Dim com3 As New OleDbCommand()
com3.Connection = conn
com3.CommandType = CommandType.Text
com3.CommandText = "update studenti set datan = " & "'" & Data.Value.ToString.Split(" ")(0) & "'" & " where nrmatricol = " & "'" & NrMatr.Text.ToString & "'"
com3.ExecuteReader()
getstud("nume", "asc")
End If
End Sub

Private Sub bdel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bdel.Click
Dim col As String = bdel.Text
connectToDB()
Dim cmd As New OleDbCommand
cmd.Connection = conn
cmd.CommandType = CommandType.Text
cmd.CommandText = "ALTER TABLE Angajati DROP COLUMN" & col
cmd.ExecuteReader()


End Sub