Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
problem in update command

Answered problem in update command

  • Tuesday, May 01, 2012 7:43 AM
     
     
    Sign In to Vote
    0


     i use this code byt doesnt work

                command.CommandText=  @"BEGIN TRAN     update claimExtensionBase  set claimExtensionBase.claim_no =" + cno + " where  claimExtensionBase.tempclaimno =" + tempno+
               " update claimExtensionBase  set claim_productExtensionBase.parent_claim_no =" + cno + " where parent_claim_no =" + tempno+
                    "update claimExtensionBase  set claim_itemsExtensionBase.parent_claim =" + cno + " where  claimExtensionBase.tempclaim =" + tempno + " COMMIT TRAN";

     

All Replies

  • Tuesday, May 01, 2012 7:48 AM
    Answerer
     
     Proposed
    Sign In to Vote
    0

    What error do you get? Why not using simple stored procedure for such things...

    CREATE PROCEDURE sp

    @cno INT,

    @tempno INT

    SET XACT_ABORT ON 
    SET NOCOUNT ON

    AS

    BEGIN TRAN

    UPDATE claimExtensionBase   SET claim_no =@cno WHERE tempclaimno =@tempno

    .......................

    COMMIT TRAN

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

     
  • Tuesday, May 01, 2012 8:54 AM
     
     
    Sign In to Vote
    0
    tank you bt i dont want to use store procedure. what you suggest?
     
  • Tuesday, May 01, 2012 9:25 AM
    Answerer
     
     
    Sign In to Vote
    0
    Again, what error do you get?

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

     
  • Tuesday, May 01, 2012 10:04 AM
     
     
    Sign In to Vote
    0
    {"Conversion failed when converting the nvarchar value 'null' to data type int."}
     
  • Tuesday, May 01, 2012 10:13 AM
     
     
    Sign In to Vote
    0

    Hi,

    keep a look you can get the result:

    http://stackoverflow.com/questions/428933/conversion-failed-when-converting-the-varchar-value-null-to-data-type-int

    http://stackoverflow.com/questions/2146276/conversion-failed-when-converting-the-nvarchar-value-to-int

    Nagendra


     
  • Tuesday, May 01, 2012 3:11 PM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    0

    Don't attempt to concatenate values into the command text. This is a very bad practice and leads to sql injection attacks. The simplest solution will be

    command.CommandText = @"BEGIN TRAN 
    update claimExtensionBase  
      set claim_no =@Claim_no where tempclaimno =@TempNo
    update claim_productExtensionBase
      set parent_claim_no =@Claim_no where parent_claim_no =@Tempno
    update claimExtensionBase  set claim_itemsExtensionBase.parent_claim =@Claim_no where  claimExtensionBase.tempclaim =@TempNo 
 COMMIT TRAN";

    and set command.Parameters accordingly.

    I am not sure I understand your update statements. Do you have 3 tables you want to update? How are they related?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog