Master Detail programming in Visual Studio 2005 (VB.NET)

Master Detail programming in Visual Studio 2005 (VB.NET)

Start Visual Studio create a new project

Create a database in MS-ACCESS in c:\temp as data.mdb

create following three table

Table Name : PURCH_HDR

INV_NO Number Long Integer primary key
CUSTOMER Number Integer
PDATE Date/Time Date/Time
TOT_AMT Number double

Table Name : PURCH_DTL

IDN Autonumber
INV_NO Number Long Integer
ITEM Number integer
RATE Number double
QTY Number double
AMOUNT Number double

Table Name : Item

ID number integer
ITEM_NAME text
Rate number double

Table Name : customer

ID integer
CustId integer
CustName text
address text

Create the following objects on Form1

Textbox1

Textbox2

Textbox3

Combobox1

DataGridView

Put your controls as follows or you may design as you like because I don’t have good concept on designing form.

Add a bindingnavigator in your form from toolbox

Add three toolstripbutton to your bindingnavigator and name those save, add & delete.

Copy paste the following code to your form code

Imports System.Data.OleDb

Public Class Form1

Friend WithEvents Column1 As System.Windows.Forms.DataGridViewTextBoxColumn

Friend WithEvents Column6 As System.Windows.Forms.DataGridViewTextBoxColumn

Friend WithEvents DataGridViewTextBoxColumn2 As System.Windows.Forms.DataGridViewComboBoxColumn

Friend WithEvents Column3 As System.Windows.Forms.DataGridViewTextBoxColumn

Friend WithEvents Column4 As System.Windows.Forms.DataGridViewTextBoxColumn

Friend WithEvents Column5 As System.Windows.Forms.DataGridViewTextBoxColumn

Dim DataGridViewCellStyle2 As System.Windows.Forms.DataGridViewCellStyle = New System.Windows.Forms.DataGridViewCellStyle

Dim BsHeader As New BindingSource

Dim bsDetail As New BindingSource

Dim bsCustomer As New BindingSource

Dim ds As New DataSet

Dim ItemBindingSource As New BindingSource

Dim da As OleDbDataAdapter

Dim da1 As OleDbDataAdapter

Dim da2 As OleDbDataAdapter

Dim dla As New OleDbDataAdapter

Dim dlC As New OleDbDataAdapter

Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp\data.mdb")

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

assigngrid()

da = New OleDbDataAdapter("select * from purch_hdr", cn)

Dim da1 As New OleDbDataAdapter("select * from purch_Dtl", cn)

Dim da2 As New OleDbDataAdapter("select * from Item", cn)

Dim dlc As New OleDbDataAdapter("select * from customer", cn)

da.Fill(ds, "Purch_hdr")

da1.Fill(ds, "purch_dtl")

da2.Fill(ds, "Item")

dlc.Fill(ds, "customer")

ds.Relations.Add("MSt_dtl", ds.Tables!purch_hdr.Columns!inv_no, ds.Tables!purch_dtl.Columns!inv_no)

ds.Relations.Add("PURCH_DTL_ITEM", ds.Tables!Item.Columns!ID, ds.Tables!Purch_dtl.Columns!Item)

ds.Relations.Add("Purch_hdr_customer", ds.Tables!customer.Columns!custid, ds.Tables!purch_hdr.Columns!customer)

BsHeader.DataMember = "Purch_hdr"

BsHeader.DataSource = ds

bsDetail.DataMember = "MSt_dtl"

bsDetail.DataSource = BsHeader

ItemBindingSource.DataMember = "Item"

ItemBindingSource.DataSource = ds

bsCustomer.DataMember = "customer"

bsCustomer.DataSource = ds

ComboBox1.DataSource = bsCustomer

ComboBox1.DisplayMember = "CustName"

ComboBox1.ValueMember = "CustId"

'/ data binding to header part

TextBox1.DataBindings.Add("text", BsHeader, "inv_no")

TextBox2.DataBindings.Add("text", BsHeader, "customer")

TextBox3.DataBindings.Add("text", BsHeader, "tot_amt")

ComboBox1.DataBindings.Add("selectedvalue", BsHeader, "customer")

'// binding to detail part

AssignCombo()

DataGridView1.AutoGenerateColumns = False

DataGridView1.DataSource = BsHeader

DataGridView1.DataMember = "MSt_dtl"

Me.DataGridView1.TabIndex = 9

BindingNavigator1.BindingSource = BsHeader

End Sub

Private Sub AssignCombo()

Me.DataGridViewTextBoxColumn2.DataPropertyName = "ITEM"

Me.DataGridViewTextBoxColumn2.DataSource = ItemBindingSource

Me.DataGridViewTextBoxColumn2.DisplayMember = "ITEM_NAME"

Me.DataGridViewTextBoxColumn2.HeaderText = "ITEM NAME"

Me.DataGridViewTextBoxColumn2.Name = "DataGridViewTextBoxColumn2"

Me.DataGridViewTextBoxColumn2.Resizable = System.Windows.Forms.DataGridViewTriState.[True]

Me.DataGridViewTextBoxColumn2.SortMode = System.Windows.Forms.DataGridViewColumnSortMode.Automatic

Me.DataGridViewTextBoxColumn2.ValueMember = "ID"

End Sub

Private Sub SAVE_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SAVE.Click

Try

Me.Validate()

Dim oleparam As New OleDbParameter

da.SelectCommand = New OleDbCommand("select * from purch_hdr")

da.SelectCommand.Connection = cn

da.InsertCommand = New OleDbCommand("insert into purch_hdr ( INV_NO, CUSTOMER, TOT_AMT ) values ( @INV_NO, @CUSTOMER, @TOT_AMT )")

da.InsertCommand.Connection = cn

da.InsertCommand.Parameters.Add("@INV_NO", OleDbType.Integer, 50).Value = Me.TextBox1.Text

da.InsertCommand.Parameters.Add("@CUSTOMER", OleDbType.Integer, 50).Value = Me.ComboBox1.SelectedValue

da.InsertCommand.Parameters.Add("@TOT_AMT", OleDbType.Double, 50).Value = Me.TextBox3.Text

da.UpdateCommand = New OleDbCommand("UPDATE purch_hdr set customer=@CUSTOMER,TOT_AMT = @TOT_AMT WHERE INV_NO = @INV_NO")

da.UpdateCommand.Connection = cn

da.UpdateCommand.Parameters.Add("@CUSTOMER", OleDbType.Integer, 50).Value = Me.ComboBox1.SelectedValue

da.UpdateCommand.Parameters.Add("@TOT_AMT", OleDbType.Double, 50).Value = Me.TextBox3.Text

da.UpdateCommand.Parameters.Add("@INV_NO", OleDbType.Integer, 50).Value = Me.TextBox1.Text

'BsHeader.EndEdit()

da.Fill(ds)

da.Update(ds, "Purch_hdr")

da.SelectCommand = New OleDbCommand("SELECT INV_NO,ITEM, RATE, QTY, AMOUNT FROM PURCH_DTL")

da.SelectCommand.Connection = cn

da.InsertCommand = New OleDbCommand("INSERT INTO PURCH_DTL (INV_NO,ITEM, RATE, QTY, AMOUNT) VALUEs ( @INV_NO,@ITEM, @RATE, @QTY, @AMOUNT)")

da.InsertCommand.Connection = cn

da.InsertCommand.Parameters.Add("@INV_NO", SqlDbType.Int, 4, "INV_NO")

da.InsertCommand.Parameters.Add("@ITEM", SqlDbType.Int, 4, "ITEM")

da.InsertCommand.Parameters.Add("@RATE", SqlDbType.Decimal, 4, "RATE")

da.InsertCommand.Parameters.Add("@QTY", SqlDbType.Decimal, 4, "QTY")

da.InsertCommand.Parameters.Add("@AMOUNT", SqlDbType.Decimal, 4, "AMOUNT")

da.UpdateCommand = New OleDbCommand("UPDATE PURCH_DTL set ITEM=@ITEM, RATE=@RATE, QTY=@QTY, AMOUNT=@AMOUNT WHERE IDN = @IDN")

da.UpdateCommand.Connection = cn

da.UpdateCommand.Parameters.Add("@ITEM", SqlDbType.Int, 4, "ITEM")

da.UpdateCommand.Parameters.Add("@RATE", SqlDbType.Decimal, 4, "RATE")

da.UpdateCommand.Parameters.Add("@QTY", SqlDbType.Decimal, 4, "QTY")

da.UpdateCommand.Parameters.Add("@AMOUNT", SqlDbType.Decimal, 4, "AMOUNT")

da.UpdateCommand.Parameters.Add("@IDN", SqlDbType.Int, 4, "IDN")

BsHeader.EndEdit()

bsDetail.EndEdit()

da.Fill(ds)

da.Update(ds, "Purch_dtl")

Catch ex As Exception

MsgBox(ex.Message)

End Try

End Sub

Private Sub ADD_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ADD.Click

BsHeader.AddNew()

bsDetail.AddNew()

inv_incr()

bsDetail.EndEdit()

BsHeader.EndEdit()

End Sub

Private Sub inv_incr()

Try

If cn.State = ConnectionState.Closed Then

cn.Open()

End If

Dim cmd As New OleDbCommand("select max(inv_no) +1 from purch_hdr", cn)

TextBox1.Text = (cmd.ExecuteScalar())

Catch ex As Exception

TextBox1.Text = 1

End Try

End Sub

Private Sub DELETE_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DELETE.Click

Try

If cn.State = ConnectionState.Closed Then

