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 SubInserting 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 TryDelete 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 TryUpdate 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.
I have attached the entire project files with this code submission so that the design as well as coding will be more clear and can be used by you guys. Hope this will help many of you guys.!! cheers.!
It is useful to me. It is simple and easy to understand.
thankyou