0

When creating an account, values cannot be inserted and error shows that column (PatronID), which is the primary key, does not allow nulls. Hence, the INSERT fails

Have tried going into the SQL Server table designer to change "Is Identity", but I am unable to save the changes 

public int add() 
{
    string strConn = ConfigurationManager.ConnectionStrings["BABAFOODSConnectionString"].ToString();

    SqlConnection conn = new SqlConnection(strConn);

    SqlCommand cmd = new SqlCommand("INSERT INTO Patron (PatronName, PatronEmail, PatronPassword)" +
                                    "OUTPUT INSERTED.PatronID " +
                                    "VALUES(@name, @email, @password)", conn);

    cmd.Parameters.AddWithValue("@name", PatronName);
    cmd.Parameters.AddWithValue("@email", PatronEmail);
    cmd.Parameters.AddWithValue("@password", PatronPassword);

    conn.Open();
    int id = (int) cmd.ExecuteScalar();
    conn.Close();

    return id;
}

At the ExecuteScalar, I get this error:

System.Data.SqlClient.SqlException: 'Cannot insert the value NULL into column 'PatronID', table 'BABAFOODS.dbo.Patron'; column does not allow nulls. INSERT fails.'

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Kkk
  • 21
  • 1
  • 2
  • as the error says: *Cannot insert the value NULL into column 'PatronID', table 'BABAFOODS.dbo.Patron'; column does not allow nulls. INSERT fails* the column is set to NOT NULL and you are trying to insert `NULL` – Prashant Pimpale Jan 13 '19 at 07:42
  • 4
    Please show your table definition. – Tim Biegeleisen Jan 13 '19 at 07:42
  • 1
    Make sure it set to `primary key` and `identity (1,1)` – Prashant Pimpale Jan 13 '19 at 07:42
  • 1
    Unrelated tips: SqlConnection and SqlCommand are both IDisposable so each should be in a `using` block. Once you've done that, you don't need to Close the connection because it will be closed by the implicit Dispose as it exits the block. You may want to read [can we stop using AddWithValue](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/). – Richardissimo Jan 13 '19 at 07:45
  • @PrashantPimpale where can I set the identity? – Kkk Jan 13 '19 at 07:45
  • 1
    If the column is *not* defined as identity, but as `NOT NULL`, then **you MUST** provide a value from your C# code. And you **cannot** change the identity flag on an existing column - you can only **create** a new column which is defined as identity, and then make that the primary key. – marc_s Jan 13 '19 at 07:55
  • Possible duplicate of [Adding an identity to an existing column](https://stackoverflow.com/questions/1049210/adding-an-identity-to-an-existing-column) – mjwills Jan 13 '19 at 11:40

1 Answers1

-1

Just use auto increment on your ID for your table.

Look https://www.w3schools.com/sql/sql_autoincrement.asp

Esperento57
  • 16,521
  • 3
  • 39
  • 45
  • This answer is basically link only. And the OP is on SQL Server not MySQL so the link isn't even relevant – Martin Smith Jan 13 '19 at 13:15
  • Not a link only, i explain his problem. And the link is about multiple type SGBD, read better before critics and note for Nothing... – Esperento57 Jan 13 '19 at 13:29