SQL: Update does not affect any row

Question

I want to update a dataset in a DB2/AS400 table.

The problem is if I there is string parameter in the parameters list the command does not find a row to update.

For example: If I run the command only with the company number the command will succeed. If I run the command with the company number and facility number the command fails.

Does anyone have any idea?

IDbConnection cn = Tools.GetCnApp();

try
{
    StringBuilder sql = new StringBuilder(); 

    sql.AppendLine("UPDATE " + Tools.GetSchemeApp() + "/ChangeReasonAssignments");
    sql.AppendLine("  SET Confirmed = @CONF, Confirmed_By = @CONFBY, Confirmed_At = @CONFAT");
    sql.AppendLine("  WHERE Company = @CONO AND Facility = @FACI AND Department = @DEPT");
    sql.AppendLine("  AND Production_Group = @PRGR AND Manufacturing_Order = @ORDR AND Order_Operation = @OPER");
    sql.AppendLine("  AND Confirmed = 0");

    IDbCommand cmd = cn.CreateCommand();

    cmd.SetParameter("@CONO", this.CompanyNumber);
    cmd.SetParameter("@FACI", this.FacilityNumber);
    cmd.SetParameter("@DEPT", this.ProductionGroup.Department.Name);
    cmd.SetParameter("@PRGR", this.ProductionGroup.Name);
    cmd.SetParameter("@ORDR", this.ManufacturingNumber);
    cmd.SetParameter("@OPER", this.OperationNumber);
    cmd.SetParameter("@CONFBY", Base.User);
    cmd.SetParameter("@CONFAT", DateTime.Now.ToString());
    cmd.SetParameter("@CONF", 1);

    cmd.CommandText = sql.ToString();

    if (cmd.ExecuteNonQuery() > 0)
    {
    }

EDIT

The datatypes in database are:

Company: INTEGER
Facility: VARCHAR
Dpartment: VARCHAR
Production_Group: VARCHAR
Manufacturing_Order:INTEGER
Order_Operation: INTEGER

The datatypes in .NET are:

CompanyNumber: int
FacilityNumber: String
Departmentname: String
ProductionGroup: String
Manufacturingorder: int
OrderOperation: int

sql.ToString() results:

UPDATE TSAEDBDEV/ChangeReasonAssignments SET Confirmed = @CONF, Confirmed_By = @CONFBY, Confirmed_At = @CONFAT WHERE Company = @CONO AND Facility = @FACI AND Confirmed = 0

What type is this.OperationNumber and Order_Operatation? Is it the same type in the .NET and the database? — DoctorMick, Jul 29 '11 at 08:38
I wonder if this is a padding issue (char[n] vs varchar[n] etc) - what are the SQL types and .NET types? — Marc Gravell, Jul 29 '11 at 08:40
I know that the naming is sometimes inconsistent but the project grew too fast :) — Gerald, Jul 29 '11 at 08:49
The databaas is DB2/AS400. The datatypes in the table and the code ara correct - I checked this very often. sql.ToString() will result this: ` UPDATE TSAEDBDEV/ChangeReasonAssignments SET Confirmed = @CONF, Confirmed_By = @CONFBY, Confirmed_At = @CONFAT WHERE Company = @CONO AND Facility = @FACI AND Confirmed = 0 ´ — Gerald, Jul 29 '11 at 08:53
@Gerald: please **don't put** large blocks of text into comments - really really hard to read. Instead: **update** your original question by **editing** it and provide the additional info !! — marc_s, Jul 29 '11 at 08:54
the .NET types are: Companynumber: int Facilitynumber: String (I know, why I name it number if its a string...) Department: String Productiongroup, Manufacturingnumber and Ordeeroperation are integers — Gerald, Jul 29 '11 at 08:57
btw on INSERT and SELECT I use the same parameters and there are no troubles — Gerald, Jul 29 '11 at 08:59

score 0 · Answer 1 · answered Jul 29 '11 at 08:38

0

Try to set the string values into ': cmd.SetParameter("@DEPT", "'" + this.ProductionGroup.Department.Name + "'");

answered Jul 29 '11 at 08:38

Stephan Bauer

9,120
5
36
58

No, this won't be necessary - SQL Server will handle this automatically (if it **is** SQL Server that the OP is using...) – marc_s Jul 29 '11 at 08:45

SQL: Update does not affect any row

EDIT

1 Answers1