cn.Open()

End If

Dim cmd1 As New OleDbCommand("delete from purch_hdr where inv_no = ?", cn)

cmd1.Parameters.Add("INV_NO", Data.OleDb.OleDbType.Integer, 4).Value = TextBox1.Text

cmd1.ExecuteNonQuery()

Dim cmd2 As New OleDbCommand("delete from purch_DTL where inv_no = ?", cn)

cmd2.Parameters.Add("INV_NO", Data.OleDb.OleDbType.Integer, 4).Value = TextBox1.Text

cmd2.ExecuteNonQuery()

BsHeader.RemoveCurrent()

Catch ex As Exception

MsgBox(ex.Message)

End Try

End Sub

Private Sub calc_me()

DataGridView1.CurrentRow.Cells(5).Value = (DataGridView1.CurrentRow.Cells(4).Value * DataGridView1.CurrentRow.Cells(3).Value)

End Sub

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

If e.ColumnIndex = 2 Then

DataGridView1.CurrentRow.Cells(3).Value = ds.Tables!item.Rows(ItemBindingSource.Position)!Rate.ToString

End If

If DataGridView1.Columns(e.ColumnIndex).Name = "Column3" Or DataGridView1.Columns(e.ColumnIndex).Name = "Column4" Or DataGridView1.Columns(e.ColumnIndex).Name = "Column5" Then

' calc_me()

End If

End Sub

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

Try

Dim sum As Int32 = 0

For Each r As DataGridViewRow In Me.DataGridView1.Rows

'sum = sum + r.Cells(3).Value * r.Cells(4).Value

r.Cells(5).Value = r.Cells(3).Value * r.Cells(4).Value

Next

Catch ex As Exception

End Try

End Sub

Sub assigngrid()

Me.Column1 = New System.Windows.Forms.DataGridViewTextBoxColumn

Me.Column6 = New System.Windows.Forms.DataGridViewTextBoxColumn

Me.DataGridViewTextBoxColumn2 = New System.Windows.Forms.DataGridViewComboBoxColumn

Me.Column3 = New System.Windows.Forms.DataGridViewTextBoxColumn

Me.Column4 = New System.Windows.Forms.DataGridViewTextBoxColumn

Me.Column5 = New System.Windows.Forms.DataGridViewTextBoxColumn

Me.DataGridView1.Columns.AddRange(New System.Windows.Forms.DataGridViewColumn() {Column1, Column6, DataGridViewTextBoxColumn2, Column3, Column4, Column5})

'Column1

'

Me.Column1.DataPropertyName = "IDN"

Me.Column1.HeaderText = "IDN"

Me.Column1.Name = "Column1"

'

'Column6

'

Me.Column6.DataPropertyName = "INV_NO"

Me.Column6.HeaderText = "INV_NO"

Me.Column6.Name = "Column6"

Me.Column6.Visible = False

'

'DataGridViewTextBoxColumn2

'

Me.DataGridViewTextBoxColumn2.HeaderText = "DATA"

Me.DataGridViewTextBoxColumn2.Name = "DataGridViewTextBoxColumn2"

Me.DataGridViewTextBoxColumn2.Resizable = System.Windows.Forms.DataGridViewTriState.[True]

Me.DataGridViewTextBoxColumn2.SortMode = System.Windows.Forms.DataGridViewColumnSortMode.Automatic

'

'Column3

'

Me.Column3.DataPropertyName = "RATE"

Me.Column3.HeaderText = "RATE"

Me.Column3.Name = "Column3"

'

'Column4

'

Me.Column4.DataPropertyName = "QTY"

Me.Column4.HeaderText = "QTY"

Me.Column4.Name = "Column4"

'

'Column5

'

Me.Column5.DataPropertyName = "AMOUNT"

DataGridViewCellStyle2.Format = "N2"

DataGridViewCellStyle2.NullValue = Nothing

Me.Column5.DefaultCellStyle = DataGridViewCellStyle2

Me.Column5.HeaderText = "AMOUNT"

Me.Column5.Name = "Column5"

End Sub

End Class

Run the project & enjoy.

Download complete source code Click Here

My first VB.NET Database Application Tutorial Source Code

INTRODUCTION

This practical guide is created for those friends who are newbie

in VB.NET database programming and searching help in Google for understanding the concept of
VB.NET. I have explained few examples in this book for creating data entry
module. This book is easy and after going through the examples given in book
you will be able to get an idea for creating Database Programming.

When I was learning VB.NET I was searching for a tutorial

which will explain step-by-step guide for creating a database programming but
fail to get so, then I have decided that after learning I will put tutorial on
net for new learner of VB.Net which will explain each and every line as we
learned at nursery.

Although I have tried to explain each and every step by

putting screenshots but you might get any trouble if so please feel free to
contact me at ranjansatpathy@gmail.com

