insert multiple values into one column in sql database table

Question

I'm wondering if it's possible to add values to specific Database table cells?

Suppose I have an existing Database table and I add a new value to specific column in a row , how would I go about adding to the new value's column without overwriting the existing columns' rows?

Suppose I have these data for one user I want to insert new phone number to the phone column as it is shown in the image

I searched in google and i found this method

"INSERT INTO Users ( phone ) VALUES('99999975')"

but it gives an error

Cannot insert the value NULL into column 'cardID', column does not allow nulls. INSERT fails.

This is my code:

protected void btnInsert_Click(object sender, EventArgs e)
{

    try
    {
        SqlConnection c = new SqlConnection();
        c.ConnectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True";
        string s = "INSERT INTO Users ( phone ) VALUES('99999975')";

        SqlCommand sqlcom = new SqlCommand(s, c);

        //sqlcom.Parameters.AddWithValue("@phone", TextNum.Text);

        c.Open();
        SqlDataReader read = sqlcom.ExecuteReader();
        while (read.Read())
        {


            Label3.Text += "name : " + read["name"].ToString() + "<br/>";//start with +=
            Label3.Text += "password: " + read["password"].ToString() + "<br/>";
            Label3.Text += "phone : " + read["phone"].ToString() + "<br/>";
            Label3.Text += "email : " + read["email"].ToString() + "<br/><br/>";
            Label3.Text += "cardID : " + read["cardID"].ToString() + "<br/><br/>";

        }
        //sqlcom.ExecuteNonQuery();
        Label3.Text = "Insert successful";
        read.Close();
        //createTable();
        c.Close();
    }
    catch (Exception ee)
    {
        Label3.Text = ee.Message;
    }
}

Any help would be appreciated.

INSERT adds a new record to your table. It doesn't UPDATE an existing record. And in any case, if your table has a column named cardID that is marked to not accept NULL values then you have to provide a value for that column — Steve, Dec 12 '16 at 08:36
http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574 — e4c5, Dec 12 '16 at 08:36
This is definitely what you want. Take a loot at [here.][1] [1]: http://www.w3schools.com/sql/sql_update.asp — Kewin Rather, Dec 12 '16 at 08:38
Also, after following the advice in the link posted by @e4c5 you should rewiew your knowledge of ADO.NET. An Update or an Insert query while is executed also by ExecuteReader doesn't return a valid DataReader and thus the code after ExecuteReader will not be executed as you expect. You should call ExecuteNonQuery to insert or update and then provide an adeguate SELECT statement to read your stored values. — Steve, Dec 12 '16 at 08:45

Dmitry Bychenko · Accepted Answer · 2016-12-12T08:55:08.687

The server's response is quite clear:

Cannot insert the value NULL into column 'cardID', column does not allow nulls. INSERT fails.

You have to transform the query into

  string s = 
    @"INSERT INTO Users ( 
        phone,
        cardId)    -- you have to insert into this column
      VALUES(
        '99999975',
        '12346789') --todo: put actual CardId here";

...

  // wrap IDisposable (SqlCommand) into using
  using (SqlCommand sqlcom = new SqlCommand(s, c)) {
    // Just Execute, you can't return cursor from INSERT
    sqlcom.ExecuteNonQuery();
    ...
  }

score 0 · Answer 2 · answered Dec 12 '16 at 08:41

It seems your table requires "cardID" to be non-null, you can either alter the table to accept null values for that field, or alternatively pass an empty string for "cardID".

You should evaluate if having an empty cardID is ok or if may lead to bugs, because at the moment it is a required field, i guess it's so for a reason

Edit: if you instead want to change an existing phone number (and not add a new row), you should use an UPDATE query.

INSERT adds a new row

UPDATE let you modify one or more rows

score 0 · Answer 3 · answered Dec 12 '16 at 08:45

Your table has cardID as primary key, and by defintion, it can't be null. When you insert into a new row, you must set a value for each non-nullable columns. In your case, the column cardId is one of them, but it might not be the only one.

There are several ways to insert the data you want to insert and avoid that message. One way to accomplish what you want to do is set a value for the primary key by executing :

Insert into users (cardId, phone) values (1234,'99999');

Another way, is to set the primary key of Users table to be identity, and as a result, it will get its own auto generated id, and you will not need to set its value.

 CREATE TABLE Users
 (
   cardID int identity(1,1),
   phone varchar(10)
 );

insert into users(phone) values('99999967');

insert multiple values into one column in sql database table

3 Answers3