Пример #1
0
        private void ShowData()
        {
            ClassConnection.connectionDatabase();
            string Sql = @"SELECT * FROM tbemployee ORDER BY Id_employee";

            ClassConnection.command = new OleDbCommand(Sql, ClassConnection.connection);
            OleDbDataReader DataReader = ClassConnection.command.ExecuteReader();

            if (DataReader.HasRows)
            {
                ClassConnection.dataTable = new DataTable();
                ClassConnection.dataTable.Load(DataReader);
                dataGridView1.DataSource = ClassConnection.dataTable;
                FormatDataGridView();
                DataReader.Close();
            }
            else
            {
                ClassConnection.dataTable = new DataTable();
                ClassConnection.dataTable.Load(DataReader);
                dataGridView1.DataSource = ClassConnection.dataTable;
                FormatDataGridView();
                DataReader.Close();
            }
        }
Пример #2
0
        private void ShowData()
        {
            ClassConnection.connectionDatabase();
            string Sql = @"SELECT tbproduct.Id_product,tbproduct.N_product,tbproduct.N_product_desc,tbcategory.N_category,tbproduct.N_seller, tbproduct.pricebuy,tbproduct.unit
                            FROM tbproduct,tbcategory WHERE tbproduct.Id_category = tbcategory.Id_category
                            ORDER BY tbproduct.Id_product";

            ClassConnection.command = new OleDbCommand(Sql, ClassConnection.connection);
            OleDbDataReader DataReader = ClassConnection.command.ExecuteReader();

            if (DataReader.HasRows)
            {
                ClassConnection.dataTable = new DataTable();
                ClassConnection.dataTable.Load(DataReader);
                dataGridView1.DataSource = ClassConnection.dataTable;
                FormatDataGridView();
                DataReader.Close();
            }
            else
            {
                ClassConnection.dataTable = new DataTable();
                ClassConnection.dataTable.Load(DataReader);
                dataGridView1.DataSource = ClassConnection.dataTable;
                FormatDataGridView();
                DataReader.Close();
            }
        }
Пример #3
0
 private void FormInventory_Load(object sender, EventArgs e)
 {
     ClassConnection.connectionDatabase();
     ShowData();
     EnabledFalse();
     buttonDelete.Enabled = false;
     buttonSave.Enabled   = false;
     buttonCancel.Enabled = false;
 }
Пример #4
0
        private void ButtonSave_Click(object sender, EventArgs e)
        {
            if (textBox1.Text == "")
            {
                return;
            }
            try
            {
                ClassConnection.connectionDatabase();
                string sqls = "";
                sqls  = "INSERT INTO tbbuy(buyid,buytotal,buydate)";
                sqls += " VALUES(@a,@b,@c)";
                OleDbCommand yourCommands = new OleDbCommand(sqls, ClassConnection.connection);
                yourCommands.Parameters.AddWithValue("a", txtID.Text);
                yourCommands.Parameters.AddWithValue("b", Convert.ToDouble(lblTotal.Text));
                yourCommands.Parameters.AddWithValue("c", dateTimepicker1.Value.ToShortDateString());
                yourCommands.ExecuteNonQuery();

                for (int i = 0; i <= listView1.Items.Count - 1; i++)
                {
                    ClassConnection.connectionDatabase();
                    string Sql = @"INSERT INTO tbbuyitem(buyid,Id_product,buyitempricebuy,buyitemqty,buyitemtotal) VALUES(@a,@b,@c,@d,@e)";
                    ClassConnection.command = new OleDbCommand(Sql, ClassConnection.connection);
                    ClassConnection.command.Parameters.AddWithValue("a", txtID.Text);
                    ClassConnection.command.Parameters.AddWithValue("b", Convert.ToString(listView1.Items[i].SubItems[0].Text));
                    ClassConnection.command.Parameters.AddWithValue("c", Convert.ToInt32(listView1.Items[i].SubItems[3].Text));
                    ClassConnection.command.Parameters.AddWithValue("d", Convert.ToDouble(listView1.Items[i].SubItems[2].Text));
                    ClassConnection.command.Parameters.AddWithValue("e", Convert.ToDouble(listView1.Items[i].SubItems[4].Text));
                    ClassConnection.command.ExecuteNonQuery();

                    string sqlStrings = "";

                    sqlStrings = "UPDATE tbproduct SET unit = unit + @a WHERE Id_product= @id";
                    OleDbCommand myCommands = new OleDbCommand(sqlStrings, ClassConnection.connection);
                    myCommands.Parameters.AddWithValue("a", Convert.ToInt32(listView1.Items[i].SubItems[2].Text));
                    myCommands.Parameters.AddWithValue("id", listView1.Items[i].SubItems[0].Text);
                    myCommands.ExecuteNonQuery();
                }

                MessageBox.Show("บันทึกเรียบร้อยแล้ว", "ตรวจสอบ", MessageBoxButtons.OK);
                //ClassSale.SalePrintID = TextBox1.Text.Trim();

                ClassConnection.connectionDatabase();
                ClearData();
                EnabledFalse();
                ButtonOpen.Enabled   = true;
                ButtonAdd.Enabled    = false;
                ButtonDelete.Enabled = false;
                ButtonCancel.Enabled = false;
                ButtonSave.Enabled   = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message.ToString());
            }
        }
