Solved

Need help updating DataAdapter (ASP.NET C#)

Posted on 2003-11-14
16
435 Views
Last Modified: 2008-02-01
I've got the following code, but I can't get my datagrid to update.  Does anyone know why this might be?  Thanks.




            string strSQL = "SELECT sc_id, property_number, resident_name, move_out_date, move_in_date, new_resident, hdct, id_code, credits, debits, debit_description, misc_charge, misc_description FROM Property_Changes WHERE property_number = '" + cookPN + "'";
            objAdapter = new SqlDataAdapter(strSQL, myConnection);
            objBuilder = new SqlCommandBuilder(objAdapter);
            objAdapter.UpdateCommand = objBuilder.GetUpdateCommand();
            objAdapter.Fill(objDataSet, "Property_Changes");

            DataTable objTable = null;
            
            objTable = objDataSet.Tables["Property_Changes"];
            DataRow objRow = null;

            foreach (DataGridItem di in dgStatus_Changes.Items)
            {
                  // Make sure this is an item and not the header or footer.
                  if (di.ItemType == ListItemType.Item || di.ItemType == ListItemType.AlternatingItem)
                  {
                        objRow = objTable.Rows[di.ItemIndex];
               
                        if(((TextBox)di.FindControl("txtMODate")).Text != "")
                        {
                              objRow["move_out_date"] = ((TextBox)di.FindControl("txtMODate")).Text;
                        }
                        else
                        {
                              objRow["move_out_date"] = DBNull.Value;
                        }

                        if(((TextBox)di.FindControl("txtMIDate")).Text != "")
                        {
                              objRow["move_in_date"] = ((TextBox)di.FindControl("txtMIDate")).Text;
                        }
                        else
                        {
                              objRow["move_in_date"] = DBNull.Value;
                        }
                        
                        objRow["new_resident"] = ((TextBox)di.FindControl("txtNR")).Text;
                        
                        if(((TextBox)di.FindControl("txtHdct")).Text != "")
                        {
                              objRow["hdct"] = ((TextBox)di.FindControl("txtHdct")).Text;
                        }

                        objRow["id_code"] = ((TextBox)di.FindControl("txtID")).Text;
                        
                        if(((TextBox)di.FindControl("txtCred")).Text != "")
                        {
                              objRow["credits"] = ((TextBox)di.FindControl("txtCred")).Text;
                        }


                        if(((TextBox)di.FindControl("txtDeb")).Text != "")
                        {
                              objRow["debits"] = ((TextBox)di.FindControl("txtDeb")).Text;
                        }
                        objRow["debit_description"] = ((TextBox)di.FindControl("txtDD")).Text;

                        if(((TextBox)di.FindControl("txtMC")).Text != "")
                        {
                              objRow["misc_charge"] = ((TextBox)di.FindControl("txtMC")).Text;
                        }
                        objRow["misc_description"] = ((TextBox)di.FindControl("txtMD")).Text;
                  }
            }

            lblUpdated.Text = "Your changes have been submitted";
            objAdapter.Update(objDataSet, "Property_Changes");

      }
0
Comment
Question by:alsharpton
  • 7
  • 7
16 Comments
 
LVL 29

Expert Comment

by:David H.H.Lee
ID: 9757930
alsharpton,
If you prefer your code, you're missing :
objAdapter.RefreshSchema();
eg:

..
 objAdapter.UpdateCommand = objBuilder.GetUpdateCommand();
 objAdapter.RefreshSchema();      
 objAdapter.Fill(objDataSet, "Property_Changes");
...

However, i had a more easy way to solve the update problems in C# environment :

objAdapter.SelectCommand.CommandText =strSQL;
objBuilder.RefreshSchema();

objDataSet.Tables.Remove(objDataSet.Tables["Property_Changes"]);
objAdapter.Fill(objDataSet, "Property_Changes");

