April 7, 2014 at 5:26 am
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
April 7, 2014 at 8:07 am
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
April 7, 2014 at 9:41 am
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/
April 7, 2014 at 11:42 am
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.
April 7, 2014 at 12:04 pm
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/
April 7, 2014 at 12:09 pm
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 🙂
April 7, 2014 at 10:19 pm
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
April 7, 2014 at 10:30 pm
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
April 7, 2014 at 10:45 pm
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
April 7, 2014 at 10:56 pm
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
April 7, 2014 at 11:10 pm
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
April 7, 2014 at 11:23 pm
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
April 8, 2014 at 6:11 am
Looks good!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply