Hello Everybody,

I am have a little problem updating some information. Basically the page updates a church record from a church table in a database. Here is how it works or at least how it is suppose to work. When you are on the modifychurch.aspx page you are presented with a gridview that holds the church name and the pastor's name of each church in the church table. There is a hyperlink field in the gridview that takes you to the updatechurch.aspx page with the request parameter churchID that hold the ChurchID value from the church record pulled from church table in that current gridview row. The Page_Upload of the updatechurch.aspx page, if not a postback, calls the GetChurch method that get the church record from the church table with the churchID that is passed to that page with the request parameter and assigns the values of each field in that record to the appropriate text boxes on the updatechurch.aspx page. This shows the you what values are currently there for the church record. You can make any changes you want in the text boxes and press the update button. This will update the information in that church record and then pulls out the same record that has just be updated and assign the values of the field in the updated record to the appropriate text boxes to show you the changes. Every thing pretty much work fine except the actual update. Which I can tell, becuase when the last action happens where the updated record is suppose to be pulled back with the new values of its fields populating the textboxes, it shows the values of the fields in the original Church record. Plus I check the database and it shows no changes in the church table after someone does the update process on the page. This is a logical error. So, the CLR does not find any exceptions in the code. So there is no error to show. However, I do have the code below for the updatechurch.aspx page. Any and all help is appreciated.