//update code
DataTable objTable = null;
         
         objTable = objDataSet.Tables["Property_Changes"];
         DataRow objRow = null;

         foreach (DataGridItem di in dgStatus_Changes.Items)
         {
              // Make sure this is an item and not the header or footer.
              if (di.ItemType == ListItemType.Item || di.ItemType == ListItemType.AlternatingItem)
              {
                   objRow = objTable.Rows[di.ItemIndex];
               
                   if(((TextBox)di.FindControl("txtMODate")).Text != "")
                   {
                        objRow["move_out_date"] = ((TextBox)di.FindControl("txtMODate")).Text;
                   }
                   else
                   {
                        objRow["move_out_date"] = DBNull.Value;
                   }

                   if(((TextBox)di.FindControl("txtMIDate")).Text != "")
                   {
                        objRow["move_in_date"] = ((TextBox)di.FindControl("txtMIDate")).Text;
                   }
                   else
                   {
                        objRow["move_in_date"] = DBNull.Value;
                   }
                   
                   objRow["new_resident"] = ((TextBox)di.FindControl("txtNR")).Text;
                   
                   if(((TextBox)di.FindControl("txtHdct")).Text != "")
                   {
                        objRow["hdct"] = ((TextBox)di.FindControl("txtHdct")).Text;
                   }

                   objRow["id_code"] = ((TextBox)di.FindControl("txtID")).Text;
                   
                   if(((TextBox)di.FindControl("txtCred")).Text != "")
                   {
                        objRow["credits"] = ((TextBox)di.FindControl("txtCred")).Text;
                   }


                   if(((TextBox)di.FindControl("txtDeb")).Text != "")
                   {
                        objRow["debits"] = ((TextBox)di.FindControl("txtDeb")).Text;
                   }
                   objRow["debit_description"] = ((TextBox)di.FindControl("txtDD")).Text;

                   if(((TextBox)di.FindControl("txtMC")).Text != "")
                   {
                        objRow["misc_charge"] = ((TextBox)di.FindControl("txtMC")).Text;
                   }
                   objRow["misc_description"] = ((TextBox)di.FindControl("txtMD")).Text;
              }
         }


lblUpdated.Text = "Your changes have been submitted";
objAdapter.Update(objDataSet, "Customers");


Regards
x_com
0
 

Author Comment

by:alsharpton
ID: 9764631
Thanks for your reply.  It didn't like the line:
objDataSet.Tables.Remove(objDataSet.Tables["Property_Changes"]);

Throws the error:
'table' argument cannot be null. Parameter name: table

Also it does not update when I comment the above line out.  Any thoughts???  Thanks.
0
 
LVL 29

Expert Comment

by:David H.H.Lee
ID: 9764713
alsharpton,
i think your problems is not rebind() the datagrid after you made the changes. Just like you had done the changes, but forget to redirect to same page in order to get it updates. If still not solve your problems, post your full source code here, this will make me more easy to figure out your problems.

x_com
0
 

Author Comment

by:alsharpton
ID: 9764928
Thanks again.  I can't seem to get it to work...



using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Web.Security;
using System.Configuration;
using System.IO;


public class S_CCB : Page
{
      protected System.Web.UI.WebControls.DataGrid dgStatus_Changes;
      protected System.Web.UI.WebControls.Label lblUpdated;
      protected System.Web.UI.WebControls.Label lblDates;
      protected System.Web.UI.WebControls.Label lblDates2;
      protected System.Web.UI.WebControls.Label lblPN;
      protected System.Web.UI.WebControls.Label lblProp;
      protected System.Web.UI.WebControls.Label lblDisclaimer;
      protected System.Web.UI.WebControls.TextBox txtDate;
      protected System.Web.UI.WebControls.DropDownList txtPN;
      protected System.Web.UI.WebControls.TextBox txtSearch;
      protected System.Web.UI.WebControls.Label lblLastUpdate;
      protected System.Web.UI.WebControls.Button btnSubmit2PG;
      protected System.Web.UI.WebControls.Button btnSubmit2PG2;

      DataSet objDataSet_MO = new DataSet();
      
      public void Page_Load(object sender, System.EventArgs e)
      {
            if(Request.Cookies["PN2"] != null)
            {
                  Response.Cookies["PN"].Value = Request.Cookies["PN2"].Value;
            }

            string cookPN = "";
            string strAccess = "";

            String strTConnection = ConfigurationSettings.AppSettings["Tracker"];
            SqlConnection myTConnection = new SqlConnection(strTConnection);

            if(Request.QueryString["pid"] != null)
            {
                  //      Response.Write(Request.QueryString["pid"]);
                  strAccess = Request.QueryString["rid"];
                  cookPN = Request.QueryString["pid"];

                  //            Response.Write("<BR>aaa" + cookPN);
            
                  myTConnection.Open();

                  string strProp = "SELECT PropName From Props WHERE PROP_ID = " + cookPN;
                  SqlCommand objCommandProp = new SqlCommand(strProp, myTConnection);
                  SqlDataReader objReaderProp = objCommandProp.ExecuteReader();

                  while (objReaderProp.Read() == true)
                  {
                        lblProp.Text = objReaderProp["PropName"].ToString();
                  }

                  myTConnection.Close();

            }
            else
            {
                  if(Request.Cookies["PN"] != null && Request.Cookies["Access"] != null)
                  {
                        cookPN = Request.Cookies["PN"].Value;
                        strAccess = Request.Cookies["Access"].Value;
                  }
                  else
                  {
                        Response.Redirect("login.aspx");
                  }
            }

            if(strAccess == "pm")
            {
                  btnSubmit2PG.Visible = false;
                  btnSubmit2PG2.Visible = false;
                  txtDate.Visible = false;
                  lblDates2.Visible = true;
            }
            else
            {
                  btnSubmit2PG.Visible = true;
                  btnSubmit2PG2.Visible = true;
                  lblPN.Visible = true;
                  txtPN.Visible = true;
                  txtDate.Visible = true;
                  lblDates2.Visible = false;

                  if(!Page.IsPostBack)
                  {

                        myTConnection.Open();

                        string strProps = "SELECT PROP_ID, cast(Prop_ID AS varchar) + ' - ' + PropName AS XYZ From Props ORDER BY PropName";
                        SqlCommand objCommandProps = new SqlCommand(strProps, myTConnection);
                        SqlDataReader objReaderProps = objCommandProps.ExecuteReader();

                        txtPN.DataSource = objReaderProps;
                        txtPN.DataValueField = "PROP_ID";
                        txtPN.DataTextField = "XYZ";
                        txtPN.DataBind();
                        txtPN.Items.Insert(0, "Select A Property");
                        //                  txtPN.SelectedIndex = 0;

                        myTConnection.Close();

                  }
            }

                  
            String strConnection = ConfigurationSettings.AppSettings["Status_Changes"];
            SqlConnection myConnection = new SqlConnection(strConnection);

            myConnection.Open();
                  
            string SQLDates = "SELECT MAX(sc_id) AS [MaxSCID] FROM Property_Changes WHERE Property_Number = '" + cookPN + "'";
            SqlCommand objCommandDates = new SqlCommand(SQLDates, myConnection);
            SqlDataReader objReaderDates = objCommandDates.ExecuteReader();

            string strMaxSCID = "";

            while (objReaderDates.Read() == true)
            {
                  strMaxSCID = objReaderDates["MaxSCID"].ToString();
            }

            objReaderDates.Close();

            string SQLDates2 = "SELECT begin_date, end_date FROM Property_Changes WHERE sc_id = '" + strMaxSCID + "'";
            SqlCommand objCommandDates2 = new SqlCommand(SQLDates2, myConnection);
            SqlDataReader objReaderDates2 = objCommandDates2.ExecuteReader();

            if(!Page.IsPostBack)
            {
                  while (objReaderDates2.Read() == true)
                  {
                        lblDates.Text = MakeDateShort(objReaderDates2["begin_date"].ToString());

                        if(objReaderDates2["end_date"].ToString() == "")
                        {
                              DateTime dteEndDate = Convert.ToDateTime(objReaderDates2["begin_date"].ToString());
                              dteEndDate = dteEndDate  + TimeSpan.FromDays(30);
                              lblDates2.Text = MakeDateShort(dteEndDate.ToString());
                              txtDate.Text = MakeDateShort(dteEndDate.ToString());
                        }
                        else
                        {
                              lblDates2.Text = MakeDateShort(objReaderDates2["end_date"].ToString());
                              txtDate.Text = MakeDateShort(objReaderDates2["end_date"].ToString());
                        }
                  }
            }

            objReaderDates2.Close();
            
            string strSQL3 = "SELECT last_update FROM Last_Update WHERE Property_Number = '" + cookPN + "'";
            SqlCommand objCommand3 = new SqlCommand(strSQL3, myConnection);
            SqlDataReader objReader3 = objCommand3.ExecuteReader();
                                    
            while (objReader3.Read() == true)
            {
                  lblLastUpdate.Text = MakeDateShort(objReader3["last_update"].ToString());
            }
            objReader3.Close();
            myConnection.Close();
            
            DataFiller();
            
            }
      
