save.txt

this is the source code to add ,delete ,edit in vb.net

//This code to save, Edit and delete data in

 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 TryEnd 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' 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