Code for updatechurch.aspx page

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class Admin_default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            Session["ChurchImage"] = null;
            Session["ChurchThumb"] = null;
            Session["ChurchID"] = null;
            Session["ChurchID"] = Request.Params["ChurchID"];
            lblConfirm.Text = "Update Church";
            GetStates();
            GetChurch();
        }
    }

    protected void GetStates()
    {
        DataTable objDT = new DataTable();
        SQL objSQL = new SQL("SDJamesConn", "GetStates");
        objDT = objSQL.QueryCommand();

        lbStates.DataSource = objDT;
        lbStates.DataValueField = "StateID";
        lbStates.DataTextField = "State";
        lbStates.DataBind();
    }

    protected void GetChurch()
    {
        DataTable objDT = new DataTable(); 
        int intChurchID = Convert.ToInt32(Session["ChurchID"]);
         
        SqlParameter[] Parameters = new SqlParameter[1];

        Parameters[0] = new SqlParameter("@ChurchID", SqlDbType.Int);
        Parameters[0].Value = intChurchID;

        SQL objSQL = new SQL("SDJamesConn", "GetChurchByID", Parameters);
        objDT = objSQL.QueryCommand();

        lbStates.SelectedValue = Convert.ToString(objDT.Rows[0]["StateID"]);
        txtChurch.Text = objDT.Rows[0]["Church"].ToString();
        txtPastor.Text = objDT.Rows[0]["Pastor"].ToString();
        txtDescription.Text = objDT.Rows[0]["Description"].ToString();
        Session["ChurchImage"] = objDT.Rows[0]["Logo"].ToString();
        Session["ChurchThumb"] = objDT.Rows[0]["Thumb"].ToString();
        txtAddress.Text = objDT.Rows[0]["Address"].ToString();
        txtCity.Text = objDT.Rows[0]["City"].ToString();
        txtZip.Text = objDT.Rows[0]["ZipCode"].ToString();
        txtPhone.Text = objDT.Rows[0]["Phone"].ToString();

        Session["Fax"] = objDT.Rows[0]["Fax"].ToString();
        string strFax = Convert.ToString(Session["Fax"]);
        if (strFax != "None")
            txtFax.Text = strFax;

        Session["Web"] = objDT.Rows[0]["WebAddress"].ToString();
        string strWeb = Convert.ToString(Session["Web"]);
        if (strWeb != "None")
            txtWeb.Text = strWeb;
    }

    protected void UpdateChurch()
    {
        int intChurchID = Convert.ToInt32(Session["ChurchID"]);
        int intStateID = Convert.ToInt32(lbStates.SelectedValue);
        string strChurch = txtChurch.Text;
        string strPastor = txtPastor.Text;
        string strDesc = txtDescription.Text;
        string strAddress = txtAddress.Text;
        string strCity = txtCity.Text;
        string strState = lbStates.SelectedItem.ToString();
        string strZipCode = txtZip.Text;
        string strPhone = txtPhone.Text;

        string strImage;
        string strThumb;

        if (fuImage.HasFile)
            strImage = System.IO.Path.GetFileName(fuImage.PostedFile.FileName);
        else
            strImage = Convert.ToString(Session["ChurchImage"]);

        if (fuThumb.HasFile)
            strThumb = System.IO.Path.GetFileName(fuThumb.PostedFile.FileName);
        else
            strThumb = Convert.ToString(Session["ChurchThumb"]);

        string strFax;
        string strWeb;

        if (txtFax.Text == "")
            strFax = "None";
        else
            strFax = txtFax.Text;

        if (txtWeb.Text == "")
            strWeb = "None";
        else
            strWeb = txtWeb.Text;

        SqlParameter[] Parameters = new SqlParameter[14];

        Parameters[0] = new SqlParameter("@ChurchID", SqlDbType.Int);
        Parameters[0].Value = intChurchID;
        Parameters[1] = new SqlParameter("@Church", SqlDbType.NVarChar);
        Parameters[1].Value = strChurch;
        Parameters[2] = new SqlParameter("@Pastor", SqlDbType.NVarChar);
        Parameters[2].Value = strPastor;
        Parameters[3] = new SqlParameter("@Desc", SqlDbType.NVarChar);
        Parameters[3].Value = strDesc;
        Parameters[4] = new SqlParameter("@Image", SqlDbType.NVarChar);
        Parameters[4].Value = strImage;
        Parameters[5] = new SqlParameter("@Thumb", SqlDbType.NVarChar);
        Parameters[5].Value = strThumb;
        Parameters[6] = new SqlParameter("@Address", SqlDbType.NVarChar);
        Parameters[6].Value = strAddress;
        Parameters[7] = new SqlParameter("@City", SqlDbType.NVarChar);
        Parameters[7].Value = strCity;
        Parameters[8] = new SqlParameter("@State", SqlDbType.NVarChar);
        Parameters[8].Value = strState;
        Parameters[9] = new SqlParameter("@ZipCode", SqlDbType.NVarChar);
        Parameters[9].Value = strZipCode;
        Parameters[10] = new SqlParameter("@Phone", SqlDbType.NVarChar);
        Parameters[10].Value = strPhone;
        Parameters[11] = new SqlParameter("@Fax", SqlDbType.NVarChar);
        Parameters[11].Value = strFax;
        Parameters[12] = new SqlParameter("@Web", SqlDbType.NVarChar);
        Parameters[12].Value = strWeb;
        Parameters[13] = new SqlParameter("@StateID", SqlDbType.Int);
        Parameters[13].Value = intStateID;

        SQL objSQL = new SQL("SDJamesConn", "UpdateChurch", Parameters);
        objSQL.NonQueryCommand();
    }

    protected void UpdateImages()
    {
        string strRootPath = Server.MapPath("/EPC");

        if (fuImage.HasFile)
        {
            string strImagePath = System.IO.Path.GetFileName(fuImage.PostedFile.FileName);
            fuImage.PostedFile.SaveAs(strRootPath + "/images/churches/images/" + strImagePath);
        }

        if (fuThumb.HasFile)
        {
            string strThumbPath = System.IO.Path.GetFileName(fuThumb.PostedFile.FileName);
            fuThumb.PostedFile.SaveAs(strRootPath + "/images/churches/thumbs/" + strThumbPath);
        }
    }

    protected void Update()
    {
        UpdateChurch();

        if ((fuImage.HasFile) || (fuThumb.HasFile))
            UpdateImages();

        btnUpdate.Enabled = false;

        lblConfirm.Text = "Church Has Been Updated Successfully";
    }

    protected void btnUpdate_Click(object sender, EventArgs e)
    {
        lblConfirm.Text = "";

        if (fuImage.HasFile || fuThumb.HasFile)
        {
            if (fuImage.HasFile)
            {
                string strImageExt = System.IO.Path.GetExtension(fuImage.PostedFile.FileName);
                if (strImageExt != ".jpg")
                {
                    lblConfirm.Text = "Images and Thumbnails Must be JPEG Files";
                }
            }

            if (fuThumb.HasFile)
            {
                string strThumbExt = System.IO.Path.GetExtension(fuThumb.PostedFile.FileName);
                if (strThumbExt != ".jpg")
                {
                    lblConfirm.Text = "Images and Thumbnails Must be JPEG Files";
                }
            }

            if (lblConfirm.Text == "Images and Thumbnails Must be JPEG Files")
            {

            }
            else
            {
                Update();
                GetChurch();
            }
        }
        else
        {
            Update();
            GetChurch();
        }
    }

    protected void btnReset_Click(object sender, EventArgs e)
    {
        lblConfirm.Text = "Insert Church";
        btnReset.Enabled = true;
    }

}

Hey,

Where does SQL come from, and what does NonQueryCommand do?   Custom object/library?

Hello bmains,

 SQL is a custom class I created to reduce the amount of code I have to write in data driven sites. NonQueryCommand is a method in the SQL class the performs insert, update, and delete comands. It contains another method called QueryCommand that does select commands. The code for this class below.

SQL Class

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;


public class SQL
{
    //Public Fields 
    private string strConnectionString;
    private string strStoredProcedure;
    private SqlParameter[] parameters;

    //Constructor
    public SQL()
    {

    }

    //Constructor with Connection String, Stored Procedure, and SqlParameter Properties
    public SQL(string ConnString, string StoredProc, SqlParameter[] Params)
    {
        ConnectionString = ConnString;
        StoredProcedure = StoredProc;
        SQLParameters = Params;
    }

    //Constructor with Connection String and Stored Procedure Properties
    public SQL(string ConnString, string StoredProc)
    {
        ConnectionString = ConnString;
        StoredProcedure = StoredProc;
    }

    //Constructor with Connection String and SqlParameter Properties
    public SQL(string ConnString, SqlParameter[] Params)
    {
        ConnectionString = ConnString;
        SQLParameters = Params;
    }

    //Constructor with Stored Procedure and SqlParameter Properties
    public SQL(SqlParameter[] Params, string StoredProc)
    {
        StoredProcedure = StoredProc;
        SQLParameters = Params;
    }

    //Constructor with Connection String Property
    public SQL(string ConnString)
    {
        ConnectionString = ConnString;
    }

    //Constructor with SqlParameter Property
    public SQL(SqlParameter[] Params)
    {
        SQLParameters = Params;
    }

    //Connection String Property
    public string ConnectionString
    {
        get
        {
            return strConnectionString;
        }

        set
        {
            strConnectionString = value;
        }
    }

    //Stored Procedure Property
    public string StoredProcedure
    {
        get
        {
            return strStoredProcedure;
        }

        set
        {
            strStoredProcedure = value;
        }
    }

    //SqlParameters Property
    public SqlParameter[] SQLParameters
    {
        get
        {
            return parameters;
        }

        set
        {
            parameters = value;
        }
    }

    //Select Command Method
    public DataTable QueryCommand()
    {
        DataTable objDt = new DataTable();

        SqlConnection objCon = new SqlConnection(ConfigurationManager.ConnectionStrings[ConnectionString].ConnectionString);
        SqlCommand objCom = new SqlCommand();
        objCom = this.Command();
        objCom.Connection = objCon;
        
        SqlDataAdapter objDA = new SqlDataAdapter(objCom);
        objDA.FillSchema(objDt, SchemaType.Mapped);
        objDA.Fill(objDt);

        return objDt;
    }

    //Insert, Update, and Delete Command Method
    public void NonQueryCommand()
    {
        SqlConnection objCon = new SqlConnection(ConfigurationManager.ConnectionStrings[ConnectionString].ConnectionString);
        SqlCommand objCom = new SqlCommand();
        objCom = Command();
        objCom.Connection = objCon;

        objCon.Open();
        objCom.ExecuteNonQuery();
        objCon.Close();
    }

    //SQLCommand Building and Parameter Adding Method
    public SqlCommand Command()
    {
        SqlCommand objCom = new SqlCommand(StoredProcedure);
        objCom.CommandType = CommandType.StoredProcedure;

        if (SQLParameters != null)
        {
            int count;
            for (count = 0; count < SQLParameters.Length; count++)
            {
                objCom.Parameters.Add(SQLParameters[count]);
            }
        }

        return objCom;
    }
}

In the btnUpdate_Click event.....

            if (lblConfirm.Text == "Images and Thumbnails Must be JPEG Files")
            {

            }
            else
            {
                Update();
                GetChurch();
            }

 Here I don't see any implementation for the "If" section, so there might be a chance that it goes into that "If" section and that's why it never performs anything.

If that's not the case and if it executes the Update() method (try to put a breakpoint in Update() method to check) and it does not update your database at all, then the problem is in your stored procedure, because your code looks fine.

Copy your stored procedure here.

Hello kammie,

 Thank you for your reply. The code for the stored procedure below. That if statement having no code is intentional, so that if the user has a image file that is not a JPEG file, it does nothing. You were right about the SQL though. In the code below where I have WHERE ChurchID = @@Identity Should be WHERE ChurchID = @ChurchID. Thanks again and God Bless.

Wrong SQL Code

ALTER PROCEDURE dbo.UpdateChurch 

	(
	@ChurchID int,
	@StateID int,
	@Church nvarchar(150),
	@Pastor nvarchar(250),
	@Desc nvarchar(4000),
	@Image nvarchar(50),
	@Thumb nvarchar(50),
	@Address nvarchar(150),
	@City nvarchar(50),
	@State nvarchar(50),
	@ZipCode nvarchar(10),
	@Phone nvarchar(16),
	@Fax nvarchar(16),
	@Web nvarchar(100)
	)

AS
 UPDATE Churches SET StateID = @StateID, Church = @Church, Pastor = @Pastor, Description = @Desc, Logo = @Image, Thumb = @Thumb, Address = @Address, City = @City, State = @State, ZipCode = @ZipCode, Phone = @Phone, Fax = @Fax, WebAddress = @Web 
 WHERE ChurchID = @@Identity
	RETURN

ALTER PROCEDURE dbo.UpdateChurch 

	(
	@ChurchID int,
	@StateID int,
	@Church nvarchar(150),
	@Pastor nvarchar(250),
	@Desc nvarchar(4000),
	@Image nvarchar(50),
	@Thumb nvarchar(50),
	@Address nvarchar(150),
	@City nvarchar(50),
	@State nvarchar(50),
	@ZipCode nvarchar(10),
	@Phone nvarchar(16),
	@Fax nvarchar(16),
	@Web nvarchar(100)
	)

AS
 UPDATE Churches SET StateID = @StateID, Church = @Church, Pastor = @Pastor, Description = @Desc, Logo = @Image, Thumb = @Thumb, Address = @Address, City = @City, State = @State, ZipCode = @ZipCode, Phone = @Phone, Fax = @Fax, WebAddress = @Web 
 WHERE ChurchID = @ChurchID
	RETURN