How to display in grideview 2 tables all colomns of 1st table and 1 colomn from 2nd table?

Question

1.00/5 (1 vote)

See more:

i want display 2 tables in single data gridview all tables from 1st table and 1 column from 2nd table.
i tried but there show only blank cells, not show stored data
help me, what should i do?

What I have tried:

public void disp_data()
       {

            DataTable dt = new DataTable();
           {
               SqlConnection con = new SqlConnection(strcon);
               dataGridView1.DataSource = null;
               dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
               con.Open();
               SqlCommand cmd = new SqlCommand("select id,cmpny_dtl.c_name as Name,cmpny_dtl.adrs as address,cmpny_dtl.state as state,s.State as state,cmpny_dtl.c_phone as phone,cmpny_dtl.wbsit as website" + " from cmpny_dtl join State s on s.State = s.State", con);
               SqlDataAdapter da = new SqlDataAdapter(cmd);
               da.Fill(dt);
               con.Close();
               dataGridView1.ColumnCount = 9;
               dataGridView1.Columns[0].HeaderText = "id";
               dataGridView1.Columns[0].DataPropertyName = "id";
               dataGridView1.Columns[0].Visible = false;
               dataGridView1.Columns[1].HeaderText = "Name";
               dataGridView1.Columns[1].DataPropertyName = "c_name";
               dataGridView1.Columns[2].HeaderText = "address";
               dataGridView1.Columns[2].DataPropertyName = "adrs";
               dataGridView1.Columns[3].HeaderText = "GST";
               dataGridView1.Columns[3].DataPropertyName = "GST";
               //dataGridView1.Columns[3].Visible = false;
               dataGridView1.Columns[4].HeaderText = "state";
               dataGridView1.Columns[4].DataPropertyName = "State";
               //dataGridView1.Columns[4].Visible = false;
               dataGridView1.Columns[5].HeaderText = "phone";
               dataGridView1.Columns[5].DataPropertyName = "c_phone";
               dataGridView1.Columns[6].HeaderText = "website";
               dataGridView1.Columns[6].DataPropertyName = "wbsit";
               dataGridView1.Columns[7].HeaderText = "servicetax";
               dataGridView1.Columns[7].DataPropertyName = "servicetax";
               dataGridView1.Columns[8].HeaderText = "adsnldtl";
               dataGridView1.Columns[8].DataPropertyName = "adsnldtl";
               dataGridView1.DataSource = dt;
           }

       }

Posted 12-Jun-17 19:04pm

Deekshaa Singh Chauhan

Updated 13-Jun-17 23:29pm

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RickZeeland · Accepted Answer · 2017-06-12T20:40:00

Solution 1

You could create a View on the SQL Server, test it, and use that as your DataSource.
Using a BindingSource is recommended, see this example: DataGridView.DataSource Property (System.Windows.Forms)[^]
Also there could be a problem with your join, take a look here for a join example:
SQL Joins[^]
I think the join should be like this:

SQL

from cmpny_dtl join State s on s.State = cmpny_dtl.State

Posted 12-Jun-17 20:40pm

RickZeeland

Updated 12-Jun-17 23:29pm

v4

Comments

Deekshaa Singh Chauhan 13-Jun-17 5:07am

this is not helpfull

RickZeeland 13-Jun-17 5:14am

Yes, it is, but you have to show some effort !
Did you try to create a view and tested that first ?

Deekshaa Singh Chauhan 13-Jun-17 6:02am

i want save state_id but show state name

RickZeeland 13-Jun-17 6:09am

To make things more clear maybe it would be a good idea to use SQL Server Management Studio, Right-click on your tables, and select "Create script". Add the generated scripts to you question by hovering over it with the mouse and select "Improve question".

Deekshaa Singh Chauhan 13-Jun-17 6:17am

this code worked bt show only 1 row and sometime that give error in data grideview id column

