Listbox add/clear programatically databound items

Question

Mr.BunyRabit -1 Junior Poster

13 Years Ago

Hey there.
ahh again its the LIstbox...

I have a dropdown box. with "Name" "Surname" and "student number"

When the "name" option is clicked i want the listbox to show all the Names in the name field of my DAtabase in my STudent table.
and then when "surname" is clicked, listbox should clear, then show the Surnames in the surname field from my database in Students table

I can do the firs part, i can put in the names in there, but i cant clear it and add the surnames etc in there? howw do i do that?

i have no idea.
And i didnt bind the listbox Programatically, i bounded it with the use of the litle block on the right hand side at the top.

Thank you =) =)

3 Contributors
11 Replies
450 Views
2 Days Discussion Span
Latest Post 13 Years Ago Latest Post by Mitja Bonca

Mitja Bonca 557 Nearly a Posting Maven

13 Years Ago

Here is the code you need;

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Feb18Exercise1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            //ATTENTION!
            //MAKE SURE that the names from comboBox are the same as column names in database!
            //otherwise this will not work!
            comboBox1.Items.AddRange(new string[] { "Name", "Surename", "Student number" });
        }

        private DataTable GetDataFromDB(string type)
        {
            DataTable table = new DataTable("StudentDetails");
            using (SqlConnection sqlConn = new SqlConnection("yourConnectionString"))
            {
                string query = @"SELECT '" + type + "' FROM Students";
                SqlCommand cmd = new SqlCommand(query, sqlConn);
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    da.Fill(table);
            }
            return table;
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            string item = this.comboBox1.GetItemText(this.comboBox1.SelectedItem);
            if (item != String.Empty)
            {
                DataTable table = GetDataFromDB(item);
                this.listBox1.Items.Clear();
                this.comboBox1.DataSource = table;
            }
        }
    }
}

PS: be careful with connection string. It has to be the correct one! I hope you know how to handle with it.
AND: about names in comboBox: now the code will work only in case if the names from comboBox and column in DataBase are the same ("Name", "Surename" "Student number")
HINT: Its good practice to avid using white spaces in the dataBase columns (like Student number, it would a way better be: StudentNumber).
That much... I hope it will help,

Mitja

profromeo 1 Newbie Poster

13 Years Ago

Please try this:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            comboBox1.Items.AddRange(new string[] { "name", "surname", "other" });
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }


        public void getdata(string type)
        {

            SqlConnection conn = new SqlConnection("yourconnstring");

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "Select * from students";

            SqlDataReader reader ;

            conn.Open();

            reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                listBox1.Items.Add(reader[type]);

            }
           conn.Close();




        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            listBox1.Items.Clear();
            getdata(comboBox1.SelectedItem.ToString());
        }

        private void button1_Click(object sender, EventArgs e)
        {
            listBox1.Items.Clear();
            getDataByDetails(textBox1.Text, comboBox1.SelectedItem.ToString());
        }


        public void getDataByDetails(string recordfilter,string type)
        {

            SqlConnection conn = new SqlConnection("yourconnstring");

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;

            if (type == "name")
            {
                cmd.CommandText = "Select * from students where name like @rec + '%'";
            }
            if (type == "surname")
            {
                cmd.CommandText = "Select * from students where surname like @rec + '%'";
            }

            //cmd.CommandText = "Select * from Admin_vehicle where name like '%' + @rec + '%'";
           // cmd.CommandText = "Select * from Admin_vehicle where name =  @rec";
         //   cmd.CommandText = "Select * from Admin_vehicle where name like '%' + '" + regnumber + "'+ '%' and speedkph > '0'";

            cmd.Parameters.AddWithValue("@rec", recordfilter);
            SqlDataReader reader;

            conn.Open();

            reader = cmd.ExecuteReader();

            while (reader.Read())
            {

                if (type == "name")
                {
                    listBox1.Items.Add(reader["name"]);
                }
                if (type == "surname")
                {
                    listBox1.Items.Add(reader["surname"]);
                }

            }
            conn.Close();




        }

    }
}

Edited 11 Years Ago by diafol because: fixed formatting

Mr.BunyRabit commented: Works Perfect! Perfect Perfect! +1

