Query regarding Update the data using application

  • Smash125

    Hall of Fame

    Points: 3527

    SELECT ContactID,FirstName,MiddleName,LastName,Description FROM Contact

    Contact table contains 4 columns as explained. in the application there is one tab called Contact where it displays Above information

    and description is non editable.

    Now the new requirement has come user can update the description information and save information in a new column say 'Description1'

    that means new column needs to be added in the db and also necessary changes needs to be done at the application side

    For ex :SELECT ContactID,FirstName,MiddleName,LastName,Description,Description1 FROM Contact

    Now when user views the contact table it should display description info by default from 'description' table.

    If he edits he should see edited data from 'description1' table.

    Can any body guide me please and send me sample update query please, and the logic should if updated data is there display that data from

    'description1' table other wise display from 'description' table

  • MyDoggieJessie

    SSC-Forever

    Points: 44276

    You could try: ALTER TABLE [YOURTABLE] ADD [Description1] varchar(255) NULL

    And something simple like:UPDATE [YOURTABLE]

    SET Description1 = 'Your new description text'

    WHERE ContactID = @ContactID

    Then to get the proper description field:SELECT

    ContactID, FirstName, MiddleName, LastName,

    CASE WHEN (Description1 IS NULL) THEN Description ELSE Description1 END [Description]

    FROM [YOURTABLE]

    ______________________________________________________________________________
    Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • Sean Lange

    SSC Guru

    Points: 286496

    You could shorten this slightly with ISNULL instead of a case expression.

    ISNULL(Description1, [Description]) as [Description]

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Oblivion

    Valued Member

    Points: 51

    Using the ISNULL() method you will also have to account for the user deleting description1 or effectively setting it to ''. You will need to pick up that change at application level and set the field back to NULL where necessary.

  • Sean Lange

    SSC Guru

    Points: 286496

    Oblivion (4/7/2014)


    Using the ISNULL() method you will also have to account for the user deleting description1 or effectively setting it to ''. You will need to pick up that change at application level and set the field back to NULL where necessary.

    Or just add a NULLIF.

    ISNULL(NULLIF(Description1, ''), [Description]) as [Description]

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Oblivion

    Valued Member

    Points: 51

    Sean Lange (4/7/2014)


    Oblivion (4/7/2014)


    Using the ISNULL() method you will also have to account for the user deleting description1 or effectively setting it to ''. You will need to pick up that change at application level and set the field back to NULL where necessary.

    Or just add a NULLIF.

    ISNULL(NULLIF(Description1, ''), [Description]) as [Description]

    Good shout Sean 🙂

  • Smash125

    Hall of Fame

    Points: 3527

    Sorry to disturb you guys. Regarding update

    UPDATE [YOURTABLE]

    SET Description1 = 'Your new description text'

    WHERE ContactID = @ContactID

    How can i bind this with the code. I am still confused with

    SET Description1 = 'Your new description text'

    since we are binding this with the code. I have very little idea regarding this

  • MyDoggieJessie

    SSC-Forever

    Points: 44276

    You said users couldn't update the Description field, so you were adding a new column to the table named Description1. Correct?

    So...in order to successfully use an update statement, you need to assign a value to the column. In the case 'Your new description text' = this means, whatever 'text' the user has entered as the description via the app.

    ______________________________________________________________________________
    Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • MyDoggieJessie

    SSC-Forever

    Points: 44276

    Please post your actual update statement that you are "binding" from the application - we may be able to better help...

    ______________________________________________________________________________
    Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • Smash125

    Hall of Fame

    Points: 3527

    This will be new update statement that will be binded. In my case

    UPDATE Projects Set Description = '(Should this be blank or what should i input) '

    WHERE ProjectId=@ProjectId

    First What i have done added new column

    1.ALTER TABLE [Core.Programs] ADD [DescriptionManual] varchar(2000) NULL

    2. Added the condition what u guys have given to me

    SELECT P.ProjectId

    ,P.ProjectName AS NAME

    ,P.ProjectName AS ProgramName

    ,V.kEyCode AS ProgramKeycode

    ,V.ProgramExecType AS ProgramExecType

    ,P.SAPProjectCode AS KeyCode

    ,ISNULL(NULLIF(ManualDescription, ''), [Description]) as [Description]

    ,P.[Description] AS ProgramScope

    ,'ProjectLevel' AS [Level]

    ,BusinessSegment

    ,T2CBT

    ,SBU

    ,@Month AS [Month]

    ,@Year AS [Year]

    FROM Core.v_ProgramDetails AS V

    INNER JOIN Core.Projects AS P ON V.ProgramId = P.ProgramId

    --WHERE V.ProgramId = @ProgramIds

    AND P.ProjectId IN (

    SELECT Value

    FROM Util.fn_Split(@ProjectIds, ',')

    )

    AND P.IsActive = @Active

    3. My only query now update statement how it should look

    UPDATE [Core.Projects]

    SET ManualDescription= 'Your new description text'

    WHERE ProjectId= @P.ProjectId

  • MyDoggieJessie

    SSC-Forever

    Points: 44276

    You are adding a column "DescriptionManual", yet in your queries referencing "ManualDescription" - am I missing something or this just a typo?

    Your update should look something like:

    UPDATE [Core.Projects]

    SET ManualDescription= [Whatever the user entered from the application]

    WHERE ProjectId= @P.ProjectId

    Assuming you are passing variables from the application layer to a stored-procedure or query within SQL Server, you would assign the text the user entered into the DescriptionManual/ManualDescription field to the variable itself.

    Example:

    UPDATE [Core.Projects]

    SET ManualDescription= @NewDescription

    WHERE ProjectId= @P.ProjectId

    Where @NewDescription = whatever text the user entered.

    Make sense?

    ______________________________________________________________________________
    Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • Smash125

    Hall of Fame

    Points: 3527

    Sorry, it's typo mistake from my side

    I have come up with this stored procedure to be binded with the application layer

    CREATE PROCEDURE UpdateDescription

    @ManualDescription VARCHAR(2000),

    @ProjectId INT

    AS

    BEGIN

    UPDATE [Core.Projects]

    SET ManualDescription= @ManualDescription

    WHERE ProjectId= @ProjectId

    END

    Correct if am wrong

  • MyDoggieJessie

    SSC-Forever

    Points: 44276

    Looks good!

    ______________________________________________________________________________
    Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply