Results 1 to 6 of 6

Thread: Add a new quantity to the old if exist

  1. #1

    Thread Starter
    Member
    Join Date
    Mar 2018
    Posts
    61

    Add a new quantity to the old if exist

    Hello Every one
    Please i need help to resolve this problem
    In my Table ( Table_Items ) i have 3 Field ( ItemId Type Numeric primary key - ItemName type Text - ItemQuantity Type Numeric )
    In my Form1 : TextBox1.Text for save ( ItemId ) , TextBox2.Text for save (ItemName) , TextBox3.Text for save (ItemQuantity)
    I want check if ( ItemName ) already exist in my ( Table_Items ) then only we add the new quantity to the old one in field ( ItemQuantity ) .. If ( ItemName ) does not exist in my ( Table_Items ) then we save this new record ( ItemName ).
    This is my code for save :
    Code:
         Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click  
     Try        
    Dim Cmd As New OleDbCommand
                    With Cmd
                        .Connection = Conne
                        .CommandType = CommandType.Text
                        .CommandText = "Insert Into Table_Items ( ItemId , ItemName, ItemQuantity ) VALUES ( @ItemId, @ItemName, @ItemQuantity )"
                        .Parameters.Clear()
                        Cmd.Parameters.Add(New OleDbParameter("@ItemId", OleDbType.Integer, 4)).Value = TextBox1.Text
                        Cmd.Parameters.Add(New OleDbParameter("@ItemName", OleDbType.VarChar)).Value = TextBox2.Text
                        Cmd.Parameters.Add(New OleDbParameter("@ItemQuantity", OleDbType.Integer)).Value = TextBox3.Text
                    End With
    If Conne.State = 1 Then Conne.Close()
                    Conne.Open()
                    Cmd.ExecuteNonQuery()
                     MsgBox("Saved", MsgBoxStyle.Information, "Confirm")
                End If
            Catch ex As Exception
                MsgBox(ex.ToString)
            Finally
                If Conne.State = ConnectionState.Open Then Conne.Close()
            End Try
    End Sub
    Thank you in advance for help
    ABE

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Add a new quantity to the old if exist

    Use a data adapter to query the database for an existing record and Fill a DataTable with the results. If there's a record in the DataTable, update the quantity. Otherwise, add a new row. Use the same data adapter to Update the database with the changes in the DataTable.

  3. #3

    Thread Starter
    Member
    Join Date
    Mar 2018
    Posts
    61

    Re: Add a new quantity to the old if exist

    Thank you very much
    Unfortunately I am new in this area I tried a lot but I could not put this thing
    ABE

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Add a new quantity to the old if exist

    Well, you haven't tried to use a data adapter, as far as I can tell. Maybe you should do some research on data adapters and DataTables and then put what you learn to use by making an attempt. If the attempt doesn't work then you can always post what you tried here and we can help you fix it. You don't know that you can't do it if you haven't tried and even if you can;t do the whole thing, there should still be parts that you can do for yourself. Do what you can on your own first, then ask for help with the specific parts that you can't.

    If you follow the CodeBank link in my signature below, you can check out my thread on Retrieving & Saving Data and the data adapter examples there. One point that pretty much everyone misses at first is that the Fill and Update method are both functions, so they return a value. In the case of Fill, that value is the number of records retrieved. In your case, that will be either 0 or 1 and that will tell you whether you need to add a new row or update an existing row.

  5. #5

    Thread Starter
    Member
    Join Date
    Mar 2018
    Posts
    61

    Re: Add a new quantity to the old if exist

    Any suggestion please .

  6. #6
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Add a new quantity to the old if exist

    I have a suggestion: make an effort. I told you what to do and I even directed you to where you could find relevant examples. If you're not willing to use that information to do some research and then try to implement something then I'm wasting my time here. You're on your own until you're prepared to try.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width