add,save,delete,find operations with datagridview

Introduction

After a long search and paining eyes, i myself tried to workout the manipulations like add,delete,save,find operations with datagridview in VB.NET through which a master data and details data are connected.

Background

Using the code

In the VB.NET - New Windows Forms:
Place 3 textboxes ,6 buttons, 1 datagridview
6 buttons are for :
new, save, clear, find, add row(for datagridview purpose), delete row.

Import the necessary things for the database connection and operations before the public class form1 line. I use PostGres here mainly for the purpose that it have the serial datatype which is very useful for autoincrement of any field. Moreover i use primary key for the tables.

In the database create 2 tables as below.
TABLE STRUCTURE IN DATABASE

1st table:

GBDEVEL=# \d test1;
Table "erp.test1"
Column | Type | Modifiers
-------- +----------------------- +-----------
eno | integer | not null -- primarykey
ename | character varying(10) |
salary | integer |
Indexes:
"test1_pkey" PRIMARY KEY, btree (eno)

use serial datatype if you are using postgres.

2nd table:


GBDEVEL=# \d test2;
Table "erp.test2"
Column | Type | Modifiers
-------- +--------- +-----------
enum | integer | -- foreign key
basic | integer |
hra | integer |

Foreign-key constraints:
"fk_t1" FOREIGN KEY (enum) REFERENCES test1(eno)

This article provides you that storing the data in the textboxes and in the datagridview in the database at 2 different tables, which are referred by foreignkey through primarykey. For Example, the particular eno will have the different basic,hra pack. May get confused...!!! Ok go further.

Imports Npgsql
Imports System.Data.SqlClient
Imports NpgsqlTypes
Imports System.Windows.Forms
Public Class Form1
Dim con As NpgsqlConnection
Dim NpgDA, NpgDA1 As NpgsqlDataAdapter
Dim ds As DataSet
Dim drow, drow1 As DataRow
Dim scb, scb1 As NpgsqlCommandBuilder
Dim cmd As NpgsqlCommand
Dim sdr As NpgsqlDataReader
Private Sub btnAddrow_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddrow.Click
Dim item As New DataGridViewRow
DataGridView1.AllowUserToAddRows = True
item.CreateCells(DataGridView1)
End Sub
Private Sub btnDelRow_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelRow.Click
For Each rows As DataGridViewRow In DataGridView1.SelectedRows
DataGridView1.Rows.Remove(rows)
Next
End Sub
Private Sub DataGridView1_CellEndEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellEndEdit
DataGridView1.AllowUserToAddRows = False
End Sub
Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
DataGridView1.Rows.Clear()
End Sub
Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
DataGridView1.Rows.Clear()
End Sub
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
Dim col1 As String
Dim col2 As String
Dim i As Integer
'trans = con.BeginTransaction
NpgDA = New NpgsqlDataAdapter("select * from test1", con)
NpgDA1 = New NpgsqlDataAdapter("select * from test2", con)
scb = New NpgsqlCommandBuilder(NpgDA)
scb1 = New NpgsqlCommandBuilder(NpgDA1)
ds = New DataSet
NpgDA.Fill(ds, "test1")
NpgDA1.Fill(ds, "test2")
'DataGridView1.DataSource = ds.Tables(1)
drow = ds.Tables(0).NewRow
'   drow1 = ds.Tables(1).NewRow
drow(0) = TextBox1.Text
drow(1) = TextBox2.Text
drow(2) = TextBox3.Text
For i = 0 To DataGridView1.Rows.Count - 1
drow1 = ds.Tables(1).NewRow
col1 = DataGridView1.Rows.Item(i).Cells(0).Value
col2 = DataGridView1.Rows.Item(i).Cells(1).Value
drow1(1) = col1
drow1(2) = col2
ds.Tables(1).Rows.Add(drow1)
NpgDA1.Update(ds, "test2")
Next
ds.Tables(0).Rows.Add(drow)
NpgDA.Update(ds, "test1")
'  trans.Commit()
MsgBox("Data Added", MsgBoxStyle.Information)
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
con = New NpgsqlConnection("Server=10.3.2.1;User Id=erp;Password=erp;Database=GBDEVEL")
con.Open()
End Sub
Private Sub btnFind_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFind.Click
Dim s As String
Dim scm As New NpgsqlCommand
s = "select * from test1 where eno=" & TextBox1.Text & ""
scm = New NpgsqlCommand(s, con)
sdr = scm.ExecuteReader
If sdr.Read = True Then
TextBox2.Text = sdr(1).ToString
TextBox3.Text = sdr(2).ToString
Else
MsgBox("No such record")
End If
sdr.Close()
scm.Cancel()
End Sub
End Class

Type the Empno in the textbox1 and click find will produce the ename and salary.

i know many changes and updations, clear explanations to be done. Sure, i'll do it soon.

Points of Interest

saving the rows of datagridview for a particular eno and looping operations, i got very much confused and irritated many times. Don't forget to use primary keys. In MSSQL you have to write procedure for autoincrement of eno.

BEST OF LUCK!

History

TO BE UPDATED. PLS WAIT FOR MORE ENJOYMENT!!!! :) :)

来自：http://www.ueow.com

来自：http://www.ueow.com