Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Development Tools
 ASP.NET
 while insertion of data taking null value....

Author  Topic 

avijit111@gmail.com
Yak Posting Veteran

55 Posts

Posted - 2010-05-27 : 09:40:38
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

22859 Posts

Posted - 2010-05-27 : 09:57:21
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
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-27 : 10:25:44
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

22859 Posts

Posted - 2010-05-27 : 11:50:53
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

22864 Posts

Posted - 2010-05-27 : 11:59:44
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
   

- Advertisement -