Пример #1
0
        private void importBtn_Click(object sender, EventArgs e)
        {
            string strQuery;
            try
            {

                Config con = new Config();
                var conn = con.ConnectSQL();
                using (SqlCommand comm = new SqlCommand())
                {
                    comm.Connection = conn;
                    conn.Open();

                    //make for biar sesuai ama jumlah row yang ada di datatable
                    for (int i = 1; i < dgExcel.Rows.Count; i++)
                    {
                        //bikin variable yang mau dimasukin ke database
                        string a, b, c, d, ee, f, g, h;
                        a = dgExcel.Rows[i].Cells["Event Time"].Value.ToString();
                        a = "'" + a + "'";
                        b = dgExcel.Rows[i].Cells["User Name"].Value.ToString();
                        b = "'" + b + "'";
                        h = dgExcel.Rows[i].Cells["Function Key"].Value.ToString();
                        h = "'" + h + "'";

                        strQuery = @"INSERT INTO Employees.Attendance values ("
                                   + a + ", "
                                   + b + ", "
                                   + h + ");";
                        comm.CommandText = strQuery;
                        comm.ExecuteNonQuery();
                    }
                }

            }
            catch (Exception)
            {
                //SIAL WKWKWKWKW
                MessageBox.Show("SUCCESS!");
                //throw;
            }
        }
Пример #2
0
        private void importBtn_Click(object sender, EventArgs e)
        {
            try
            {
                DialogResult dialogResult = MessageBox.Show("IMPORT DATA? \nMAKE SURE ALL THE FORM FIELDS FILLED CORRECTLY", "DATA ENTRY", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
                if (dialogResult == DialogResult.Yes)
                {
                    if (tbName.Text == "" || tbPath.Text == "" || dgExcel.Rows.Count == 0)
                    {
                        MessageBox.Show("FORM FIELD(S) CAN NOT BE LEFT EMPTY. PLEASE COMPLETE THE FORM TO IMPORT DATA", "DATA ENTRY ERROR", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                        tbName.Focus();
                    }
                    else
                    {
                        Config con = new Config();
                        SqlConnection SQLcon = con.ConnectSQL();
                        using (SqlCommand SQLcom = new SqlCommand())
                        {
                            SQLcom.Connection = SQLcon;
                            SQLcon.Open();

                            for (int i = 1; i <= dgExcel.Rows.Count - 2; i++)
                            {
                                string CompleteDateTime;
                                string year, month, date, FinalDate;
                                string hh, mm, ss, FinalTime;

                                string FuncKey, EmpNIP, AttDateTime;
                                CompleteDateTime = dgExcel.Rows[i].Cells["Event Time"].Value.ToString();

                                date = CompleteDateTime.Substring(0, 2);
                                month = CompleteDateTime.Substring(3, 2);
                                year = CompleteDateTime.Substring(6, 4);
                                FinalDate = year + "-" + month + "-" + date;

                                hh = CompleteDateTime.Substring(11, 2);
                                mm = CompleteDateTime.Substring(14, 2);
                                ss = CompleteDateTime.Substring(17, 2);
                                FinalTime = hh + ":" + mm + ":" + ss;

                                AttDateTime = "'" + FinalDate + " " + FinalTime + "'";

                                FuncKey = dgExcel.Rows[i].Cells["Function Key"].Value.ToString();

                                EmpNIP = dgExcel.Rows[i].Cells["NIP"].Value.ToString();
                                EmpNIP = "'" + EmpNIP + "'";

                                string SuperQuery;
                                string QueryCheck = "SELECT TOP 1 AttendanceDate, EmployeeNIP FROM Employees.Attendance WHERE AttendanceDate = " + "'" + FinalDate + "'" + " AND EmployeeNIP = " + EmpNIP + "";
                                SqlCommand sqlcom = new SqlCommand(QueryCheck, SQLcon);
                                SqlDataReader dr = sqlcom.ExecuteReader();
                                if (dr.HasRows)
                                {
                                    SQLcon.Close();
                                    SQLcon.Open();
                                    if (FuncKey == "F1")
                                    {
                                        SuperQuery = "UPDATE Employees.Attendance SET AttendanceIn=" + AttDateTime + " WHERE AttendanceDate=" + "'" + FinalDate + "'" + " AND EmployeeNIP=" + EmpNIP + "";
                                    }
                                    else
                                    {
                                        SuperQuery = "UPDATE Employees.Attendance SET AttendanceOut=" + AttDateTime + " WHERE AttendanceDate=" + "'" + FinalDate + "'" + " AND EmployeeNIP=" + EmpNIP + "";
                                    }

                                    SqlCommand SQLcomm = new SqlCommand(SuperQuery, SQLcon);
                                    SQLcomm.ExecuteNonQuery();
                                }
                                else
                                {
                                    SQLcon.Close();
                                    SQLcon.Open();
                                    if (FuncKey == "F1")
                                    {
                                        SuperQuery = "INSERT INTO Employees.Attendance(AttendanceDate, AttendanceIn, EmployeeNIP) VALUES (" + "'" + FinalDate + "'" + ", " + AttDateTime + ", " + EmpNIP + ")";
                                    }
                                    else
                                    {
                                        SuperQuery = "INSERT INTO Employees.Attendance(AttendanceDate, AttendanceOut, EmployeeNIP) VALUES (" + "'" + FinalDate + "'" + ", " + AttDateTime + ", " + EmpNIP + ")";
                                    }

                                    SqlCommand sqlComm = new SqlCommand(SuperQuery, SQLcon);
                                    sqlComm.ExecuteNonQuery();
                                }
                            }
                            SQLcon.Close();

                            InsertStatus();
                        }
                        MessageBox.Show("SUCCESSFULLY IMPORTED", "DATA SAVED TO DATABASE", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        tbName.Clear();
                        tbPath.Clear();
                    }
                }
            }
            catch (SqlException ed)
            {
                MessageBox.Show(ed.ToString(), "ERROR OCCURS", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            catch (InvalidOperationException)
            {
                MessageBox.Show("INVALID OPERATION!", "ERROR OCCURS", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString(), "ERROR OCCURS", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }