private void deleteItem() { try { openConnection(); command = new SqlCommand("Select * From VoucherLog where VoucherItemID=" + ID.ToString(), conn); reader = command.ExecuteReader(); //int found = command.ExecuteNonQuery(); //found > 0 if (reader.HasRows) { reader.Close(); command = new SqlCommand("Update VoucherItems set Inactive = 1 where UID = " + ID.ToString(), conn); command.ExecuteNonQuery(); } else { reader.Close(); command = new SqlCommand("Delete From VoucherItems where UID = " + ID.ToString(), conn); command.ExecuteNonQuery(); } closeConnection(); loadList(0); } catch (System.Exception ex) { closeConnection(); CCFBGlobal.appendErrorToErrorReport("Command Text = " + command.CommandText, ex.GetBaseException().ToString(), CCFBGlobal.serverName); } }
public void openWhere(string whereClause) { try { openConnection(); command = new SqlCommand("SELECT * FROM " + tbName + " " + whereClause + " ORDER BY RptGroup, Grouping,[Display Name]", conn); dadAdpt.SelectCommand = command; dset.Clear(); iRowCount = dadAdpt.Fill(dset, tbName); closeConnection(); if (iRowCount > 0) { dRow = dset.Tables[tbName].Rows[0]; return; } dRow = null; } catch (SqlException ex) { closeConnection(); iRowCount = 0; CCFBGlobal.appendErrorToErrorReport("", ex.GetBaseException().ToString(), CCFBGlobal.serverName); } }
public void openForDate(DateTime TrxDate) { try { closeConnection(); command = new SqlCommand("SELECT * FROM " + tbName + " WHERE TrxDate='" + TrxDate.ToString() + "'", conn); dadAdpt.SelectCommand = command; dset.Clear(); iRowCount = dadAdpt.Fill(dset, tbName); closeConnection(); if (iRowCount > 0) { dRow = dset.Tables[tbName].Rows[0]; } isValid = false; } catch (SqlException ex) { CCFBGlobal.appendErrorToErrorReport("Command Text = " + command.CommandText, ex.GetBaseException().ToString(), CCFBGlobal.serverName); iRowCount = 0; closeConnection(); isValid = false; } }
public int[] getVolList(string sql) { int[] volList = new int[1]; try { int rows; openConnection(); volGrpComm = new SqlCommand(sql, conn); volGrpsDadAdapt.SelectCommand = volGrpComm; volGrpsDset.Clear(); rows = volGrpsDadAdapt.Fill(volGrpsDset); volList = new int[rows]; closeConnection(); for (int i = 0; i < rows; i++) { volList[i] = volGrpsDset.Tables[0].Rows[i].Field <int>("GroupID"); } } catch (SqlException ex) { CCFBGlobal.appendErrorToErrorReport("volList=" + sql, ex.GetBaseException().ToString()); closeConnection(); } return(volList); }
public void update(bool bReload) { if (dset.HasChanges() == true) { try { openConnection(); if (dadAdpt.UpdateCommand == null || dadAdpt.InsertCommand == null) { SqlCommandBuilder commBuild = new SqlCommandBuilder(dadAdpt); } dadAdpt.Update(dset, tbName); if (bReload == true) { dset.Clear(); iRowCount = dadAdpt.Fill(dset, tbName); } closeConnection(); } catch (SqlException ex) { CCFBGlobal.appendErrorToErrorReport("", ex.GetBaseException().ToString()); closeConnection(); } } }
public void openWhere(string sWhereClause) { try { swhereclause = sWhereClause; openConnection(); command = new SqlCommand("SELECT * FROM " + tbName + " " + sWhereClause, conn); dadAdpt.SelectCommand = command; dset.Clear(); iRowCount = dadAdpt.Fill(dset, tbName); closeConnection(); isValid = false; if (iRowCount > 0) { drow = dset.Tables[tbName].Rows[0]; } } catch (SqlException ex) { closeConnection(); CCFBGlobal.appendErrorToErrorReport(sWhereClause, ex.GetBaseException().ToString()); isValid = false; iRowCount = 0; } }
/// <summary> /// Gets the ditinct values for any collumn /// </summary> /// <param name="columnName">Collumn name</param> public void getDistincts(string columnName, string whereClause) { try { openConnection(); sqlCmd = new SqlCommand("SELECT " + columnName + ", COUNT(*) FROM " + tblName + whereClause + " Group By " + columnName + " Order By " + columnName, conn); dadAdpt.SelectCommand = sqlCmd; dset.Clear(); dset.Tables.Clear(); iRowCount = dadAdpt.Fill(dset, tblName); closeConnection(); isValid = false; if (iRowCount > 0) { drow = dset.Tables[tblName].Rows[0]; } } catch (SqlException ex) { closeConnection(); CCFBGlobal.appendErrorToErrorReport("columnName=" + columnName, ex.GetBaseException().ToString()); iRowCount = 0; } }
public bool open(string toFind, string collumnName) { try { openConnection(); command = new SqlCommand("SELECT * FROM " + tbName + " WHERE " + collumnName + "='" + toFind + "' " + "AND State='" + CCFBPrefs.DefaultState + "'", conn); dadAdpt.SelectCommand = command; dset.Clear(); iRowCount = dadAdpt.Fill(dset, tbName); closeConnection(); if (iRowCount > 0) { drow = dset.Tables[tbName].Rows[0]; return(isValid = true); } return(isValid = false); } catch (SqlException ex) { closeConnection(); CCFBGlobal.appendErrorToErrorReport("", ex.GetBaseException().ToString(), CCFBGlobal.serverName); return(isValid = false); } }
private void loadDescriptions() { try { conn.Open(); command = new SqlCommand("Select * From VoucherItems Order By Description ASC", conn); reader = command.ExecuteReader(); rowIndex = 0; if (reader.HasRows) { while (reader.Read()) { gridVouchers.Rows.Add(); gridVouchers["clmDesc", rowIndex].Tag = reader.GetValue(0); gridVouchers["clmDesc", rowIndex].Value = reader.GetValue(1); gridVouchers["clmAmount", rowIndex].Value = 0; gridVouchers["clmComments", rowIndex].Value = ""; rowIndex++; } } conn.Close(); } catch (SqlException ex) { CCFBGlobal.appendErrorToErrorReport("Command Text= " + command.CommandText, ex.GetBaseException().ToString(), CCFBGlobal.serverName); } }
public bool update() { if (dset.HasChanges() == true) { try { openConnection(); if (dadAdpt.UpdateCommand == null) { //Sets the Commands in the DataAdapter SqlCommandBuilder commBuilder = new SqlCommandBuilder(dadAdpt); } drow["ModifiedBy"] = CCFBGlobal.dbUserName; drow["Modified"] = DateTime.Now; dadAdpt.Update(dset, "HouseholdMembers"); closeConnection(); return(true); } catch (SqlException ex) { CCFBGlobal.appendErrorToErrorReport("", ex.GetBaseException().ToString()); return(false); } } return(false); }
public void openWhere(string sWhereClause = "") { try { openConnection(); if (sWhereClause == "") { command = new SqlCommand("SELECT * FROM " + tbName, conn); } else { command = new SqlCommand("SELECT * FROM " + tbName + " WHERE " + sWhereClause, conn); } dadAdpt.SelectCommand = command; dset.Clear(); iRowCount = dadAdpt.Fill(dset, tbName); closeConnection(); if (iRowCount > 0) { drow = dset.Tables[tbName].Rows[0]; } isValid = false; } catch (SqlException ex) { closeConnection(); CCFBGlobal.appendErrorToErrorReport("", ex.GetBaseException().ToString(), CCFBGlobal.serverName); iRowCount = 0; isValid = false; } }
private void sqlErrorOccured(string commandText, string errorText) { closeConnection(); CCFBGlobal.appendErrorToErrorReport(commandText, errorText); drow = null; iRowCount = 0; }
public void openForWeekDay(int weekday) { string sql = "SELECT jp.*, vp.Name PrimaryName, vb.Name BackupName " + " FROM FBJobsPlan jp " + " LEFT JOIN Volunteers vp ON jp.VolIDPrimary = vp.ID " + " LEFT JOIN Volunteers vb ON jp.VolIDBackup = vb.ID " + " WHERE jp.WeekDay =" + weekday.ToString() + " ORDER BY jp.JobTitle, jp.ShiftStart"; try { command = new SqlCommand(sql, conn); if (dadAdpt == null) { dadAdpt = new SqlDataAdapter(command); } else { dadAdpt.SelectCommand = command; } dset.Clear(); iRowCount = dadAdpt.Fill(dset, tblName); if (iRowCount > 0) { drow = dset.Tables[tblName].Rows[0]; } } catch (SqlException ex) { CCFBGlobal.appendErrorToErrorReport("sql=" + sql, ex.GetBaseException().ToString()); closeConnection(); iRowCount = 0; } }
public void open(System.Int32 key) { try { openConnection(); command = new SqlCommand("SELECT * FROM " + tbName + " WHERE TrxID=" + key.ToString(), conn); dadAdpt.SelectCommand = command; dset.Clear(); iRowCount = dadAdpt.Fill(dset, tbName); closeConnection(); if (iRowCount > 0) { dRow = dset.Tables[tbName].Rows[0]; } isValid = false; } catch (SqlException ex) { CCFBGlobal.appendErrorToErrorReport("Command Text = " + command.CommandText, ex.GetBaseException().ToString()); iRowCount = 0; closeConnection(); isValid = false; } }
private void insertIntoLog() { try { //conn.Open(); //command = new SqlCommand("Insert Into VoucherLog (TrxDate, HouseholdID, Amount, Notes, VoucherItemID," // + "Infants, Youth, Teens, Eighteen, Adults, Seniors, TotalFamily, Disabled, Homeless, Transient, Created, CreatedBy" // + ", FiscalFirstTime, CalFirstTime, MonthFirstTime) " // + "Values('" + dtpTrxDate.Value.ToShortDateString() + "', " + curHHID.ToString() + ", " // + amount.ToString() + ", '" + comments + "', " + gridVouchers["clmDesc", rowIndex].Tag.ToString() // + ", " + tbInfants.Text + ", " + tbYouth.Text + ", " + tbTeens.Text + ", " + "0," + tbAdults.Text + ", " // + tbSeniors.Text + ", " + tbTotalFam.Text + ", " + tbDisabled.Text // + ", " + Convert.ToInt32( chkHomeless.Checked).ToString() + "," + cboClientType.SelectedValue // + ",'" + DateTime.Now.ToString() + "','" + CCFBGlobal.dbUserName + "',0,0,0)" // , conn); //command.ExecuteNonQuery(); //conn.Close(); //loadVItmsList(); } catch (System.Exception ex) { CCFBGlobal.appendErrorToErrorReport("Command Text = " + command.CommandText, ex.GetBaseException().ToString()); conn.Close(); } }
public void getFavorites() { try { openConnection(); string sql = "SELECT TOP 21 fd.DonorID,d.Name,sum(Pounds)" + " FROM FoodDonations fd INNER JOIN Donors d ON fd.DonorID=d.ID" + " WHERE TrxDate > DateAdd(yy,-1,GetDate()) " + " GROUP BY fd.DonorID,d.Name " + "HAVING sum(pounds) > 500" + "ORDER BY Sum(Pounds) DESC"; command = new SqlCommand(sql, conn); sqldadAdpt = new SqlDataAdapter(command); //sqldadAdpt.SelectCommand = command; dset = new DataSet(); iRowCount = sqldadAdpt.Fill(dset, tbName); if (iRowCount > 0) { dRow = dset.Tables[tbName].Rows[0]; } } catch (SqlException ex) { CCFBGlobal.appendErrorToErrorReport("GetFavorites", ex.GetBaseException().ToString()); iRowCount = 0; } closeConnection(); }
public void update() { if (dset.HasChanges() == true) { try { openConnection(); if (dadAdpt.UpdateCommand == null) { SqlCommandBuilder commands = new SqlCommandBuilder(dadAdpt); } drow["Modified"] = DateTime.Now.ToString(); drow["ModifiedBy"] = CCFBGlobal.dbUserName; dadAdpt.Update(dset, "EmailRecipients"); closeConnection(); } catch (SqlException ex) { CCFBGlobal.appendErrorToErrorReport("", ex.GetBaseException().ToString()); closeConnection(); } } }
public bool open(int TrxID) { try { openConnection(); command = new SqlCommand("SELECT * FROM " + tbName + " WHERE TrxID=" + TrxID.ToString(), conn); dadAdpt = new SqlDataAdapter(command); dadAdpt.SelectCommand = command; dset = new DataSet(); iRowCount = dadAdpt.Fill(dset, tbName); closeConnection(); if (iRowCount > 0) { dRow = dset.Tables[tbName].Rows[0]; return(isValid = true); } return(isValid = false); } catch (SqlException ex) { CCFBGlobal.appendErrorToErrorReport("TrxID=" + TrxID.ToString(), ex.GetBaseException().ToString()); iRowCount = 0; closeConnection(); return(isValid = false); } }
/// <summary> /// Runs the query. /// </summary> /// <param name="connectionString"></param> /// <param name="sqlSelectQuery"></param> /// <returns>DataTable containing the result</returns> public virtual DataTable sqlSelectQuery(string connectionString, string sqlQuery) { try { SqlConnection databaseConnection = new SqlConnection(connectionString); databaseConnection.Open(); SqlCommand dataCommand = new SqlCommand(sqlQuery, databaseConnection); DataTable dTable = new DataTable(); SqlDataAdapter dadAdpt = new SqlDataAdapter(); dadAdpt.SelectCommand = dataCommand; dadAdpt.Fill(dTable); databaseConnection.Close(); return(dTable); } catch (Exception ex) { string param = "Con str: " + connectionString + " Sql Query: " + sqlQuery; CCFBGlobal.appendErrorToErrorReport(param, ex.ToString()); return(new DataTable()); } }
public void openForDate(int selectfield, DateTime TrxDate) { try { openConnection(); string queryText = "SELECT fd.*, d.Name DonorName FROM " + tbName + " fd LEFT JOIN Donors d ON fd.DonorID = d.ID" + " WHERE Convert(varchar(10), fd." + selectFieldName(selectfield) + ",101)" + " = '" + CCFBGlobal.formatDate(TrxDate) + "' ORDER BY TrxDate, DonorName, fd.Created"; command = new SqlCommand(queryText, conn); sqldadAdpt = new SqlDataAdapter(command); //sqldadAdpt.SelectCommand = command; dset = new DataSet(); iRowCount = sqldadAdpt.Fill(dset, tbName); closeConnection(); if (iRowCount > 0) { dRow = dset.Tables[tbName].Rows[0]; } isValid = false; } catch (SqlException ex) { CCFBGlobal.appendErrorToErrorReport("TrxID=" + TrxDate.ToString(), ex.GetBaseException().ToString()); iRowCount = 0; closeConnection(); isValid = false; } }
public void openWithGroupIdWhere(string whereClause, string grouplist) { try { if (grouplist == "") { sqlCmd = new SqlCommand("SELECT * FROM " + tblName + whereClause + " Order By [Name]", conn); } else { sqlCmd = new SqlCommand("SELECT * FROM " + tblName + " v " + whereClause + " AND EXISTS(SELECT * FROM VolunteerGroups vg WHERE GroupID IN (" + grouplist + ") AND vg.Volid = v.Id)" + " Order By [Name]", conn); } dadAdpt.SelectCommand = sqlCmd; dset.Clear(); iRowCount = dadAdpt.Fill(dset, tblName); if (iRowCount > 0) { drow = dset.Tables[tblName].Rows[0]; } } catch (SqlException ex) { CCFBGlobal.appendErrorToErrorReport("whereClause=" + whereClause, ex.GetBaseException().ToString()); closeConnection(); iRowCount = 0; } }
public void openDistinctDonationDates(int selectfield) { string fldname = selectFieldName(selectfield); try { openConnection(); command = new SqlCommand("SELECT Convert(varchar(10)," + fldname + ",101), Convert(varchar(10)," + fldname + ",111) FROM " + tbName + " Group By Convert(varchar(10)," + fldname + ",101), Convert(varchar(10)," + fldname + ",111)" + " Order By Convert(varchar(10)," + fldname + ",111) DESC", conn); sqldadAdpt = new SqlDataAdapter(command); sqldadAdpt.SelectCommand = command; dset = new DataSet(); iRowCount = sqldadAdpt.Fill(dset, tbName); if (iRowCount > 0) { dRow = dset.Tables[tbName].Rows[0]; } } catch (SqlException ex) { CCFBGlobal.appendErrorToErrorReport("", ex.GetBaseException().ToString()); iRowCount = 0; } catch (Exception e) { iRowCount = 0; } closeConnection(); }
public bool openWhere(String WhereClause) { try { openConnection(); command = new SqlCommand("SELECT * FROM " + tbName); if (WhereClause != "") { command.CommandText += " WHERE " + WhereClause; } command.CommandText += " ORDER BY ItemType, ItemDesc"; dadAdpt = new SqlDataAdapter(command); dadAdpt.SelectCommand = command; dset.Clear(); dadAdpt.SelectCommand.Connection = conn; iRowCount = dadAdpt.Fill(dset, tbName); closeConnection(); if (iRowCount > 0) { drow = dset.Tables[tbName].Rows[0]; } return(false); } catch (SqlException ex) { CCFBGlobal.appendErrorToErrorReport("", ex.GetBaseException().ToString()); closeConnection(); return(isValid = false); } }
public void openDonationForMonth(string sDonorId, string sDonationType, string sBetween) { try { closeConnection(); command = new SqlCommand("SELECT *, DATEPART(ww,TrxDate) WeekOfYear,DATEPART(dw,TrxDate) DayOfWeek FROM " + tbName + " WHERE DonationType = " + sDonationType + " AND DonorId = " + sDonorId + " AND TrxDate Between " + sBetween + " ORDER BY WeekOfYear, DayOfWeek,FoodClass", conn); sqldadAdpt = new SqlDataAdapter(command); sqldadAdpt.SelectCommand = command; dset = new DataSet(); iRowCount = sqldadAdpt.Fill(dset, tbName); if (iRowCount > 0) { dRow = dset.Tables[tbName].Rows[0]; } closeConnection(); } catch (SqlException ex) { CCFBGlobal.appendErrorToErrorReport("", ex.GetBaseException().ToString()); closeConnection(); iRowCount = 0; } }
public bool open(System.Int32 key) { try { openConnection(); command = new SqlCommand("SELECT * FROM " + tbName + " WHERE ID=" + key.ToString(), conn); dadAdpt.SelectCommand = command; dset.Clear(); iRowCount = dadAdpt.Fill(dset, tbName); closeConnection(); if (iRowCount > 0) { dRow = dset.Tables[0].Rows[0]; return(isValid = true); } return(isValid = false); } catch (SqlException ex) { CCFBGlobal.appendErrorToErrorReport("key=" + ID.ToString(), ex.GetBaseException().ToString(), CCFBGlobal.serverName); closeConnection(); iRowCount = 0; return(isValid = false); } }
public bool update() { if (dset.HasChanges() == true) { try { openConnection(); if (dadAdpt.UpdateCommand == null) { SqlCommandBuilder commBuilder = new SqlCommandBuilder(dadAdpt); } dadAdpt.Update(dset, tbName); closeConnection(); return(true); } catch (SqlException ex) { CCFBGlobal.appendErrorToErrorReport("", ex.GetBaseException().ToString()); return(false); } } return(false); }
public void openWhere(string whereClause) { try { openConnection(); command = new SqlCommand("SELECT * FROM " + tbName + whereClause + " Order By TrxDate ASC, DonorID", conn); dadAdpt.SelectCommand = command; dset.Clear(); iRowCount = dadAdpt.Fill(dset, tbName); closeConnection(); if (iRowCount > 0) { dRow = dset.Tables[tbName].Rows[0]; } isValid = false; } catch (SqlException ex) { CCFBGlobal.appendErrorToErrorReport("Command Text = " + command.CommandText, ex.GetBaseException().ToString(), CCFBGlobal.serverName); iRowCount = 0; closeConnection(); isValid = false; } }
public bool insertMember() { try { if (datAdptHhM.InsertCommand == null) { SqlCommandBuilder commBuild = new SqlCommandBuilder(datAdptHhM); datAdptHhM.MissingSchemaAction = MissingSchemaAction.AddWithKey; } if (datAdptDemo.InsertCommand == null) { SqlCommandBuilder commBldrDemog = new SqlCommandBuilder(datAdptDemo); datAdptDemo.MissingSchemaAction = MissingSchemaAction.AddWithKey; } openConnection(); datAdptHhM.Update(dset, tblHhMName); datAdptDemo.Update(dset, tblDemoName); dset.Clear(); datAdptHhM.Fill(dset, tblHhMName); datAdptDemo.Fill(dset, tblDemoName); closeConnection(); return(true); } catch (SqlException ex) { CCFBGlobal.appendErrorToErrorReport("", ex.GetBaseException().ToString(), CCFBGlobal.serverName); closeConnection(); return(false); } }
public int LoadDateList(int ApptmntStatus) { apptStatus = ApptmntStatus; string StatusList = ""; SqlCommand command; SqlDataAdapter dataAdpt; rowCount = 0; if (apptStatus == 0) { StatusList = "0"; } else { StatusList = "1,2"; } try { command = new SqlCommand("SELECT DISTINCT TrxDate, Count(*) FROM TrxLog WHERE TrxStatus IN (" + StatusList + ") GROUP BY TrxDate ORDER BY TrxDate"); dataAdpt = new SqlDataAdapter(command); dataAdpt.SelectCommand.Connection = conn; dSet.Clear(); rowCount = dataAdpt.Fill(dSet, tbName); CurRowNumber = 0; return(rowCount); } catch (SqlException ex) { CCFBGlobal.appendErrorToErrorReport("", ex.GetBaseException().ToString(), CCFBGlobal.serverName); return(rowCount); } }
/// <summary> /// Updates the hosuehold in the database with any changes made to the DataSet /// </summary> public void update(bool changeModified) { if (dset.HasChanges() == true) { try { if (drow.RowState != DataRowState.Unchanged && changeModified == true) { drow["ModifiedBy"] = CCFBGlobal.dbUserName; drow["Modified"] = DateTime.Now; } if (dadAdpt.UpdateCommand == null) { SqlCommandBuilder commBuild = new SqlCommandBuilder(dadAdpt); } openConnection(); dadAdpt.Update(dset, "Household"); closeConnection(); } catch (SqlException ex) { closeConnection(); CCFBGlobal.appendErrorToErrorReport("", ex.GetBaseException().ToString()); } } }