Пример #5
0
 private void FormSale_Load(object sender, EventArgs e)
 {
     ClassConnection.connectionDatabase();
     ClearData();
     EnabledFalse();
     ButtonOpen.Enabled   = true;
     ButtonAdd.Enabled    = false;
     ButtonDelete.Enabled = false;
     ButtonCancel.Enabled = false;
     ButtonSave.Enabled   = false;
 }
Пример #6
0
 private void buttonCancel_Click(object sender, EventArgs e)
 {
     ClassConnection.connectionDatabase();
     ClearData();
     ShowData();
     EnabledFalse();
     buttonAdd.Enabled    = true;
     buttonEdit.Enabled   = true;
     buttonDelete.Enabled = false;
     buttonSave.Enabled   = false;
     buttonCancel.Enabled = false;
 }
Пример #7
0
        private void buttonSearch_Click(object sender, EventArgs e)
        {
            ClassConnection.connectionDatabase();
            String sql = @"SELECT * FROM tbcategory WHERE Id_category Like @name OR N_category Like @name ORDER BY Id_category";

            ClassConnection.command = new OleDbCommand(sql, ClassConnection.connection);
            ClassConnection.command.Parameters.AddWithValue("name", "%" + textBoxSearch.Text + "%");
            OleDbDataReader DataReader = ClassConnection.command.ExecuteReader();

            ClassConnection.dataTable = new DataTable();
            ClassConnection.dataTable.Load(DataReader);
            dataGridView1.DataSource = ClassConnection.dataTable;
            FormatDataGridView();
        }
Пример #8
0
        private void buttonSearch_Click(object sender, EventArgs e)
        {
            ClassConnection.connectionDatabase();
            string Sql = @"SELECT tbproduct.Id_product, tbproduct.N_product, tbproduct.N_product_desc, tbproduct.N_seller,
                           tbcategory.N_category, tbproduct.pricebuy, tbproduct.unit
                            FROM tbproduct,tbcategory WHERE tbproduct.Id_category = tbcategory.Id_category
                            AND (tbproduct.Id_product Like @name
                            OR tbproduct.N_product Like @name)
                            ORDER BY tbproduct.Id_product";

            ClassConnection.command = new OleDbCommand(Sql, ClassConnection.connection);
            ClassConnection.command.Parameters.AddWithValue("name", "%" + textBoxSearch.Text + "%");//ค้นหา
            OleDbDataReader DataReader = ClassConnection.command.ExecuteReader();

            ClassConnection.dataTable = new DataTable();
            ClassConnection.dataTable.Load(DataReader);
            dataGridView1.DataSource = ClassConnection.dataTable;
            FormatDataGridView();
        }
