Click here to Skip to main content
14,976,190 members
Please Sign up or sign in to vote.
1.00/5 (3 votes)
See more:
Hi Buddies,

I have a combobox which contains 2 or more tables (tblEmp, tblPdts, etc), a checked listbox and a datagridview. I wanna display the columns of a choosen table into the checkedListBox if the table is selected in the combobox dynamically.

And also display the records of a selected checkedlist into dgv.

Example:
cb = tblEmp
checkedListBox1 = []FName, []LName, []Telephone, []Age, []City

Then if checkedListBox1 =

[x]FName, []LName, []Telephone, [x]Age, [x]City

Datagridview
Philip   21   New York
Charles  25   India
Maciej   24   London
etc

So the records in the dgv depends on what column i choose in the checkedListBox1.

Any suggestions


MY CODE CONSISTS oF TWO FORMS:

C#
    public partial class Form1: Form
    {
        SqlConnection con = new SqlConnection();
        SqlCommand cmd = new SqlCommand();
        SqlDataReader dr;
        DataTable dt;


        public Form1()
        {
            InitializeComponent();
            Fillcombobox();
            
        }
     
        //Change tbl name on selection isn't working 

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {

       loaddata(comboBox1.SelectedValue.ToString());         
          
        }
        private void Form1_Load(object sender, EventArgs e)
        {
            // Sql Connection if page loads
          }

private void Fillcombobox()
        {
            cmd.CommandText = ("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME ASC");
            con.Open();
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            dt = new DataTable();
            adapter.Fill(dt);
            comboBox1.DisplayMember = "TABLE_NAME";
            comboBox1.ValueMember = "TABLE_NAME";
            //Fill combobox with data in DT
            comboBox1.DataSource = dt;
            // Empty bzw. clear the combobox
            comboBox1.SelectedIndex = -1;

            }
        
       
        // A methode to execute a command if page opens
        private void loaddata(string sTableName)
        {
       string CS = (@"Data Source=;Initial Catalog=;Integrated Security=True");
            using (SqlConnection con = new SqlConnection(S))
            {
          con.Open();
cmd.CommandText = String.Format("SELECT [column_name] FROM information_schema.columns WHERE table_name = '{0}'", sTableName);

        SqlCommand command = new SqlCommand(cmd.CommandText, con);
        SqlDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        checkedListBox1.Items.Add(reader[0].ToString());
                    }
                }
            }
        }


        /* Display selected options*/
        private void btnShow_Click(object sender, EventArgs e)
        {
            Form2 f2 = new Form2();
            int f = 0;
            string qry = "";
            for (int i = 0; i < checkedListBox1.Items.Count; i++)
            {
                if(checkedListBox1.GetItemChecked(i))
                {
                    if(f == 1)
                    {
                        qry = qry + "," + checkedListBox1.Items[i].ToString();
                    }
                    if (f == 0)
                    {
                        qry = checkedListBox1.Items[i].ToString();
                        f = 1;
                    }
                }
            }
            // A new query to to display the records after checkedbox is clicked
            string newq = "select " + qry + " from tblEmp where [city] = 'Brazil' ";

            // Open form two when btn is clicked
            if (qry != "")
            {
                f2.copyfun(newq);
                f2.Show();
                this.Hide();
            }

            else
            {
                MessageBox.Show("Select an item");
            }

        }

        private void btnZurück_Click(object sender, EventArgs e)
        {
            this.Hide();
            new EinForm().ShowDialog();

        }

    }
}



C#
        public Form2()
        {
            InitializeComponent();
        }

        // Load dgv with a parameter from the query form
        private void loadgrid(string qry)
        {
            string S = ConfigurationManager.ConnectionStrings[""].ConnectionString;
            SqlConnection con = new SqlConnection(S);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = (qry);
            DataSet d = new DataSet();
            //DataTable dt = new DataTable();

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(d);
            dataGridView1.DataSource = d.Tables[0];

        }


        public void copyfun(string qry)
        {
            loadgrid(qry);
        }

        private void Form2_Load(object sender, EventArgs e)
        {
            // Sql Connection if page loads
            string S = ConfigurationManager.ConnectionStrings[""].ConnectionString;
            SqlConnection con = new SqlConnection(S);

        }

        private void btnZurück_Click(object sender, EventArgs e)
        {
            this.Hide();
            new Form1().ShowDialog();
        }
    }
}


How do i get the combobox to implement my intention. 
And my sql statement is static instead of making it dynamic. 
Posted
Updated 11-Dec-14 22:07pm
v10
Comments
vishal_pawar 11-Dec-14 4:38am
   
1. on selectedIndexchanged Event of combobox ,create one list and insert columns of selected table inside list and bind it to checkbox list.

at same time select all data from that table and bind it to grid.

2.after binding columns list to checkbox list, on change o checkbox list,
get checked columns and select only those columns from table and bind to grid.
Thanks7872 11-Dec-14 4:41am
   
Suggestions on what? You know what you need. Start coding that is the only suggestion.
BillWoodruff 11-Dec-14 4:44am
   
I agree with Rohan, it sounds like you are ready to get coding; but do feel free to edit this post when you have specific questions, or get stuck.
mikybrain1 11-Dec-14 5:33am
   
U guys are right i do know what i want and i have finished coding it but it isn't dynamic and my idea abt the combobox isn't working. I have edit my post
BillWoodruff 11-Dec-14 6:03am
   
What do you mean "it isn't dynamic:" please clarify.
mikybrain1 11-Dec-14 6:41am
   
Hi it isn' dynamic bcos of this lines:
cmd.CommandText = ("SELECT [column_name] FROM information_schema.columns WHERE table_name = 'tblEmp' ");

and

string newq = "select " + qry + " from tblEmp where [city] = 'Brazil' ";

That means it always take the tblEmp and display its columns.

Praveen Kumar Upadhyay 11-Dec-14 6:14am
   
Are you able to Load your Combobox and on selection of combobobx are you able to load columns and display in the checkedlistBox??
mikybrain1 11-Dec-14 6:39am
   
No the combobx isn't working that's why i have commented it out. It can only load columns and display in the checkedlistbox if Form1 page is loaded.


1 solution

You're very close to find solution ;)

I'd suggest to change loaddata() method to accept table name as input parameter:
C#
private void loaddata(string sTableName)
 {
     cmd.CommandText = String.Format("SELECT [column_name] FROM information_schema.columns WHERE table_name = '{0}'", sTableName);
     //clear CheckedListBox
     //add CheckBoxes to CheckedListBox here
 }


You need to call this procedure inside comboBox1_SelectedIndexChanged or comboBox1_SelectedValueChanged method:
C#
loaddata(comboBox1.Value.ToString());


Now, you should be able to finish your project. Good luck!
   
Comments
mikybrain1 12-Dec-14 3:55am
   
Hi Maciej
I have tried my best but it isn't working properly. It is still firing the columns of tblEmp even if i change the combobox column to tblPrdt. i get two main problems
1. i chanhed the query in the loaddata method what abt this line?:
// A new query to to display the records after checkedbox is clicked
string newq = "select " + qry + " from tblEmp where [city] = 'Brazil' ";

2. I couldn't figure out what u meant this in ur help:
//clear CheckedListBox
//add CheckBoxes to CheckedListBox here

I have also updated my post.
Maciej Los 12-Dec-14 4:16am
   
Sorry, but you did not change as i suggested. Please, read my answer one more time and compare my code to yours.

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900