How to Update, Delete, Search, Insert in GridView in vb.Net Windows Application

Introduction

This article shows how to insert, update and delete and search records in a DataGridView in a vb.net Windows Forms application.There are three steps to insert, Update,Delete and search data into database.

1. Create table in database.
2. Design .aspx page.
3. Write Code on .aspx.CS page

Step 1. Create table in Database

Table Name:  Customer

Query for creating a table

Step 2. Create/design .aspx page

Step 3. C# Code

Imports System.Data
Imports System.Data.SqlClient

Public Class Form1

    Dim con As SqlConnection
    Dim cmd As SqlCommand

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        Me.CustomerTableAdapter.Fill(Me.Database1DataSet.customer)
        bindgrid()

    End Sub
    Public Sub bindgrid()
        Dim con As New SqlConnection("Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\KRUNAL\Documents\Visual Studio 2012\Projects\BloggerData\InsertUpdateDelete\InsertUpdateDelete\Database1.mdf;Integrated Security=True")
        con.Open()
        Dim da As New SqlDataAdapter("select * from customer", con)
        Dim ds As New DataSet()
        da.Fill(ds, "customer")
        DataGridView1.DataSource = ds.Tables("customer")
        con.Close()

    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim con As New SqlConnection("Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\KRUNAL\Documents\Visual Studio 2012\Projects\BloggerData\InsertUpdateDelete\InsertUpdateDelete\Database1.mdf;Integrated Security=True")
        Dim cmd As New SqlCommand()
        con.Open()
        cmd.CommandText = "insert into customer values(@firstname,@lastname,@address,@email,@contactno)"
        cmd.Connection = con
        cmd.Parameters.AddWithValue("@firstname", TextBox1.Text)
        cmd.Parameters.AddWithValue("@lastname", TextBox2.Text)
        cmd.Parameters.AddWithValue("@address", TextBox3.Text)
        cmd.Parameters.AddWithValue("@email", TextBox4.Text)
        cmd.Parameters.AddWithValue("@contactno", TextBox5.Text)
        cmd.ExecuteNonQuery()
        MessageBox.Show("Inserted Data...")
        bindgrid()
        Me.CustomerTableAdapter.Fill(Me.Database1DataSet.customer)
        con.Close()
        TextBox1.Clear()
        TextBox2.Clear()
        TextBox3.Clear()
        TextBox4.Clear()
        TextBox5.Clear()

    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Dim con As New SqlConnection("Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\KRUNAL\Documents\Visual Studio 2012\Projects\BloggerData\InsertUpdateDelete\InsertUpdateDelete\Database1.mdf;Integrated Security=True")
        Dim cmd As New SqlCommand()
        con.Open()
        cmd.CommandText = "update customer set firstname=@firstname,lastname=@lastname,address=@address,emailid=@email,contactno=@contactno where id='" + ComboBox1.Text + "'"

        cmd.Connection = con
        cmd.Parameters.AddWithValue("@firstname", TextBox1.Text)
        cmd.Parameters.AddWithValue("@lastname", TextBox2.Text)
        cmd.Parameters.AddWithValue("@address", TextBox3.Text)
        cmd.Parameters.AddWithValue("@email", TextBox4.Text)
        cmd.Parameters.AddWithValue("@contactno", TextBox5.Text)
        cmd.ExecuteNonQuery()
        MessageBox.Show("Updated  Data...")
        bindgrid()
        Me.CustomerTableAdapter.Fill(Me.Database1DataSet.customer)
        con.Close()
        TextBox1.Clear()
        TextBox2.Clear()
        TextBox3.Clear()
        TextBox4.Clear()
        TextBox5.Clear()
    End Sub

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Dim con As New SqlConnection("Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\KRUNAL\Documents\Visual Studio 2012\Projects\BloggerData\InsertUpdateDelete\InsertUpdateDelete\Database1.mdf;Integrated Security=True")
        Dim cmd As New SqlCommand()
        con.Open()
        cmd.CommandText = "delete from customer where id='" + ComboBox1.Text + "'"
        cmd.Connection = con
        cmd.ExecuteNonQuery()
        MessageBox.Show("Deleted  Data...")
        bindgrid()
        Me.CustomerTableAdapter.Fill(Me.Database1DataSet.customer)
        con.Close()
       
    End Sub

    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        Dim con As New SqlConnection("Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\KRUNAL\Documents\Visual Studio 2012\Projects\BloggerData\InsertUpdateDelete\InsertUpdateDelete\Database1.mdf;Integrated Security=True")
        con.Open()
        Dim da As New SqlDataAdapter("select * from customer where id='" + ComboBox1.Text + "'", con)
        Dim ds As New DataSet()
        da.Fill(ds, "customer")
        TextBox1.Text = ds.Tables(0).Rows(0)("firstname").ToString()
        TextBox2.Text = ds.Tables(0).Rows(0)("lastname").ToString()
        TextBox3.Text = ds.Tables(0).Rows(0)("address").ToString()
        TextBox4.Text = ds.Tables(0).Rows(0)("emailid").ToString()
        TextBox5.Text = ds.Tables(0).Rows(0)("contactno").ToString()
        con.Close()
    End Sub

End Class

Thank You...