c# add number to number without sum

Question

1.00/5 (1 vote)

See more:

i have table1 contains 2 columns : itmname - itmcode

all itmname values are shown in listview1 column0

and textbox1 where user input the number he want

I want what user to input a number in textbox1 and data be added not sum to the value that's already in itmcode on table1 where itmname = listview1 column0
and if no data in itmcode just insert it

for all items in listview1

I tried this approach but didn't work :

C++

if (cn.State == ConnectionState.Closed) cn.Open();
            cm.Connection = cn;
            string sql = "UPDATE table1 SET [itmcode] = [itmcode] + @itmcode WHERE [itmname] = @itmname AND [itmname] IS NOT NULL";
            cm.CommandText = sql;
            foreach (ListViewItem item in listView1.Items)
            {
                cm.Parameters.Clear();
                SqlParameter par_itmcode = new SqlParameter("@itmcode", SqlDbType.Int);
                par_itmcode.Value = textBox1.Text;
                cm.Parameters.Add(par_itmcode);

                SqlParameter par_itmname = new SqlParameter("@itmname", SqlDbType.VarChar);
                par_itmname.Value = item.Text;
                cm.Parameters.Add(par_itmname);
                {
                    cm.ExecuteNonQuery();
                }
            }
            cn.Close();
            MessageBox.Show("done");

C#

so it would look like this


 itmname    itmcode

  name1        3

 if user enter 4 in textbox1 then it give me this result :

 itmname    itmcode

   name1       34

Posted 1-Jan-16 0:49am

Ahmed Zoeil

Updated 1-Jan-16 1:19am

v2

Add a Solution

Comments

_Asif_ 1-Jan-16 6:58am

what error are you getting ? what is the datatype of itemcode field?

Ahmed Zoeil 1-Jan-16 7:06am

no error just itmcode never changed

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2016-01-01T01:03:00

Solution 1

I would write a stored procedure for this: check if the row exists, and if so UPDATE it. If it doesn't, do an INSERT instead:

SQL

CREATE PROCEDURE spInsertOrUpdate
    @ItemCode INT,
    @ItemName NVARCHAR
AS
BEGIN
UPDATE MyTable SET ItemCode = ItemCode + @ItemCode WHERE ItemName = @ItemName
IF @@ROWCOUNT=0
    INSERT INTO MyTable (ItemCode, ItemName) VALUES (@ItemCode, @ItemName)
END

Posted 1-Jan-16 1:03am

OriginalGriff

Comments

Ahmed Zoeil 1-Jan-16 7:10am

thank you for your reply but I don't want to insert itmname if itmcode is null I want itmcode only to be inserted if it's value is null and if there's value in itmcode add it next to it

so it would look like this

itmname itmcode

name1 3

if user enter 4 in textbox1 then it give me this result :

itmname itmcode

name1 34

OriginalGriff 1-Jan-16 7:22am

You can do it with a less efficient SELECT:

IF EXISTS (SELECT * FROM MyTable WHERE @ItemName IS NOT NULL AND ItemName = @ItemName
UPDATE MyTable SET ItemCode = ItemCode + @ItemCode WHERE ItemName = @ItemName
ELSE
INSERT INTO MyTable (ItemCode, ItemName) VALUES (@ItemCode, @ItemName)

As the body of your SP - but I'd do the null check in my C# code instead myself and not bother sending nulls to SQL. Again, it's more efficient that way.

Himanshu Jain · Answer 2 · 2016-01-01T03:05:00

SQL

--Here I am assuming the ItemName column is varchar in your table

Create procedure spInsertName
(
@ItemCode int,
@ItemName varchar (100)
)


If Exists(Select ItemCode from MyTable where ItemCode == @ItemCode)
BEGIN
Update MyTable set ItemName = ItemName + @ItemName
Where ItemCode == @ItemCode
END
Else
BEGIN
Insert into MyTable(ItemName, ItemCode) values(@ItemName, @ItemCode)
END