using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data;
 
namespace Export_to_Excel
{
    public partial class Form1 : Form
    {
        DateTime date1;
        public Form1()
        {
            InitializeComponent();
            FillComboCOK();
            FillComboMESYAC();
        }
        void FillComboCOK()  //Функция заполнения комбобокс ЦОКи
        {
            string connstring = @"Data Source";
            string query1 = "select [Код],[ЦОК] from [Access_BD]...[ЦОК];";
            SqlConnection sqlconn = new SqlConnection(connstring);
            SqlCommand sqlcomm1 = new SqlCommand(query1, sqlconn);
            SqlDataReader sqldatareader1;
            try
            {
                sqlconn.Open();
                sqldatareader1 = sqlcomm1.ExecuteReader();
                while (sqldatareader1.Read())
                {
                    string columnname1 = sqldatareader1.GetString(1); 
                    comboBox1.Items.Add(columnname1);
                    comboBox3.Items.Add(columnname1);
                    //string cb1 = comboBox1.Text;
                    //string cb3 = comboBox3.Text;
                }                
            }
            catch (Exception ex) { }
        } 
 
        void FillComboMESYAC()  //Функция заполнения комбобокс Месяца
        {
            string connstring = @"Data Source";
            string query2 = "select month([Дата]) from [Access_BD]...[Платежи_] group by month([Дата]) order by month([Дата]);";
            SqlConnection sqlconn = new SqlConnection(connstring);
            SqlCommand sqlcomm2 = new SqlCommand(query2, sqlconn);
            SqlDataReader sqldatareader2;
            try
            {
                sqlconn.Open();
                sqldatareader2 = sqlcomm2.ExecuteReader();
                while (sqldatareader2.Read())
                {
                    string columnname2 = sqldatareader2.GetString(1);
                    comboBox2.Items.Add(columnname2);
                    
                }                
            }
            catch (Exception ex) { }
        }
 
        private void monthCalendar1_DateChanged(object sender, DateRangeEventArgs e)
        {
            date1 = Convert.ToDateTime(monthCalendar1.SelectionStart.ToString());
           // string queryday = ";";
        }
 
        private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
        {
            string constring = @"Data Source";
            SqlConnection sqlconn = new SqlConnection(constring);
            SqlCommand sqlcomm = new SqlCommand("Платежи", sqlconn);
            sqlcomm.CommandType = System.Data.CommandType.StoredProcedure;
            sqlcomm.Parameters.Add("@idCP", System.Data.SqlDbType.Int).Value = 1; //Подвязать ID c comboBox1
            string cb2 = comboBox2.Text;
            sqlcomm.Parameters.Add("@month", System.Data.SqlDbType.Int).Value = Convert.ToInt32(cb2);
            try
            {
                SqlDataAdapter sqladap = new SqlDataAdapter(sqlcomm);
                DataTable dtable = new DataTable();
                sqladap.Fill(dtable);
                BindingSource bso = new BindingSource();
                bso.DataSource = dtable;
                dataGridView1.DataSource = bso;
                sqladap.Update(dtable);
            }
            catch (Exception ex) { }
        }
    }
}

 try
            {
                sqlconn.Open();
                sqldatareader1 = sqlcomm1.ExecuteReader();
                while (sqldatareader1.Read())
                {
                    string columnname2_0 = sqldatareader1.GetString(1);
                    string columnname2_1 = sqldatareader1.GetString(0);
                    comboBox1.ValueMember = columnname2_1;
                    comboBox1.DisplayMember = columnname2_0;
                }         

try
            {
                sqlconn.Open();
                sqldatareader1 = sqlcomm1.ExecuteReader();
 
                    DataTable dt = new DataTable();
                    dt.Columns.Add("[Код]", typeof(Int32));
                    dt.Columns.Add("[ЦОК]", typeof(string));
                    comboBox1.ValueMember = "[Код]";
                    comboBox1.DisplayMember = "[ЦОК]";
                    comboBox1.DataSource = dt;
                    sqlconn.Close();

string connstring = @"Data Source=";
            string query1 = "select [Код],[ЦОК] from [Access_BD]...[ЦОК];";
            SqlConnection sqlconn = new SqlConnection(connstring);
            SqlCommand sqlcomm1 = new SqlCommand(query1, sqlconn);
            try
            {
            sqlconn.Open();
            SqlDataAdapter sqladap1 = new SqlDataAdapter(sqlcomm1);
            DataTable dt1 = new DataTable();
            sqladap1.Fill(dt1);
            comboBox1.ValueMember = "Код";
            comboBox1.DisplayMember = "ЦОК";
            comboBox1.DataSource = dt1;     
            sqladap1.Update(dt1);
            sqlconn.Close();
            }
             catch (Exception ex) { };