HI there,

I have written a simple form that inserts a new record when none exists and updates an existing record when one does. It uses ASP.net and C#. The insert statement works fine, but the update statement does not work at all, though no error is returned. Any help would be appreciated. The code in questions is in the Submit_Click function.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

namespace PathfinderCharGen
{
    public partial class HeroDetails : System.Web.UI.Page
    {
        #region SQLConnection
        SqlConnection conn;
        SqlCommand cmd;
        SqlCommand cmd1;
        SqlDataReader rdr;
        String uid;

        private void closeConn()
        {
            if (conn != null)
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn.Dispose();
            }
        }

        private SqlConnection createConn(string database)
        {
            // Here you define your server. Values can not be NULL.        //Database Server Name.
            string myDSN = "SQLEXPRESS";

            //Local Server Name.
            string myLSN = "ST-PC";

            //Define the type of security, 'TRUE' or 'FALASE'.
            string mySecType = "TRUE";

            //Here you have your connection string you can edit it here.
            string connString = ("server=" + myLSN + "\\" + myDSN + ";database=" + database + ";Integrated Security=" + mySecType);

            //If you wish to use SQL security, well just make your own connection string...
            // I make sure I have declare what conn stand for.
            if (conn == null) { conn = new SqlConnection(); };

            // Since i will be reusing the connection I will try this it the connection dose not exist.
            if (conn.ConnectionString == string.Empty || conn.ConnectionString == null)
            {
                // I use a try catch stament cuz I use 2 set of arguments to connect to the database
                try
                {
                    //First I try with a pool of 5-40 and a connection time out of 4 seconds. then I open the connection.
                    conn.ConnectionString = "Min Pool Size=5;Max Pool Size=40;Connect Timeout=4;" + connString + ";";
                    conn.Open();
                }
                catch (Exception)
                {
                    //If it did not work i try not using the pool and I give it a 45 seconds timeout.
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                    conn.ConnectionString = "Pooling=false;Connect Timeout=45;" + connString + ";";
                    conn.Open();
                }
                return conn;
            }
            //Here if the connection exsist and is open i try this.
            if (conn.State != ConnectionState.Open)
            {
                try
                {
                    conn.ConnectionString = "Min Pool Size=5;Max Pool Size=40;Connect Timeout=4;" + connString + ";";
                    conn.Open();
                }
                catch (Exception)
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                    conn.ConnectionString = "Pooling=false;Connect Timeout=45;" + connString + ";";
                    conn.Open();
                }
            }
            return conn;
        }
        #endregion

        protected void Page_Load(object sender, EventArgs e)
        {

            uid = Request.QueryString["uid"];
            if (uid == null)
                return;
            createConn("PathfinderCharGen");
            cmd = new SqlCommand("SELECT * FROM characters WHERE uid='" + uid + "'", conn);

            rdr = cmd.ExecuteReader();

            if (!rdr.HasRows)
                return;

            while (rdr.Read())
            {
                characterName.Text = Convert.ToString(rdr["name"]);
                playerName.Text = Convert.ToString(rdr["player"]);
                height.Text = Convert.ToString(rdr["height"]);
                weight.Text = Convert.ToString(rdr["weight"]);
                eyes.Text = Convert.ToString(rdr["eyes"]);
                hair.Text = Convert.ToString(rdr["hair"]);
                gender.Text = Convert.ToString(rdr["gender"]);
                age.Text = Convert.ToString(rdr["age"]);
                homeland.Text = Convert.ToString(rdr["homeland"]);
                campaign.Text = Convert.ToString(rdr["campaign"]);
            }
            cmd.Dispose();
            rdr.Dispose();

            closeConn();
        }

        protected void submit_Click(object sender, EventArgs e)
        {
            createConn("PathfinderCharGen");

            uid = Request.QueryString["uid"];

            if (uid != null)
                cmd1 = new SqlCommand("UPDATE characters SET name = @name, player = @player," +
                    " height = @height, weight = @weight, eyes = @eyes, hair = @hair," +
                    " gender = @gender, age = @age, homeland = @homeland, campaign = @campaign" +
                    " WHERE uid = @uid", conn);
            else
                cmd1 = new SqlCommand("INSERT into characters (name,player,height,weight,eyes," +
                    "hair,gender,age,homeland,campaign) VALUES(@name,@player,@height,@weight," +
                    "@eyes,@hair,@gender,@age,@homeland,@campaign); Select Scope_Identity()", conn);

            cmd1.Parameters.AddWithValue("@name", (characterName.Text == null) ? " " : name.Text);
            cmd1.Parameters.AddWithValue("@player", (playerName.Text == null) ? " " : player.Text);
            cmd1.Parameters.AddWithValue("@height", (height.Text == null) ? " " : height.Text);
            cmd1.Parameters.AddWithValue("@weight", (weight.Text == null) ? " " : weight.Text);
            cmd1.Parameters.AddWithValue("@eyes", (eyes.Text == null) ? " " : eyes.Text);
            cmd1.Parameters.AddWithValue("@hair", (hair.Text == null) ? " " : hair.Text);
            cmd1.Parameters.AddWithValue("@gender", (gender.Text == null) ? " " : gender.Text);
            cmd1.Parameters.AddWithValue("@age", (age.Text == null) ? " " : age.Text);
            cmd1.Parameters.AddWithValue("@homeland", (homeland.Text == null) ? " " : homeland.Text);
            cmd1.Parameters.AddWithValue("@campaign", (campaign.Text == null) ? " " : campaign.Text);
            cmd1.Parameters.AddWithValue("@uid", (uid == null) ? 0 : Convert.ToInt32(uid));
            if (uid == null)
            {
                uid = Convert.ToString(cmd1.ExecuteScalar());
            }
            else
            {
                SqlDataReader rdr1 = cmd1.ExecuteReader();
                rdr1.Dispose();
            }

            cmd1.Dispose();

            closeConn();

            Response.AppendHeader("Refresh", "0; URL=HeroDetails.aspx?uid=" + uid);

            return;
        }

    }
}

Recommended Answers

All 5 Replies

Remove lines from 155 to 163 and use ExecuteNonQuery instead of ExecuteReader.

cmd1.ExecuteNoneQuery()

I fixed the problem.

Basically what was happening was the form action was set to the same page, which is necessary from my understanding in ASP.NET 2.0. It looks like when I click submit it would post back to the page, reload the page again, and then submit my values.

So what was happening was I would enter values into the text fields, the page would reload, and load the blank/previous values from the database into the textboxes, and then execute the submit_click function, which would then try to update the fields with the same values.

There are two ways to fix the problem that I know of. You can either read the previous values into a label or div field and have a textbox beside it for changing the value, or you can not read the values in at all. If there are other options, I would love to hear them, but this is fine with me and I will be able to continue along with this.

ViewState of a page is managed between the Init and Load events. You are right, by prepopulating those fields during the Page_Load handler, you are essentially overwriting the changes the user is making.

One option is to move the prepopulation of those fields to the Page_Init handler, that will allow the user's changes to stick because when ViewState is handled, the user's values will be made available for you to use (thus reversing the negation you're presently experiencing).

The other option is to simply leave your code exactly where it is, except wrap an if block around the part that is prepopulating your fields.

if (!this.IsPostBack)
{
   // this code will not execute for any postback event (including button clicks)
}
commented: He was knowledgable, polite, and friendly... a winning combo... oh and best of all, he was right, and fixed my problem with the best possible solution! +0

Thanks, those are both good solutions that I wasn't aware of. I appreciate the advice.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.