Use Inner Join with Bindingsource

Asked

Viewed 318 times

2

I’m trying to make a query in an access bank using inner join along with BindingSource, this Binding will send the data to a Datagridview.

However, when the data is loaded into the grid, the displayed results are from a select common and not the inner join I’ve been looking for the solution for some time and I couldn’t find it.

Follows code snippet to aggregate data to datagrid

private void VinculaDadosDataGridView()
    {
        try
        {
            conn = new OleDbConnection(strConexaoSQL);
            conn.Close();
            string query = @"SELECT Inventario.Código, Polos.Polo, Inventario.Tipo, Inventario.Equipamento, Inventario.Marca,
                             Inventario.Modelo, Inventario.NS, Inventario.Patrimonio, Inventario.[Constar no CC], Inventario.Usuario
                             FROM (Inventario
                             INNER JOIN Polos ON Inventario.Alocado_em = Polos.Código);";
            conn.Open();
            DataSet ds = new DataSet();
            OleDbCommand cmd = new OleDbCommand(query, conn);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            da.Fill(ds, "Inventario");
            bs.DataSource = ds;
            bs.DataMember = ds.Tables[0].TableName;
            dgvDados.DataSource = bs;
            formataGridView();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
        finally
        {
            //dr.Close();
            conn.Close();
        }
    }

I don’t know if it’s important, but it follows excerpt from formatteGridView

private void formataGridView()
    {
        var grd = dgvDados;
        //grd.AutoGenerateColumns = false;
        //grd.RowHeadersVisible = false;
        grd.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.DisplayedCellsExceptHeaders;
        grd.ColumnHeadersBorderStyle = DataGridViewHeaderBorderStyle.Single;
        grd.Columns[0].Visible = false;
        //altera a cor das linhas alternadas no grid
        grd.RowsDefaultCellStyle.BackColor = Color.White;
        grd.AlternatingRowsDefaultCellStyle.BackColor = Color.Aquamarine;
        /*//altera o nome das colunas
        grd.Columns[0].HeaderText = "Código";
        grd.Columns[1].HeaderText = "Alocado em";
        grd.Columns[2].HeaderText = "Tipo";
        grd.Columns[3].HeaderText = "Equipamento";
        grd.Columns[4].HeaderText = "Marca";
        grd.Columns[5].HeaderText = "Modelo";
        grd.Columns[6].HeaderText = "NS";
        grd.Columns[7].HeaderText = "Patrimônio";
        grd.Columns[8].HeaderText = "Constar no CC";
        grd.Columns[9].HeaderText = "Outros";
        //largura colunas
        grd.Columns[0].Width = 50;
        grd.Columns[1].Width = 150;
        grd.Columns[2].Width = 150;
        grd.Columns[3].Width = 150;
        grd.Columns[4].Width = 150;
        grd.Columns[5].Width = 150;
        grd.Columns[6].Width = 150;
        grd.Columns[7].Width = 150;
        grd.Columns[8].Width = 150;
        grd.Columns[9].Width = 150;*/
        //seleciona a linha inteira
        grd.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
        //não permite seleção de multiplas linhas
        grd.MultiSelect = false;
        // exibe nulos formatados
        //grd.DefaultCellStyle.NullValue = " - ";
        //permite que o texto maior que célula não seja truncado
        grd.DefaultCellStyle.WrapMode = DataGridViewTriState.True;
    }

Upshot resultado

Notice that the column alocado_em continues in ID

1 answer

1


Oledbcommand receives a normal database query.

See if this one works:

string query = @"SELECT
Inv. Code
, Polos.Polo
, Inv. Type
, Inv. Equipment
, Inv. Brand
, Inv. Model
, Inv.NS
, Inv. Assets
, Inv. [appear in the CC]
, Inv. User
FROM inventario Inv
INNER JOIN POLES
ON Inv.alocado_em = poles.code;"

Regarding the Oledbdataadapter leave only the Dataset with an argument.

da. Fill(ds)

  • It didn’t work and when I change the da. Fill(ds, "Invincible") to da.Fill(ds) the code ignores the formatting where I hide the code field (yes, the datagrid needs to have this field and hide it).

  • Data returning wrong yet? Bs.Datamember = "Inventory";

  • Still returning wrong, I realized that even removing some field from the query, the code ignores and brings back the field even so, as for Datamember, it did not accept the way you indicated.

  • Tries calling the formatteGridView() method after the Finally block{}

  • Also it was not, even without the formatteGridView it does not show properly, it gives the normal select without JOIN and picks all same deleting.

  • I did a test and filled Datagridview directly with Dataset, apparently who is ignoring the query is Bindingsource, I do not know if this will help, I am using Bindingsource to use the "Filter" function in future queries.

Show 1 more comment

Browser other questions tagged

You are not signed in. Login or sign up in order to post.