Tuesday 16 April 2013

How to insert update select and delete data into datagridview through textboxes in vb.net

Aslam O Alaikum!

My todays topic of discussion is "How to insert update select and delete data into datagridview  through textboxes in vb.net".

first i have 2 form i n one form we have only one grid and one button 
and in the second form we have 7 text box field and we have 4 button
here i used databinding and binding manager concept
in above that is the pics of grid view


form1


Imports System.Windows.Forms

Imports System.Data.SqlClient
Imports System.Data.DataRow
Public Class Form1
    Dim adapter As SqlDataAdapter
    Dim ds As DataSet
    Dim connstring As SqlConnection
    Dim bmb As BindingManagerBase


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
        adapter.InsertCommand = New SqlCommand("INSERT INTO person ( id,name,fathername,address,marks,rollno,qualification) " & "VALUES (@id,@name,@fathername,@address,@marks,@rollno,@qualification)", connstring)
        adapter.InsertCommand.Parameters.Add("@id", SqlDbType.Int, 15, "id")
        adapter.InsertCommand.Parameters.Add("@name", SqlDbType.NVarChar, 15, "name")
        adapter.InsertCommand.Parameters.Add("@fathername", SqlDbType.NVarChar, 100, "fathername")
        adapter.InsertCommand.Parameters.Add("@address", SqlDbType.NVarChar, 100, "address")
        adapter.InsertCommand.Parameters.Add("@marks", SqlDbType.Int, 15, "marks")
        adapter.InsertCommand.Parameters.Add("@rollno", SqlDbType.Int, 15, "rollno")
        adapter.InsertCommand.Parameters.Add("@qualification", SqlDbType.NVarChar, 100, "qualification")
    

        adapter.UpdateCommand = New SqlCommand("UPDATE person SET id = @id,name=@name,fathername=@fathername,address=@address,marks=@marks,rollno=@rollno,qualification=@qualification " & "WHERE id = @id", connstring)

        adapter.UpdateCommand.Parameters.Add("@id", SqlDbType.Int, 15, "id")
        adapter.UpdateCommand.Parameters.Add("@name", SqlDbType.NVarChar, 15, "name")
        adapter.UpdateCommand.Parameters.Add("@fathername", SqlDbType.NVarChar, 100, "fathername")
        adapter.UpdateCommand.Parameters.Add("@address", SqlDbType.NVarChar, 100, "address")
        adapter.UpdateCommand.Parameters.Add("@marks", SqlDbType.Int, 15, "marks")
        adapter.UpdateCommand.Parameters.Add("@rollno", SqlDbType.Int, 15, "rollno")
        adapter.UpdateCommand.Parameters.Add("@qualification", SqlDbType.NVarChar, 100, "qualification")

        adapter.DeleteCommand = New SqlCommand("DELETE FROM person WHERE id = @id", connstring)

        adapter.DeleteCommand.Parameters.Add("@id", SqlDbType.Int, 15, "id")
        
        adapter.Update(ds)
    End Sub

    

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.PersonTableAdapter.Fill(Me.AhmadDataSet.person)
        adapter = New SqlDataAdapter
        ds = New DataSet
        connstring = New SqlConnection(checking.My.Settings.constring)
        adapter.SelectCommand = New SqlCommand("SELECT id, name,fathername,address,marks,rollno,qualification FROM person", connstring)
        adapter.Fill(ds)
        
        DataGridView1.DataSource = ds.Tables(0)
        ' DataGridView1.Columns(0).Visible = False
        'DataGridView1.Columns(1).ReadOnly = True
        ' DataGridView1.Columns(2).ReadOnly = True
        ' DataGridView1.Columns(3).ReadOnly = True
        Button1.Hide()
    End Sub
    'Private Sub BindControls()

    'End Sub


   

  
   
    Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick

    End Sub


    Private Sub DataGridView1_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellDoubleClick

        Form2.Show()
        Me.Hide()
    End Sub
End Class

form2



Imports System.Windows.Forms
Imports System.Data.SqlClient
Imports System.Data.DataRow


Public Class Form2

    Dim adapter As SqlDataAdapter
    Dim ds As DataSet
    Dim connstring As SqlConnection
    Dim bmb As BindingManagerBase




    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


        Form1.PersonTableAdapter.Fill(Form1.AhmadDataSet.person)

        adapter = New SqlDataAdapter
        ds = New DataSet
        connstring = New SqlConnection(checking.My.Settings.constring)
        adapter.SelectCommand = New SqlCommand("SELECT id, name,fathername,address,marks,rollno,qualification FROM person", connstring)
        adapter.Fill(ds)
        BindControls()
        bmb = BindingContext(ds.Tables(0))
        bmb.Position = 0
        Form1.DataGridView1.DataSource = ds.Tables(0)
    End Sub
    Private Sub BindControls()
        TextBox1.DataBindings.Add("Text", ds.Tables(0), "id")
        TextBox2.DataBindings.Add("Text", ds.Tables(0), "name")
        TextBox3.DataBindings.Add("text", ds.Tables(0), "fathername")
        TextBox4.DataBindings.Add("text", ds.Tables(0), "address")
        TextBox5.DataBindings.Add("Text", ds.Tables(0), "marks")
        TextBox6.DataBindings.Add("Text", ds.Tables(0), "rollno")
        TextBox7.DataBindings.Add("text", ds.Tables(0), "qualification")

    End Sub


   

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        bmb.Position = bmb.Position + 1
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        bmb.Position = bmb.Position - 1
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

        Dim dr As DataRow = ds.Tables(0).NewRow()
        ds.Tables(0).Rows.Add(dr)
        bmb.Position = bmb.Count - 1
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click

        Form1.Show()
        Me.Hide()
    End Sub
End Class

i hope you will be understand my that work and it will be helpfull to u


ALLAH blessed me and all the readers. Keep remember me in your prayers.