DataTable dt = new DataTable();
{
SqlConnection con = new SqlConnection(strcon);
dataGridView1.DataSource = null;
dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
con.Open();
SqlCommand cmd = new SqlCommand("select id,cmpny_dtl.c_name as c_name,cmpny_dtl.adrs as adrs,cmpny_dtl.GST as GST,t.State as state,cmpny_dtl.srvctax as srvctax,cmpny_dtl.adsnldtl as adsnldtl,cmpny_dtl.logo as logo" + " from cmpny_dtl join State t on t.State_ID = cmpny_dtl.state_id ", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
con.Close();
//dataGridView1.ColumnCount = 7;
//dataGridView1.Columns[0].HeaderText = "id";
//dataGridView1.Columns[0].DataPropertyName = "id";
//dataGridView1.Columns[0].Visible = false;
//dataGridView1.Columns[1].HeaderText = "c_name";
//dataGridView1.Columns[1].DataPropertyName = "c_name";
//dataGridView1.Columns[2].HeaderText = "adrs";
//dataGridView1.Columns[2].DataPropertyName = "adrs";
//dataGridView1.Columns[3].HeaderText = "GST";
//dataGridView1.Columns[3].DataPropertyName = "GST";
////dataGridView1.Columns[3].Visible = false;
//dataGridView1.Columns[4].HeaderText = "state";
//dataGridView1.Columns[4].DataPropertyName = "state_id";
//dataGridView1.Columns[4].Visible = false;
//dataGridView1.Columns[5].HeaderText = "srvctax";
//dataGridView1.Columns[5].DataPropertyName = "srvctax";
//dataGridView1.Columns[6].HeaderText = "adsnldtl";
//dataGridView1.Columns[6].DataPropertyName = "adsnldtl";
dataGridView1.DataSource = dt;
}

private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
{
if (dataGridView1.CurrentRow.Index != -1)
{
//if (dataGridView1.CurrentRow.Cells[10].Value != DBNull.Value)
//{
// byte[] img = (byte[])dataGridView1.CurrentRow.Cells[10].Value;
// MemoryStream ms = new MemoryStream(img);
// pictureBox1.Image = Image.FromStream(ms);
//}
//else
//{
// pictureBox1.Image = null;
//}

//id = Convert.ToInt32(dataGridView1.CurrentRow.Cells[0].Value.ToString());
CmpnyName_txt.Text = dataGridView1.CurrentRow.Cells[2].Value.ToString();
Adrs_txt.Text = dataGridView1.CurrentRow.Cells[3].Value.ToString();
CmpnyPhone_txt.Text = dataGridView1.CurrentRow.Cells[8].Value.ToString();
//Email_txt.Text = dataGridView1.CurrentRow.Cells[4].Value.ToString();
//Webst_txt.Text = dataGridView1.CurrentRow.Cells[5].Value.ToString();
//Pan_txt.Text = dataGridView1.CurrentRow.Cells[6].Value.ToString();
//Tin_txt.Text = dataGridView1.CurrentRow.Cells[7].Value.ToString();
SrvcTaxNo_txt.Text = dataGridView1.CurrentRow.Cells[6].Value.ToString();
AdsnlDtl_txt.Text = dataGridView1.CurrentRow.Cells[7].Value.ToString();
STATE_comboBox.Text = dataGridView1.CurrentRow.Cells[5].Value.ToString();
GST_textBox.Text = dataGridView1.CurrentRow.Cells[4].Value.ToString();
//id_txt.Text = dataGridView1.CurrentRow.Cells[0].Value.ToString();
SUBMIT_btn.Text = "UPDATE";

RickZeeland 13-Jun-17 6:28am

I would recommend to test your query first in SQL Server Management Studio and see if that returns more rows.

Deekshaa Singh Chauhan 13-Jun-17 6:46am

I tested that this only show one row but why? where is problem

RickZeeland 13-Jun-17 7:52am

As I do not have access to your database and have no information about it, I can not help you further. You can try modifying your query in SQL Server Management Studio and see what happens, good luck !

Deekshaa Singh Chauhan · Accepted Answer · 2017-06-13T23:29:00

That's perfect answer,this run successfully

private void DisplayData()
{
    DataTable dt = new DataTable();
    {
        dataGridView1.DataSource = null;
        dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
        con.Open();
        SqlCommand cmd = new SqlCommand("select id,p.c_name c_name,p.adrs as adrs,p.c_phone as c_phone,p.state_id as state_id,p.GST as GST,t.State as State,p.pan_no as pan_no,p.srvctax as srvctax,p.wbsit as wbsit,p.email as email,p.adsnldtl as adsnldtl,p.logo as logo from cmpny_dtl p join State t on t.State_ID = p.state_id ", con);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        da.Fill(dt);
        con.Close();
        dataGridView1.ColumnCount = 14;
        dataGridView1.Columns[0].HeaderText = "ID";
        dataGridView1.Columns[0].DataPropertyName = "id";
        dataGridView1.Columns[0].Visible = false;
        dataGridView1.Columns[1].HeaderText = "Name";
        dataGridView1.Columns[1].DataPropertyName = "c_name";
        dataGridView1.Columns[2].HeaderText = "adrs";
        dataGridView1.Columns[2].DataPropertyName = "adrs";
        dataGridView1.Columns[3].HeaderText = "state_id";
        dataGridView1.Columns[3].DataPropertyName = "state_id";
        dataGridView1.Columns[3].Visible = false;
        dataGridView1.Columns[4].HeaderText = "GST";
        dataGridView1.Columns[4].DataPropertyName = "GST";
        //dataGridView1.Columns[4].Visible = false;
        dataGridView1.Columns[5].HeaderText = "State";
        dataGridView1.Columns[5].DataPropertyName = "State";
        dataGridView1.Columns[6].HeaderText = "srvctax";
        dataGridView1.Columns[6].DataPropertyName = "srvctax";
        dataGridView1.Columns[7].HeaderText = "c_phone";
        dataGridView1.Columns[7].DataPropertyName = "c_phone";
        dataGridView1.Columns[8].HeaderText = "tin_no";
        dataGridView1.Columns[8].DataPropertyName = "tin_no";
        dataGridView1.Columns[8].Visible = false;
        dataGridView1.Columns[9].HeaderText = "pan_no";
        dataGridView1.Columns[9].DataPropertyName = "pan_no";
        dataGridView1.Columns[9].Visible = false;
        dataGridView1.Columns[10].HeaderText = "wbsit";
        dataGridView1.Columns[10].DataPropertyName = "wbsit";
        dataGridView1.Columns[10].Visible = false;
        dataGridView1.Columns[11].HeaderText = "email";
        dataGridView1.Columns[11].DataPropertyName = "email";
        dataGridView1.Columns[11].Visible = false;
        dataGridView1.Columns[12].HeaderText = "adsnldtl";
        dataGridView1.Columns[12].DataPropertyName = "adsnldtl";
        dataGridView1.Columns[12].Visible = false;
        dataGridView1.Columns[13].HeaderText = "logo";
        dataGridView1.Columns[13].DataPropertyName = "logo";
        dataGridView1.Columns[13].Visible = false;
        dataGridView1.DataSource = dt;
    }


}


private void dataGridView1_CellClick(object sender,ataGridViewCellEventArgs e)
{
    if (dataGridView1.CurrentRow.Index != -1)
    {

        //id =         Convert.ToInt32(dataGridView1.CurrentRow.Cells[0].Value.ToString());
        CmpnyName_txt.Text = dataGridView1.CurrentRow.Cells[2].Value.ToString();
        Adrs_txt.Text = dataGridView1.CurrentRow.Cells[3].Value.ToString();
        CmpnyPhone_txt.Text = dataGridView1.CurrentRow.Cells[4].Value.ToString();
        STATE_comboBox.SelectedValue = dataGridView1.CurrentRow.Cells[5].Value.ToString();
       GST_textBox.Text = dataGridView1.CurrentRow.Cells[6].Value.ToString();

       Email_txt.Text = dataGridView1.CurrentRow.Cells[11].Value.ToString();

       Webst_txt.Text = dataGridView1.CurrentRow.Cells[10].Value.ToString();
       Pan_txt.Text = dataGridView1.CurrentRow.Cells[8].Value.ToString();
        Tin_txt.Text = dataGridView1.CurrentRow.Cells[4].Value.ToString();
        SrvcTaxNo_txt.Text = dataGridView1.CurrentRow.Cells[9].Value.ToString();
        AdsnlDtl_txt.Text = dataGridView1.CurrentRow.Cells[12].Value.ToString();
        ////AdsnlDtl_txt.Text = dataGridView1.CurrentRow.Cells[0].Value.ToString();

        if (dataGridView1.CurrentRow.Cells[13].Value != DBNull.Value)
        {
            byte[] img = (byte[])dataGridView1.CurrentRow.Cells[13].Value;
            MemoryStream ms = new MemoryStream(img);
            pictureBox1.Image = Image.FromStream(ms);

            Browes_btn.Text = "Remove";
        }
        else
        {
            pictureBox1.Image = null;
            Browes_btn.Text = "Browes";
        }

        SUBMIT_btn.Text = "UPDATE";
        Display_btn.Text = "RESET";
    }
}