private void _getOnlineRecords() { string strQuery = string.Empty; strQuery = "select recrno ,Surname, Othernames,Initials from recruittab where used = 'false'"; datamethods2 dmth = new datamethods2(); DataTable dt = new DataTable(); dt = dmth._dataTable_NoParameter_webdb(strQuery); if (dmth.sqlUserError == String.Empty) { if (dt.Rows.Count > 0) { dgvSource.Rows.Clear(); for (int x = 0; x < dt.Rows.Count; x++) { int n = dgvSource.Rows.Add(); dgvSource.Rows[n].Cells[0].Value = dt.Rows[x]["recrno"].ToString().Trim(); dgvSource.Rows[n].Cells[1].Value = dt.Rows[x]["surname"].ToString().Trim(); dgvSource.Rows[n].Cells[2].Value = dt.Rows[x]["othernames"].ToString().Trim(); dgvSource.Rows[n].Cells[3].Value = dt.Rows[x]["initials"].ToString().Trim(); } } else { MessageBox.Show("Records not available! ","RnS Notification"); } } else { MessageBox.Show(dmth.sqlUserError,"RnS Notification"); } }
private void saveRecords() { if (dgvDestination.Rows.Count == 0 ) { MessageBox.Show("Move records into destination table", "RnS Notification"); return; } lblprocessing.Text = "Processing pls wait..."; string strRecrno = String.Empty; string sqlInsertwhere = string.Empty; List<string> lstRecrno = new List<string>(); int _totalSaved = 0; // get all the recrno for (int x = 0; x < dgvDestination.Rows.Count; x++) { if (dgvDestination.Rows[x].Cells[0].Value.ToString() != null && dgvDestination.Rows[x].Cells[0].Value.ToString().Trim() != string.Empty) { if (Convert.ToBoolean(dgvDestination.Rows[x].Cells[4].Value) == false) lstRecrno.Add(dgvDestination.Rows[x].Cells[0].Value.ToString().Trim()); } } if (lstRecrno.Count > 0) { sqlInsertwhere = "where recrno = '" + lstRecrno[0] + "'"; if (lstRecrno.Count - 1 > 0) { for (int x = 1; x < lstRecrno.Count; x++) { sqlInsertwhere += " or recrno = '" + lstRecrno[x] + "'"; } } } if (sqlInsertwhere.Trim() == string.Empty) // do insert { MessageBox.Show("There are no new records to save", "RnS Notification"); lblprocessing.Visible = false; return; } // save the records datamethods2 dthm = new datamethods2(); DataTable dt = new DataTable(); string sqlQueury = "Select * from recruittab "; sqlQueury += sqlInsertwhere; dt = dthm._dataTable_NoParameter_webdb(sqlQueury); if (dthm.sqlUserError == string.Empty) { if (dt.Rows.Count > 0) { _totalSaved = 0; string strLgaDescr = String.Empty; for (int x = 0; x < dt.Rows.Count; x++) { lblprocessing.Visible = true; lblprocessing.Text = "Processing Record " + (x + 1).ToString() + " of " + dt.Rows.Count.ToString(); string strSex = "Male"; if (dt.Rows[x]["sex"].ToString() == "2") { strSex = "Female"; } strLgaDescr = ""; if (dt.Rows[x]["lga"].ToString().Trim() != string.Empty ) { string sqlgetDescr = string.Empty; sqlgetDescr = "select desc1 from codestab where option1='LGACODE' and code = '" + dt.Rows[x]["lga"].ToString() + "'"; SqlDataReader dr = dthm._dataReader_NoParameter(sqlgetDescr, ConfigurationManager.ConnectionStrings["rnsConString_rims"].ConnectionString); if (dthm.sqlUserError == string.Empty) { dr.Read(); if (dr.HasRows) strLgaDescr = dr["desc1"].ToString(); } dr.Dispose(); dr.Close(); } string sqlInsert = string.Empty; sqlInsert = @" Insert into database1 " + "(recrno,surname,firstname,middlename,initials,address, " + " title,sex,age,phone,email,lgacode,lgadescr,birthdate,gradecode,gradedescr,qualcode,qualdescr,testresult,screened,aptest,active)" + " values ('" + dt.Rows[x]["recrno"].ToString() + "'," + "'" + dt.Rows[x]["surname"].ToString() + "'," + "'" + dt.Rows[x]["othernames"].ToString() + "'," + "'" + dt.Rows[x]["initials"].ToString() + "'," + "'" + " " + "'," + "'" + dt.Rows[x]["address"].ToString() + "'," + "'" + dt.Rows[x]["title"].ToString() + "'," + "'" + strSex + "'," + "'" + (DateTime.Now.Year - Convert.ToDateTime(dt.Rows[x]["birthdate"]).Year).ToString() + "'," + "'" + dt.Rows[x]["phone"].ToString() + "'," + "'" + dt.Rows[x]["email"].ToString() + "'," + "'" + dt.Rows[x]["lga"].ToString() + "'," + "'" + strLgaDescr + "'," + "'" + dt.Rows[x]["birthdate"].ToString() + "'," + "'" + " " + "'," + "'" + " " + "'," + "'" + " " + "'," + "'" + " " + "'," + "'" + " " + "'," + "'" + false + "'," + "'" + false + "'," + "'" + false + "')"; dthm._update_Insert_NoParameter(sqlInsert); if (dthm.sqlUserError.Trim() != string.Empty) MessageBox.Show(dthm.sqlUserError, "RnS Notification"); else { _totalSaved += 1; // update the webdb recruittaab string sqlUpdate = string.Empty; sqlUpdate = @" update recruittab set used = 'true'" + " where recrno = '" + dt.Rows[x]["recrno"].ToString() + "'"; dthm._update_Insert_NoParameter(sqlUpdate, ConfigurationManager.ConnectionStrings["rnsConString_webdb"].ConnectionString); try { dgvDestination.Rows[x].Cells[4].Value = true; } catch { } } } } else { MessageBox.Show("There are no records to processed", "RnS Notification"); lblprocessing.Visible = false; } } else MessageBox.Show("dthm.sqlUserError","RnS Notification"); if (_totalSaved > 0) MessageBox.Show(_totalSaved.ToString() + " records was committed Successfully", "RnS Notification"); lblprocessing.Visible = false; }