      public void DataFiller()
      {
      Response.Write("BIND DATA<BR>");
            string cookPN = "";
            string strAccess = "";
                  
            if(Request.Cookies["PN"] != null && Request.Cookies["Access"] != null)
            {
                  cookPN = Request.Cookies["PN"].Value;
                  strAccess = Request.Cookies["Access"].Value;
            }
            else
            {
                  Response.Redirect("login.aspx");
            }
            
            String strConnection = ConfigurationSettings.AppSettings["Status_Changes"];
            SqlConnection myConnection = new SqlConnection(strConnection);
      
            myConnection.Open();
            string strSQL7 = "SELECT sc_id, begin_date, end_date, property_number, unit_number AS [Unit], resident_name AS [Status], move_out_date AS [Move Out Date], move_in_date AS [Move In Date], new_resident AS [New Resident], hdct AS [Headcount], id_code AS [ID Code], credits AS [Credits], debits AS [Debits], debit_description AS [Debit Description], misc_charge AS [Miscellaneous Charge], misc_description AS [Miscellaneous Description] FROM Property_Changes WHERE property_number = '" + cookPN + "' AND uploaded = '0'";
            SqlDataAdapter objAdapter_MO = null;
            objAdapter_MO = new SqlDataAdapter(strSQL7, myConnection);
            objAdapter_MO.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            
            objAdapter_MO.Fill(objDataSet_MO, "Property_Changes");
            DataTable objTable_MO = objDataSet_MO.Tables["Property_Changes"];
            DataRow objNewRow_MO = objTable_MO.NewRow();

//            dgStatus_Changes.PagerStyle.NextPageText = "Next";
//            dgStatus_Changes.PagerStyle.PrevPageText = "Previous";
            
            dgStatus_Changes.DataSource = objTable_MO.DefaultView;
            dgStatus_Changes.DataKeyField = "sc_id";
            dgStatus_Changes.DataBind();
                  
            myConnection.Close();
      }
      
      
      public void GridPageChange(object s, DataGridPageChangedEventArgs e)
      {
      dgStatus_Changes.CurrentPageIndex = e.NewPageIndex;
      DataFiller();
      }


      protected double ShowMoney(string strVary)
      {
            if(strVary != "")
            {
                  double dblRetVal = Math.Round(Convert.ToDouble(strVary), 2);
                  return dblRetVal;
            }
            else
            {
                  double dblRetVal = Math.Round(Convert.ToDouble(0), 2);
                  return dblRetVal;
            }
      }
            
      protected string MakeDateShort(string chngDate)
      {
            if(chngDate != "")
            {
                  String DTChng =chngDate.ToString();
                  DateTime DTChanged = DateTime.Parse(DTChng);
                  return DTChanged.ToString("d");
            }
            else
            {
                  return "";
            }
      }

      protected string ChkVacant(string Res)
      {
            if(Res != "")
            {
                  return Res;
            }
            else
            {
                  return "Vacant";
            }
      }

      public void btnSubmit_Click(Object sender, System.EventArgs e)
      {
            if(Page.IsValid)
            {
                  foreach (DataGridItem di in dgStatus_Changes.Items)
                  {
                        if (di.ItemType == ListItemType.Item || di.ItemType == ListItemType.AlternatingItem)
                        {
                              DataRow dr = objDataSet_MO.Tables["Property_Changes"].Rows.Find(dgStatus_Changes.DataKeys[di.ItemIndex]);
                        
                              if(((TextBox)di.FindControl("txtMODate")).Text != "")
                              {
                              //      Response.Write("MO DATE<BR>");
                                    dr["Move Out Date"] = ((TextBox)di.FindControl("txtMODate")).Text;
                              }
                        
                              if(((TextBox)di.FindControl("txtMIDate")).Text != "")
                              {
                              //      Response.Write("MI DATE<BR>");
                                    dr["Move In Date"] = ((TextBox)di.FindControl("txtMIDate")).Text;
                              }
                              dr["New Resident"] = ((TextBox)di.FindControl("txtNR")).Text;
                        }
                  }
            
                  if (objDataSet_MO.HasChanges())
                  {
                        SaveChanges();
                  }
            }
            
      }

      private void SaveChanges()
      {
            DateTime date;

            try
            {
                  date = Convert.ToDateTime(txtDate.Text);
                  System.Console.Out.WriteLine(date.ToString());
            }
            catch(SystemException e)
            {
            //      Page.IsValid = false;
            }

            string cookPN = Request.Cookies["PN"].Value;
            
            String strConnection = ConfigurationSettings.AppSettings["Status_Changes"];
            SqlConnection myConnection = new SqlConnection(strConnection);

            DataSet objDataSet = new DataSet();
            SqlDataAdapter objAdapter = null;
            SqlCommand objCommand = null;
            SqlCommandBuilder objBuilder = null;


            //UPDATE LAST UPDATE

            string strSelUp = "SELECT Last_Update FROM last_update WHERE property_number = '" + cookPN + "'";
            myConnection.Open();
            SqlCommand objCommand32 = new SqlCommand(strSelUp, myConnection);
            SqlDataReader objReader32 = objCommand32.ExecuteReader();

            int intRows2 = 0;
            
            while (objReader32.Read() == true)
            {
                  intRows2 = intRows2 + 1;
            }

            objReader32.Close();
            myConnection.Close();


            string strD = DateTime.Now.ToString();
            
            if(intRows2 == 0)
            {
                  string strINSERT = "INSERT INTO Last_Update VALUES('" + cookPN + "', '"  + strD + "')";
                  myConnection.Open();
                  SqlCommand objCommand37 = new SqlCommand(strINSERT, myConnection);
                  SqlDataReader objReader37 = objCommand37.ExecuteReader();
                  objReader37.Close();
                  myConnection.Close();
            }
            else
            {
                  string strUPDATE = "UPDATE Last_Update SET last_update = '" + strD + "' WHERE property_number = '" + cookPN + "'";
                  myConnection.Open();
                  SqlCommand objCommand3 = new SqlCommand(strUPDATE, myConnection);
                  SqlDataReader objReader3 = objCommand3.ExecuteReader();
                  objReader3.Close();
                  myConnection.Close();
            }

            //BUILD ARRAY FOR DATES

            myConnection.Open();
            string strSQL33 = "SELECT sc_id FROM Property_Changes WHERE property_number = '" + cookPN + "' AND uploaded = '0'";
            SqlCommand objCommand33 = new SqlCommand(strSQL33, myConnection);
            SqlDataReader objReader33 = objCommand33.ExecuteReader();
            string strSC_ID4;
            int intRows = 0;
            
            while (objReader33.Read() == true)
            {
                  intRows = intRows + 1;
            }
            objReader33.Close();

            SqlCommand objCommand34 = new SqlCommand(strSQL33, myConnection);
            SqlDataReader objReader34 = objCommand34.ExecuteReader();

            int[] aryID = new int[intRows];
            int intCounter = 0;

            while (objReader34.Read() == true)
            {
                  strSC_ID4 = objReader34["sc_id"].ToString();
                  aryID[intCounter] = Convert.ToInt32(strSC_ID4);
                  intCounter = intCounter + 1;
            }
            objReader34.Close();
            myConnection.Close();

            SqlCommand myCommand4 = new SqlCommand("sp_Update_EndDate", myConnection);
            myCommand4.CommandType = CommandType.StoredProcedure;
            myCommand4.Parameters.Add (new SqlParameter("@B_Date", SqlDbType.DateTime,8));
            myCommand4.Parameters.Add (new SqlParameter("@E_Date", SqlDbType.DateTime,8));
            myCommand4.Parameters.Add (new SqlParameter("@sc_id", SqlDbType.Int,4));
            
            DateTime dateR;
 
            foreach (int ID in aryID)
            {
                  myCommand4.Parameters["@B_Date"].Value = lblDates.Text;
                  myCommand4.Parameters["@E_Date"].Value = txtDate.Text;
                  myCommand4.Parameters["@sc_id"].Value = ID;
                  myConnection.Open();
                  
                  myCommand4.ExecuteNonQuery();
                  myConnection.Close();
            }


            //UPDATE DATAGRID

            string strSQL = "SELECT sc_id, property_number, resident_name, move_out_date, move_in_date, new_resident, hdct, id_code, credits, debits, debit_description, misc_charge, misc_description FROM Property_Changes WHERE property_number = '" + cookPN + "'";

            objAdapter = new SqlDataAdapter(strSQL, myConnection);
            objBuilder = new SqlCommandBuilder(objAdapter);
            objAdapter.SelectCommand.CommandText =strSQL;
            objBuilder.RefreshSchema();
            
//            objDataSet.Tables.Remove(objDataSet.Tables["Property_Changes"]);
            objAdapter.Fill(objDataSet, "Property_Changes");
            
            //update code
            DataTable objTable = null;
                         
                         objTable = objDataSet.Tables["Property_Changes"];
                         DataRow objRow = null;
            
                         foreach (DataGridItem di in dgStatus_Changes.Items)
                         {
                                // Make sure this is an item and not the header or footer.
                                if (di.ItemType == ListItemType.Item || di.ItemType == ListItemType.AlternatingItem)
                                {
                                       objRow = objTable.Rows[di.ItemIndex];
                                
                                       if(((TextBox)di.FindControl("txtMODate")).Text != "")
                                       {
                                                objRow["move_out_date"] = ((TextBox)di.FindControl("txtMODate")).Text;
                                       }
                                       else
                                       {
                                                objRow["move_out_date"] = DBNull.Value;
                                       }
            
                                       if(((TextBox)di.FindControl("txtMIDate")).Text != "")
                                       {
                                                objRow["move_in_date"] = ((TextBox)di.FindControl("txtMIDate")).Text;
                                       }
                                       else
                                       {
                                                objRow["move_in_date"] = DBNull.Value;
                                       }
                                       
                                       objRow["new_resident"] = ((TextBox)di.FindControl("txtNR")).Text;
                                       
                                       if(((TextBox)di.FindControl("txtHdct")).Text != "")
                                       {
                                                objRow["hdct"] = ((TextBox)di.FindControl("txtHdct")).Text;
                                       }
            
                                       objRow["id_code"] = ((TextBox)di.FindControl("txtID")).Text;
                                       
                                       if(((TextBox)di.FindControl("txtCred")).Text != "")
                                       {
                                                objRow["credits"] = ((TextBox)di.FindControl("txtCred")).Text;
                                       }
            
            
                                       if(((TextBox)di.FindControl("txtDeb")).Text != "")
                                       {
                                                objRow["debits"] = ((TextBox)di.FindControl("txtDeb")).Text;
                                       }
                                       objRow["debit_description"] = ((TextBox)di.FindControl("txtDD")).Text;
            
                                       if(((TextBox)di.FindControl("txtMC")).Text != "")
                                       {
                                                objRow["misc_charge"] = ((TextBox)di.FindControl("txtMC")).Text;
                                       }
                                       objRow["misc_description"] = ((TextBox)di.FindControl("txtMD")).Text;
                                }
                         }            
            lblUpdated.Text = "Your changes have been submitted";
            
      }

      