Mitja Bonca 557 Nearly a Posting Maven

13 Years Ago

Sorry for the mess, I forgot this code:

if (item != String.Empty)
            {
                DataTable table = GetDataFromDB(item);
                this.comboBox1.DataSource = table;
                this.comboBox1.DisplayMember = item;
                this.comboBox1.ValueMember = item;
            }

PS: DO NOT use listBox.Clear method now!
This has to work
Mitja

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Mitja Bonca 557 Nearly a Posting Maven · Answer 1 · 2011-02-18T04:19:05+00:00

Hi again,
it would help if you would paste the code that you have in here, it will be a way easier salved.

HINT: ayou said you can bind Names to the listBox, so when you want to bind "SureNames", 1st you set the dataSource to null (to clear it) and then create a new instance of a datasouce and bind it to thw listBox again.

Thx
Mitja

Mr.BunyRabit -1 Junior Poster · Answer 2 · 2011-02-18T04:40:04+00:00

Hi again,
it would help if you would paste the code that you have in here, it will be a way easier salved.
HINT: ayou said you can bind Names to the listBox, so when you want to bind "SureNames", 1st you set the dataSource to null (to clear it) and then create a new instance of a datasouce and bind it to thw listBox again.
Thx
Mitja

Again your quick response =)
thank you

So when i load this is the code

private void ViewStudent_Load(object sender, EventArgs e)
        {
          // TODO: This line of code loads data into the 'studentsdb1DataSet.StudentsTable' table. You can move, or remove it, as needed.
          this.studentsTableTableAdapter.Fill(this.studentsdb1DataSet.StudentsTable);
 
        }

And thats it. I added a picuture of how i bounded the data to the listbox, as to say that theres no more code accept the above.

Now how would i change that bounded data inside the program to another column?

Ruan
Thank you =)

Mitja Bonca 557 Nearly a Posting Maven · Answer 3 · 2011-02-18T05:02:51+00:00

You should start avoiding this auto generated dataSets. They suck. I know you are a begginer, but you better get some book and do some basic learning. Otherwise you will stuck here with us for a long time (dont get me wrong, I will still help you, but I only want to help You, to learn some basics - for easier understanding and coding).

ok, you said:
"I have a dropdown box. with "Name" "Surname" and "student number""
Does that mean you have 3 dropDowns or just one? Its really not understandable to me.
Plase explain, then I can go on..
thx
Mitja

Mr.BunyRabit -1 Junior Poster · Answer 4 · 2011-02-18T06:33:36+00:00

You should start avoiding this auto generated dataSets. They suck. I know you are a begginer, but you better get some book and do some basic learning. Otherwise you will stuck here with us for a long time (dont get me wrong, I will still help you, but I only want to help You, to learn some basics - for easier understanding and coding).
ok, you said:
"I have a dropdown box. with "Name" "Surname" and "student number""
Does that mean you have 3 dropDowns or just one? Its really not understandable to me.
Plase explain, then I can go on..
thx
Mitja

=) Thank you Mitja.
Ive realized the inconvenience of those stupid auto generated datasets. Just like the wizard of connecting a db.

So i have ONE dropdown. In that dropdown the options "Name" "Surname" and "student number"
When this happens

private void SelectedIndexChanged(object sender, EventArgs e)

and the text is changed to Surname, i want the data in the listbox to show my Surname column in my Students table in my Database

Then when that same dropdown is changed to Studentnumber i want the listbox to clear, then fill with the data of the column "student numbers" of the students Database.

I have tried code to get that information into my listbox from my mql db without doing it with that automated way, but i havent found any correct ways on the net yet..

Thank you for your help Mitja
=) and patients =)

Mr.BunyRabit -1 Junior Poster · Answer 5 · 2011-02-20T01:29:03+00:00

