add values to combobox from different tables in database

[prateek29chandra]

hi guys. i hve a database name event. i hve a studentaccount table and faculty account table. studentaccount table has id,vstudentname columns and faculty account has id,vfacultyname columns. i want to add both table ids to combobox. how can i acheive that. i hve successfully managed to do it for single table.

private void AdminChat_Load(object sender, EventArgs e)
        {
            comboBox1.Visible = false;
            string ConnectionString = "Data Source=GHOST-PC\\SQLEXPRESS;Initial Catalog=Event;Integrated Security=True";
            SqlConnection con = new SqlConnection(ConnectionString);
            con.Open();
            SqlCommand sc = new SqlCommand("select vuser,vStudentName from StudentAccount", con);
            SqlDataReader reader;
            reader = sc.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Columns.Add("vuser", typeof(string));
            dt.Columns.Add("vStudentName", typeof(string));
            dt.Load(reader);
            comboBox2.ValueMember = "vuser";
            comboBox2.DisplayMember = "vStudentName";
            comboBox2.DataSource = dt;
            }

[pitic]

Do you want to add them to the same combobox? If so you could use UNION ALL:

select vuser, vStudentName from StudentAccount UNION ALL select <columns requested> from FacultyAccount

replace the <columns request> with what you need from FacultyAccount

Just remember you have to select the same number/order/types of columns between each select

[RvSon]

Hi,
Are you performing any action after selection of ID's?

Follow this site, surly you will get appropriate result
http://www.mssqltips.com/sqlservertip/1387/joining-data-using-union-and-union-all-in-sql-server/

[prateek29chandra]

yups i need to add ids frm studentaccount table and faculty account table and add it to same combobox. in my case its combobox1. thnkz for the solution. i will try above method and will let u knw if it works in my case.

[ChrisHunter]

It depends on what you want to do with the data when you've retrieved it. Doing a union all on ID columns will cause a lot of problems because the two tables will have conflicting IDs.

For example, if you want to query some data using the ID of a value selected in the combobox, how would you be able to identify whether the ID belongs to the student table or the faculty table?

[prateek29chandra]

sir, am making a project on student information system for my college. its a windows application. am making a kind of inbuilt messenger. for eg: i have a admin account, faculty account and student account. faculties and students hve their respective ids. now if i wnt to send msg to someone i want combobox to have ids of those faculties and students that exist in database. now if admin want to send message he needs to select recipient from combobox. In that combobox i want to add all ids of student and faculty dynamically. the above code manage to add successfully from 1 table. now i need it from student and faculty both.

[prateek29chandra]

sir i hve one more problem. my project name is student. whenever i try to bind data to a control. 2 error comes 1- "the type name 'Eventdataset' does not exist in the type 'student.student'.
2- The type name 'Eventdatasettableadapters' does not exist in the type 'student.student. what the hell is this suppose to mean. am tired of it. i hvnt found any solution. the steps i folllow is :
Add new data source. select Event database nd then a particular table from it. then an 'eventdataset' is created. binding source i add datasouce property to 'eventdataset'.then i hve a control textbox1 and i set its databinding> text property to carresponding field from the table of event database. for eg if i need first name in textbox1 i select fname from studentinfo. table of event dataset. am i doing something wrong.

[cyberdaemon]

try to use join, after joining the field try to concetenate the field that you want the user can see on the GUI..

[ChrisHunter]

The best thing to do might be to have everyone in 1 table called something like Users and a second table called UserType which could have the following fields:

Users table:
* ID
* Name
* Address
* UserTypeID (which is a reference to the UserType ID)

UserType table:
* ID
* Description (for example student, admin, faculty)

Then when you add a user you give them a UserType ID in the UserTypeID column.

This will give your database another level of normalisation (this is a good thing) because there will be no conflicting user ID's like their would be if you had different tables for each type of user, there will be less chance of duplication data between the different user tables(for example if someone changes from a faculty user to an admin user there won't be an entry in each table), and it will also mean it is easier to know what type of user it is.

All this would make it wayyyy easier when you're trying to something like populating a comboBox as you're trying to do as it means less complicated querying and quicker execution time which if you explain that in the justification (along with the normalisation bit from above) will help boost your marks (if this is for a college project that is).

[prateek29chandra]

thnkz everyone for help. i did it. i took 2 comboboxes. in on comboboxes i got ids from 2 tables and in another i got respective names. i disabled the second one. whenever i change the id the value in another combobox changed. thats more than what i wished. Here is the code.

string ConnectionString = "Data Source=GHOST-PC\\SQLEXPRESS;Initial Catalog=Event;Integrated Security=True";
            SqlConnection con = new SqlConnection(ConnectionString);
con.Open();
           SqlCommand sc = new SqlCommand("select vuser, vStudentName from StudentAccount UNION ALL select vuser,vStaffName from StaffAccount", con);
            SqlDataReader reader;
            reader = sc.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Columns.Add("vuser", typeof(string));
            dt.Columns.Add("vStudentName", typeof(string));
            dt.Columns.Add("vStaffName", typeof(string));
            dt.Load(reader);
            comboBox2.DisplayMember = "vUser";
            comboBox1.ValueMember = "vStudentName";
            comboBox1.ValueMember = "vStaffName";
            comboBox2.DataSource = dt;
            comboBox1.DataSource = dt;