Wednesday, January 16, 2013

Grid view Binding Data,insert,Update,Delete into DB Code In Vb .Net


Are you looking for the code to bind data to the Grid View control and then perform insert,update and delete operation on it that will update in the database as well? Then this article will surely help you.


Binding Data


Using ado dot net connection objects data table , data adapter and data set i have performed binding the table from DB to Data Grid View.

Insert,Update,Delete Data in Grid View in VB.Net:


Insert,update and delete in grid view control the changes will take place in grid view as well in the db which has been binded to the data grid.

If you have the basic knowledge of binding the data with grid view and have some knowledge about the connection objects you can use the code as you want.

I have developed the following code in VB .Net 3.5 version 2008 release and MSSQL 2005.

Binding Database table to the data grid view:



Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim str1 As String = "select * from userinfo"
Dim da As New SqlDataAdapter(str1, con)
Dim dt As New DataTable
con.Open()
da.Fill(dt)
con.Close()
DataGridView1.DataSource = dt
End Sub

Inserting into Grid view as well as into DB:


Try
Dim str2 As String
Dim count As Integer
con.Open()
Dim str4 As String = "select count (*) from userinfo"
Dim str5 As New SqlCommand(str4, con)
Dim count1 As Integer = str5.ExecuteScalar()
con.Close()
If (DataGridView1.Rows.Count > 0) Then
For i As Integer = 0 To DataGridView1.Rows.Count - 2
str2 = "select count (*) from userinfo where userid = '" + DataGridView1.Rows(i).Cells(0).Value + "'"
str3 = New SqlCommand(str2, con)
con.Open()
count = str3.ExecuteScalar
con.Close()
If (count = 0) Then
Dim sqlcmd = New SqlCommand("insert into userinfo values('" + DataGridView1.Rows(i).Cells(0).Value + "','" + DataGridView1.Rows(i).Cells(1).Value + "')", con)
con.Open()
sqlcmd.ExecuteNonQuery()
con.Close()
End If
Next
End If
con.Open()
Dim str6 As String = "select count (*) from userinfo"
Dim str7 As New SqlCommand(str6, con)
Dim count2 As Integer = str7.ExecuteScalar()
con.Close()
If count1 = count2 Then
MsgBox("No data to be inserted")
Else
MsgBox("successfully inserted")
End If
Catch ex As Exception
MsgBox(ex.Message)
End Try

Delete From Grid view as well from DB:


Try
Dim str2 As String
str2 = "delete from userinfo where userid = '" + DataGridView1.SelectedCells(0).Value + "'"
con.Open()
Dim str3 As New SqlCommand(str2, con)
str3.ExecuteNonQuery()
con.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try

Update contents in Grid view as well in DB:


Try
Dim str2 As String
str2 = "update userinfo set password='" + DataGridView1.SelectedCells(0).Value + "' where userid = '" + DataGridView1.SelectedCells(1).Value + "'"
con.Open()
Dim str3 As New SqlCommand(str2, con)
str3.ExecuteNonQuery()
con.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try

**The table i have used to manipulate the insert,delete and update operation consists of 2 columns Userid,password. Userid is primary key which cannot be updated so.**

No comments :

Post a Comment