Sql lite exception failed constraint failed UNIQUE constraint failed: tbllocations.SiteID

Question

_{Wednesday, July 31, 2019 11:53 PM}

I am having an issue in editing information that was there before within my database.When ever i want to  edit a different

field i get the above error.I  am using DB browser for sqlite.Kindly assist on how to resolve it

//set execute query 
        private void ExecuteQuery(string txtQuery)
        {
            SetConnection();
            sql_con.Open();
            sql_cmd = sql_con.CreateCommand();
            sql_cmd.CommandText = txtQuery;
            sql_cmd.ExecuteNonQuery();
            sql_con.Close();
        }
        //set loadDB
        private void LoadData()
        {
            SetConnection();
            sql_con.Open();
            sql_cmd = sql_con.CreateCommand();
            string CommandText = "select *  from tbllocations ";
            DB = new SQLiteDataAdapter(CommandText, sql_con);
            DS.Reset();
            DB.Fill(DS);
            DT = DS.Tables[0];
            dataGridView1.DataSource = DT;
            sql_con.Close();
        }
        //add
        private void buttonadd_Click(object sender, EventArgs e)
        {
            string txtQuery = "insert into tbllocations ( SiteId,Description,OpsArea,BoxNo,ContractStartDate,ContractEndDate,OBEndDate,OBStartDate)values('" + textBoxSiteID.Text + "','" + textBoxDescription.Text + "','" + textBoxOpsArea.Text + "','" + textBoxBoxNumber.Text + "','" + dateTimePickerContractStartDate.Text + "','" + dateTimePickerContractEndDate.Text + "','"  + dateTimePickerOBEndDate.Text + "','" + dateTimePickerOBStartDate.Text + "')";
            
            ExecuteQuery(txtQuery);
            LoadData();
        }

        private void buttonEdit_Click(object sender, EventArgs e)
        {
            textBoxSiteID.Focus();
            //string txtQuery = " update tbllocations set Description = '" + textBoxDescription.Text+"' where  SiteId ='"+ textBoxSiteID.Text;
            //ExecuteQuery(txtQuery);
            //LoadData();

          
        }

All replies (1)

_{Thursday, August 1, 2019 2:05 AM ✅Answered | 1 vote}

Hi mcfee12345,

Thank you for posting here.

I make a simple test according to your code. I could reproduce your problem.

If you set SiteId to be primary key, it will mean SiteId will only have one value. If you add the same value to the different record, it will throw the exception.

You could look at the picture to understand it.

Therefore, there are two solutions.

First, you could delete primary key from the code, like the following.

 string sql = "create table test1(SiteId int ,Description nvarchar(20), ContractEndDate datetime)";

Second, you could keep primary key and set primary key to be autoincrement. Then, try to delete somes word in sql sentence.

Create table sql code:

 string sql = "create table test1(SiteId INTEGER PRIMARY KEY ,Description nvarchar(20), ContractEndDate datetime)";

insert sql code:

string txtQuery = "insert into tbllocations (Description,OpsArea,BoxNo,ContractStartDate,ContractEndDate,OBEndDate,OBStartDate)values('" + textBoxDescription.Text + "','" + textBoxOpsArea.Text + "','" + textBoxBoxNumber.Text + "','" + dateTimePickerContractStartDate.Text + "','" + dateTimePickerContractEndDate.Text + "','"  + dateTimePickerOBEndDate.Text + "','" + dateTimePickerOBStartDate.Text + "')";

Best Regards,

Jack

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [email protected].