Question Textbox to Database Problem

[dharmang1910]

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

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.

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

 

[jmcilhinney]

And what actually happens when you execute that code?

 

[dharmang1910]

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.

 

[jmcilhinney]

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?

 

[dharmang1910]

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

    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]

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]

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.