[RESOLVED] How to add mathematic operation to update database values!?

[HOTFIX]

Hi…


I need help to update the item QTY after performing search criteria by adding and subtracting new values which they will be saved to the database after the updating!

Imports System.Data
Imports System.Data.OleDb


Public Class Form2
    Dim frm As New Form1
    Dim ConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source =" & Application.StartupPath & "\data.mdb"
    Dim Conn As New OleDbConnection(ConStr)
    Dim DataSet1 As New DataSet
    Dim SQLstr As String
    Dim m As String


    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click

        DataSet1 = New DataSet

        Static m As Integer = 0
        If tbSearch.Text = Trim("") Then Exit Sub
        ExactSearch()
        Conn.Open()
        Dim DataAdapter1 As New OleDbDataAdapter(SQLstr, Conn)
        DataAdapter1.Fill(DataSet1, "store")

        Conn.Close()
        tbSearchResulItemPartNumber.DataBindings.Clear()
        tbSearchResultItemDeascriptions.DataBindings.Clear()
        tbSearchResultItemLocation.DataBindings.Clear()
        tbItemQuantitySearchResult.DataBindings.Clear()

        tbSearchResulItemPartNumber.DataBindings.Add("Text", DataSet1, "store.Item Part Number")
        tbSearchResultItemDeascriptions.DataBindings.Add("Text", DataSet1, "store.Item Descriptions")
        tbSearchResultItemLocation.DataBindings.Add("Text", DataSet1, "store.Item Location")
        tbItemQuantitySearchResult.DataBindings.Add("Text", DataSet1, "store.Item Quantity")

        DataGridView1.DataSource = DataSet1
        DataGridView1.DataMember = "store"

        If Me.BindingContext(DataSet1, "store").Count = 0 Then
            MsgBox("No Result??!! ")
            Form1.Show()
            Me.Close()
        End If

        Exit Sub

    End Sub

    Public Sub ExactSearch()
        SQLstr = "SELECT * FROM store WHERE [Item Part Number ]= '" & tbSearch.Text & "'"
    End Sub

    
    Private Sub btnBACK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBACK.Click
        Me.Close()
        Form1.Show()
    End Sub

   

    Private Sub Form2_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Conn.Open()
        Try
            Dim cmd As OleDbCommand = New OleDbCommand("Select [Item Part Number] from store", Me.Conn)
            Dim reader As OleDbDataReader = cmd.ExecuteReader()
            Dim list As New AutoCompleteStringCollection
            While reader.Read
                list.Add(reader.Item("Item Part Number"))
            End While
            tbSEARCH.AutoCompleteMode = AutoCompleteMode.SuggestAppend
            tbSEARCH.AutoCompleteSource = AutoCompleteSource.CustomSource
            tbSEARCH.AutoCompleteCustomSource = list

        Catch ex As Exception
            Conn.Close()
            Throw ex

        End Try
        Conn.Close()
    End Sub

    Private Sub TextBox2_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles tbItemQuantityCalculation.KeyPress
        If (Char.IsControl(e.KeyChar) = False) Then
            If (Char.IsDigit(e.KeyChar)) Then

            Else
                e.Handled = True
                MsgBox("Sorry Only Digits Allowed!!", MsgBoxStyle.Information, "Store Mangement")
                tbItemQuantityCalculation.Focus()

            End If
        End If
    End Sub

    
    Private Sub btnSUBTRACT_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSUBTRACT.Click

    End Sub

    Private Sub btnADD_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnADD.Click

    End Sub
End Class

Any idea about the codes for adding and subtracting

Regards…

[GaryMazzone]

I would just write an Update query and perform

Dim x As Integer
 
If Not Integer.TryParse(Me.TextBox1.Text,x)
    MessageBox("Only Integer values Here")
    TextBox1.SelectAll
    TextBox1.Focus
    Exit Sub
End If
Dim strUPD as String
strUPD = "Update TableName Set "
strUPD &= "FieldName = FieldName + " & x.ToString() 
strUPD &= "Where ItemId = " & TheItemID
Open the connection
Create a command Object
Execute the SQL on the command Object

[HOTFIX]

I would just write an Update query and perform

Dim x As Integer
 
If Not Integer.TryParse(Me.TextBox1.Text,x)
    MessageBox("Only Integer values Here")
    TextBox1.SelectAll
    TextBox1.Focus
    Exit Sub
End If
Dim strUPD as String
strUPD = "Update TableName Set "
strUPD &= "FieldName = FieldName + " & x.ToString() 
strUPD &= "Where ItemId = " & TheItemID
Open the connection
Create a command Object
Execute the SQL on the command Object

Thanks GaryMazzone for your support

In which event I put your suggested codes

Regards...

[GaryMazzone]

If you have buttons to add/subtract put it there

[HOTFIX]

I put the codes in the events but still getting many errors

Private Sub btnSUBTRACT_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSUBTRACT.Click
        Dim x As Integer
        If Not Integer.TryParse(Me.tbItemQuantityCalculation.Text, x) Then
            MessageBox.Show("Only Integer values Here")
            tbItemQuantityCalculation.SelectAll()
            tbItemQuantityCalculation.Focus()
            Exit Sub
        End If
        Dim strUPD As String
        strUPD = "Update store set"
        strUPD &= "[Item Quantity] = [Item Quantity] - " & x.ToString()
        strUPD &= "Where [Item Part Number] = '" & tbSearchResulItemPartNumber.Text & "'"
        Open the connection        
        Create a command Object
        Execute the SQL on the command Object
    End Sub

    Private Sub btnADD_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnADD.Click
        Dim x As Integer
        If Not Integer.TryParse(Me.tbItemQuantityCalculation.Text, x) Then
            MessageBox.Show("Only Integer values Here")
            tbItemQuantityCalculation.SelectAll()
            tbItemQuantityCalculation.Focus()
            Exit Sub
        End If
        Dim strUPD As String
        strUPD = "Update store set"
        strUPD &= "[Item Quantity] = [Item Quantity] + " & x.ToString()
        strUPD &= "Where [Item Part Number] = '" & tbSearchResulItemPartNumber.Text & "'"
        Open the connection
        Create a command Object
        Execute the SQL on the command Object
    End Sub

any idea

[GaryMazzone]

That is example code only use your connection string. Create a command object then execute on it.

[HOTFIX]

That is example code only use your connection string. Create a command object then execute on it.

is this what you mean:

Private Sub btnADD_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnADD.Click
        Dim x As Integer
        If Not Integer.TryParse(Me.tbItemQuantityCalculation.Text, x) Then
            MessageBox.Show("Only Integer values Here")
            tbItemQuantityCalculation.SelectAll()
            tbItemQuantityCalculation.Focus()
            Exit Sub
        End If
        Dim strUPD As String
        strUPD = "Update store set"
        strUPD &= "[Item Quantity] = [Item Quantity] + " & x.ToString()
        strUPD &= "Where [Item Part Number] = '" & tbSearchResulItemPartNumber.Text & "'"

        Dim ConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source =" & Application.StartupPath & "\data.mdb"
        Dim Conn As New OleDbConnection(ConStr)
        Dim DataSet1 As New DataSet
    End Sub

[GaryMazzone]

There is no need to create a dataset. You are not going to return anything from the database just do an update. So the first 2 lines are correcrt. Next create a OleDbCommand object. Set the command objects connection property to your connection then use the command objects ExecuteNonQuery method to execute the SQL string.

[HOTFIX]

i try this code

rivate Sub btnADD_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnADD.Click
        Dim x As Integer
        If Not Integer.TryParse(Me.tbItemQuantityCalculation.Text, x) Then
            MessageBox.Show("Only Integer values Here")
            tbItemQuantityCalculation.SelectAll()
            tbItemQuantityCalculation.Focus()
            Exit Sub
        End If
        Dim strUPD As String
        strUPD = "Update store set"
        strUPD &= "[Item Quantity] = [Item Quantity] + " & x.ToString()
        strUPD &= "Where [Item Part Number] = '" & tbSearchResulItemPartNumber.Text & "'"

        Dim ConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source =" & Application.StartupPath & "\data.mdb"
        Dim Conn As New OleDbConnection(ConStr)
        Dim SavInto As New OleDb.OleDbCommand
        SavInto.Connection = Conn
        SavInto.CommandType = CommandType.Text
        SavInto.CommandText = "UPDATE store SET [Item Part Number] = '" & tbSearchResulItemPartNumber.Text & "'"        Conn.Open()
        SavInto.ExecuteNonQuery()
        Conn.Close()
    End Sub

but its return this error:
"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

[GaryMazzone]

What are you trying to do.... You are trying to set every Item Part Number to the same thing. If that is the PK for the table of course it will fail

[HOTFIX]

What are you trying to do.... You are trying to set every Item Part Number to the same thing. If that is the PK for the table of course it will fail

sorry I dont know how to manage this code

[GaryMazzone]

SavInto.CommandText = strUPD

[HOTFIX]

SavInto.CommandText = strUPD

Thanks GaryMazzone for your Continuous support...

Private Sub btnADD_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnADD.Click
        Dim x As Integer
        If Not Integer.TryParse(Me.tbItemQuantityCalculation.Text, x) Then
            MessageBox.Show("Only Integer values Here")
            tbItemQuantityCalculation.SelectAll()
            tbItemQuantityCalculation.Focus()
            Exit Sub
        End If
        Dim strUPD As String
        strUPD = "Update store set"
        strUPD &= "[Item Quantity] = [Item Quantity] + " & x.ToString()
        strUPD &= "Where [Item Part Number] = '" & tbSearchResulItemPartNumber.Text & "'"

        Dim ConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source =" & Application.StartupPath & "\data.mdb"
        Dim Conn As New OleDbConnection(ConStr)
        Dim SavInto As New OleDb.OleDbCommand
        SavInto.Connection = Conn
        SavInto.CommandType = CommandType.Text
        SavInto.CommandText = strUPD
        Conn.Open()
        SavInto.ExecuteNonQuery()
        Conn.Close()
    End Sub

still getting error this time on query expression :"Syntax error (missing operator) in query expression '[Item Quantity] + 3Where [Item Part Number] = 'AF090-111-T22''

Any idea please

[GaryMazzone]

space betwee 3 and Where

[HOTFIX]

Finally some success

The codes now are working but when clicking on add button the updated value doesn’t appear in the item quantity textbox until a I do another search neither in the grid view

Any idea how to fix this

Regards...

[GaryMazzone]

Either call a refresh of the the gridview or update the column when you get successful update of the database.

[HOTFIX]

Either call a refresh of the the gridview or update the column when you get successful update of the database.

I try this codes but they are not working

 Private Sub btnADD_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnADD.Click
        Dim x As Integer
        If Not Integer.TryParse(Me.tbItemQuantityCalculation.Text, x) Then
            MessageBox.Show("Only Integer values Here")
            tbItemQuantityCalculation.SelectAll()
            tbItemQuantityCalculation.Focus()
            Exit Sub
        End If
        Dim strUPD As String
        strUPD = "Update store set"
        strUPD &= "[Item Quantity] = [Item Quantity] + " & x.ToString()
        strUPD &= " Where [Item Part Number] = '" & tbSearchResulItemPartNumber.Text & "'"

        Dim ConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source =" & Application.StartupPath & "\data.mdb"
        Dim Conn As New OleDbConnection(ConStr)
        Dim SavInto As New OleDb.OleDbCommand
        SavInto.Connection = Conn
        SavInto.CommandType = CommandType.Text
        SavInto.CommandText = strUPD
        Conn.Open()
        SavInto.ExecuteNonQuery()
        Conn.Close()

        tbItemQuantitySearchResult.Refresh()
        DataGridView1.DataSource = DataSet1.GetChanges
        DataGridView1.DataMember = "store"
    End Sub

