none
C# - Insert data in one table from another two tables data

    Question

  • Hi developers, i'm new in C# and need to solve a (3 days) problem.
    I have 3 Tables
    Table 1 - fields >   cod1 - name1 - bool1
    Table 2 - fields >   cod1 - name2 - bool2 
    and Table 3 - fields > cod1 - cod2
    Ím using tableadapters and all in one form
    My mission is , - IF bool1=true and bool2=true >>> Insert cod1 and cod2 in Table3
    Maybe is so simple and i'm too confuse with tableadapters.

    Tuesday, June 02, 2009 6:29 PM

Answers

  • Solved. simple way... ty all

    private

     

    void btnIncluir_Click(object sender, EventArgs e)

    {

     

    DataRowView param1 = this.disciplinasBindingSource.Current as DataRowView;

     

    DataRowView param2 = this.anosBindingSource.Current as DataRowView;

     

    this.disciplinas_anosBindingSource.EndEdit();

     

    try

    {

     

    this.disciplinas_anosTableAdapter.Insert(Convert.ToString(param1["cod_dis"]), Convert.ToString(param2["cod_ano"]), 0);

     

    this.disciplinas_anosTableAdapter.Update(this.abc_gestDataSet);

    }

     

    catch (System.Exception ex)

    {

     

    MessageBox.Show("Disciplinas j est associada");

    }

     

    finally

    {

    RefreshDisAno();

    }

    }

    and delete




     

    private void btnExcluir_Click(object sender, EventArgs e)

    {

     

    DataRowView param1 = this.disciplinasporanoBindingSource.Current as DataRowView;

     

    this.disciplinas_anosBindingSource.EndEdit();

     

    try

    {

     

    this.disciplinas_anosTableAdapter.Delete(Convert.ToString(param1["cod_ano"]), Convert.ToString(param1["cod_dis"]), 0);

     

    this.disciplinas_anosTableAdapter.Update(this.abc_gestDataSet);

    }

     

    catch (System.Exception ex)

    {

     

    MessageBox.Show("Falha ao Excluir.");

    }

     

    finally

    {

    RefreshDisAno();

    }

    }

    and a refresh grid after insert or delete

     

    private void RefreshDisAno()

    {

     

    DataRowView param1 = this.anosBindingSource.Current as DataRowView;

     

    try

    {

     

    this.disciplinasporanoTableAdapter.Fill(this.abc_gestDataSet.disciplinasporano, Convert.ToString(param1["cod_ano"]));

    }

     

    catch (System.Exception ex)

    {

    System.Windows.Forms.

    MessageBox.Show(ex.Message);

    }

    }

    Friday, June 05, 2009 2:07 PM

