none
Sql lite exception failed constraint failed UNIQUE constraint failed: tbllocations.SiteID RRS feed

  • Question

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


    Wednesday, July 31, 2019 11:53 PM

Answers

  • 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 MSDNFSF@microsoft.com.

    • Marked as answer by mcfee12345 Friday, August 2, 2019 5:36 AM
    Thursday, August 1, 2019 2:05 AM
    Moderator