' Programmed By Jery M
 'this Following code shows how to Save, Edit, Delete Data using VB.Net and SQL Server 2000 as database.
 'this code needed some control :
 'a database with 4 column (Id[primary key],FirstName,LastName,Age)
 '3 button (cmdSave,cmdEdit,cmdDelete)
 '4 text box (txtId,txtFirstName,txtLastName,txtAge).
 '1 datagrid (named dgStudent)

 ' This Proceduere to refresh form and refresh data in datagrid (always show the newest data)
 Private Sub Refresh_Form()
        Dim conn As SqlConnection
        Dim cmdStudent As New SqlCommand
        Dim daStudent As New SqlDataAdapter
        Dim dsStudent As New DataSet
        Dim dtStudent As New DataTable

	'clear all textbox
        txtId.Text = ""
        txtFirstName.Text = ""
        txtLastName.Text = ""
        txtAge.Text = ""
        
	'this part to call data from database and show in datagrid
        conn = GetConnect()
        Try
            cmdStudent = conn.CreateCommand
            cmdStudent.CommandText = "SELECT * FROM Student"
            daStudent.SelectCommand = cmdStudent
            daStudent.Fill(dsStudent, "Student")
            dgStudent.DataSource = dsStudent
            dgStudent.DataMember = "Student"
            dgStudent.ReadOnly = True
        Catch ex As Exception
            MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OKOnly, "Connection Error !!")
        End Try
End Sub

  Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        Dim check As Integer
        Dim conn As SqlConnection
        Dim cmdStudent As New SqlCommand
        Dim cmdStudent1 As New SqlCommand
        Dim daStudent As New SqlDataAdapter
        Dim dsStudent As New DataSet
        Dim dtStudent As New DataTable

        If txtId.text = "" Or txtFirstName.Text = "" txtLastName.Text = "" Or txtAge.Text = "" Then
            MsgBox("Student Data is not completed", MsgBoxStyle.OKOnly)
        Else
            If MsgBox("Are you sure to save Student data with Id : " & txtId.text & " ?", MsgBoxStyle.OKCancel, "Input confirm") = MsgBoxResult.Cancel Then
                ' do nothing
            Else
                Try
                    conn = GetConnect()
                    conn.Open()
                    cmdStudent = conn.CreateCommand
                    cmdStudent.CommandText = "SELECT * FROM Student WHERE Id='" & Trim(txtId.text) & " ' "
                    daStudent.SelectCommand = cmdStudent
                    daStudent.Fill(dsStudent, "Student")
                    dtStudent = dsStudent.Tables("Student")

                    If (dtStudent.Rows.Count > 0) Then
                        MsgBox("Student dengan Id " & Trim(cmbId.Text) & " already in database", MsgBoxStyle.OKOnly, "Message :")
                    Else
                        
                        cmdStudent1 = conn.CreateCommand
                        cmdStudent1.CommandText = "INSERT INTO Student(Id, FirstName, LastName,Age) VALUES('" & Trim(txtId.text) & "','" & Trim(txtFirstName.Text) & "','" & Trim(txtLastName.Text) & "','" & Trim(txtAge.Text) & "')"
                        check = cmdStudent1.ExecuteReader.RecordsAffected()
                        If check > 0 Then
                            MsgBox("Student With Id " & Trim(cmbId.Text) & " succesfully to added", MsgBoxStyle.OKOnly, "Message :")
                        Else
                            MsgBox("Student With Id " & Trim(cmbId.Text) & " Failure  to added", MsgBoxStyle.OKOnly, "Message :")
                        End If
                        Refresh_Form()
                        conn.Close()

                    End If

                Catch ex As Exception
                    MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OKOnly, "Connection Error !!")
                End Try
            End If
        End If
    End Sub

    Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
        Dim check As Integer
        Dim cmdStudent As New SqlCommand
        Dim daStudent As New SqlDataAdapter
        Dim dsStudent As New DataSet

        If txtId.text = "" Then
            MessageBox.Show("Please fill all data!!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        Else
            If txtId.text = "" Or txtFirstName.Text = "" txtLastName.Text = "" Or txtAge.Text = "" Then
			MsgBox("Student Data is not completed", MsgBoxStyle.OKOnly)
		Else
                If MsgBox("Are you sure to edit Student data with Id : " & txtId.text & " ?", MsgBoxStyle.OKCancel, "Edit confirm") = MsgBoxResult.Cancel Then
                    ' do nothing
                Else
                    Try
                        conn = GetConnect()
                        conn.Open()
                        cmdStudent = conn.CreateCommand
                        cmdStudent.CommandText = "UPDATE Student SET FirstName ='" & Trim(txtFirstName.Text) & "', LastName= '" & Trim(txtLastName.Text) & "' , Age='" & Trim(txtAge.Text) & "' WHERE Id ='" & Trim(txtId.text) & "'"
                        check = cmdStudent.ExecuteReader.RecordsAffected
                        If check > 0 Then
                            MsgBox("Student With Id " & Trim(txtId.text) & " Succesfully To Edit", MsgBoxStyle.OKOnly, "Info Update Data Student ")
                        Else
                            MsgBox("Student With Id " & Trim(txtId.text) & " Failure To Edit", MsgBoxStyle.OKOnly, "Info Update Data Student ")
                        End If
                        Refresh_Form()
                        conn.Close()

                    Catch ex As Exception
                        MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OKOnly, "Connection Error !!")
                    End Try
                End If
            End If
        End If
    End Sub

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        Dim check As Integer
        Dim conn As SqlConnection
        Dim cmdStudent As New SqlCommand
        Dim daStudent As New SqlDataAdapter
        Dim dsStudent As New DataSet

        If txtId.text <> "" Then
            If MsgBox("Are you sure to delete data with Id : " & txtId.text & " ?", MsgBoxStyle.OKCancel, "Delete confirm") = MsgBoxResult.Cancel Then
                ' do nothing
            Else
                conn = GetConnect()
                Try
                    conn.Open()
                    cmdStudent = conn.CreateCommand
                    cmdStudent.CommandText = "DELETE FROM Student WHERE Id ='" & Trim(txtId.text) & "'"
                    check = cmdStudent.ExecuteReader.RecordsAffected
                    If check > 0 Then
                        MsgBox("Student With Id " & Trim(txtId.text) & " Succesfully To Delete", MsgBoxStyle.OKOnly, "Info Delete Student")
                    Else
                        MsgBox("Student With Id " & Trim(txtId.text) & " Failure To Delete", MsgBoxStyle.OKOnly, "Info Delete Student")
                    End If
                    Refresh_Form()
                    conn.Close()

                Catch ex As Exception
                    MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OKOnly, "Connection Error !!")
                End Try
            End If
        Else
                MsgBox("fill Id Student on Id textbox which student to delete!!", MsgBoxStyle.OKOnly, "Info Data")
            End If
    End Sub