insert update delete in vb.net with sql server
  • hi today i am sharing how can we use basic insert, update, delete and search functionality in datagridview using vb.net and sql database.
    This article is vb.net version of this c# article
    http://codingresolved.com/discussion/1755/insert-update-delete-in-datagridview-in-c-windows-application/p1

    Source code is also attached you can also download it.
    First create table in database with name of tblSale and my database name is dbTest2.

    CREATE TABLE [dbo].[tblSale](
        [SaleNo] [int] NULL,
        [ProductCode] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [Qty] [int] NULL,
        [Price] [decimal](18, 2) NULL
    )



    image


    Imports System.Data.SqlClient
    Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    dataGridView1.AutoGenerateColumns = False
    End Sub

    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
    Dim con As SqlConnection = New SqlConnection("Data Source=.\SqlExpress;Initial Catalog=dbTest2;Integrated Security=True")
    Dim dt As DataTable = New DataTable
    Dim sda As SqlDataAdapter = New SqlDataAdapter(("select * from tblSale where SaleNo='" _
    + (txtSearch.Text + "'")), con)
    sda.Fill(dt)
    dataGridView1.DataSource = dt
    End Sub

    Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click
    Dim objCon As SqlConnection = New SqlConnection("Data Source=.\SqlExpress;Initial Catalog=dbTest2;Integrated Security=True")
    Dim objCmd As SqlCommand = New SqlCommand
    objCmd.Connection = objCon
    Dim query As String = ""
    Dim i As Integer = 0
    Do While (i _
    < (dataGridView1.Rows.Count - 1))
    query = (query + "insert into tblSale values(@SaleNo,@ProductCode,@Qty,@Price) ")
    query = query.Replace("@SaleNo", ("'" _
    + (txtSearch.Text + "'")))
    query = query.Replace("@ProductCode", ("'" _
    + (dataGridView1.Rows(i).Cells("ProductCode").Value.ToString + "'")))
    query = query.Replace("@Qty", ("'" _
    + (dataGridView1.Rows(i).Cells("Qty").Value.ToString + "'")))
    query = query.Replace("@Price", ("'" _
    + (dataGridView1.Rows(i).Cells("Price").Value.ToString + "'")))
    i = (i + 1)
    Loop
    objCmd.CommandText = query
    objCon.Open()
    Dim j As Integer = objCmd.ExecuteNonQuery
    If (j > 0) Then
    MessageBox.Show("Record Inserted", "www.codingresolved.com")
    dataGridView1.Rows.Clear()
    End If
    objCon.Close()
    End Sub

    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
    Dim objCon As SqlConnection = New SqlConnection("Data Source=.\SqlExpress;Initial Catalog=dbTest2;Integrated Security=True")
    Dim objCmd As SqlCommand = New SqlCommand
    objCmd.Connection = objCon
    Dim query As String = ("delete from tblSale where SaleNo='" _
    + (txtSearch.Text + "'"))
    Dim i As Integer = 0
    Do While (i _
    < (dataGridView1.Rows.Count - 1))
    query = (query + " insert into tblSale values(@SaleNo,@ProductCode,@Qty,@Price) ")
    query = query.Replace("@SaleNo", ("'" _
    + (txtSearch.Text + "'")))
    query = query.Replace("@ProductCode", ("'" _
    + (dataGridView1.Rows(i).Cells("ProductCode").Value.ToString + "'")))
    query = query.Replace("@Qty", ("'" _
    + (dataGridView1.Rows(i).Cells("Qty").Value.ToString + "'")))
    query = query.Replace("@Price", ("'" _
    + (dataGridView1.Rows(i).Cells("Price").Value.ToString + "'")))
    i = (i + 1)
    Loop
    objCmd.CommandText = query
    objCon.Open()
    Dim j As Integer = objCmd.ExecuteNonQuery
    If (j > 0) Then
    MessageBox.Show("Record Updated", "www.codingresolved.com")
    dataGridView1.DataSource = Nothing
    End If
    objCon.Close()
    End Sub

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
    Dim objCon As SqlConnection = New SqlConnection("Data Source=.\SqlExpress;Initial Catalog=dbTest2;Integrated Security=True")
    Dim objCmd As SqlCommand = New SqlCommand
    objCmd.Connection = objCon
    Dim query As String = ("delete from tblSale where SaleNo='" _
    + (txtSearch.Text + "'"))
    objCmd.CommandText = query
    objCon.Open()
    Dim j As Integer = objCmd.ExecuteNonQuery
    If (j > 0) Then
    MessageBox.Show("Record Deleted", "www.codingresolved.com")
    dataGridView1.DataSource = Nothing
    End If
    objCon.Close()
    End Sub
    End Class
    InsertUpdateDeleteSearchVb.jpg
    443 x 373 - 32K
    InsertUpdateDeleteVB.zip
    74K
  • 2 Comments sorted by
  • SALAM HI 
    VERY NICE BUT IF YOU USE THE DATASET IS THE BEST IN MY OPINION AND THANK YOU MR.WAQAS
  • good work :)

Howdy, Stranger!

It looks like you're new here. If you want to get involved, or you want to Ask a new Question, Please Login or Create a new Account by Clicking below

Login with Facebook

Tagged

Popular Posts of the Week