Question Textbox to Database Problem

dharmang1910

New member
Joined
Mar 25, 2017
Messages
4
Programming Experience
Beginner
Hello,
I'm new to vb.net and I'm trying to make a simple application of a daily cash register. It calculate the total of the cash according to denomination of the currency and store them to the database. In this application there is a textbox named textbox1 which calculate the total of the cash and display it.Its code is like
Code:
100*val(textbox2.text)+50*val(textbox3.text)+.....
I want this total value to add into my database's total column but i can't able to do this. I have attached my other textbox and datagridview to the database with help of wizard but can not do the total thing.Here is my code, Please help me to correct this.

Code:
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        OpeningCashBindingSource.AddNew()
        Dim vartot As Integer = TextBox1.Text
        Dim cnt As New OleDb.OleDbConnection
        Dim cmt As New OleDb.OleDbCommand
        cnt.ConnectionString = "provider = microsoft.jet.oledb.4.0; data source=|DataDirectory|\teller.mdb"
        cnt.Open()
        cmt.Connection = cnt
        cmt.CommandText = "INSERT INTO `Opening Cash` (`Total`) values (" & vartot & ")"
        cmt.ExecuteNonQuery()
        cnt.Close()
    End Sub
 

dharmang1910

New member
Joined
Mar 25, 2017
Messages
4
Programming Experience
Beginner
And what actually happens when you execute that code?
Thanks jmcilhinney for reply.
It stores denomination of currency notes to the respected column in datagridview but total column of datagridview remain clear. Total column is not filled by calculated figure.
 

dharmang1910

New member
Joined
Mar 25, 2017
Messages
4
Programming Experience
Beginner
My guess is that the code is working as it should but you're just not looking for the data correctly. What value is returned by that call to ExecuteNonQuery?
Now i have to use a long code instead of a single line code. Here is it
Code:
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Try
            Dim vartot As Integer = TextBox1.Text
            Dim var100 As Integer = TextBox2.Text
            Dim var50 As Integer = TextBox3.Text
            Dim var20 As Integer = TextBox4.Text
            Dim var10 As Integer = TextBox5.Text
            Dim var5 As Integer = TextBox6.Text
            Dim var2 As Integer = TextBox7.Text
            Dim var1 As Integer = TextBox8.Text
            Dim cnt As New OleDb.OleDbConnection
            Dim cmt As New OleDb.OleDbCommand
            cnt.ConnectionString = "provider = microsoft.jet.oledb.4.0; data source=|DataDirectory|\teller.mdb"
            cnt.Open()
            cmt.Connection = cnt
            cmt.CommandText = "INSERT INTO `Opening Cash` (`Total`, `100`, `50`, `20`, `10`, `5`, `2`, `1`) values (" & vartot & ", " & var100 & ", " & var50 & ", " & var20 & ", " & var10 & ", " & var5 & ", " & var2 & ", " & var1 & ")"
            cmt.CommandType = CommandType.Text
            cmt.ExecuteNonQuery()
            cnt.Close()
        Catch ex As Exception
            MsgBox("Add failed")
        End Try
        For Each ctl In Me.Controls
            If TypeName(ctl) = "TextBox" Then
                ctl.Value = 0
            End If
        Next
        Me.TextBox2.Focus()
    End Sub
It is adding data to the database perfectly but now i am facing other problems
1) Datagridview is not updating until i restart the application
2) Textboxes are not being clear despite of write code for it.
Can you give me any idea to remove this problems.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,111
Location
Sydney, Australia
Programming Experience
10+
1) Datagridview is not updating until i restart the application
Why would it? You're not making any changes to the grid or the data in or bound to the grid. The grid knows nothing about the database so making changes to the database will not magically update the grid.

You're doing things the wrong way around. What you should be doing is creating a DataTable and, if appropriate, populating it with existing data. You should then bind that DataTable to the grid, preferably via a BindingSource. Whatever changes the user makes to the data in the grid directly will then be pushed to the DataTable and any changes you make to the DataTable in code will be pushed to the grid. Once you're done editing, you use a data adapter - the same one you used to populate the DataTable in the first place if you did, in fact, retrieve initial data - to save all the changes to the database in one batch.
2) Textboxes are not being clear despite of write code for it.
You're setting the Value property to 0. TextBoxes don't even have a Value property so, unless they are custom controls that inherit TextBox and add that property, that code can't work. If they are custom controls then I'm guessing that that TypeName method won't return TextBox so the Value property will never be set.
 

dharmang1910

New member
Joined
Mar 25, 2017
Messages
4
Programming Experience
Beginner
you use a data adapter - the same one you used to populate the DataTable in the first place if you did, in fact, retrieve initial data - to save all the changes to the database in one batch.
Sir,
I feel same, Actually i have imported access databse with data source configuration wizard and bind textboxes and datagridview by selecting this data source. I want to calculate sum of the cash on real time in total textbox but this textbox data which is calculated is different than that binding source. So i can not figured out how can i save realtime calulated result to the database's total column.
 
Top Bottom