how the textbox fetch the total price column in database using vb.net
here is my code and i want when i add purchase the textbox6 will change into total of the suprice
Code:
Imports System.Data
Imports System.Data.OleDb
Public Class Form2
Dim myconn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Marian Erana Ando\Desktop\NullBux\db.mdb")
Dim cmd As OleDbCommand
Dim dr As OleDbDataReader
Dim str As String
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
GroupBox2.Visible = False
End Sub
Public Function GetData() As DataView
Dim SelectQry = "SELECT * FROM item "
Dim SampleSource As New DataSet
Dim TableView As DataView
Try
Dim SampleCommand As New OleDbCommand()
Dim SampleDataAdapter = New OleDbDataAdapter()
SampleCommand.CommandText = SelectQry
SampleCommand.Connection = myconn
SampleDataAdapter.SelectCommand = SampleCommand
SampleDataAdapter.Fill(SampleSource)
TableView = SampleSource.Tables(0).DefaultView
Catch ex As Exception
Throw ex
End Try
Return TableView
End Function
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
str = "INSERT INTO purchase(prno,dte,total,pay,stat) VALUES ('" & TextBox1.Text & "','" & DateTimePicker1.Text & "','" & TextBox6.Text & "','" & TextBox6.Text & "','" & TextBox8.Text & "')"
cmd = New OleDbCommand(str, myconn)
myconn.Open()
cmd.ExecuteReader()
myconn.Close()
End Sub
Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
End Sub
Private Sub DataGridView1_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellClick
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Me.Close()
End Sub
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
GroupBox2.Visible = True
DataGridView2.DataSource = GetData()
End Sub
Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
GroupBox2.Visible = False
End Sub
Private Sub DataGridView2_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView2.CellClick
TextBox2.Text = DataGridView2.CurrentRow.Cells(0).Value.ToString
TextBox3.Text = DataGridView2.CurrentRow.Cells(1).Value.ToString
End Sub
Private Sub DataGridView2_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView2.CellContentClick
End Sub
Private Sub TextBox2_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox2.TextChanged
TextBox4.Text = ""
TextBox5.Text = ""
End Sub
Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
str = "INSERT INTO ptable(prno,itemno,suprice,qty) VALUES ('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox5.Text & "','" & TextBox4.Text & "')"
cmd = New OleDbCommand(str, myconn)
myconn.Open()
cmd.ExecuteReader()
myconn.Close()
DataGridView1.DataSource = pur()
End Sub
Public Function pur() As DataView
Dim SelectQry = "SELECT itemno,suprice,qty FROM ptable where prno ='" & TextBox1.Text & "' "
Dim SampleSource As New DataSet
Dim TableView As DataView
Try
Dim SampleCommand As New OleDbCommand()
Dim SampleDataAdapter = New OleDbDataAdapter()
SampleCommand.CommandText = SelectQry
SampleCommand.Connection = myconn
SampleDataAdapter.SelectCommand = SampleCommand
SampleDataAdapter.Fill(SampleSource)
TableView = SampleSource.Tables(0).DefaultView
Catch ex As Exception
Throw ex
End Try
Return TableView
End Function
Private Sub TextBox6_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox6.TextChanged
End Sub
Private Sub TextBox5_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox5.TextChanged
Dim totalSum As Integer
For i As Integer = 0 To DbDataSet.Tables(0).Rows.Count - 1
totalSum += DbDataSet.Tables(0).Rows(i).Item("suprice")
Next
TextBox6.Text = totalSum.ToString()
End Sub
End Class
please help