Пример #9
0
 private void comboBox1_TextChanged(object sender, EventArgs e)
 {
     try
     {
         //comboBox1.Items.Clear();
         ClassConnection.connectionDatabase();
         string Sql = "SELECT Id_category FROM tbcategory WHERE N_category =@cat";
         ClassConnection.command = new OleDbCommand(Sql, ClassConnection.connection);
         ClassConnection.command.Parameters.AddWithValue("cat", comboBox1.Text);
         OleDbDataReader DataReader = ClassConnection.command.ExecuteReader();
         DataReader.Read();
         _idcat = DataReader["Id_category"].ToString();
         DataReader.Close();
     }
     catch (Exception ex)
     {
         //MessageBox.Show(ex.Message.ToString());
         comboBox1.Text = "";
     }
 }
Пример #10
0
        private void MaxID()
        {
            int maxId = 0;

            ClassConnection.connectionDatabase();
            string Sql = @"SELECT MAX(saleid) FROM tbsale";

            ClassConnection.command = new OleDbCommand(Sql, ClassConnection.connection);
            //ClassConnection.DataReader = ClassConnection.Command.ExecuteReader();
            try
            {
                maxId = Convert.ToInt32(ClassConnection.command.ExecuteScalar());
                //MessageBox.Show(maxId.ToString());
            }
            catch (Exception ex)
            {
                maxId = 0;
            }
            txtID.Text = (maxId + 1).ToString("000");
        }
Пример #11
0
 private void showcombobox()
 {
     try
     {
         comboBox1.Items.Clear();
         ClassConnection.connectionDatabase();
         string Sql = "SELECT Distinct N_category FROM tbcategory ORDER BY N_category";
         ClassConnection.command = new OleDbCommand(Sql, ClassConnection.connection);
         OleDbDataReader DataReader = ClassConnection.command.ExecuteReader();
         while (DataReader.Read())
         {
             comboBox1.Items.Add(DataReader["N_category"]);
         }
         DataReader.Close();
     }
     catch (Exception ex)
     {
         //MessageBox.Show(ex.Message.ToString());
         comboBox1.Text = "";
     }
 }
Пример #12
0
        private void buttonDelete_Click(object sender, EventArgs e)
        {
            DialogResult result = MessageBox.Show("ต้องการลบข้อมูลจริงหรือไม่", "ยืนยันการลบ", MessageBoxButtons.YesNo);

            if (result == DialogResult.Cancel || result == DialogResult.No)
            {
                return;
            }
            OleDbTransaction tr = ClassConnection.connection.BeginTransaction();

            try
            {
                ClassConnection.connectionDatabase();
                string Sql = @"DELETE FROM tbemployee WHERE Id_employee = @id";
                ClassConnection.command = new OleDbCommand(Sql, ClassConnection.connection);
                ClassConnection.command.Parameters.AddWithValue("id", textBoxID.Text);
                int r = (int)ClassConnection.command.ExecuteNonQuery();
                if (r == -1)
                {
                    MessageBox.Show("เกิดข้อผิดพลาด ไม่สามารถลบข้อมูลได้", "ตรวจสอบ");
                    tr.Rollback();
                }
                else
                {
                    MessageBox.Show("ลบข้อมูลแล้ว", "ตรวจสอบ");
                    tr.Commit();
                    ClearData();
                    ShowData();
                }
            }
            catch
            {
                tr.Rollback();
            }
            finally
            {
                ClassConnection.connectionDatabase();
                tr.Dispose();
            }
        }