All replies

  • Can you post the code that you have so far?

    There are so many ways to do this ...depending on how you have it set up. So seeing your approach would allow us to help you better.
    www.insteptech.com
    We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!
    Tuesday, June 02, 2009 6:45 PM

  • Im using (RAD-vs2008) design interface to create form and drag and drop 3 tables.

    anos - disciplinas - disciplinas_anos


    Table anos have a cod_ano field (PK) , table disciplinas have cod_dis field (pk) and esc_dis bool field.

    With a anos(cod_ano) selected in a grid , i check some disciplinas(esc_dis) to true in another grid.
    after click save toolstripbutton i need to
    1-delete all rows in disciplinas_anos where disciplinas_anos.cod_ano = anos.cod_ano, next
    2-insert into disciplinas_anos (cod_ano,cod_dis) values (@cod_ano,@cod_dis)

    @cod_ano - from anos.cod_ano 
    @cod_dis - from disciplinas.cod_dis  where esc_dis=true


    ;) Im just came from Pascal - c# rules lol

     
    The anos table structure:
      this.columncod_ano = new global::System.Data.DataColumn("cod_ano", typeof(string), null, global::System.Data.MappingType.Element);
                    base.Columns.Add(this.columncod_ano);
                    this.columndes_ano = new global::System.Data.DataColumn("des_ano", typeof(string), null, global::System.Data.MappingType.Element);
                    base.Columns.Add(this.columndes_ano);
                    this.columnord_ano = new global::System.Data.DataColumn("ord_ano", typeof(decimal), null, global::System.Data.MappingType.Element);
                    base.Columns.Add(this.columnord_ano);
                    this.columncic_ens = new global::System.Data.DataColumn("cic_ens", typeof(string), null, global::System.Data.MappingType.Element);
                    base.Columns.Add(this.columncic_ens);
                    this.columndis_ano = new global::System.Data.DataColumn("dis_ano", typeof(string), null, global::System.Data.MappingType.Element);
                    base.Columns.Add(this.columndis_ano);
                    this.columngru_ano = new global::System.Data.DataColumn("gru_ano", typeof(decimal), null, global::System.Data.MappingType.Element);
                    base.Columns.Add(this.columngru_ano);
                    this.columncon_cot = new global::System.Data.DataColumn("con_cot", typeof(string), null, global::System.Data.MappingType.Element);
                    base.Columns.Add(this.columncon_cot);
                    this.columnesc_imp = new global::System.Data.DataColumn("esc_imp", typeof(bool), null, global::System.Data.MappingType.Element);
                    base.Columns.Add(this.columnesc_imp);
    Table Disciplinas structure is:

                    this.columncod_dis = new global::System.Data.DataColumn("cod_dis", typeof(string), null, global::System.Data.MappingType.Element);
                    base.Columns.Add(this.columncod_dis);
                    this.columndes_dis = new global::System.Data.DataColumn("des_dis", typeof(string), null, global::System.Data.MappingType.Element);
                    base.Columns.Add(this.columndes_dis);
                    this.columnins_dis = new global::System.Data.DataColumn("ins_dis", typeof(bool), null, global::System.Data.MappingType.Element);
                    base.Columns.Add(this.columnins_dis);
                    this.columnman_dis = new global::System.Data.DataColumn("man_dis", typeof(string), null, global::System.Data.MappingType.Element);
                    base.Columns.Add(this.columnman_dis);
                    this.columnord_dis = new global::System.Data.DataColumn("ord_dis", typeof(decimal), null, global::System.Data.MappingType.Element);
                    base.Columns.Add(this.columnord_dis);
                    this.columnesc_dis = new global::System.Data.DataColumn("esc_dis", typeof(bool), null, global::System.Data.MappingType.Element);
                    base.Columns.Add(this.columnesc_dis);
                    this.columnpar_dis = new global::System.Data.DataColumn("par_dis", typeof(string), null, global::System.Data.MappingType.Element);
                    base.Columns.Add(this.columnpar_dis);
                    this.columndim_dis = new global::System.Data.DataColumn("dim_dis", typeof(string), null, global::System.Data.MappingType.Element);
                    base.Columns.Add(this.columndim_dis);
                    this.columntip_dis = new global::System.Data.DataColumn("tip_dis", typeof(string), null, global::System.Data.MappingType.Element);
                    base.Columns.Add(this.columntip_dis);
                    this.columnman_dis2 = new global::System.Data.DataColumn("man_dis2", typeof(string), null, global::System.Data.MappingType.Element);
                    base.Columns.Add(this.columnman_dis2);
                    this.columntip_dis2 = new global::System.Data.DataColumn("tip_dis2", typeof(string), null, global::System.Data.MappingType.Element);
                    base.Columns.Add(this.columntip_dis2);
    Table DisciplinasAnos structure is:
                    this.columncod_dis = new global::System.Data.DataColumn("cod_dis", typeof(string), null, global::System.Data.MappingType.Element);
                    base.Columns.Add(this.columncod_dis);
                    this.columncod_ano = new global::System.Data.DataColumn("cod_ano", typeof(string), null, global::System.Data.MappingType.Element);
                    base.Columns.Add(this.columncod_ano);
                    this.columnpes_dis = new global::System.Data.DataColumn("pes_dis", typeof(decimal), null, global::System.Data.MappingType.Element);
                    base.Columns.Add(this.columnpes_dis);


    using System;
    
    using System.Collections.Generic;
    
    using System.ComponentModel;
    
    using System.Data;
    
    using System.Drawing;
    
    using System.Linq;
    
    using System.Text;
    
    using System.Windows.Forms;
    
    using System.Data.SqlClient;
    
    namespace ABCGESTPED.NET
    
    {
    
    public partial class FrmAnos : Form
    
    {
    
    public FrmAnos()
    
    {
    
    InitializeComponent();
    
    }
    
    private void anosBindingNavigatorSaveItem_Click(object sender, EventArgs e)
    
    {
    
    this.Validate();
    
    this.anosBindingSource.EndEdit();
    
    this.tableAdapterManager.UpdateAll(this.abc_gestDataSet);
    } private void FrmAnos_Load(object sender, EventArgs e) { // TODO: This line of code loads data into the 'abc_gestDataSet.disciplinas' table. You can move, or remove it, as needed. this.disciplinasTableAdapter.Fill(this.abc_gestDataSet.disciplinas); // TODO: This line of code loads data into the 'abc_gestDataSet.disciplinas_anos' table. You can move, or remove it, as needed. this.disciplinas_anosTableAdapter.Fill(this.abc_gestDataSet.disciplinas_anos); // TODO: This line of code loads data into the 'abc_gestDataSet.anos' table. You can move, or remove it, as needed. this.anosTableAdapter.Fill(this.abc_gestDataSet.anos); } } }
    Tuesday, June 02, 2009 7:22 PM
  • Something like what you are trying to do sounds like you'd want to use a bit of SQL on it.  I also want some clarification on how you are comparing the two tables.  Are you

    1)  Comparing all rows of Table #1 with all rows of Table #2?
    2)  Comparing row N of Table #1 with row N of Table #2?
    3)  Using your own relationship to match a row from Table #1 to Table #2?

    You could potentially just dump product of a JOIN into Table #3 right on the database using a stored procedure using an IF conditional in your WHERE statement.
    Tuesday, June 02, 2009 8:22 PM
  • Ty for reply wheaties


    1) I dont compare table 1 with table 2, they are independent

    The table 3 have cod_ano from table 1 and cod_dis from table2 where esc_dis(bit) from table2 = true 
    After that i clean esc_dis value to false.
    Maybe i can use table2grid selectedrows?

    I make some queries and add in the tableadapters in my dataset
    Like  
    DELETE FROM disciplinas_anos
    WHERE     (cod_ano = @Original_cod_ano)   --- in the disciplinas_anostableadapter
    to clean cod_anos from disciplinas

    Make other for insert data INSERT INTO [disciplinas_anos] ([cod_dis], [cod_ano], [pes_dis]) VALUES (@cod_dis, @cod_ano, @pes_dis);

    Other to retrieve only cod_dis where esc_dis=true
    The esc_dis(bit) field is a autogenerate checkbox in disciplinas grid.
    The save (toolstrip) is workin good
    But after save i want to insert in another table the fields cod_ano(many) and cod_dis(unique)

    Ty
    Tuesday, June 02, 2009 9:45 PM
  • Solved. simple way... ty all

    private

     

    void btnIncluir_Click(object sender, EventArgs e)

    {

     

    DataRowView param1 = this.disciplinasBindingSource.Current as DataRowView;

     

    DataRowView param2 = this.anosBindingSource.Current as DataRowView;

     

    this.disciplinas_anosBindingSource.EndEdit();

     

    try

    {

     

    this.disciplinas_anosTableAdapter.Insert(Convert.ToString(param1["cod_dis"]), Convert.ToString(param2["cod_ano"]), 0);

     

    this.disciplinas_anosTableAdapter.Update(this.abc_gestDataSet);

    }

     

    catch (System.Exception ex)

    {

     

    MessageBox.Show("Disciplinas j est associada");

    }

     

    finally

    {

    RefreshDisAno();

    }

    }

    and delete




     

    private void btnExcluir_Click(object sender, EventArgs e)

    {

     

    DataRowView param1 = this.disciplinasporanoBindingSource.Current as DataRowView;

     

    this.disciplinas_anosBindingSource.EndEdit();

     

    try

    {

     

    this.disciplinas_anosTableAdapter.Delete(Convert.ToString(param1["cod_ano"]), Convert.ToString(param1["cod_dis"]), 0);

     

    this.disciplinas_anosTableAdapter.Update(this.abc_gestDataSet);

    }

     

    catch (System.Exception ex)

    {

     

    MessageBox.Show("Falha ao Excluir.");

    }

     

    finally

    {

    RefreshDisAno();

    }

    }

    and a refresh grid after insert or delete

     

    private void RefreshDisAno()

    {

     

    DataRowView param1 = this.anosBindingSource.Current as DataRowView;

     

    try

    {

     

    this.disciplinasporanoTableAdapter.Fill(this.abc_gestDataSet.disciplinasporano, Convert.ToString(param1["cod_ano"]));

    }

     

    catch (System.Exception ex)

    {

    System.Windows.Forms.

    MessageBox.Show(ex.Message);

    }

    }

    Friday, June 05, 2009 2:07 PM