Click here to Skip to main content
15,885,546 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.

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!
 
Share this answer
 
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