      public void OnExportGridToCSV(object sender, System.EventArgs e)
      {
            if(Page.IsValid)
            {
                  SaveChanges();
                  
                  string cookPN = Request.Cookies["PN"].Value;
            
                  // Create the CSV file to which grid data will be exported.
                  StreamWriter sw = new StreamWriter(Server.MapPath("/statuschanges/csv/" + cookPN + ".txt"), false);
                  // First we will write the headers.
                  DataTable dt = objDataSet_MO.Tables["Property_Changes"];
                  int iColCount = dt.Columns.Count;

                  // WRITE COLUMN HEADERS            
                  /*      
                        for(int i = 8; i < iColCount; i++)
                        {
                              sw.Write(dt.Columns[i]);
                              if (i < iColCount - 1)
                              {
                                    sw.Write(",");
                              }
                        }
      
                        sw.Write(sw.NewLine);
                  */
                  // Now write all the rows.
                  foreach (DataRow dr in dt.Rows)
                  {
                        for (int i = 1; i < iColCount; i++)
                        {
                              
                              if(i == 1 || i == 2 || i == 6 || i == 7)
                              {
                                    if (!Convert.IsDBNull(dr[i]))
                                    {
                                          sw.Write(MakeDateShort(dr[i].ToString()));
                                    }
                                    if ( i < iColCount - 1)
                                    {
                                          sw.Write(",");
                                    }
                              }
                              else if(i == 11 || i == 12 || i == 14)
                              {
                                    if (!Convert.IsDBNull(dr[i]))
                                    {
                                          sw.Write(Math.Round(Convert.ToDouble((dr[i].ToString())), 2));
                                    }
                                    if ( i < iColCount - 1)
                                    {
                                          sw.Write(",");
                                    }
                              }
                              else
                              {
                                    if (!Convert.IsDBNull(dr[i]))
                                    {
                                          sw.Write(dr[i].ToString());
                                    }
                                    if ( i < iColCount - 1)
                                    {
                                          sw.Write(",");
                                    }
                              }
                        }
                        sw.Write(sw.NewLine);
                  }
                  sw.Close();
                  SaveChanges();

                  EOM();

                  lblUpdated.Text = "Your file has been uploaded to PG";


            }
      }

