Posted by: Sourav | June 1, 2015

Undo the delete from database ,VB.Net


Ok so I want to give an option to the users  to undo the deleted data they just deleted

So I added a groupbox containing labels and corresponding textboxes such as ID,Name,Age,Username,Password and a checkbox having text property of active,also a button having text like undo delete which is disabled by default

Now my delete button contain these line of codes

 Private Sub Button3_Click_1(sender As Object, e As EventArgs) Handles Button3.Click
        If TextBox7.Text <> “” Then
            If MsgBox(“Do you really want to delete ” & RTrim(TextBox7.Text) & ” ?”, MsgBoxStyle.YesNoCancel) = MsgBoxResult.Yes Then

                Dim id As Integer = sqlcon.findid(“select ID from test where Name = ‘” & TextBox7.Text & “‘”)

                TextBox19.Text = sqlcon.bringbackdata(“select ID from test where Name = ‘” & TextBox7.Text & “‘”)
                TextBox18.Text = sqlcon.bringbackdata(“select Name from test where Name = ‘” & TextBox7.Text & “‘”)
                TextBox17.Text = sqlcon.bringbackdata(“select Age from test where Name = ‘” & TextBox7.Text & “‘”)
                TextBox16.Text = sqlcon.bringbackdata(“select Username from test where Name = ‘” & TextBox7.Text & “‘”)
                TextBox15.Text = sqlcon.bringbackdata(“select Password from test where Name = ‘” & TextBox7.Text & “‘”)
                If (sqlcon.bringbackdata(“select Active from test where Name = ‘” & TextBox7.Text & “‘”)) = True Then
                    Active.Checked = True
                Else
                    Active.Checked = False

                End If

                ‘ MsgBox(id)
                sqlcon.dataupdate(“Delete from test where Name = ‘” & TextBox7.Text & “‘”)

                sqlcon.idorganize(id)
                populategrid()
                TextBox7.Clear()
                TextBox7.Focus()
                If MsgBox(“Undo the delete ” & RTrim(TextBox7.Text) & ” ?”, MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
                    Button5.Enabled = True
                Else
                    TextBox19.Text = “”
                    TextBox18.Text = “”
                    TextBox17.Text = “”
                    TextBox16.Text = “”
                    TextBox15.Text = “”
                    Active.Checked = False
                    Button5.Enabled = False

                End If

            End If
        End If
    End Sub

My Undo Delete button have the code

 Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
        If MsgBox(“Do you really want to undo the deleted row with the name ” & RTrim(TextBox18.Text) & ” ?”, MsgBoxStyle.YesNoCancel) = MsgBoxResult.Yes Then
            Dim activevalue As Integer
            If Active.Checked = True Then
                activevalue = 1
            Else
                activevalue = 0

            End If
            sqlcon.organizeafterdelete(Convert.ToInt32(TextBox19.Text), TextBox18.Text, Convert.ToInt32(TextBox17.Text), TextBox16.Text, TextBox15.Text, activevalue)
            populategrid()
            ‘MsgBox(“hello”)
        Else

        End If

    End Sub

The subroutine organizeafterdelete is defined in my Sqlcon class

I have created an object of Sqlcon class in my form

Public Class Form1
    Dim sqlcon As New Sqlcon

The subroutine I mentioned resides in Sqlcon class

it is

Public Sub organizeafterdelete(deletedid As Integer, name As String, age As Integer, username As String, password As String, activevalue As Integer)
        MsgBox(deletedid)

        Dim maxid As Integer = findlastid()
        MsgBox(maxid)

        If deletedid < maxid Then
            For i As Integer = maxid To deletedid Step -1
                Try
                    sqlcon.Open()
                    sqlcommand = New SqlCommand(“UPDATE test  SET ID = ‘” & (i + 1) & “‘ where(ID = ‘” & i & “‘)”, sqlcon)
                    sqlcommand.ExecuteNonQuery()

                    sqlcon.Close()

                Catch ex As Exception
                    MsgBox(ex.Message)
                    If sqlcon.State = ConnectionState.Open Then
                        sqlcon.Close()
                    End If
                End Try

            Next
            Try
                sqlcon.Open()
                sqlcommand = New SqlCommand(“insert into test (ID,Name,Age,Username,Password,Active) values(‘” & deletedid & “‘, ‘” & name & “‘, ‘” & age & “‘, ‘” & username & “‘, ‘” & password & “‘, ‘” & activevalue & “‘)”, sqlcon)
                MsgBox(sqlcommand.CommandText)
                sqlcommand.ExecuteNonQuery()

                sqlcon.Close()

            Catch ex As Exception
                MsgBox(ex.Message)
                If sqlcon.State = ConnectionState.Open Then
                    sqlcon.Close()
                End If
            End Try
        End If
    End Sub

Another Subroutine named populategrid I used defined in the form itself

Public Sub populategrid2()
        If sqlcon.hasconnection = True Then
            sqlcon.populategridview(“select * from test order by ID”)
            If sqlcon.sqldataset.Tables.Count > 0 Then
                ‘DataGridView1.DataSource = sqlcon.sqldataset.Tables(0)
                DataGridView2.DataSource = sqlcon.sqldataset.Tables(0)
            End If
        End If

    End Sub

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: