SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Development Tools
 ASP.NET
 while insertion of data taking null value....
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

avijit111@gmail.com
Yak Posting Veteran

India
55 Posts

Posted - 05/27/2010 :  09:40:38  Show Profile  Reply with Quote
while insertion of data taking null value in @company_name...
i am seng my code and tables ddl also...

please check once.....


/***************************************************************************************/
-- EXEC INSERT_EMPLOYEE_DETAIL '02260','Mr.','AVIJIT','CCL','NAV','info. tech.','15','avijit111@gmail.com','4237','4238','','',''
--Grant all on INSERT_EMPLOYEE_DETAIL to public
alter PROCEDURE INSERT_EMPLOYEE_DETAIL
(
@ECODE VARCHAR(10),
@PREF VARCHAR(5) ,
@ENAME VARCHAR(50),
@COMPANY_NAME varchar(20),
@LOCATION_NAME varchar(50),
@DEPARTMENT_NAME varchar(50),
@DEPT_CODE VARCHAR(20),
@EMAIL_ID VARCHAR(50),
@OFFICENO1 VARCHAR(20),
@OFFICENO2 VARCHAR(20),
@RESIDENCE VARCHAR(20),
@CELLNO1 VARCHAR(20),
@CELLNO2 VARCHAR(20)
)



AS
BEGIN


DECLARE @CATEGORY VARCHAR(20),
@LOCCODE VARCHAR(50)

if @Ecode = '' or @Ecode = NULL
begin
SELECT @LOCCODE =

CASE
WHEN @LOCATION_NAME = 'NAV' THEN 'CCL'
WHEN @LOCATION_NAME = 'bad' THEN 'BAD'




END

select @Ecode = (SELECT (MAX(SR_NO) + 1) FROM CCL_EMPLOYEE_MASTER)
SELECT @ECODE = (SELECT @LOCCODE+@ECODE)
SELECT @CATEGORY = 'UNEMPLOYED'



end
else
begin
SELECT @LOCCODE =
CASE
WHEN @LOCATION_NAME = 'NAV' THEN 'CCL'
WHEN @LOCATION_NAME = 'bad' THEN 'BAD'

END
SELECT @CATEGORY = 'EMPLOYED'


end



select @LOCATION_NAME = ltrim(rtrim(@LOCATION_NAME))
select @DEPARTMENT_NAME = ltrim(rtrim(@DEPARTMENT_NAME))
select @COMPANY_NAME = ltrim(rtrim(@COMPANY_NAME))




set nocount on




if exists (select 'x'from CCL_EMPLOYEE_MASTER)(nolock)
where ecode = @ECODE)

begin
select '88888','The Name You Have Entered Already Exist'
end

else
begin

INSERT INTO CCL_EMPLOYEE_MASTER)(ECODE,PREF,ename,CCODE,LOCN_CODE,Dept_Code,EmailID,CATEGORY) --,Admin_Userid)
SELECT @ECODE,@PREF,@ENAME,A.CCODE,B.LOCN_CODE,C.DEPT_CODE,@EMAIL_ID,@CATEGORY

FROM CCL_COMPANY_MASTER A,
CCL_LOCATION B,
CCL_DEPARTMENT C

WHERE A.CNAME = @COMPANY_NAME
AND B.LOCN_NAME = @LOCATION_NAME
AND C.DEPT_NAME = @DEPARTMENT_NAME

end




DECLARE @MAXSR VARCHAR(10)
SELECT @MAXSR = MAX(Sr_No)FROM CCL_EMPLOYEE_MASTER


if exists (select 'x'from CCL_PHONENO (nolock)
where ecode = @ECODE)

begin
select '88888','The Name You Have Entered Already Exist'
end

else
begin

INSERT INTO CCL_PHONENO(CCode,LOCN_CODE,DEPT_CODE,ECode,OfficeNo1,OfficeNo2,ResidanceNo,CellNo1,CellNo2)
SELECT A.CCODE,A.LOCN_CODE,A.DEPT_CODE,A.ECODE,@OFFICENO1,@OFFICENO2,@RESIDENCE,@CELLNO1,@CELLNO2
FROM CCL_EMPLOYEE_MASTER A
WHERE A.ENAME = @ENAME
AND A.Sr_No = @MAXSR

end






-- set nocount off
END



/**************************************************************************************/







/*CREATE TABLE [dbo].[CCL_EMPLOYEE_MASTER] (
[Sr_No] [int] IDENTITY (1, 1) NOT NULL ,
[company] [varchar] (10) COLLATE SQL_1xCompat_CP850_CI_AS NOT NULL ,
[ECode] [varchar] (50) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[PREF] [varchar] (5) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[EName] [varchar] (50) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[CCode] [varchar] (4) COLLATE SQL_1xCompat_CP850_CI_AS NOT NULL ,
[Locn_Code] [int] NOT NULL ,
[Dept_Code] [int] NULL ,
[EmailID] [varchar] (40) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[Category] [varchar] (20) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[Admin_Userid] [varchar] (50) COLLATE SQL_1xCompat_CP850_CI_AS NULL
) ON [PRIMARY]
GO*/

/*
CREATE TABLE [dbo].[CCL_Phoneno] (
[ID] [int] NULL ,
[CCode] [varchar] (4) COLLATE SQL_1xCompat_CP850_CI_AS NOT NULL ,
[Locn_Code] [int] NOT NULL ,
[Dept_Code] [int] NULL ,
[ECode] [varchar] (10) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[OfficeNo1] [varchar] (15) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[OfficeNo2] [varchar] (15) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[ResidanceNo] [varchar] (25) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[CellNo1] [varchar] (25) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[CellNo2] [varchar] (25) COLLATE SQL_1xCompat_CP850_CI_AS NULL
) ON [PRIMARY]
GO
*/

/*
CREATE TABLE [dbo].[CCL_LOCATION] (
[LOCN_CODE] [int] IDENTITY (1, 1) NOT NULL ,
[LOCN_NAME] [varchar] (50) COLLATE SQL_1xCompat_CP850_CI_AS NULL
) ON [PRIMARY]
GO
*/

/*
CREATE TABLE [dbo].[CCL_DEPARTMENT] (
[DEPT_CODE] [int] IDENTITY (1, 1) NOT NULL ,
[DEPT_NAME] [varchar] (50) COLLATE SQL_1xCompat_CP850_CI_AS NULL
) ON [PRIMARY]
GO

*/

/*
CREATE TABLE [dbo].[CCL_COMPANY_MASTER] (
[CCODE] [varchar] (4) COLLATE SQL_1xCompat_CP850_CI_AS NOT NULL ,
[CNAME] [varchar] (30) COLLATE SQL_1xCompat_CP850_CI_AS NULL
) ON [PRIMARY]
GO
*/

regards with lot of Thanks....

Kristen
Test

United Kingdom
22415 Posts

Posted - 05/27/2010 :  09:57:21  Show Profile  Reply with Quote
I took out all the blank lines and, given that your code already had tabs and spaces in it, I put [CODE] tags around it - you could do that yourself next time to save us the bother and make your code more readable - although the formatting is still atrocious

/***************************************************************************************/
-- EXEC INSERT_EMPLOYEE_DETAIL '02260','Mr.','AVIJIT','CCL','NAV','info. tech.','15','avijit111@gmail.com','4237','4238','','',''
 --Grant all on INSERT_EMPLOYEE_DETAIL to public
alter  PROCEDURE INSERT_EMPLOYEE_DETAIL
 (
 @ECODE           VARCHAR(10),
 @PREF            VARCHAR(5) ,  
 @ENAME           VARCHAR(50),  
 @COMPANY_NAME    varchar(20),  
 @LOCATION_NAME   varchar(50),  
 @DEPARTMENT_NAME varchar(50),
 @DEPT_CODE       VARCHAR(20),  
 @EMAIL_ID        VARCHAR(50),
 @OFFICENO1       VARCHAR(20),
 @OFFICENO2       VARCHAR(20),
 @RESIDENCE       VARCHAR(20),
 @CELLNO1         VARCHAR(20),
 @CELLNO2	  VARCHAR(20)
 )  
AS   
BEGIN  
	 DECLARE @CATEGORY   VARCHAR(20),
	         @LOCCODE    VARCHAR(50)
 
            if @Ecode = '' or @Ecode = NULL
            begin
	 	SELECT @LOCCODE =
                  CASE  
			WHEN @LOCATION_NAME = 'NAV' THEN 'CCL'
			WHEN @LOCATION_NAME = 'bad' THEN 'BAD'
	    END
		select @Ecode = (SELECT (MAX(SR_NO) + 1) FROM CCL_EMPLOYEE_MASTER)
	 	SELECT @ECODE = (SELECT @LOCCODE+@ECODE)
		SELECT @CATEGORY = 'UNEMPLOYED'	
end
 	    else 
	    begin
			SELECT @LOCCODE =
			CASE  
					WHEN @LOCATION_NAME = 'NAV' THEN 'CCL'
					WHEN @LOCATION_NAME = 'bad' THEN 'BAD'
	     END
			SELECT @CATEGORY  = 'EMPLOYED'
