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;
}
}
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);
}
}