Click here to Skip to main content
11,500,106 members (66,179 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#
Hi,

Below are my method-->
public static bool SaveXmlInfo(Sales1 sales1)
{
   int recordsAffected = 0;
   XElement planPropertyElement = XElement.Parse(orderXml);
   
   using (SqlConnection conn = new SqlConnection(ConnectionString1))
   {
      conn.Open();
      SqlParameter[] parms = new SqlParameter[2];
      parms[0] = new SqlParameter("@RCustId", sales1.RCustId);
      parms[1] = new SqlParameter("@SesId", sales1.SesId);
      parms[2] = new SqlParameter
                     {   ParameterName = "@CenXml",
                         SqlDbType = SqlDbType.Xml,
                         Value = new SqlXml(planPropertyElement.CreateReader())
                     };
      recordsAffected = SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "usp_PurchXml", parms);
 
      salesPurchase = null;
   }
   
   return recordsAffected > 0 ? true : false;
}
In my Stored Procedure, I am checking SesId available or not.If it is available then i am updating record else I am inserting new record into table.
Below are my Stored Procedure
IF EXISTS(SELECT * FROM Cust1 WITH(NOLOCK) WHERE Cust1.SesId=@SesId)  
   BEGIN  
      UPDATE Cust1
         SET Cust1.OrderXml=@CenXml  
        FROM Cust1 INNER JOIN Cust ON Cust1.CustomerId=Cust.RepCustId  
       WHERE Cust1.CustomerId=@RCustId
   END  
ELSE  
   BEGIN  
      INSERT INTO Cust1(CustomerId,SessionId,OrderDateTime,colXml)
      VALUES(@RCustId,@SesId,GETDATE(),@CenXml);  
   END
Error:
Everytime variable recordsAffected set to -1 I want to set it 1 if any transaction has been made.
[Edit]Code block added, shouting removed[/Edit]
Posted 2-Apr-13 4:35am
Edited 2-Apr-13 6:59am
v4
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi,

Have a look here:
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx[^]
For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

A Stored Procedure is another type of statement, so the return value is -1.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

You could add a simple
IF @@ERROR <> 0 BEGIN RETURN @@ERROR END ELSE RETURN 1
statement to force a return of 1 when successful.
  Permalink  
Comments
Member 8090436 at 3-Apr-13 5:33am
   
Hi,

I got solution onthis.

I did SET NOCOUNT ON

To

SET NOCOUNT OFF and problem got resolved!!

Many Thanks to all....

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 345
1 Maciej Los 290
2 DamithSL 230
3 Peter Leow 181
4 OriginalGriff 167
0 Sergey Alexandrovich Kryukov 335
1 Maciej Los 290
2 DamithSL 220
3 Peter Leow 181
4 OriginalGriff 167


Advertise | Privacy | Mobile
Web03 | 2.8.150520.1 | Last Updated 2 Apr 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100