Problem writing ComboBox data in Postgresql

1

First is the code of the bank 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 record the values in the questions table but the value of the 'category' combobox is writing wrong when I try to write the first value.

Image of the form:

Savebuttoncode:

privatevoidbtnGravar_Click(objectsender,EventArgse){//Verificaqualradiobuttonestáselecionadointvalor;valor=0;if(rbCorreta1.Checked==true)valor=1;elseif(rbCorreta2.Checked==true)valor=2;elseif(rbCorreta3.Checked==true)valor=3;elseif(rbCorreta4.Checked==true)valor=4;elseMessageBox.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 happens only when I want to record the 1st value of the combobox because the rest of it is to write only that it writes the wrong index:

Ex.

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

CodetopopulatetheCombobox:

stringstr="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();
            }
    
asked by anonymous 30.08.2017 / 15:12

1 answer

1

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

What you need to save is the Category Id selected . Since you fill the combobox with DisplayMember and ValueMember , you can only change a line of code to solve it.

This line

string IndexSelecionado = cbCategoria.SelectedIndex.ToString();

Should be

string IndexSelecionado = cbCategoria.SelectedValue.ToString();

Incidentally, it would be nice to also rename the variable so as not to get confused, but this is up to you.

I think it's important to note that you are misusing the exceptions by displaying only the message from it. One of the things you will most need when an exception is thrown in the application is the stacktrace (being ignored) so that you can track it.

Another thing is that it would be legal to separate the responsibilities of the application, to make the connection with the bank in one place only and to reuse it in other places. This avoids enough code repetition and helps with maintenance later.

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

    
30.08.2017 / 15:21