Here is the code you need;

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Feb18Exercise1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            //ATTENTION!
            //MAKE SURE that the names from comboBox are the same as column names in database!
            //otherwise this will not work!
            comboBox1.Items.AddRange(new string[] { "Name", "Surename", "Student number" });
        }

        private DataTable GetDataFromDB(string type)
        {
            DataTable table = new DataTable("StudentDetails");
            using (SqlConnection sqlConn = new SqlConnection("yourConnectionString"))
            {
                string query = @"SELECT '" + type + "' FROM Students";
                SqlCommand cmd = new SqlCommand(query, sqlConn);
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    da.Fill(table);
            }
            return table;
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            string item = this.comboBox1.GetItemText(this.comboBox1.SelectedItem);
            if (item != String.Empty)
            {
                DataTable table = GetDataFromDB(item);
                this.listBox1.Items.Clear();
                this.comboBox1.DataSource = table;
            }
        }
    }
}

PS: be careful with connection string. It has to be the correct one! I hope you know how to handle with it.
AND: about names in comboBox: now the code will work only in case if the names from comboBox and column in DataBase are the same ("Name", "Surename" "Student number")
HINT: Its good practice to avid using white spaces in the dataBase columns (like Student number, it would a way better be: StudentNumber).
That much... I hope it will help,

Mitja

Hey there, thank you once again for your reply Mitja.
I tried the code, theres just a few hickups.
the last part of the code should be

this.listbox.DataSource = table;

instead of

this.comboBox1.DataSource = table;

but thats minor.
also when the program runs it doesnt throw me the records, it thorws me this.

No idea why it did that.
I see One of my Very Good friends also added a solution on here, and it looks like that one is working great for now. =)

So thans alot Mitja, Once again
Hope you have a great day!

Mr.BunyRabit -1 Junior Poster · Answer 6 · 2011-02-20T01:34:25+00:00

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            comboBox1.Items.AddRange(new string[] { "name", "surname", "other" });
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }


        public void getdata(string type)
        {

            SqlConnection conn = new SqlConnection("yourconnstring");

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "Select * from students";

            SqlDataReader reader ;

            conn.Open();

            reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                listBox1.Items.Add(reader[type]);
            
            }
           conn.Close();



        
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            listBox1.Items.Clear();
            getdata(comboBox1.SelectedItem.ToString());
        }

        private void button1_Click(object sender, EventArgs e)
        {
            listBox1.Items.Clear();
            getDataByDetails(textBox1.Text, comboBox1.SelectedItem.ToString());
        }


        public void getDataByDetails(string recordfilter,string type)
        {

            SqlConnection conn = new SqlConnection("yourconnstring");

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;

            if (type == "name")
            {
                cmd.CommandText = "Select * from students where name like @rec + '%'";
            }
            if (type == "surname")
            {
                cmd.CommandText = "Select * from students where surname like @rec + '%'";
            }

            //cmd.CommandText = "Select * from Admin_vehicle where name like '%' + @rec + '%'";
           // cmd.CommandText = "Select * from Admin_vehicle where name =  @rec";
         //   cmd.CommandText = "Select * from Admin_vehicle where name like '%' + '" + regnumber + "'+ '%' and speedkph > '0'";

            cmd.Parameters.AddWithValue("@rec", recordfilter);
            SqlDataReader reader;

            conn.Open();

            reader = cmd.ExecuteReader();

            while (reader.Read())
            {

                if (type == "name")
                {
                    listBox1.Items.Add(reader["name"]);
                }
                if (type == "surname")
                {
                    listBox1.Items.Add(reader["surname"]);
                }

            }
            conn.Close();




        }
    
    }
}

Ahhh Thank you Romeo!! More than i expected, but exactly what i wanted to doo! The ssorting, exactly.. =)
So thank you!!!
=)

Mr.BunyRabit -1 Junior Poster · Answer 7 · 2011-02-20T03:40:43+00:00

Sorry for the mess, I forgot this code:

if (item != String.Empty)
            {
                DataTable table = GetDataFromDB(item);
                this.comboBox1.DataSource = table;
                this.comboBox1.DisplayMember = item;
                this.comboBox1.ValueMember = item;
            }

PS: DO NOT use listBox.Clear method now!
This has to work
Mitja

haha no problem, thank you Mitja.
Saving all that code, Just know im gona use all of it again some day.
Works perfect. =)
Ruan

Mitja Bonca 557 Nearly a Posting Maven · Answer 8 · 2011-02-20T03:41:50+00:00

I am glad you are satisfied with the code :)
thats what I want.
bye, bye,
Mitja