Пример #13
0
        private void buttonLogin_Click(object sender, EventArgs e)
        {
            if (textBoxUsername.Text == "")
            {
                textBoxUsername.Focus();
                return;
            }
            else if (textBoxPassword.Text == "")
            {
                textBoxPassword.Focus();
                return;
            }

            ClassConnection.connectionDatabase();
            string sql = @"SELECT * FROM tbemployee WHERE username = @username AND password = @password";

            ClassConnection.command = new OleDbCommand(sql, ClassConnection.connection);
            ClassConnection.command.Parameters.AddWithValue("username", textBoxUsername.Text);
            ClassConnection.command.Parameters.AddWithValue("password", textBoxPassword.Text);

            OleDbDataReader DataReader = ClassConnection.command.ExecuteReader();//รับค่าcommand

            DataReader.Read();
            if (DataReader.HasRows)             //hasrow มีค่าซ้ำ
            {
                FormMain form = new FormMain(); //เชื่อมไปอีกฟอม
                MessageBox.Show("ยินดีต้อนรับเข้าสู่ระบบ", "ตรวจสอบ", MessageBoxButtons.OK);
                this.Hide();
                form.ShowDialog();
            }
            else
            {
                MessageBox.Show("username หรือ password ไม่ถูกต้อง", "ตรวจสอบ", MessageBoxButtons.OK);
            }
            ClearData();
        }
Пример #14
0
        private void buttonSave_Click(object sender, EventArgs e)
        {
            if (textBoxID.Text == "")
            {
                textBoxID.Focus();
                return;
            }
            else if (textBoxName.Text == "")
            {
                textBoxName.Focus();
                return;
            }
            else if (textBoxCard.Text == "")
            {
                textBoxCard.Focus();
                return;
            }
            else if (textBoxAddress.Text == "")
            {
                textBoxAddress.Focus();
                return;
            }
            else if (textBoxTel.Text == "")
            {
                textBoxTel.Focus();
                return;
            }
            else if (textBoxUsername.Text == "")
            {
                textBoxUsername.Focus();
                return;
            }
            else if (textBoxPassword.Text == "")
            {
                textBoxPassword.Focus();
                return;
            }

            if (ClassConnection.count == 1)
            {
                ClassConnection.connectionDatabase();

                string Sql = @"SELECT * FROM tbemployee WHERE N_employee = '" + textBoxName.Text + "'";

                ClassConnection.command = new OleDbCommand(Sql, ClassConnection.connection);

                OleDbDataReader DataReader = ClassConnection.command.ExecuteReader();

                if (DataReader.HasRows)
                {
                    MessageBox.Show("มีข้อมูลซ้ำกรุณาตรวจสอบ", "ตรวจสอบ");
                    return;
                }
                else
                {
                    DataReader.Close();
                    OleDbTransaction tr = ClassConnection.connection.BeginTransaction();
                    try
                    {
                        ClassConnection.connectionDatabase();
                        string SqlInsert = @"INSERT INTO tbemployee(Id_employee, N_employee, card_employee, address_employee, tel_employee, [username], [password])
                                                VALUES(@a,@b,@c,@d,@e,@f,@g)";
                        ClassConnection.command = new OleDbCommand(SqlInsert, ClassConnection.connection);
                        ClassConnection.command.Parameters.AddWithValue("a", textBoxID.Text);
                        ClassConnection.command.Parameters.AddWithValue("b", textBoxName.Text);
                        ClassConnection.command.Parameters.AddWithValue("c", textBoxCard.Text);
                        ClassConnection.command.Parameters.AddWithValue("d", textBoxAddress.Text);
                        ClassConnection.command.Parameters.AddWithValue("e", textBoxTel.Text);
                        ClassConnection.command.Parameters.AddWithValue("f", textBoxUsername.Text);
                        ClassConnection.command.Parameters.AddWithValue("g", textBoxPassword.Text);
                        int result = ClassConnection.command.ExecuteNonQuery();
                        if (result == -1)
                        {
                            MessageBox.Show("เกิดข้อผิดพลาด ไม่สามารถเพิ่มข้อมูลได้", "ตรวจสอบ");
                            tr.Rollback();
                        }
                        else
                        {
                            MessageBox.Show("บันทึกข้อมูลแล้ว", "ตรวจสอบ");
                            tr.Commit();
                        }
                    }
                    catch (System.Exception ex)
                    {
                        MessageBox.Show(ex.Message.ToString());
                        tr.Rollback();
                    }
                    finally
                    {
                        ClassConnection.connectionDatabase();
                        tr.Dispose();
                    }
                }
            }
            else if (ClassConnection.count == 2)
            {
                OleDbTransaction tr = ClassConnection.connection.BeginTransaction();
                try
                {
                    ClassConnection.connectionDatabase();
                    string SqlUpdate = @"UPDATE tbemployee SET N_employee =@b,card_employee =@c,address_employee =@d,tel_employee =@e,[username] =@f,[password] =@g WHERE Id_employee = @id";
                    ClassConnection.command = new OleDbCommand(SqlUpdate, ClassConnection.connection);
                    ClassConnection.command.Parameters.AddWithValue("b", textBoxName.Text);
                    ClassConnection.command.Parameters.AddWithValue("c", textBoxCard.Text);
                    ClassConnection.command.Parameters.AddWithValue("d", textBoxAddress.Text);
                    ClassConnection.command.Parameters.AddWithValue("e", textBoxTel.Text);
                    ClassConnection.command.Parameters.AddWithValue("f", textBoxUsername.Text);
                    ClassConnection.command.Parameters.AddWithValue("g", textBoxPassword.Text);
                    ClassConnection.command.Parameters.AddWithValue("id", textBoxID.Text);
                    int result = ClassConnection.command.ExecuteNonQuery();
                    if (result == -1)
                    {
                        MessageBox.Show("เกิดข้อผิดพลาด ไม่สามารถแก้ไขข้อมูลได้", "ตรวจสอบ");
                        tr.Rollback();
                    }
                    else
                    {
                        MessageBox.Show("แก้ไขข้อมูลแล้ว", "ตรวจสอบ");
                        tr.Commit();
                    }
                }
                catch (System.Exception ex)
                {
                    MessageBox.Show(ex.Message.ToString());
                    tr.Rollback();
                }
                finally
                {
                    ClassConnection.connectionDatabase();
                    tr.Dispose();
                }
            }
            buttonSave.Enabled   = false;
            buttonCancel.Enabled = false;
            buttonAdd.Enabled    = true;
            buttonEdit.Enabled   = true;
            buttonDelete.Enabled = true;
            ClearData();
            ShowData();
            ClassConnection.count = 0;
            EnabledFalse();
        }
