Click here to Skip to main content
15,305,969 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Dear Friend,
In my form some combo boxes are there.I retrieved some values to another table (same database).But In that comboboxes values r repeated somany times.i used distint and group by also not working.

Please check below table.

Course  Branch  ExamType         Month  Year
B-TECH	CIVIL	Supplementary	October	2011												
B-TECH	CIVIL	Supplementary	October	2011									
B-TECH	ECE	Regular	        April	2011	


If I select combobox i have two B-TECH ,Civil,ECE r visible.That values are showed on different branch names and course also.Please rectify my problem.


Iam using below code for combobox fill.

C#
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 StudentDetails
{
    public partial class StudentDetails : Form
    {
        //SqlConnection con = new SqlConnection("Password=123;Persist Security Info=True;User ID=sa;Initial Catalog=C M S;Data Source=NITHYANANDAM");
        SqlConnection con = new SqlConnection("Password=123;Persist Security Info=True;User ID=sa;Initial Catalog=C M S;Data Source=SHIRISHAJALLU");
        SqlDataAdapter da,da1;
        SqlDataReader dr;
        SqlCommand cmd;
        DataSet ds;
        String Cb="empty";
        string rcfu = string.Empty;
        public StudentDetails()
        {
            InitializeComponent();
        }

        private void StudentDetails_Load(object sender, EventArgs e)
        {
            combofill();
            groupfill();
            groupBox1.Visible = false;
            dgvstudentwise.Visible = false;
            dgvsmemo.Visible = false;
            btnsave.Visible = false;
         }
        private void combofill()
        {
            if (con.State == 0)
            {
                con.Open();
            }
            cmd = new SqlCommand("select Course,Branch,Year,Semister,ExamType,MonthofAppear,YearofAppear from ExamEntry group by Course,Branch,Year,Semister,ExamType,MonthofAppear,YearofAppear", con);
            dr = cmd.ExecuteReader();
            while (dr.Read())
                {
                    cmbcourse.Items.Add(dr.GetString(0));
                    cmbbranch.Items.Add(dr.GetString(1));
                    cmbyear.Items.Add(dr.GetString(2));
                    cmbsem.Items.Add(dr.GetString(3));
                    cmbexamtype.Items.Add(dr.GetString(4));
                    cmbmonthofappear.Items.Add(dr.GetString(5));
                    cmbyearofappear.Items.Add(dr.GetString(6));

                }
                    
            dr.Close();
            con.Close();


        }
               
        private void btnclose_Click(object sender, EventArgs e)
        {
            this.Close();
        }
        private void btnsubmit_Click(object sender, EventArgs e)
        {
                dgvsmemo.Visible = true;
                btnsave.Visible = true;
                btnsearchstudentwise.Visible = true;
                dgvsmemo.Columns.Clear();
                DataGridViewCheckBoxColumn cl = new DataGridViewCheckBoxColumn();
                cl.Name = "CertificatesReceivedFromUniversity" ;
                cl.HeaderText = "CertificatesReceivedFromUniversity";
                cl.Width = 80;                      
                dgvsmemo.Columns.Add(cl);
                dgvsmemo.Columns[0].DefaultCellStyle.BackColor = Color.White;
                con.Open();
                da = new SqlDataAdapter("Select StudentID,SName as StudentName,Course,Branch,Year,Semister,ExamType,MonthofAppear,YearofAppear from ExamEntry where Course='" + cmbcourse.Text + "' and Branch='" + cmbbranch.Text + "' and Year='" + cmbyear.Text + "' and  Semister='" + cmbsem.Text + "' and ExamType='" + cmbexamtype.Text + "' and MonthofAppear='" + cmbmonthofappear.Text + "' and YearofAppear='" + cmbyearofappear.Text + "'", con);
                ds = new DataSet();
                ds.Tables.Clear();
                da.Fill(ds, "ExamEntry");             
                dgvsmemo.DataSource = ds.Tables["ExamEntry"];
               
          

            con.Close();
        }
        private void cmbcourse_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cmbcourse.Text == "MCA")
            {

            }
        }
        private void clear()
        {
            cmbcourse.Text = "";
            cmbbranch.Text = "";
            cmbyear.Text = "";
            cmbsem.Text = "";
            cmbexamtype.Text = "";
            cmbmonthofappear.Text = "";
            cmbyearofappear.Text = "";
            
        }
        private void btnsave_Click(object sender, EventArgs e)
        {

            string StudentID = string.Empty, Sname = string.Empty, Course = string.Empty, Branch = string.Empty, Year = string.Empty, Semister = string.Empty, ExamType = string.Empty, MonthofAppear = string.Empty, YearofAppear = string.Empty;
            int i = dgvsmemo.RowCount;
            for (int j = 0; j < i; j++)
            {
                DataGridViewCheckBoxCell c1 = new DataGridViewCheckBoxCell();
                c1 = (DataGridViewCheckBoxCell)dgvsmemo.Rows[j].Cells[0];
                if (c1.Value == null)
                    c1.Value = false;
                switch (c1.Value.ToString())
                {
                    case "True":
                        Cb = "Yes";
                        break;
                    case "False":
                        Cb = "No";
                        break;
                }
                
                StudentID = dgvsmemo.Rows[j].Cells["StudentID"].Value.ToString();
                Sname = dgvsmemo.Rows[j].Cells["StudentName"].Value.ToString();
                Course = dgvsmemo.Rows[j].Cells["Course"].Value.ToString();
                Branch = dgvsmemo.Rows[j].Cells["Branch"].Value.ToString();
                Year = dgvsmemo.Rows[j].Cells["Year"].Value.ToString();
                Semister = dgvsmemo.Rows[j].Cells["Semister"].Value.ToString();
                ExamType = dgvsmemo.Rows[j].Cells["ExamType"].Value.ToString();
                MonthofAppear = dgvsmemo.Rows[j].Cells["MonthofAppear"].Value.ToString();
                YearofAppear = dgvsmemo.Rows[j].Cells["YearofAppear"].Value.ToString();
                con.Open();
                cmd = new SqlCommand("insert into StudentMemoDetails values('" + Cb + "','" + StudentID + "','" + Sname + "','" + Course + "','" + Branch + "','" + Year + "','" + Semister + "','" + ExamType + "','" + MonthofAppear + "','" + YearofAppear + "')", con);
                cmd.ExecuteNonQuery();
                con.Close();
                MessageBox.Show("Certificates Details Saved Successfully");
                clear();
            }

        }

        private void btncancel_Click(object sender, EventArgs e)
        {
            cmbstudentid.Text = "";
            dgvstudentwise.Visible = false;
            groupBox1.Visible = false;
        }

        private void groupfill()
        {
            if (con.State == 0)
            {
                con.Open();
            }
            cmd = new SqlCommand("select distinct StudentID from ExamEntry", con);
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                cmbstudentid.Items.Add(dr.GetString(0));
            }

            dr.Close();
            con.Close();
        }

        private void btnok_Click(object sender, EventArgs e)
        {
            dgvstudentwise.Visible = true;
            da1 = new SqlDataAdapter("Select * from StudentMemoDetails where StudentID='"+cmbstudentid.Text+"'",con);
            ds = new DataSet();
            ds.Tables.Clear();
            da1.Fill(ds, "StudentMemoDetails");
            dgvstudentwise.DataSource = ds.Tables["StudentMemoDetails"];
               
        }

       
        private void btnsearchstudentwise_Click(object sender, EventArgs e)
        {
            groupBox1.Visible = true;
        }
                 
                                                   
    }
}
Posted
Updated 17-Oct-11 0:05am
v5
Comments
Prerak Patel 17-Oct-11 5:34am
   
Share some code.
Hari Krishna Prasad Inakoti 17-Oct-11 5:44am
   
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 StudentDetails
{
public partial class StudentDetails : Form
{
//SqlConnection con = new SqlConnection("Password=123;Persist Security Info=True;User ID=sa;Initial Catalog=C M S;Data Source=NITHYANANDAM");
SqlConnection con = new SqlConnection("Password=123;Persist Security Info=True;User ID=sa;Initial Catalog=C M S;Data Source=SHIRISHAJALLU");
SqlDataAdapter da,da1;
SqlDataReader dr;
SqlCommand cmd;
DataSet ds;
String Cb="empty";
string rcfu = string.Empty;
public StudentDetails()
{
InitializeComponent();
}

private void StudentDetails_Load(object sender, EventArgs e)
{
combofill();
groupfill();
groupBox1.Visible = false;
dgvstudentwise.Visible = false;
dgvsmemo.Visible = false;
btnsave.Visible = false;
}
private void combofill()
{
if (con.State == 0)
{
con.Open();
}
cmd = new SqlCommand("select Course,Branch,Year,Semister,ExamType,MonthofAppear,YearofAppear from ExamEntry group by Course,Branch,Year,Semister,ExamType,MonthofAppear,YearofAppear", con);
dr = cmd.ExecuteReader();
while (dr.Read())
{
cmbcourse.Items.Add(dr.GetString(0));
cmbbranch.Items.Add(dr.GetString(1));
cmbyear.Items.Add(dr.GetString(2));
cmbsem.Items.Add(dr.GetString(3));
cmbexamtype.Items.Add(dr.GetString(4));
cmbmonthofappear.Items.Add(dr.GetString(5));
cmbyearofappear.Items.Add(dr.GetString(6));

}

dr.Close();
con.Close();


}

private void btnclose_Click(object sender, EventArgs e)
{
this.Close();
}
private void btnsubmit_Click(object sender, EventArgs e)
{
dgvsmemo.Visible = true;
btnsave.Visible = true;
btnsearchstudentwise.Visible = true;
dgvsmemo.Columns.Clear();
DataGridViewCheckBoxColumn cl = new DataGridViewCheckBoxColumn();
cl.Name = "CertificatesReceivedFromUniversity" ;
cl.HeaderText = "CertificatesReceivedFromUniversity";
cl.Width = 80;
dgvsmemo.Columns.Add(cl);
dgvsmemo.Columns[0].DefaultCellStyle.BackColor = Color.White;
con.Open();
da = new SqlDataAdapter("Select StudentID,SName as StudentName,Course,Branch,Year,Semister,ExamType,MonthofAppear,YearofAppear from ExamEntry where Course='" + cmbcourse.Text + "' and Branch='" + cmbbranch.Text + "' and Year='" + cmbyear.Text + "' and Semister='" + cmbsem.Text + "' and ExamType='" + cmbexamtype.Text + "' and MonthofAppear='" + cmbmonthofappear.Text + "' and YearofAppear='" + cmbyearofappear.Text + "'", con);
ds = new DataSet();
ds.Tables.Clear();
da.Fill(ds, "ExamEntry");
dgvsmemo.DataSource = ds.Tables["ExamEntry"];



con.Close();
}
private void cmbcourse_SelectedIndexChanged(object sender, EventArgs e)
{
if (cmbcourse.Text == "MCA")
{

}
}
private void clear()
{
cmbcourse.Text = "";
cmbbranch.Text = "";
cmbyear.Text = "";
cmbsem.Text = "";
cmbexamtype.Text = "";
cmbmonthofappear.Text = "";
cmbyear
Xeshan Ahmed 17-Oct-11 5:37am
   
insufficient information
Xeshan Ahmed 17-Oct-11 5:38am
   
share your code for getting data from DB and populating in combox
Hari Krishna Prasad Inakoti 17-Oct-11 5:44am
   
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 StudentDetails
{
public partial class StudentDetails : Form
{
//SqlConnection con = new SqlConnection("Password=123;Persist Security Info=True;User ID=sa;Initial Catalog=C M S;Data Source=NITHYANANDAM");
SqlConnection con = new SqlConnection("Password=123;Persist Security Info=True;User ID=sa;Initial Catalog=C M S;Data Source=SHIRISHAJALLU");
SqlDataAdapter da,da1;
SqlDataReader dr;
SqlCommand cmd;
DataSet ds;
String Cb="empty";
string rcfu = string.Empty;
public StudentDetails()
{
InitializeComponent();
}

private void StudentDetails_Load(object sender, EventArgs e)
{
combofill();
groupfill();
groupBox1.Visible = false;
dgvstudentwise.Visible = false;
dgvsmemo.Visible = false;
btnsave.Visible = false;
}
private void combofill()
{
if (con.State == 0)
{
con.Open();
}
cmd = new SqlCommand("select Course,Branch,Year,Semister,ExamType,MonthofAppear,YearofAppear from ExamEntry group by Course,Branch,Year,Semister,ExamType,MonthofAppear,YearofAppear", con);
dr = cmd.ExecuteReader();
while (dr.Read())
{
cmbcourse.Items.Add(dr.GetString(0));
cmbbranch.Items.Add(dr.GetString(1));
cmbyear.Items.Add(dr.GetString(2));
cmbsem.Items.Add(dr.GetString(3));
cmbexamtype.Items.Add(dr.GetString(4));
cmbmonthofappear.Items.Add(dr.GetString(5));
cmbyearofappear.Items.Add(dr.GetString(6));

}

dr.Close();
con.Close();


}

private void btnclose_Click(object sender, EventArgs e)
{
this.Close();
}
private void btnsubmit_Click(object sender, EventArgs e)
{
dgvsmemo.Visible = true;
btnsave.Visible = true;
btnsearchstudentwise.Visible = true;
dgvsmemo.Columns.Clear();
DataGridViewCheckBoxColumn cl = new DataGridViewCheckBoxColumn();
cl.Name = "CertificatesReceivedFromUniversity" ;
cl.HeaderText = "CertificatesReceivedFromUniversity";
cl.Width = 80;
dgvsmemo.Columns.Add(cl);
dgvsmemo.Columns[0].DefaultCellStyle.BackColor = Color.White;
con.Open();
da = new SqlDataAdapter("Select StudentID,SName as StudentName,Course,Branch,Year,Semister,ExamType,MonthofAppear,YearofAppear from ExamEntry where Course='" + cmbcourse.Text + "' and Branch='" + cmbbranch.Text + "' and Year='" + cmbyear.Text + "' and Semister='" + cmbsem.Text + "' and ExamType='" + cmbexamtype.Text + "' and MonthofAppear='" + cmbmonthofappear.Text + "' and YearofAppear='" + cmbyearofappear.Text + "'", con);
ds = new DataSet();
ds.Tables.Clear();
da.Fill(ds, "ExamEntry");
dgvsmemo.DataSource = ds.Tables["ExamEntry"];



con.Close();
}
private void cmbcourse_SelectedIndexChanged(object sender, EventArgs e)
{
if (cmbcourse.Text == "MCA")
{

}
}
private void clear()
{
cmbcourse.Text = "";
cmbbranch.Text = "";
cmbyear.Text = "";
cmbsem.Text = "";
cmbexamtype.Text = "";
cmbmonthofappear.Text = "";
cmbyear
dasblinkenlight 17-Oct-11 5:46am
   
That's too much code now :) Could you please post only the SQL portion that reads the data for your combo box, and remove everything else?
Hari Krishna Prasad Inakoti 17-Oct-11 5:55am
   
cmd = new SqlCommand("select Course,Branch,Year,Semister,ExamType,MonthofAppear,YearofAppear from ExamEntry group by Course,Branch,Year,Semister,ExamType,MonthofAppear,YearofAppear", con);

1 solution

I feel there are problem in group by of data. you have not provided the values in columns "Year,Semister". I feel there are unique value in anyone of these columns for all duplicated rows.

If this is the case remove "Year,Semister" from query, you'll get correct result.
   
Comments
Hari Krishna Prasad Inakoti 17-Oct-11 6:05am
   
I want all fields
d2niraj 17-Oct-11 8:20am
   
In this case you have to check for value exists while adding to drop down. If value exists then skip else add to drop down.

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