      public void EOM()
      {
            string cookPN = Request.Cookies["PN"].Value;

            String strConnection = ConfigurationSettings.AppSettings["Status_Changes"];
            SqlConnection myConnection = new SqlConnection(strConnection);
            myConnection.Open();

            
            //  DELETE MOVEOUTS /MAKE NEW RESIDENT OR VACANT
                  
            //  GET MAX BEG & END DATES

            string strGetMaxDates = "SELECT MAX(begin_date) AS BD, MAX(end_date) AS ED FROM Property_Changes WHERE property_number = '" + cookPN + "' AND uploaded = '0'";
            SqlCommand objCommand3 = new SqlCommand(strGetMaxDates, myConnection);
            SqlDataReader objReader3 = objCommand3.ExecuteReader();

            string strBegDate = "";
            string strEndDate = "";
                                    
            while (objReader3.Read() == true)
            {
                  strBegDate = objReader3["BD"].ToString();
                  strEndDate = objReader3["ED"].ToString();
            }
            objReader3.Close();

       
            //  CLEAR MOVE-OUTS FOR LATEST PERIOD

            string strClearMoveOuts = "UPDATE Property_Changes SET resident_name = 'VACANT', move_out_date = NULL WHERE property_number = '" + cookPN + "' AND move_out_date BETWEEN '" + strBegDate + "' AND '" + strEndDate + "' AND uploaded = '0'";
            
            SqlCommand objCommand8 = new SqlCommand(strClearMoveOuts, myConnection);
            SqlDataReader objReader8 = objCommand8.ExecuteReader();
            objReader8.Close();
            
            
            // MOVE IN NEW MOVE INS

            string strSELMoveIns = "SELECT sc_id, new_resident FROM Property_Changes WHERE resident_name = 'VACANT' AND property_number = '" + cookPN + "' AND move_in_date BETWEEN '" + strBegDate + "' AND '" + strEndDate + "' AND uploaded = '0'";
            SqlCommand objCommand81 = new SqlCommand(strSELMoveIns, myConnection);
            SqlDataReader objReader81 = objCommand81.ExecuteReader();

            int  intCounter = 0;

            while (objReader81.Read() == true)
            {
                  intCounter = intCounter + 1;
            }

            objReader81.Close();


            SqlCommand objCommand82 = new SqlCommand(strSELMoveIns, myConnection);
            SqlDataReader objReader82 = objCommand82.ExecuteReader();

            string [] strAryNewRes = new string[intCounter];
            string [] strArySCID = new string[intCounter];
            int intLoop = 0;

            while (objReader82.Read() == true)
            {
                  strAryNewRes[intLoop] = objReader82["new_resident"].ToString();
                  strArySCID[intLoop] = objReader82["sc_id"].ToString();
                  intLoop = intLoop + 1;
            }
                      
            objReader82.Close();

            SqlCommand myCommand4 = new SqlCommand("sp_MoveIn", myConnection);
            myCommand4.CommandType = CommandType.StoredProcedure;
            myCommand4.Parameters.Add (new SqlParameter("@new_res", SqlDbType.VarChar,75));
            myCommand4.Parameters.Add (new SqlParameter("@sc_id", SqlDbType.Int,8));
            

            for (int intTempLoop = 0; intTempLoop < intLoop; intTempLoop++)
            {
                  string strMoveIn = "UPDATE Property_Changes SET new_resident = '" + strAryNewRes[intTempLoop] + "' WHERE sc_id = '" + strArySCID[intTempLoop] + "'";
                  
                  myCommand4.Parameters["@new_res"].Value = strAryNewRes[intTempLoop];
                  myCommand4.Parameters["@sc_id"].Value = strArySCID[intTempLoop];
                  myCommand4.ExecuteNonQuery();
            }

            // ADD 30 DAYS TO END DATE

            DateTime dteEndDate2 = Convert.ToDateTime(strEndDate);
            dteEndDate2 = dteEndDate2  + TimeSpan.FromDays(30);

            string strAddDays = "UPDATE Property_Changes SET begin_date = '" + strEndDate + "', end_date = '" + dteEndDate2 + "' WHERE property_number = '" + cookPN + "' AND end_date = '" + strEndDate + "'";
            SqlCommand objCommand6 = new SqlCommand(strAddDays, myConnection);
            SqlDataReader objReader6 = objCommand6.ExecuteReader();
            objReader6.Close();

            myConnection.Close();
      }

      public void lnkLogout_Click(object sender, EventArgs e)
      {
            FormsAuthentication.SignOut();
            Server.Transfer("login.aspx");
      }

      
      
      public void btnGo_Click(object sender, EventArgs e)
      {
            //      if(Page.IsValid)
            //      {
                  
            string mySearchString = txtSearch.Text;
            Response.Redirect("default.aspx?pid=" + Request.QueryString["pid"] + "#" + mySearchString);
            //      }
      }

      public void DD_Click(object sender, EventArgs e)
      {
            string strPropNUM = txtPN.SelectedItem.Value;
            Response.Cookies["PN2"].Value = txtPN.SelectedItem.Value;
            Response.Redirect("http://rciweb/statuschanges/default.aspx?pid=" + strPropNUM);
            
      }

}

0
 
LVL 29

Expert Comment