Пример #15
0
        private void buttonSave_Click(object sender, EventArgs e)
        {
            if (textBoxID.Text == "")
            {
                textBoxID.Focus();
                return;
            }
            else if (textBoxName.Text == "")
            {
                textBoxName.Focus();
                return;
            }
            else if (textboxDesc.Text == "")
            {
                textBoxName.Focus();
                return;
            }
            else if (comboBox1.Text == "")
            {
                comboBox1.Select();
                return;
            }
            else if (textboxSeller.Text == "")
            {
                textBoxPriceBuy.Focus();
                return;
            }
            else if (textBoxPriceBuy.Text == "")
            {
                textBoxPriceBuy.Focus();
                return;
            }
            else if (textBoxNum.Text == "")
            {
                textBoxNum.Focus();
                return;
            }

            if (ClassConnection.count == 1)
            {
                ClassConnection.connectionDatabase();

                string Sql = @"SELECT * FROM tbproduct WHERE N_product = '" + textBoxName.Text + "'";

                ClassConnection.command = new OleDbCommand(Sql, ClassConnection.connection);

                OleDbDataReader DataReader = ClassConnection.command.ExecuteReader();

                if (DataReader.HasRows)
                {
                    MessageBox.Show("มีข้อมูลซ้ำกรุณาตรวจสอบ", "ตรวจสอบ");
                    return;
                }
                else
                {
                    DataReader.Close();
                    OleDbTransaction tr = ClassConnection.connection.BeginTransaction();//คนแรกให้เข้าคนที่สองเข้ามาซ้ำไม่ได้
                    try
                    {
                        ClassConnection.connectionDatabase();
                        string SqlInsert = @"INSERT INTO tbproduct(Id_product, N_product, N_product_desc, Id_category, N_seller, pricebuy ,unit)
                                                VALUES(@a,@b,@c,@d,@e,@f,@g)";
                        ClassConnection.command = new OleDbCommand(SqlInsert, ClassConnection.connection);
                        ClassConnection.command.Parameters.AddWithValue("a", textBoxID.Text);
                        ClassConnection.command.Parameters.AddWithValue("b", textBoxName.Text);
                        ClassConnection.command.Parameters.AddWithValue("c", textboxDesc.Text);
                        ClassConnection.command.Parameters.AddWithValue("d", _idcat);
                        ClassConnection.command.Parameters.AddWithValue("e", textboxSeller.Text);
                        ClassConnection.command.Parameters.AddWithValue("f", textBoxPriceBuy.Text);
                        ClassConnection.command.Parameters.AddWithValue("g", textBoxNum.Text);
                        int result = ClassConnection.command.ExecuteNonQuery();
                        if (result == -1)
                        {
                            MessageBox.Show("เกิดข้อผิดพลาด ไม่สามารถเพิ่มข้อมูลได้", "ตรวจสอบ");
                            tr.Rollback();
                        }
                        else
                        {
                            MessageBox.Show("บันทึกข้อมูลแล้ว", "ตรวจสอบ");
                            tr.Commit();
                        }
                    }
                    catch (System.Exception ex)
                    {
                        MessageBox.Show(ex.Message.ToString());
                        tr.Rollback();
                    }//หาสาเหตุ
                    finally
                    {
                        ClassConnection.connectionDatabase();
                        tr.Dispose();
                    }
                }
            }
            else if (ClassConnection.count == 2)
            {
                OleDbTransaction tr = ClassConnection.connection.BeginTransaction();
                try
                {
                    ClassConnection.connectionDatabase();
                    string SqlUpdate = @"UPDATE tbproduct SET N_product =@b, N_product_desc =@c, Id_category =@d, N_seller =@e, pricebuy =@f,unit =@g WHERE Id_product = @id";
                    ClassConnection.command = new OleDbCommand(SqlUpdate, ClassConnection.connection);
                    ClassConnection.command.Parameters.AddWithValue("b", textBoxName.Text);
                    ClassConnection.command.Parameters.AddWithValue("c", textboxDesc.Text);
                    ClassConnection.command.Parameters.AddWithValue("d", _idcat);
                    ClassConnection.command.Parameters.AddWithValue("e", textboxSeller.Text);
                    ClassConnection.command.Parameters.AddWithValue("f", textBoxPriceBuy.Text);
                    ClassConnection.command.Parameters.AddWithValue("g", textBoxNum.Text);
                    ClassConnection.command.Parameters.AddWithValue("id", textBoxID.Text);
                    int result = ClassConnection.command.ExecuteNonQuery();
                    if (result == -1)
                    {
                        MessageBox.Show("เกิดข้อผิดพลาด ไม่สามารถแก้ไขข้อมูลได้", "ตรวจสอบ");
                        tr.Rollback();
                    }
                    else
                    {
                        MessageBox.Show("แก้ไขข้อมูลแล้ว", "ตรวจสอบ");
                        tr.Commit();
                    }
                }
                catch (System.Exception ex)
                {
                    MessageBox.Show(ex.Message.ToString());
                    tr.Rollback();
                }
                finally
                {
                    ClassConnection.connectionDatabase();
                    tr.Dispose();
                }
            }
            buttonSave.Enabled   = false;
            buttonCancel.Enabled = false;
            buttonAdd.Enabled    = true;
            buttonEdit.Enabled   = true;
            buttonDelete.Enabled = true;
            ClearData();
            ShowData();
            ClassConnection.count = 0;
            EnabledFalse();
        }