none
Add string to the same column in the same row RRS feed

  • Question

  • Hi,

    How can I add more then one string to a specific row in a specific column ? If I am writing an insert it will add the string to a new row and if I write an update it will replace the string. Ho can I append a new string to the same column ? 

    Friday, June 3, 2011 8:14 AM

Answers

  • Hi,

    += can be used in SQL 2008 and later. If you use older edition please use like this:

    UPDATE #T1 SET [name] = [name] + 'NewStr'
    WHERE [id] = 5;

    I hope it helps.

    J.


    There are 10 type of people. Those who understand binary and those who do not.
    Sunday, June 5, 2011 3:51 PM
  • Your code to call UPDATE SQL statement should look like

    myCommand = new SqlCommand("UPDATE ShiftsTbl SET Morning = Morning + @UserParameter", myConnection);

    myCommand.Parameters.Add(new SqlParameter(“@UserParameter”, user));

    myReader = myCommand.ExecuteNonQuery();


    Val Mazur (MVP) http://www.xporttools.net
    Monday, June 6, 2011 10:45 AM
    Moderator

All replies

  • Hi,

    try this:

    --works on SQL2008+
    CREATE TABLE #T1
    (
    	[id] int identity,
    	[name] varchar(10),
    	[color] varchar(10)
    );
    GO
    
    INSERT INTO #T1 ([name], [color]) VALUES
    ('Dave', 'Green'),
    ('Dave', 'Yellow'),
    ('Dave', 'Blue'),
    ('Paul', 'Red'),
    ('Paul', 'Purple'),
    ('Paul', 'Orange');
    GO
    --check original record
    SELECT * FROM #T1 WHERE [id] = 5;
    
    --appending a new string to a record
    UPDATE #T1 SET [name] += 'NewStr'
    WHERE [id] = 5;
    
    --check result
    SELECT * FROM #T1 WHERE [id] = 5;
    

    I hope it helps.

    J.


    There are 10 type of people. Those who understand binary and those who do not.
    Friday, June 3, 2011 8:36 AM
  • This is my code:

    if (CheckBoxList1.Items[0].Selected && !CheckBoxList1.Items[1].Selected && !CheckBoxList1.Items[2].Selected)

                { //only morning  

                   // myCommand = new SqlCommand("INSERT INTO ShiftsTbl (Morning) VALUES (" + user + ")", myConnection);

                    myCommand = new SqlCommand("UPDATE ShiftsTbl SET Morning +=" + user, myConnection);

                    myReader = myCommand.ExecuteReader();

                    myReader.Close();

       And I get syntax error because of the +. Can you let me know what is wrong here ? I also tried this:

    UPDATE ShiftsTbl SET Morning = WHERE  (Date = CONVERT(DATETIME, '2011-06-05 00:00:00', 102)) and got the same error. You sure you can right + in sql server ?

    Sunday, June 5, 2011 3:49 PM
  • Hi,

    += can be used in SQL 2008 and later. If you use older edition please use like this:

    UPDATE #T1 SET [name] = [name] + 'NewStr'
    WHERE [id] = 5;

    I hope it helps.

    J.


    There are 10 type of people. Those who understand binary and those who do not.
    Sunday, June 5, 2011 3:51 PM
  • Your code to call UPDATE SQL statement should look like

    myCommand = new SqlCommand("UPDATE ShiftsTbl SET Morning = Morning + @UserParameter", myConnection);

    myCommand.Parameters.Add(new SqlParameter(“@UserParameter”, user));

    myReader = myCommand.ExecuteNonQuery();


    Val Mazur (MVP) http://www.xporttools.net
    Monday, June 6, 2011 10:45 AM
    Moderator