none
ADDING TWO VALUES RRS feed

  • Question

  • i wrote a code to update stock without creating any relationship

    code is working fine only on one point i am getting issue 

     updateQuery = "UPDATE items SET itemavailablestock = itemavailablestock + '" & Received_qtyTextBox.Text & "'  WHERE itemnumber = '" & ProductcodeTextBox.Text & "'" updateQuery = "UPDATE items SET itemavailablestock = itemavailablestock + '" & Received_qtyTextBox.Text & "'  WHERE itemnumber = '" & ProductcodeTextBox.Text & "'"

    i am trying to add old stock with new stock 

    it adding the stock but not increasing the stock but adding additional numbers with old

    like if i have stock of pen =10

    new stock is =20 so it must add total stock =30

    but it adding =1020 

    please guide me with it 

    thanks

    Friday, March 1, 2019 6:32 AM

Answers

  • Make sure that the ‘itemavailablestock’ column is numeric, not a text.

    • Marked as answer by Omi4u Saturday, March 2, 2019 5:31 AM
    Friday, March 1, 2019 9:48 AM

All replies

  • Hi 

    You can add your new values with existing values using below command.Just modify the code according to your needs.

    update items set itemavailablestock=itemavailablestock+5 WHERE itemnumber=1


    Thanks


    • Edited by ME_77 Friday, March 1, 2019 9:46 AM
    Friday, March 1, 2019 9:42 AM
  • Make sure that the ‘itemavailablestock’ column is numeric, not a text.

    • Marked as answer by Omi4u Saturday, March 2, 2019 5:31 AM
    Friday, March 1, 2019 9:48 AM
  • not working sir 

    it not updating the stock now sir 

    • Edited by Omi4u Friday, March 1, 2019 9:53 AM
    Friday, March 1, 2019 9:53 AM
  • i am not using +5 and 1 in code

    im using textboxes to do it as in below code

      updateQuery = "UPDATE itemx SET itemqty = itemqty + '" & TXTQTY.Text & "'  WHERE itemID = '" & txtPROCODE.Text & "'"

    Friday, March 1, 2019 10:11 AM
  • I have tried to replicate the same with text box, This works.

    On click of button I have called below code,

    All SQL fields I have used integer datatype.

      protected void Update_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection("Data Source=(local);Initial Catalog=test;Integrated Security=SSPI");
            con.Open();
            SqlCommand cmd = new SqlCommand("update itemx set itemqty=itemqty+@NewValue WHERE itemID=@Id", con);
            cmd.Parameters.AddWithValue("@Id", 1);
            cmd.Parameters.AddWithValue("@NewValue", TXTQTY.Text);
            cmd.ExecuteNonQuery();
        }

    My Screen Design,



    • Proposed as answer by ME_77 Friday, March 1, 2019 10:47 AM
    • Edited by ME_77 Friday, March 1, 2019 10:48 AM
    Friday, March 1, 2019 10:46 AM
  • Hello,

    My recommendation is to always first write your SQL in either SSMS (SQL-Server Management Studio) or to create a .SQL file in your project and run the SQL to ensure it works before running it in code.

    So here I create a table to match what I believe yours should be, note the column types.

    Then write the UPDATE statement, in this case in SSMS.

    • Declare to parameters, one to find the record @ItemNumber, @NewValue is the value to increase itemavaiablestock.
    • Run the update
    • Run a select to see the results
    DECLARE @ItemNumber AS INT = 101;
    DECLARE @NewValue AS INT = 9;
    
    UPDATE ForumExample.dbo.Items
    SET    itemavailablestock = itemavailablestock + @NewValue
    WHERE  ItemNumber = @ItemNumber;
    
    SELECT id ,
           ItemNumber ,
           itemavailablestock
    FROM   ForumExample.dbo.Items
    WHERE  ItemNumber = @ItemNumber;

    What you have more likely than not is a string field which would explain your results so change itemavailablestock to int.

    If unsure how to check the column types use the following statement.

    SELECT COLUMN_NAME ,
           DATA_TYPE
    FROM   INFORMATION_SCHEMA.COLUMNS
    WHERE  TABLE_NAME = 'dbo.Items';
    Dump the results to a DataTable.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Friday, March 1, 2019 11:06 AM
    Moderator