Gridview showing only one column data

Question

1.00/5 (1 vote)

See more:

I have dropdown(tablenames) if i select any table name from dropdown, the column names displayed in listbox(all coumn name from selected dropdwon table,when i multiple select column name(listbox) i want to disply all data(selected columns in listbox) in grid view with multiple columns.

error
while multiple select gridview displays only one column(bind).help me to bind multiple columns in grid view.

What I have tried:

C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;


namespace entity
{
    public partial class _Default : System.Web.UI.Page
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString);
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            /*  if (DropDownList1.SelectedValue == DropDownList1.SelectedItem.Text)
              {
                  string query = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME="+ "DropDownList1.SelectedItem.Text+" ;
                  DataTable dt = _Default.ExecuteQuery(query);
                  ListBox1.Items.Clear();
                  ListBox1.DataSource = dt;
                  ListBox1.DataBind();

              }*/
            string query = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='" + DropDownList1.SelectedItem.Text + "'";



            using (SqlCommand cmd = new SqlCommand(query))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    cmd.Connection = con;
                    sda.SelectCommand = cmd;
                    using (DataSet ds = new DataSet())
                    {

                        sda.Fill(ds);
                        ListBox1.DataMember = "COLUMN_NAME";
                        ListBox1.DataValueField = "COLUMN_NAME";
                        ListBox1.DataSource = ds.Tables[0];
                        ListBox1.DataBind();

                    }

                }
            }
        }







        protected void Button2_Click(object sender, EventArgs e)
        {

            //ListBox1_SelectedIndexChanged(sender, e);
            if (ListBox1.Items.Count > 0)
            {

                DataTable dt = new DataTable();

                DataSet ds = new DataSet();

                for (int i = 0; i < ListBox1.Items.Count; i++)
                {
                    if (ListBox1.Items[i].Selected)
                    {
                        string Listbfrom = ListBox1.Items[i].Text;





                        con.Open();

                        string str = "SELECT  " + Listbfrom + "  FROM  " + DropDownList1.SelectedItem.Text;

                        SqlCommand com = new SqlCommand(str, con);

                        DataSet dsBooking = new DataSet();

                        SqlDataAdapter dap = new SqlDataAdapter(com);

                        dap.Fill(dsBooking);

                        con.Close();


                        

                            GridView1.DataSource = dsBooking;

                            GridView1.DataBind();

                        

                    }

                }

            }



        }

       /* protected void ListBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (ListBox1.Items.Count > 0)
            {

                DataTable dt = new DataTable();

                DataSet ds = new DataSet();

                for (int i = 0; i < ListBox1.Items.Count; i++)
                {
                    if (ListBox1.Items[i].Selected)
                    {
                        string Listbfrom = ListBox1.Items[i].Text;





                        con.Open();

                        string str = "SELECT  " + Listbfrom + "  FROM  " + DropDownList1.SelectedItem.Text;

                        SqlCommand com = new SqlCommand(str, con);

                        DataSet dsBooking = new DataSet();

                        SqlDataAdapter dap = new SqlDataAdapter(com);

                        dap.Fill(dsBooking);

                        con.Close();


                        {

                            GridView1.DataSource = dsBooking;

                            GridView1.DataBind();

                        }

                    }

                }

            }

        }
*/
    }
}

Posted 19-Apr-17 4:04am

GrpSMK

Updated 19-Apr-17 4:40am

Add a Solution

Comments

[no name] 19-Apr-17 10:12am

What do you mean by "while multiple select gridview displays only one column(bind)." Share more details..

GrpSMK 19-Apr-17 10:17am

in listbox multiple select,gridview showing last selected column name only

[no name] 19-Apr-17 10:24am

Still not clear. But I can see 1 issue as you are binding the data to gridview in for loop so causing it to bind only last item on your collection.

Try moving below 2 statements out of for loop it should work.

GridView1.DataSource = dsBooking;
GridView1.DataBind();

GrpSMK 19-Apr-17 10:30am

yea this is my problem,how to resolve this

[no name] 19-Apr-17 10:32am

As said where you binding your data to grid view move it out of for loop.

GrpSMK 19-Apr-17 10:36am

i tried,but getting error.all my declared variable inside for loop only.then how to move it?

[no name] 19-Apr-17 10:39am

When you populating Dataset move it out of the loop. Each time you iterate new item in the loop you are creating new Dataset causing it to hold only last item on your list and remove the previously added item. So at end you have only last item in DS to bind your grid view control.

GrpSMK 19-Apr-17 10:40am

yea,done thank you so much..

1 solution

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)

CHill60 · Accepted Answer · 2017-04-19T04:40:00

As Vino Jangle has pointed out you are doing the query for the column within the loop - what you actually need to do is get the list of columns that are selected into a comma-separated list and use that.

You should also be using Parameterized queries - never, ever concatenate strings to create sql commands.

Try this instead:

protected void Button2_Click(object sender, EventArgs e)
{

    if (ListBox1.Items.Count > 0)
    {
        List<string> listbfrom = new List<string>();

        for (int i = 0; i < ListBox1.Items.Count; i++)
            if (ListBox1.Items[i].Selected)
                listbfrom.Add(ListBox1.Items[i].Text);

        string csv = string.Join(",", listbfrom);

        con.Open();

        string str = "SELECT @listbfrom FROM  @tabfrom";

        using (SqlCommand com = new SqlCommand(str, con))
        {
            com.Parameters.AddWithValue("@listbfrom", csv);
            com.Parameters.AddWithValue("@tabfrom", DropDownList1.SelectedItem.Text);
            DataSet dsBooking = new DataSet();
            SqlDataAdapter dap = new SqlDataAdapter(com);

            dap.Fill(dsBooking);
            con.Close();
        }

        GridView1.DataSource = dsBooking;
        GridView1.DataBind();
    }
}

If you have further questions please do not include commented out code or empty methods. Only post the code you are having a problem with.

[EDIT] The solution above only works for .NET 4 and above. For .NET 3.5 replacing

C#

string csv = string.Join(",", listbfrom);

with

C#

string csv = string.Join(",", listbfrom.ToArray());