Append values to a column value in database

Question

I want to append a large string values with the existing values of a column value in DB. this column is set as nvarchar(MAX). But when I am trying, only first few parts of new string is appending with old value. Others are not appending. Please suggest .

string initial_result ="xxxxxx";//reading values from db column and assigning to string
string final_result="yyyyyyyyyy";//lengthier one
SqlCommand cmd71 = new SqlCommand("update details set  result='" + initial_result + "'+'"+finalresult+"' where student_id ='11' ", con7);
cmd71.ExecuteNonQuery();

[Use Command Parameters.](http://stackoverflow.com/questions/3216233/what-is-passing-parameters-to-sql-and-why-do-i-need-it) This will make the code better *and* "magically fix" the problem. Also, depending on the actual operation it may be possible to skip "reading" in `initial_result`. And, as always, strive for a normalized database.. — user2864740, Oct 17 '14 at 07:46

Soner Gönül · Accepted Answer · 2014-10-17T07:50:17.507

Because you are using unnecessary single quotes when you concatenate initial_result and finalresult values.

result='" + initial_result + "'+'"+finalresult+"'
                                ^               ^

But more important, you should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks.

Also use using statement to dispose your database connections and objects.

using (SqlConnection con7 = new SqlConnection(cs))
{
   using (SqlCommand cmd71 = con7.CreateCommand())
   {
       cmd71.CommandText = "update details set  result = @result where student_id ='11'";
       cmd71.Parameters.Add("@result", SqlDbType.NVarChar).Value = initial_result + finalresult;
       cmd71.ExecuteNonQuery();
   }
}

score 0 · Answer 2 · answered Oct 17 '14 at 07:52

0

Try this:

"update details set result=result+'" + finalresult +  "' where student_id ='11'"

this would append, and u'll no need to read to initial_result

answered Oct 17 '14 at 07:52

Valentyn Vynogradskiy

633
1
6
22

I don't think this will generate same result. It generates same results only if `result` column has `initial_result` where `student_id ='11'`. Also using string concatenation is not a good approach. Parameterized queries always recomended. – Soner Gönül Oct 17 '14 at 08:26

Chandan Kumar · Answer 3 · 2014-10-17T08:26:26.160

As "Soner Gönül" mentioned to avoid Sql Injection attack, Format your code like this :

//reading values from db column and assigning to string
string initial_result ="xxxxxx";
//lengthier one
string final_result="yyyyyyyyyy";
string connectionstring = "your connection string here";
string query = "update details set  result=@result where student_id = 11";
using(SqlConnection con = new SqlConnection(connectionstring))
{
   SqlCommand cmd = new SqlCommand(query,con);
   con.Open();
   cmd.Parameters.Add(new SqlParameter("@result", initial_result + finalresult));
   int executeresult = cmd.ExecuteNonQuery();
   if(executeresult > 0)
   {
      Response.Write("Update Success");
   }
   else
   {
      Response.Write("Unable to Update");
   }
   cmd.Dispose();
}

You don't need to use `ToString()` method for `initial_result` and `finalresult` since they are already strings. — Soner Gönül, Oct 17 '14 at 08:25

Append values to a column value in database

3 Answers3