[GaryMazzone]

Dim intNumEfft As Integer = 0
intNumEff = SavInto.ExecuteNonQuery()
Conn.Close()
 
If intNumEff > 0 Then
  tbItemQuantitySearchResult += tbItemQuantityCalculation
End If

then call the part that fills the grid again

[HOTFIX]

Dim intNumEfft As Integer = 0
intNumEff = SavInto.ExecuteNonQuery()
Conn.Close()
 
If intNumEff > 0 Then
  tbItemQuantitySearchResult += tbItemQuantityCalculation
End If

then call the part that fills the grid again

I've try it, it doesn't work

Private Sub btnADD_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnADD.Click
        Dim x As Integer
        If Not Integer.TryParse(Me.tbItemQuantityCalculation.Text, x) Then
            MessageBox.Show("Only Integer values Here")
            tbItemQuantityCalculation.SelectAll()
            tbItemQuantityCalculation.Focus()
            Exit Sub
        End If
        Dim strUPD As String
        strUPD = "Update store set"
        strUPD &= "[Item Quantity] = [Item Quantity] + " & x.ToString()
        strUPD &= " Where [Item Part Number] = '" & tbSearchResulItemPartNumber.Text & "'"

        Dim ConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source =" & Application.StartupPath & "\data.mdb"
        Dim Conn As New OleDbConnection(ConStr)
        Dim SavInto As New OleDb.OleDbCommand
        SavInto.Connection = Conn
        SavInto.CommandType = CommandType.Text
        SavInto.CommandText = strUPD
        Conn.Open()
        SavInto.ExecuteNonQuery()
        Conn.Close()
        Dim intNumEfft As Integer = 0
        intNumEfft = SavInto.ExecuteNonQuery()
        Conn.Close()
        If intNumEfft > 0 Then
            tbItemQuantitySearchResult.Text += tbItemQuantityCalculation.Text
        End If
        DataGridView1.DataSource = DataSet1
        DataGridView1.DataMember = "store"
    End Sub

[GaryMazzone]

Private Sub btnADD_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnADD.Click
        Dim x As Integer
        If Not Integer.TryParse(Me.tbItemQuantityCalculation.Text, x) Then
            MessageBox.Show("Only Integer values Here")
            tbItemQuantityCalculation.SelectAll()
            tbItemQuantityCalculation.Focus()
            Exit Sub
        End If
        Dim strUPD As String
        strUPD = "Update store set"
        strUPD &= "[Item Quantity] = [Item Quantity] + " & x.ToString()
        strUPD &= " Where [Item Part Number] = '" & tbSearchResulItemPartNumber.Text & "'"
 
        Dim ConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source =" & Application.StartupPath & "\data.mdb"
        Dim Conn As New OleDbConnection(ConStr)
        Dim SavInto As New OleDb.OleDbCommand
        SavInto.Connection = Conn
        SavInto.CommandType = CommandType.Text
        SavInto.CommandText = strUPD
        Conn.Open()
        Dim intNumEfft As Integer = 0
        intNumEfft = SavInto.ExecuteNonQuery()
        Conn.Close()
        If intNumEfft > 0 Then
            tbItemQuantitySearchResult.Text += tbItemQuantityCalculation.Text
        End If
        DataGridView1.DataSource = DataSet1
        DataGridView1.DataMember = "store"
    End Sub

[HOTFIX]

Many thanks GaryMazzone for your assistance and your time for helping

Unfortunately, the last code for displaying updated values in the textbox result and grid view didn’t work as I expected…


Anyway I will mark this thread as resolved

Again I appreciate your support and I hope you keep up helping sprit with me and other members…

Best Regards…