-3

This code is not working. I'm trying to insert the data into SQL Server:

int a = 1;

DateTime cDate;
cDate = DateTime.Today;

string insertString = "insert into tbl_complaint(Date, User_id, department_name, Product_name, complaint_details, Status) values (@date, @uid, @dept, @product, @details, @status)";

SqlCommand cmd = new SqlCommand(insertString, con);
cmd.CommandType = CommandType.Text;

cmd.Parameters.AddWithValue("@date", cDate);
cmd.Parameters.AddWithValue("@uid", a);
cmd.Parameters.AddWithValue("@dept", ddlDept.SelectedValue);
cmd.Parameters.AddWithValue("@product", txtPName.Text);
cmd.Parameters.AddWithValue("@details", txtCDes.Text);
cmd.Parameters.AddWithValue("@status", "Submited");

try {
    int check;
    con.Open();

    check= cmd.ExecuteNonQuery();

    con.Close();
}

The error that I get:

SqlException (0x80131904):
Cannot insert the value NULL into column 'Complaint_id', table 'complaint_management.dbo.tbl_complaint'; column does not allow nulls.
INSERT fails. The statement has been terminated

System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) +2442126
System.Data.SqlClient.SqlInternalConnection.OnError(SqlExcep‌​tion exception, Boolean breakConnection, Action1 wrapCloseInAction) +5736904

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
rahul mundaware
  • 7
  • 6
  • 3
    Please post error message – Etienne May 05 '17 at 01:14
  • it is not showing any error message – rahul mundaware May 05 '17 at 01:15
  • @rahulmundaware, remove the try catch. you will see the errors. – Bagus Tesa May 05 '17 at 01:18
  • [SqlException (0x80131904): Cannot insert the value NULL into column 'Complaint_id', table 'complaint_management.dbo.tbl_complaint'; column does not allow nulls. INSERT fails. The statement has been terminated.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +2442126 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +5736904 – rahul mundaware May 05 '17 at 01:22
  • 2
    Well, `Complaint_id` is a primary key, right? You need to insert value manually if it isn't identity autoincrement column with that query (include `Complaint_id` field). – Tetsuya Yamamoto May 05 '17 at 01:25
  • I'm guessing Complaint_id is your primary key column ? If that's the case make sure it's set up properly as auto-increment. Otherwise you need to provide a valid value to insert for that Column (since it cannot be null). – Etienne May 05 '17 at 01:26
  • i have tried to give auto increment but sql server not allowing me to do so – rahul mundaware May 05 '17 at 01:30
  • Why autoincrement not allowed there? Is that column already filled with other data? If it's the case, the `Complaint_id` must be included in query (insert primary key manually) and ensure that no other same PK value exists before inserting. – Tetsuya Yamamoto May 05 '17 at 01:34
  • @rahulmundaware: If you can not give auto increment in sql. Maybe you can get max(Complaint_id) and then add 1. Remember to add complaint_id field into Insert query. – Tomato32 May 05 '17 at 03:03
  • @TetsuyaYamamoto and @Etienne: An already defined table cannot have an `IDENTITY` column added after data has been added (SQL Server). The best thing to do would be to replicate the table and data and recreate the original table with the Identity column assigned at the time. – Mad Myche May 05 '17 at 03:14
  • You can do `ALTER TABLE...SWITCH` following with rename trick as given there: http://stackoverflow.com/a/1730868/6378815. Since you can't change directly a PK column which already have inserted data to identity column, you need another table with same structure for temporary switch when setting identity column. – Tetsuya Yamamoto May 05 '17 at 03:57
  • You should give table structure to make question easy to be answered – Joko Wandiro May 05 '17 at 09:34

1 Answers1

0

Looks like you have a column 'Complaint_id' in your table with the Not Null property. You could take any of the below mentioned steps:

  1. Send a value for the 'Complaint_id' column through your insert query
  2. Change the property of the column 'Complaint_id' to accept NULL value and if it is present as Primary Key, then change it too.
  3. Change the property of the column 'Complaint_id' to make it an identity column to produce the value Automatically.
ROY
  • 63
  • 8