by:David H.H.Lee
ID: 9765261
alsharpton ,
As i said, you forget to rebind the datagrid after changes.
eg:
public void btnSubmit_Click(Object sender, System.EventArgs e)
    {
         if(Page.IsValid)
         {
              foreach (DataGridItem di in dgStatus_Changes.Items)
              {
                   if (di.ItemType == ListItemType.Item || di.ItemType == ListItemType.AlternatingItem)
                   {
                        DataRow dr = objDataSet_MO.Tables["Property_Changes"].Rows.Find(dgStatus_Changes.DataKeys[di.ItemIndex]);
                   
                        if(((TextBox)di.FindControl("txtMODate")).Text != "")
                        {
                        //     Response.Write("MO DATE<BR>");
                             dr["Move Out Date"] = ((TextBox)di.FindControl("txtMODate")).Text;
                        }
                   
                        if(((TextBox)di.FindControl("txtMIDate")).Text != "")
                        {
                        //     Response.Write("MI DATE<BR>");
                             dr["Move In Date"] = ((TextBox)di.FindControl("txtMIDate")).Text;
                        }
                        dr["New Resident"] = ((TextBox)di.FindControl("txtNR")).Text;
                   }
              }
         
              if (objDataSet_MO.HasChanges())
              {
                   SaveChanges();
                   DataFiller() //ADD THIS LINE!! <----------------MISSING THIS LINE?
              }
         }
         
    }

If that no the problems, then is that you mean your data no being updated in the database? Let's me know more details about it.

Regards
x_com
0
 

Author Comment

by:alsharpton
ID: 9765372
Correct the data is not being updated in the database...
0
 

Author Comment

by:alsharpton
ID: 9766989
if(((TextBox)di.FindControl("txtHdct")).Text != "")
                                 {
                                        objRow["hdct"] = ((TextBox)di.FindControl("txtHdct")).Text;
Response.Write(((TextBox)di.FindControl("txtHdct")).Text + "<BR>");
                                 }

This doesn't display the updated value, it simply displays the value currently in the db.  Plz help...
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 29

Expert Comment

by:David H.H.Lee
ID: 9768667
i notice that you had made some common mistake inside your update query. You need to double check about it.
eg:
You should use
objCommand8.ExecuteNonQuery();

instead of
objCommand8.ExecuteReader().

That's just 1 of the mistake you made. Please check again.

That's should solve your problems.
Regards
x_com
         
0
 

Author Comment

by:alsharpton
ID: 9771586
Yea, I changed it to nonquery and it still doesn't update.
0
 
LVL 29

Expert Comment

by:David H.H.Lee
ID: 9775296
alsharpton ,
Still not solve the problems after made the changes for :
objCommand37, objCommand3, objCommand8 , objCommand6 ?
0
 

Author Comment

by:alsharpton
ID: 9780458
Hmmm.... yea, I changed them all, to no avail.  I think the problem is that the data is being bound to the datagrid prior to the update, thus overwriting the new values.  In the Page_Load event I changed the last couple of lines to:

if(!Page.IsPostBack)
                  {
                  DataFiller();
                  }

Which gives me the error:
Object reference not set to an instance of an object
DataRow dr = objDataSet_MO.Tables["Property_Changes"].Rows.Find(dgStatus_Changes.DataKeys[di.ItemIndex]);

Any thoughts?
0
 
LVL 29

Expert Comment

by:David H.H.Lee
ID: 9785293
alsharpton ,
If you put DataFilter() inside !Page.IsPostBack, you only will get the old values come from DB, not the latest values from DB. You need to put that after you made the changes in the related event, not inside page load. Your code is suitable for first load situations only.
Moreover,
Instead of
DataRow dr = objDataSet_MO.Tables["Property_Changes"].Rows.Find(dgStatus_Changes.DataKeys[di.ItemIndex]);
Should be
DataRow dr = new DataRow() ;
dr = objDataSet_MO.Tables["Property_Changes"].Rows.Find(dgStatus_Changes.DataKeys[di.ItemIndex]);

Regards
x_com
0
 

Author Comment

by:alsharpton
ID: 9800064
Nevermind, it was the structure of the code that was screwing me.  Thanks anyways.
0
 
LVL 29

Accepted Solution

by:
David H.H.Lee earned 250 total points
ID: 9801687
alsharpton ,
I suggest you to divide your web application into presentation, business and data component. This will make everythings much easy to solve. Here's the complete guidelines about how to build a complex web applications into 3 tier model.
http://msdn.microsoft.com/architecture/default.aspx?pull=/library/en-us/dnbda/html/BOAGag.asp

Regards
x_com
0
 
LVL 7

Expert Comment

by:vnvk
ID: 12154837
This Question is now classified abandoned as no comment has been added to this question in more than 21 days.

I will leave the following recommendation on this question to the moderators in the Cleanup topic area:
    Accept: alsharpton {http:#9780458}

Any objections should be posted here in the next 4 days. After that time, the question will be closed at the discretion of the moderator.

vnvk
EE Cleanup Volunteer
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now