CODEWITHSUNDEEP
c#sqlasp.netdatabase
moon light
moon light

Reputation: 23

insert multiple values into one column in sql database table

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.

Upvotes: 1

Views: 4990

Answers (3)

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186823

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();
    ...
  }

Upvotes: 2

Ahmad
Ahmad

Reputation: 12737

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');

Upvotes: 0

Jacopo
Jacopo

Reputation: 527

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

Upvotes: 0

Related Questions