Problem saving Combobox data to Postgresql

Question

First this is the bank code I’m using:

CREATE TABLE perguntas (
    cod_pergunta SERIAL PRIMARY KEY NOT NULL,
    pergunta VARCHAR(500),
    opcao_um  VARCHAR(500),
    opcao_dois VARCHAR(500),
    opcao_tres VARCHAR(500),
    opcao_quatro VARCHAR(500),
    opcao_correta INTEGER,
    IDcategoria INTEGER,
    CONSTRAINT fk_categoria FOREIGN KEY (IDcategoria) REFERENCES categoria(cod_categoria)
);

CREATE TABLE categoria (
    cod_categoria SERIAL PRIMARY KEY NOT NULL,
    categoria VARCHAR(15),
    descricao VARCHAR(140)
);

I can save the values in the table questions however the combobox value 'category' is recording wrong when I try to save the first value.

Image of the form:

Code of the Save button:

    private void btnGravar_Click(object sender, EventArgs e)
        {
//Verifica qual radio button está selecionado
            int valor;
            valor = 0;
            if (rbCorreta1.Checked == true)
                valor = 1;
            else if (rbCorreta2.Checked == true)
                valor = 2;
            else if (rbCorreta3.Checked == true)
                valor = 3;
            else if (rbCorreta4.Checked == true)
                valor = 4;
            else
                MessageBox.Show("Selecione a resposta correta!");


//Verifica qual o valor do combobox está selecionado e guarda o ID para gravar
            string IndexSelecionado = cbCategoria.SelectedIndex.ToString();



            string str = "Host=127.0.0.1;Username=postgres;Password=adm;Database=dbquiz";
            string gravarsql = "INSERT INTO perguntas (pergunta, opcao_um, opcao_dois, opcao_tres, opcao_quatro, opcao_correta, idcategoria) " + " VALUES ('" + txtPergunta.Text + "', '" + txtResposta1.Text + "', '" + txtResposta2.Text + "', '" + txtResposta3.Text + "', '" + txtResposta4.Text + "', '" + valor + "', '"+ IndexSelecionado + "');";
            Npgsql.NpgsqlConnection con = new Npgsql.NpgsqlConnection(str);
            Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(gravarsql, con);
            cmd.CommandType = CommandType.Text;
            con.Open();

            try
            {
                int n = cmd.ExecuteNonQuery();
                if (n > 0)
                {
                    MessageBox.Show("Efetuado!");
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
            }
            finally
            {
                con.Close();
            }
        }

The error that appears is this below, but it only happens when I want to record the first value of the combobox because the other to record only records the wrong index:

Ex.

Categories: 1-Test, 2-Foo, 3-Stack; When I select 2, save 3.

Code to fill the Combobox:

string str = "Host=127.0.0.1;Username=postgres;Password=adm;Database=dbquiz";
            Npgsql.NpgsqlConnection con = new Npgsql.NpgsqlConnection(str);
            con.Open();

            try
            {
                string sql = "SELECT categoria.cod_categoria, categoria.categoria FROM categoria;";
                Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, con);
                Npgsql.NpgsqlDataReader reader = cmd.ExecuteReader();
                DataTable dt = new DataTable();
                dt.Load(reader);

                this.cbCategoria.DataSource = dt;

                this.cbCategoria.DisplayMember = "categoria";
                this.cbCategoria.ValueMember = "cod_categoria";

                reader.Close();
                reader.Dispose();

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                con.Close();
                con.Dispose();
            }

Answer 1

This happens because you are using the SelectedIndex as the category value, only that not necessarily these values (index of the combobox and id of the table) will match. In fact, it’s very difficult for that to happen.

What you need to save is the Id of selected category. How do you fill the combobox with DisplayMember and ValueMember, can just change a line of code to resolve this.

This line

string IndexSelecionado = cbCategoria.SelectedIndex.ToString();

Should be

string IndexSelecionado = cbCategoria.SelectedValue.ToString();

By the way, it would be nice to also change the name of the variable to not get confused, but this is with you.

---

I think it’s important to comment that you’re misusing the exceptions by showing only her message. One of the things you’ll need most when an exception occurs in the application is the stacktrace (that is being ignored) to be able to track it.

Another thing is that it would be nice to separate the responsibilities of the application, make the connection with the bank in one place and reuse it elsewhere. This avoids a lot of code repetition and helps maintenance later.

Of course these are not main points of the question, I just thought it important to quote them.