Update Statement Updates 0 Rows via the C# Winform Application?

Question

First of all, please help me out! I can not take this anymore. I could not find where the error is located. Here is my problem:

I'm trying to update a row via c# winform application. The update query generated from the application is formatted correctly. I tested it in the sql server environment, it worked well. When i run it from the application i get 0 rows updated.

Here is the snippet that generates the update statement using reflection - don't try to figure it out. Carry on reading after the code portion:

        public void Update(int cusID)
        {
            SqlCommand objSqlCommand = new SqlCommand();
            Customer cust = new Customer();

            string SQL = null;

            try
            {
                if ((cusID != 0))
                {
                        foreach (PropertyInfo PropertyItem in this.GetType().GetProperties())
                        {
                            if (!(PropertyItem.Name.ToString() == cust.PKName))
                            {
                                if (PropertyItem.Name.ToString() != "TableName")
                                {
                                    if (SQL == null)
                                    {
                                        SQL = PropertyItem.Name.ToString() + " = @" + PropertyItem.Name.ToString();
                                    }
                                    else
                                    {
                                        SQL = SQL + ", " + PropertyItem.Name.ToString() + " = @" + PropertyItem.Name.ToString();
                                    }
                                 }
                                 else
                                 {
                                    break;
                                 }
                            }
                       }

                        objSqlCommand.CommandText = "UPDATE " + this.TableName + " SET " + SQL + " WHERE " + cust.PKName + " = @cusID AND PhoneNumber = " + "'" + "@phNum" + "'";

                        foreach (PropertyInfo PropertyItem in this.GetType().GetProperties())
                        {
                            if (!(PropertyItem.Name.ToString() == cust.PKName))
                            {
                                if (PropertyItem.Name.ToString() != "TableName")
                                {
                                    objSqlCommand.Parameters.AddWithValue("@" + PropertyItem.Name.ToString(), PropertyItem.GetValue(this, null));
                                }
                                else
                                {
                                    break;
                                }

                            }
                        }

                        objSqlCommand.Parameters.AddWithValue("@cusID", cusID);
                        objSqlCommand.Parameters.AddWithValue("@phNum", this.PhoneNumber);
                        DAL.ExecuteSQL(objSqlCommand);
                }
                else
                {
                    //AppEventLog.AddWarning("Primary Key is not provided for Update.")
                }

            }
            catch (Exception ex)
            {
                //AppEventLog.AddError(ex.Message.ToString)
            }
        }

This part below:

objSqlCommand.CommandText = "UPDATE " + this.TableName + " SET " + SQL + " WHERE " + cust.PKName + " = @cusID AND PhoneNumber = " + "'" + "@phNum" + "'";

generates dml:

UPDATE CustomerPhone SET PhoneTypeID = @PhoneTypeID, PhoneNumber = @PhoneNumber WHERE CustomerID = @cusID AND PhoneNumber = '@phNum'

@PhoneTypeID and @PhoneNumber are gotten from two properties. We assigned the value to these properties in the presentation layer from the user input text box. The portion below where fetches the values:

objSqlCommand.Parameters.AddWithValue("@" + PropertyItem.Name.ToString(), PropertyItem.GetValue(this, null));

The code below fills the values of WHERE:

                    objSqlCommand.Parameters.AddWithValue("@cusID", cusID);
                    objSqlCommand.Parameters.AddWithValue("@phNum", this.PhoneNumber);

The final code should look as:

UPDATE CustomerPhone 
SET PhoneTypeID = 7, PhoneNumber = 999444
WHERE CustomerID = 500 AND PhoneNumber = '911';

Phone type id is 7 - user value that is taken from text box Phone number is 999444 - user value that is taken from text box

The above final update statement works on the sql environment, but when running via the application, the execute non query runs ok and gets 0 rows updated! I wonder why?

Answer 1

Is PhoneNumber a string, or an integer?

I see you're SETting as a integer, but checking in the WHERE as a literal. Could this not be the problem?

If it's an integer, try:

UPDATE CustomerPhone 
SET PhoneTypeID = 7, PhoneNumber = 999444
WHERE CustomerID = 500 AND PhoneNumber = 911;

If it's a string, try:

UPDATE CustomerPhone 
SET PhoneTypeID = 7, PhoneNumber = '999444'
WHERE CustomerID = 500 AND PhoneNumber = '911';

Answer 2

This is the problem:

AND PhoneNumber = '@phNum'

That's looking for a phone number which is exactly the text '@phNum' - it's not using a parameter called phNum. You want

AND PhoneNumber = @phNum

You're also breaking up your string literals for no obvious reason. This statement:

objSqlCommand.CommandText = "UPDATE " + this.TableName + " SET " + SQL + 
    " WHERE " + cust.PKName + " = @cusID AND PhoneNumber = " + 
    "'" + "@phNum" + "'";

would be more easily readable as:

objSqlCommand.CommandText = "UPDATE " + this.TableName + " SET " + SQL +
    " WHERE " cust.PKName + " = @cusID AND PhoneNumber = '@phNum'";

Obviously you want to drop the single quotes from it, to make it just:

objSqlCommand.CommandText = "UPDATE " + this.TableName + " SET " + SQL +
    " WHERE " cust.PKName + " = @cusID AND PhoneNumber = @phNum";

A little refactoring wouldn't go amiss, either. This loop:

foreach (PropertyInfo PropertyItem in this.GetType().GetProperties())
{
    if (!(PropertyItem.Name.ToString() == cust.PKName))
    {
        if (PropertyItem.Name.ToString() != "TableName")
        {
            if (SQL == null)
            {
                SQL = PropertyItem.Name.ToString() + " = @" + PropertyItem.Name.ToString();
            }
            else
            {
                SQL = SQL + ", " + PropertyItem.Name.ToString() + " = @" + PropertyItem.Name.ToString();
            }
         }
         else
         {
            break;
         }
    }

}

would be more simpler and more readable like this:

StringBuilder sqlBuilder = new StringBuilder();
foreach (PropertyInfo property in this.GetType().GetProperties())
{
    string name = property.Name;
    // I believe you had a bug before - the properties being updated
    // would depend on the ordering of the properties - if it
    // ran into "TableName" first, it would exit early!
    // I *suspect* this is what you want
    if (name != cust.PKName && name != "TableName")
    {
        sqlBuilder.AppendFormat("{0} = @{0}, ", name);
    }
}
// Remove the trailing ", "
if (sqlBuilder.Length > 0)
{
    sqlBuilder.Length -= 2;
}

You can do something similar with the final loop too.