locked
add two columns value into third column RRS feed

  • Question

  • I have this table tblMenu  which has values like

    menuCode as PK

    moduleCode as FK from tblmodule

    parentCode 

    What I want is if the value in textbox for menucode is 01

    and in moduleCode is 11  when I press the save button these both


    code will join like this 11-01 or (1101) and save in parentCode..

    what query should I write

    Thanks

    here is the table

    CREATE TABLE [dbo].[tblMenu](
    	[menuCode] [varchar](150) NOT NULL,
    	[moduleCode] [varchar](150) NULL,
    	[menuName] [varchar](150) NULL,
    	[menuSource] [nvarchar](500) NULL,
    	[active] [bit] NULL,
    	[ParentCode] [varchar](150) NULL


    Friday, March 21, 2014 10:58 AM

Answers

  • for that easiest thing you can do is to just make parentcode as a computed column.

    In that case table create script would be as below

    CREATE TABLE [dbo].[tblMenu](
    	[menuCode] [varchar](150) NOT NULL,
    	[moduleCode] [varchar](150) NULL,
    	[menuName] [varchar](150) NULL,
    	[menuSource] [nvarchar](500) NULL,
    	[active] [bit] NULL,
    	[ParentCode] AS moduleCode + '-' + menuCode,
    ...
    )

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, March 21, 2014 11:41 AM
  • Not sure, how did you do that, please check the below:

    CREATE TABLE [dbo].[tblMenu](
    	[menuCode] [varchar](150) NOT NULL Primary key,
    	[moduleCode] [varchar](150) NULL,
    	[menuName] [varchar](150) NULL,
    	[menuSource] [nvarchar](500) NULL,
    	[active] [bit] NULL,
    	[ParentCode] AS moduleCode + '-' + menuCode,
    )
    
    Insert into tblMenu Select '01','11','abcmenu','abcsource',1
    
    Select * From tblMenu
    
    Drop table tblmenu

    Friday, March 21, 2014 12:30 PM
    Answerer

All replies

  • for that easiest thing you can do is to just make parentcode as a computed column.

    In that case table create script would be as below

    CREATE TABLE [dbo].[tblMenu](
    	[menuCode] [varchar](150) NOT NULL,
    	[moduleCode] [varchar](150) NULL,
    	[menuName] [varchar](150) NULL,
    	[menuSource] [nvarchar](500) NULL,
    	[active] [bit] NULL,
    	[ParentCode] AS moduleCode + '-' + menuCode,
    ...
    )

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, March 21, 2014 11:41 AM
  • thanks bro I'd done that before but when I change menuCode into primary key every thing just disappear
    Friday, March 21, 2014 12:22 PM
  • Not sure, how did you do that, please check the below:

    CREATE TABLE [dbo].[tblMenu](
    	[menuCode] [varchar](150) NOT NULL Primary key,
    	[moduleCode] [varchar](150) NULL,
    	[menuName] [varchar](150) NULL,
    	[menuSource] [nvarchar](500) NULL,
    	[active] [bit] NULL,
    	[ParentCode] AS moduleCode + '-' + menuCode,
    )
    
    Insert into tblMenu Select '01','11','abcmenu','abcsource',1
    
    Select * From tblMenu
    
    Drop table tblmenu

    Friday, March 21, 2014 12:30 PM
    Answerer
  • Oh sorry bro I just right clicked it and marked that as primary and it changed all the settings of the table. thank you too
    Friday, March 21, 2014 12:35 PM
  • please wait

    When I open and close it it gives me this error

    Table 'tblMenu' has changed:
    - Data type property of column 'ParentCode' was changed and will be modified.
    - Formula of column 'ParentCode' was changed and will be modified.
    - Index 'PK_tblMenu_1' was deleted and will be recreated.
    - Index 'PK__tblMenu__327329004BAC3F29' was added and will be deleted.

    Friday, March 21, 2014 12:38 PM
  • sORRY iT wORKS fINE nOW THANK YOU ALL AND i MARKED YOUR ANSWERS AS ANSWERS

    tHANKS

    Friday, March 21, 2014 12:41 PM
  • >[ParentCode]AS moduleCode + '-' +menuCode,

    What is the value of such a computed column? The concatenation can be done in the query.

    When you design a computed column it must have some measurable benefits:

    http://technet.microsoft.com/en-us/library/ms188300.aspx

    Discussion: http://social.msdn.microsoft.com/Forums/en-US/a6eacddb-68ae-4576-af77-69c057102d5a/decision-factor-benefits-of-computed-columns?forum=transactsql

    Thanks.


    Kalman Toth Database & OLAP Architect SELECT Video Tutorials 4 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012





    • Edited by Kalman Toth Saturday, March 22, 2014 8:13 AM
    Friday, March 21, 2014 10:25 PM