duplicate key value violates unique constraint (primary key)

Hi I have a program here that stores the checked values in checkedlistbox to database. The problem is I always encounter an exception saying "duplicate key value violates unique constraint pk_famcon." I already tried other alternatives but it would always end up on this excpetion. 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Npgsql;

namespace WindowsFormsApplication1
{
    public partial class Form8 : Form
    {
        public Form8()
        {
            InitializeComponent();
            this.Load += Form8_Load;
            button1.Click += button1_Click;
        }

            DataSet ds = new DataSet();

        private void Form8_Load(object sender, EventArgs e)
        {
            Populate_DataSet();
            FillCheckListBox();
        }

        private void Populate_DataSet()
        {
            string connstring = "Server=localhost;Port=5432;User Id=postgres;Password=021393;Database=postgres;";
            using (NpgsqlConnection conn = new NpgsqlConnection(connstring))
            {
                string conditionName = "SELECT * FROM condition";
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(conditionName, conn);
                da.Fill(ds, "conname");
                da.Fill(ds, "conid");
            }
        }

        private void FillCheckListBox()
        {
            DataRow row1 = null;
            int iRowCnt = 0;

            checkedListBox1.Items.Clear();

            foreach (DataRow row_1 in ds.Tables["conname"].Rows)
            {
                row1 = row_1;
                checkedListBox1.Items.Add(ds.Tables["conname"].Rows[iRowCnt][1]);
                iRowCnt = iRowCnt + 1;     
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            MessageBox.Show("Data has been saved");
          if (checkedListBox1.Items.Count > 0)
          {
              string connstring = ("Server=localhost;Port=5432;User Id=postgres;Password=021393;Database=postgres;");
              NpgsqlConnection conn = new NpgsqlConnection(connstring);
              conn.Open();

              for (int i = 0; i <= checkedListBox1.CheckedItems.Count - 1; i++)
              {
                  NpgsqlCommand cmd = new NpgsqlCommand("Insert into famhistory(famcon) Values (@famcon)", conn);
                  cmd.Parameters.AddWithValue("@famcon", checkedListBox1.Text);
                  cmd.ExecuteNonQuery();
                  string value = checkedListBox1.CheckedItems[i].ToString(); 
              }
              MessageBox.Show("Data has been saved");
              conn.Close();
          }

        }
    }
}
Jon Skeet
people
quotationmark

Look at this loop:

for (int i = 0; i <= checkedListBox1.CheckedItems.Count - 1; i++)
{
    NpgsqlCommand cmd = new NpgsqlCommand("Insert into famhistory(famcon) Values (@famcon)", conn);
    cmd.Parameters.AddWithValue("@famcon", checkedListBox1.Text);
    cmd.ExecuteNonQuery();
    string value = checkedListBox1.CheckedItems[i].ToString(); 
}

You're inserting the same value (checkedListBox1.Text) on every iteration of the loop... into a primary key field, by the sounds of it. You're not using i (the index in the loop) until the final statement - and that's just declaring and giving a value to a local variable which immediately goes out of scope. I suspect you meant this:

for (int i = 0; i <= checkedListBox1.CheckedItems.Count - 1; i++)
{
    NpgsqlCommand cmd = new NpgsqlCommand("Insert into famhistory(famcon) Values (@famcon)", conn);
    string value = checkedListBox1.CheckedItems[i].ToString(); 
    cmd.Parameters.AddWithValue("@famcon", value);
    cmd.ExecuteNonQuery();
}

Aside from that, a few other things worth changing:

  • The loop condition would be written more conventionally as:

    for (int i = 0; i < checkedListBox1.CheckedItems.Count; i++)

... or use a foreach loop.

  • You should use a using statement to dispose of your command each time
  • It's generally cleaner to specify the parameter type explicitly, and then set the parameter value with the Value property

Put these together, and you have:

foreach (var item in checkedListBox1.CheckedItems)
{
    using (var cmd = new NpgsqlCommand("Insert into famhistory(famcon) Values (@famcon)", conn)
    {
        cmd.Parameters.Add("@famcon", NpgsqlDbType.Varchar).Value = item.ToString();
        cmd.ExecuteNonQuery();
    }
}

people

See more on this question at Stackoverflow