SQL QUERY CHALLENGING

sairam

17y4k

I have two tables. table1 and table2.

table 1 has the following fields

CNO[Primarykey],Cname,Question,Date

table 2 has follwoing fields

CNO[Foreign key],SOQ.

when i write query select SOQ from table 2 where CNO = 1336

i get output as

101

102

103

for a different CNO the o/p varies. the o/p is nopt always 3 rows

. My question is

I have a datagridview in windows forms. the header of that grid should be as follows

CNO Cname Question 101 102 103

So the output of table 2 should be included as header for datagrid. how should i write the query

Dont worry about the data in the cells below 101 102 103. they will be blank for ever.

Thanks

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Kirtan Patel17y ago
Accepted answer
For Sample

i have Created Table With

One Column
no
----
1
2
3
4
5
6

Now if i want to Show No column and another Blank Columns With name That is Values in No Column
like Below Screen Shot

Here i have created 2 function The ShowSimpleData() Function Select the Data From Database and Simply Show it as it is

The Second Function Reads the Perticuler Values from Database and Then Make Columns and Add it to DataGridView

Note : this Can Also Be Done With Stored Procedure that will Build the Select Query on the Fly by Programming Logic With Null Values but it is Much More Difficult than this one

private void Form1_Load(object sender, EventArgs e)
        {
            ShowSimpleData();
            AddExtraColumns();

        }

        public void ShowSimpleData()
        {

            SqlConnection con = new SqlConnection(@"Data Source=.\SQlEXPRESS;AttachDbFileName=|DataDirectory|\Database1.mdf;Integrated Security=true;User Instance=true");
            con.Open();
            SqlCommand comm = new SqlCommand("select * from t1", con);
            SqlDataAdapter da = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            da.Fill(ds, "VData");
            dataGridView1.DataSource = ds.Tables[0];
            con.Close();
        }

        public void AddExtraColumns()
        {
            SqlConnection con = new SqlConnection(@"Data Source=.\SQlEXPRESS;AttachDbFileName=|DataDirectory|\Database1.mdf;Integrated Security=true;User Instance=true");
            con.Open();
            SqlCommand comm = new SqlCommand("select * from t1", con);
            SqlDataReader reader = comm.ExecuteReader();

            while (reader.Read())
            {

                DataGridViewCell c1 = new DataGridViewTextBoxCell();
                DataGridViewColumn col1 = new DataGridViewColumn(c1);

                col1.HeaderText = reader["no"].ToString();
                dataGridView1.Columns.Add(col1);


            }

            con.Close();
        }

Happy Coding :)

0
Kirtan Patel17y ago
Set Display Index While Adding Columns in Code Like Below to Change Their Order

                    DataGridViewCell cell1 = new DataGridViewTextBoxCell();

                DataGridViewColumn c1 = new DataGridViewColumn(cell1);
                c1.DisplayIndex = i + 2;
                c1.HeaderText = i.ToString();
                dataGridView1.Columns.Add(c1);
Or

Use While Adding Column to DataGridView

dataGridView1.Columns.Insert(,c1);

Happy Coding :)

0
sairam17y ago
Kirtan,

The extra columns that are added are displaying as added at the end of existing columns which is perfect but the extra columns - column index is showing as 0,1,2 instead of 4,5,6....
0
sairam17y ago
Thanks a ton Kirtan, you are the man...
0