Problem writing data to Postgres

2

I have two tables in postgres (PRODUCT and CATEGORY)

In the PRODUCT table I have the FIELDS - > ID, productName, description, value, Category ID .

In the CATEGORY table I have the FIELDS - > Category ID

In my project in Visual Studio, I created a form to write new categories. Informing only an ex NAME (Perfume, Accessories etc).

And I also have a PRODUCT registration form where you need to enter the Product name, description, value and select a category code that fits (choose in combobox)

My problem is now: I can not save the PRODUCT form because COMBOBOX is related to the CATEGORY NAME because the person needs to know what to select and not see the codes. And in POSTGRES the field is INTEGER (categoryID), so it does not write the NAME.

LOAD event of the form PRODUCT. (old)

 private void CadastroProduto_Load(object sender, EventArgs e)
    {
        banco conexao = new banco(); // Classe de conexão.

        // Como carregar dados que estão no PostgreSQL no ComboBox

        NpgsqlCommand cmd = new NpgsqlCommand();
        cmd.Connection = conexao.conecta(); // Abre conexão
        cmd.CommandText = "Select * from categoria"; // Seleciona a tabela
        cmd.ExecuteNonQuery(); // Executa a busca
        try
        {
            NpgsqlDataReader ler = cmd.ExecuteReader(); ; // Leitura de dados

            while (ler.Read()) // Enquanto tiver campos para ler 
            {
                cmbCategoriaProduto.Items.Add(ler["nome"]); 
            }
            ler.Close();
            ler.Dispose();

        }
        catch(Exception ex)
        {
            MessageBox.Show(ex.Message);
        }

        conexao.desconecta();
    }

FORM LOAD event updated (Updated: 05/10):

private void CadastroProduto_Load(object sender, EventArgs e)
    {
        banco conexao = new banco(); // Classe de conexão.

        // Como carregar dados que estão no PostgreSQL no ComboBox

        NpgsqlCommand cmd = new NpgsqlCommand();
        cmd.Connection = conexao.conecta(); // Abre conexão
        cmd.CommandText = "Select cod_categoria, nome from categoria"; // Seleciona a tabela
        cmd.ExecuteNonQuery(); // Executa a busca
        try
        {
            NpgsqlDataReader ler = cmd.ExecuteReader(); ; // Leitura de dados
            DataTable dt = new DataTable();

            dt.Columns.Add("cod_categoria", typeof(string));
            dt.Columns.Add("nome", typeof(string));
            dt.Load(ler);

            cmbCategoriaProduto.DisplayMember = "nome";
            cmbCategoriaProduto.ValueMember = "cod_categoria";
            cmbCategoriaProduto.DataSource = dt;
           // while (ler.Read()) // Enquanto tiver campos para ler 
           //{
           //     cmbCategoriaProduto.Items.Add(ler["nome"]); 
           // }
            ler.Close();
            ler.Dispose();

        }
        catch(Exception ex)
        {
            MessageBox.Show(ex.Message);
        }

        conexao.desconecta();
    }

ComboBox SelectedIndex Event

private void cmbCategoriaProduto_SelectedIndexChanged(object sender, EventArgs e)
    {
        string id = cmbCategoriaProduto.SelectedValue.ToString();
    }

Even though the error occurred while writing: Error:

Attributesandmethods

//AtribulosprivatestringNome;privatestringDescricao;privatedoubleValor;privatestringcCategoria;//metodoconstrutorpublicproduto(stringpn,stringpd,doublepv,stringpccat){Nome=pn;Descricao=pd;cCategoria=pccat;Valor=pv;}

ProductClass-IncludeMethod();

publicvoidIncluirProduto(){NpgsqlCommandcmd=newNpgsqlCommand();cmd.Connection=conexao.conecta();//Instaciaometodoconecta()queestánaclasseBANCOcmd.CommandText="Insert into produto (nome, descricao, cod_categoria, valor) values(@nome,@descricao,@cod_categoria,@valor)";
        cmd.Parameters.Add(new NpgsqlParameter("@nome", Nome)); // atributo e campo declarado banco de dados.
        cmd.Parameters.Add(new NpgsqlParameter("@descricao", Descricao));
        cmd.Parameters.Add(new NpgsqlParameter("@cod_categoria", cCategoria));
        cmd.Parameters.Add(new NpgsqlParameter("@valor", Valor));

        cmd.ExecuteNonQuery();
        conexao.desconecta(); // instancia o metodo desconecta() que está na classe BANCO
    }

Register button

private void btnCadastrarProduto_Click(object sender, EventArgs e)
    {
        try
        {
            produto pdt = new produto(txtNomeProduto.Text, txtDescricaoProduto.Text, Convert.ToDouble(txtValorProduto.Text),cmbCategoriaProduto.Text);
            pdt.IncluirProduto();
            MessageBox.Show("Produto Incluso com sucesso!");        
        }
        catch (Exception ex)    
        {
            MessageBox.Show(ex.ToString());
        }
    }
    
asked by anonymous 01.10.2015 / 20:54

2 answers

1

As far as I could understand,

You are filling in the combobox using a query to the database, and in that query you get only the name of the category. The product table has only the category identifier.

If this is the problem, it can be solved in the following ways:

First solution, in this question the situation is similar. What you can do is that the query to populate the combobox has the two columns, identifier and category name, and use the ValueMember property for the CategoryID column and the property DisplayMember to the CategoryName column. As is shown in the example of question .

Second solution, if the category names do not repeat themselves in the table, re-search the selected category name in the category table by searching for the identifier and fill in the products table (I do not like the idea).

    
01.10.2015 / 21:35
3

You could use a dictionary when loading your combobox.

Dictionary<string, int> options = new Dictionary<string, int>();

//função que você chama para carregar seu combobox
//dentro do loop de leitura do banco de dados
options.Add(nomeCategoria, ID_categoria);

Now where you call Save from your form, you just need to retrieve the id equivalent to the category name.

int idDesejado;
options.TryGetValue(nomeSelecionado, out idDesejado)
    
01.10.2015 / 21:33