Posted by: Sourav | June 1, 2015

After Delete a row,Reorganize Data in database,so that no row is blank,VB.Net


My delete button code is

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 & “‘”)
                ‘ MsgBox(id)
                sqlcon.dataupdate(“Delete from test where Name = ‘” & TextBox7.Text & “‘”)

                sqlcon.idorganize(id)
                populategrid()
                TextBox7.Clear()
                TextBox7.Focus()

            End If
        End If
    End Sub

 

First let me explain the populategrid subroutine,I created this inside the main form so that after delete the datagridview refresh and show the changes instantly

The code is

Public Sub populategrid()
        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

I have class file named Sqlcon.vb which have a public class Sqlcon

I have created subroutines such as dataupdate,idorganize and populateid,I am accessing them using an object of Sqlcon class which I created in my form

Public Class Form1
    Dim sqlcon As New Sqlcon

 

The code for dataupdate is

Public Sub dataupdate(query As String)
        Try
            sqlcon.Open()
            sqlcommand = New SqlCommand(query, sqlcon)
            Dim change As Integer = sqlcommand.ExecuteNonQuery
            sqlcon.Close()
            If change = 0 Then
                MsgBox(“you haven’t found what you where looking for”)
            Else
                MsgBox(change & ” rows affected”)

            End If

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

The code for idorganize is

 Public Sub idorganize(id As Integer)
        Dim lastid As Integer = findlastid()
        ‘MsgBox(lastid)
        If id < lastid Then
            For i As Integer = (id + 1) To lastid
                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
        End If
    End Sub

 

As we can see idorganize subroutine uses another function named findlastid which returns an integer

The code for findlastid is

Public Function findlastid() As Integer

        Dim query As String = “SELECT MAX(ID) FROM test”
       
        Dim sqlcommand As New SqlCommand(query, sqlcon)
        sqlcommand.CommandType = CommandType.Text

        Dim id As Integer = 0
        Try
            sqlcon.Open()
            id = Convert.ToInt32(sqlcommand.ExecuteScalar())
            Return id
        Catch ex As Exception
            MsgBox(ex.Message)
            Return 0
        Finally
            sqlcon.Close()
        End Try
    End Function

 

In the public class Sqlcon the sqlcommand and sqlcon are defined like this

Public Class Sqlcon
    Public sqlcon As New SqlConnection With {.ConnectionString = “Server=HOMEPC\SQLEXPRESS;Database=test;User=sa;Pwd=******;”}

    Public sqlcommand As SqlCommand
    Public sqldataadapter As SqlDataAdapter
    Public sqldataset As DataSet

 

So doing that my database reorganizes itself after delete ,I feel so happy

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: