public partial class Form4 : Form
{
private DBConnect conn = new DBConnect();
private DataSet dsDataGrid;
private DataSet dsMaster;
private MySqlDataAdapter daDataGrid;
public Form4()
{
InitializeComponent();
}
private void cbDataYear_SelectedIndexChanged(object sender, EventArgs e)
{
string sql;
if (this.cbDataYear.SelectedValue.ToString() != "")
{
sql = "select wy.year_th,wd.data_month,wd.service_date,wd.wbc_no,wd.birthday,wd.cid,wd.hn,cp.pname,concat(wd.fname,' ',wd.lname) as pt_name ";
sql += " ,wc.case_name,thp.PROVINCE_NAME,tha.AMPHUR_NAME,thd.DISTRICT_NAME ";
sql += " FROM wbc_data wd ";
sql += " left outer join c_pname cp on wd.pname = cp.oid";
sql += " left outer join wbc_case wc on wd.`case` = wc.oid";
sql += " left outer join ref_thaiaddress_province thp on wd.chwpart = thp.PROVINCE_CODE";
sql += " left outer join ref_thaiaddress_amphur tha on concat(wd.chwpart,wd.amppart) = tha.AMPHUR_CODE";
sql += " left outer join ref_thaiaddress_district thd on concat(wd.chwpart,wd.amppart,wd.tmbpart) = thd.DISTRICT_CODE";
sql += " left outer join wbc_year wy on wd.data_year = wy.year_en";
sql += " where wd.data_year = '" + this.cbDataYear.SelectedValue.ToString() + "' ";
sql += " ORDER BY wd.hn asc";
loadDataToDataGrid(sql);
}
}
public void loadDataToDataGrid(string sql) {
MySqlCommand command;
daDataGrid = new MySqlDataAdapter();
conn.OpenConn();
command = new MySqlCommand(sql, conn.OpenConn());
dsDataGrid = new DataSet();
daDataGrid.SelectCommand = command;
daDataGrid.Fill(dsDataGrid,"wbc_data2");
conn.CloseConn();
dataGridView1.DataSource = dsDataGrid;
dataGridView1.DataMember = "wbc_data2"; //Error !!! Child list for field wbc_data cannot be created.
dataGridView1.Columns[0].HeaderText = "ข้อมูลปี";
dataGridView1.Columns[1].HeaderText = "ข้อมูลเดือน"; ///Error !!! ->Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index
dataGridView1.Columns[2].HeaderText = "วันที่รับบริการ";
dataGridView1.Columns[3].HeaderText = "wbc_no";
dataGridView1.Columns[4].HeaderText = "วันเกิด";
dataGridView1.Columns[5].HeaderText = "CID";
dataGridView1.Columns[6].HeaderText = "HN";
dataGridView1.Columns[7].HeaderText = "pname";
dataGridView1.Columns[8].HeaderText = "pt_name";
dataGridView1.Columns[9].HeaderText = "case_name";
dataGridView1.Columns[10].HeaderText = "PROVINCE_NAME";
dataGridView1.Columns[11].HeaderText = "AMPHUR_NAME";
dataGridView1.Columns[12].HeaderText = "DISTRICT_NAME";
}
public void test(){
MySqlDataAdapter daMaster = new MySqlDataAdapter();
MySqlCommand command;
string sql1,sql2;
sql1 = "select wy.year_th,wd.data_month,wd.service_date,wd.wbc_no,wd.birthday,wd.cid,wd.hn,cp.pname,concat(wd.fname,' ',wd.lname) as pt_name ";
sql1 += " ,wc.case_name,thp.PROVINCE_NAME,tha.AMPHUR_NAME,thd.DISTRICT_NAME ";
sql1 += " FROM wbc_data wd ";
sql1 += " left outer join c_pname cp on wd.pname = cp.oid";
sql1 += " left outer join wbc_case wc on wd.`case` = wc.oid";
sql1 += " left outer join ref_thaiaddress_province thp on wd.chwpart = thp.PROVINCE_CODE";
sql1 += " left outer join ref_thaiaddress_amphur tha on concat(wd.chwpart,wd.amppart) = tha.AMPHUR_CODE";
sql1 += " left outer join ref_thaiaddress_district thd on concat(wd.chwpart,wd.amppart,wd.tmbpart) = thd.DISTRICT_CODE";
sql1 += " left outer join wbc_year wy on wd.data_year = wy.year_en";
sql1 += " where wd.data_year = '2013' ";
sql1 += " ORDER BY wd.hn asc";
conn.OpenConn();
command = new MySqlCommand(sql1, conn.OpenConn());
dsMaster = new DataSet();
daMaster.SelectCommand = command;
daMaster.Fill(dsMaster, "wbc_data1");
daMaster.Dispose();
command.Dispose();
conn.CloseConn();
dataGridView1.Columns.Clear(); // <-- clear columns
dataGridView1.DataSource = dsMaster;
dataGridView1.DataMember = "wbc_data1";
}
private void Form4_Load(object sender, EventArgs e)
{
test();
}
}