none
System.data.sqlclient.sqlDataAdapter.Update not working at C#2005 RRS feed

  • Question

  •  

    Hi,

     

    We are presently converting all our c#2003 Web Service projects to 2005.

     

    The web method is working fine in c#2003 but in 2005 not working.

    I mean the record not get deleted from the table but if we run 2003 Web Service working fine.

    I have given below the code for your reference.

    Its not throws any errors. Based on the SQL profiler investigation the sp_reset_connection  & [DeleteTestTable] SP has not triggered.

    Is any one got solutions?

     

     

     

    private System.Data.SqlClient.SqlConnection phoneSqlConnection = New System.Data.SqlClient.SqlConnection();

               phoneSqlConnection.ConnectionString = "server=TestServer;uid=Uid;pwd=erterwtdfg;database=TestDb";

               phoneSqlConnection.Open();

     

                private System.Data.SqlClient.SqlCommand allocationSqlDeleteCommand;

     

                this.allocationSqlDeleteCommand.CommandText = "[DeleteTestTable]";

                this.allocationSqlDeleteCommand.CommandType = System.Data.CommandType.StoredProcedure;

                this.allocationSqlDeleteCommand.Connection = this.phoneSqlConnection;

     

                this.allocationSqlDeleteCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, false, ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));

     

                this.allocationSqlDeleteCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_AID", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "AID", System.Data.DataRowVersion.Original, null));

     

     

                SqlDataAdapter allocationSqlAdapter = new System.Data.SqlClient.SqlDataAdapter();

                this.allocationSqlDataAdapter.DeleteCommand = this.allocationSqlDeleteCommand;

     

                dataSet = new DataSet("TestDS");

                if(phoneSqlConnection.State != ConnectionState.Open) return;

                allocationSqlAdapter.Fill(dataSet);

     

                DataTable allocationTable = dataSet.Tables["Allocation"]

                DataRow numberRow = number.DataRow;

                numberRow.Delete();

                allocationTable.Rows.Remove(numberRow);   !

                number.DataRow = null;               

     

                allocationSqlAdapter.Update(allocationTable);  

     

     

     

    Cheers

     

    Monday, September 24, 2007 3:04 AM

All replies

  • The really surprising part is not that this fails in 2005, but that it works in 2003. It should not be deleting anything in either version.

    Notice the part where you do:

     

    numberRow.Delete();

    allocationTable.Rows.Remove(numberRow);

    [...]

    allocationSqlAdapter.Update(allocationTable);

     

    The "Delete" on the DataRow marks it as "deleted" but leaves it in the DataTable. If, at this point, you do a dataAdapter.Update, the dataadapter examines all rows in the datatable, notices the state of each row, and for each row that is marked as deleted, sends a deletecommand to the server, which is what you want.

     

    However, if you add a Remove like you did, the row is permanently removed from the datatable. If you then do an Update, the dataadapter does not find any row marked as deleted, and therefore no deletecommand is sent to the database.

     

    Monday, September 24, 2007 7:40 AM
  •  

    Hi Alberto,

     

    Thanks for your comments.

    I am not familer with SqlAdapter class method, this code was desiged by our ex-colleage.

    The 2003 Web Services is in production from year 2002 onwards, and I am sure its working fine untill now (tested as well).  

    Based on the SQL profiler investigation the sp_reset_connection  & [DeleteTestTable] Stored Procedure is not triggered for 2005 WS method

    However it is triggering at 2003 version.

     

    Do you have any idea that how to solve the above?

     

    Is there any different syntax changed for 2005?

     

    Thanks

    Monday, September 24, 2007 8:44 PM
  • As per your advice I changed the code below and its working fine.

                But still I am not understand how its working in 2003. thanks much

     

      //numberRow.Delete();

                  //allocationTable.Rows.Remove(numberRow);   // remove this row now!

                 

      DataColumn[] dc = new DataColumn[1];

                  dc[0] = allocationTable.Columns["AllocationID"];

                  allocationTable.PrimaryKey = dc;

                  allocationTable.Rows.Find(int.Parse(numberRow["AllocationID"].ToString())).Delete();

     

    Please answer if anyone knows...

    Monday, September 24, 2007 9:36 PM
  • Well, in this snippet of your code: 

     

              DataTable allocationTable = dataSet.Tables["Allocation"]

                DataRow numberRow = number.DataRow;

                numberRow.Delete();

                allocationTable.Rows.Remove(numberRow);   !

                number.DataRow = null;               

     

                allocationSqlAdapter.Update(allocationTable);

     

    We need "numberRow" to be a reference to a row in the allocationTable.

    In your entire code posted I do not see what "number" is to determine what numberRow gets assigned.  Maybe numberRow is actually not properly defined.

     

    Jerry 

     

     

     

    Monday, September 24, 2007 10:14 PM
  •  

    Hi Jerry,

     

    Number is class which return the rows from the table. Here the code

     

       DataRow row = null;

             DataTable allocationTable = dataSet.Tables["TableName"]; // get allocation table

     

             // retrieve rows where we match prefix and number

             string sqlStr = "prefix = " + prefix.ToString() + " AND "

                + "number = " + number.ToString();

     

        

                DataRow[] rows = allocationTable.Select(sqlStr);

                     

    I belive there is issue at number class, but its miracle working at 2003 version.

    The code designed by our ex-colleage, and more over I am not much familer with the SqlAdapter class.

    I am planing to re-desing all into Oledb version.

    Monday, September 24, 2007 10:46 PM