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
No comments:
Post a Comment