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?