Chapter – I

Requirements
Before creating data entry form in VB.NET you need to

install the VB.NET Express Edition 2008, SQL Server Express & SQL Server
Management Studio which is easily available in Microsoft Website.

For downloading Microsoft VB.NET 2008

Figure 1.0 : Screenshot of VB.NET
URL:
For downloading Microsoft SQL SERVER Express

Figure 1.1 : Screenshot of SQL SERVER
URL:
For downloading SQL Server Management Studio Express

Figure 1.2: Screen
Shot of Management Studio

URL:

Chapter – II

Database Designing

To create a new database Challan
you need to open management studio as shown in Figure 1.2 ,
Right click on database tree select new database and type the database name challan and click Ok .

Creating table

From the very left top corner click on new query type the
following script to create the table party.

create table Party (

slno int primary key,
compcd varchar(50),
cname varchar(50),
add1 text,
add2 text,
add3 text,
tin text,
cstate text,
supcd text,
central text,
excom text,
mailname text)

and click on execute button to create the table
Party.

Chapter – III

Creating Form and Module

Now Open VB.NET and create a new project

Create a Module from Project Add Module and type the
following.

Imports System.Data.SqlClient

Module Module1
Public cn As New SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=CHALLAN;Data Source=RANJAN\SQLEXPRESS")
Public da As New SqlDataAdapter
Public DS As New DataSet
End Module

Note: In above connection parameter
you need to change the datasource as showing in your
SQL SERVER. As it is RANJAN\SQLEXPRESS in my PC.

Design

the form by putting the shown objects, and change the objects name according to
the table fieldname SlNo,CompCd,CName,Add1,Add2,Add3, TinNo,Cstate,SupCd

Central,ExCom

and MailName

Chapter – IV

Code writing

Now you have designed the Form now you need to put some code
in event of combobox changing, click of add button, click
of save button just type what I am giving below and try to understand the each
and every line of code what it is doing when a user is clicking on certain
button or object.

Imports System.Data.SqlClient
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If cn.State = ConnectionState.Closed Then
cn.Open()
End If
da = New SqlDataAdapter("SELECT * FROM PARTY", cn)
DA.Fill(DS, "TABLE")
COMBOBOX1.DATASOURCE = DS.Tables(0)
ComboBox1.DisplayMember = DS.Tables(0).Columns("cname").ToString()
ComboBox1.ValueMember = DS.Tables(0).Columns("COMPCD").ToString()
End Sub
Private Sub CALL_dATA()
''Try
If cn.State = ConnectionState.Closed Then
cn.Open()
End If
Dim dr As SqlDataReader
Dim SRCH As String
SRCH = ComboBox1.SelectedValue.ToString
''MsgBox(SRCH)
Dim cmd As New SqlCommand("select * from party where compcd = '" & SRCH & "'", cn)
dr = cmd.ExecuteReader()
dr.Read()
SlNo.Text = dr("slno")
CompCd.Text = dr("compcd")
CName.Text = dr("cname")
Add1.Text = dr("add1")
Add2.Text = dr("add2")
Add3.Text = dr("add3")
TinNo.Text = dr("tin")
Cstate.Text = dr("cstate")
SupCd.Text = dr("supcd")
Central.Text = dr("central")
ExCom.Text = dr("excom")
MailName.Text = IIf(IsDBNull(dr("mailname")), "", dr("MAILNAME"))
cn.Close()
'' Catch ex As Exception
''MsgBox(ex.Message)
''End Try
End Sub
Private Sub ComboBox1_DropDownClosed(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.DropDownClosed
CALL_dATA()
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Try
save_data()
MsgBox("RECORDSAVED")
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Sub save_data()
If cn.State = ConnectionState.Closed Then
cn.Open()
End If
''Dim dr As
SqlDataReader()
Dim dbread()
Dim cmd1 As New SqlCommand()
cmd1 = New SqlCommand("insert into pARTY (SlNo,CompCd,CName,Add1,Add2,Add3,Tin,Cstate,SupCd,Central,ExCom) VALUES (" & SlNo.Text & ", '" & CompCd.Text & "','" & CName.Text & "', '" & Add1.Text & "', '" & Add2.Text & "', '" & Add3.Text & "', '" & TinNo.Text & "', '" & Cstate.Text & "', '" & SupCd.Text & "', '" & Central.Text & "', '" & ExCom.Text & "')", cn)
dbread = cmd1.ExecuteNonQuery()
End Sub
Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
SlNo.Clear()
CompCd.Clear()
CName.Clear()
Add1.Clear()
Add2.Clear()
Add3.Clear()
TinNo.Clear()
Cstate.Clear()
SupCd.Clear()
Central.Clear()
ExCom.Clear()
MailName.Clear()
End Sub
End Class

Now with the help of above example you will be able to
design form for any data entry module.