end
			select @LOCATION_NAME   = ltrim(rtrim(@LOCATION_NAME))   
			select @DEPARTMENT_NAME = ltrim(rtrim(@DEPARTMENT_NAME))   
			select @COMPANY_NAME    = ltrim(rtrim(@COMPANY_NAME))
  set nocount on
        if exists (select 'x'from CCL_EMPLOYEE_MASTER)(nolock)
                   where ecode = @ECODE)
	           begin
	                 select '88888','The Name You Have Entered Already Exist'
	           end
        else 
                  begin 
			INSERT INTO CCL_EMPLOYEE_MASTER)(ECODE,PREF,ename,CCODE,LOCN_CODE,Dept_Code,EmailID,CATEGORY)
			              --,Admin_Userid)  
			SELECT      @ECODE,@PREF,@ENAME,A.CCODE,B.LOCN_CODE,C.DEPT_CODE,@EMAIL_ID,@CATEGORY 
			FROM        CCL_COMPANY_MASTER A,
			            CCL_LOCATION       B, 
			            CCL_DEPARTMENT     C 
			WHERE  A.CNAME       = @COMPANY_NAME 
                        AND    B.LOCN_NAME   = @LOCATION_NAME 
                        AND    C.DEPT_NAME   = @DEPARTMENT_NAME       
                   end
			DECLARE @MAXSR   VARCHAR(10)
			SELECT  @MAXSR = MAX(Sr_No)FROM CCL_EMPLOYEE_MASTER 
                  if exists (select 'x'from CCL_PHONENO (nolock)
                             where ecode = @ECODE)
		           begin
		                 select '88888','The Name You Have Entered Already Exist'
		           end
           else 
                  begin
                        INSERT INTO CCL_PHONENO(CCode,LOCN_CODE,DEPT_CODE,ECode,OfficeNo1,OfficeNo2,ResidanceNo,CellNo1,CellNo2)
			SELECT   A.CCODE,A.LOCN_CODE,A.DEPT_CODE,A.ECODE,@OFFICENO1,@OFFICENO2,@RESIDENCE,@CELLNO1,@CELLNO2
		        FROM     CCL_EMPLOYEE_MASTER A
			WHERE    A.ENAME = @ENAME
			AND      A.Sr_No = @MAXSR
                    end
-- set nocount off  
END
/**************************************************************************************/

/*CREATE TABLE [dbo].[CCL_EMPLOYEE_MASTER] (
	[Sr_No] [int] IDENTITY (1, 1) NOT NULL ,
	[company] [varchar] (10) COLLATE SQL_1xCompat_CP850_CI_AS NOT NULL ,
	[ECode] [varchar] (50) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
	[PREF] [varchar] (5) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
	[EName] [varchar] (50) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
	[CCode] [varchar] (4) COLLATE SQL_1xCompat_CP850_CI_AS NOT NULL ,
	[Locn_Code] [int] NOT NULL ,
	[Dept_Code] [int] NULL ,
	[EmailID] [varchar] (40) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
	[Category] [varchar] (20) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
	[Admin_Userid] [varchar] (50) COLLATE SQL_1xCompat_CP850_CI_AS NULL 
) ON [PRIMARY]
GO*/

/*
CREATE TABLE [dbo].[CCL_Phoneno] (
	[ID] [int] NULL ,
	[CCode] [varchar] (4) COLLATE SQL_1xCompat_CP850_CI_AS NOT NULL ,
	[Locn_Code] [int] NOT NULL ,
	[Dept_Code] [int] NULL ,
	[ECode] [varchar] (10) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
	[OfficeNo1] [varchar] (15) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
	[OfficeNo2] [varchar] (15) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
	[ResidanceNo] [varchar] (25) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
	[CellNo1] [varchar] (25) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
	[CellNo2] [varchar] (25) COLLATE SQL_1xCompat_CP850_CI_AS NULL 
) ON [PRIMARY]
GO
*/

/*
CREATE TABLE [dbo].[CCL_LOCATION] (
	[LOCN_CODE] [int] IDENTITY (1, 1) NOT NULL ,
	[LOCN_NAME] [varchar] (50) COLLATE SQL_1xCompat_CP850_CI_AS NULL 
) ON [PRIMARY]
GO
*/

/*
CREATE TABLE [dbo].[CCL_DEPARTMENT] (
	[DEPT_CODE] [int] IDENTITY (1, 1) NOT NULL ,
	[DEPT_NAME] [varchar] (50) COLLATE SQL_1xCompat_CP850_CI_AS NULL 
) ON [PRIMARY]
GO

*/

/*
CREATE TABLE [dbo].[CCL_COMPANY_MASTER] (
	[CCODE] [varchar] (4) COLLATE SQL_1xCompat_CP850_CI_AS NOT NULL ,
	[CNAME] [varchar] (30) COLLATE SQL_1xCompat_CP850_CI_AS NULL 
) ON [PRIMARY]
GO
*/
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 05/27/2010 :  10:25:44  Show Profile  Visit webfred's Homepage  Reply with Quote
But you have not corrected the wrong parentheses


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 05/27/2010 :  11:50:53  Show Profile  Reply with Quote
TBH having formatted it I couldn't understand the question related to the code I was looking at.

Has SQL Team swallowed some parentheses in trying to format something that looks to be an email address into a hyper-link?
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 05/27/2010 :  11:59:44  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by Kristen

TBH having formatted it I couldn't understand the question related to the code I was looking at.

Has SQL Team swallowed some parentheses in trying to format something that looks to be an email address into a hyper-link?


Yes. You need to add some dummy tags
See the difference

tech.','15','avijit111@gmail.com','4237','4238','','',''

tech.','15','avijit111@gmail.com','4237','4238','','',''


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000