Click here to Skip to main content
15,885,309 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I m trying to update the record its giving error Error converting data type nvarchar to bit

What I have tried:

ALTER Procedure [dbo].[sp_Insert_UpDateTypeTable]
(
    @AppNo nvarchar(max),
	@Unit VarChar(10),
	@Post VarChar(10),
	@MPR_RejectionReason MPR_RejectionReason READONLY,
	@MPR_ScrutinyDocument MPR_ScrutinyDocument READONLY,
	@DV_Education_ID int,
	@DV_Type_ID int=0,
	@DV_Elligible_NonElligible bit=0,
	@DV_FromNaxaliteArea bit=0,	
	@DV_Hsc_Ssc_7th_Marks int=0,
	@DV_Cast nvarchar(max)=null,
	@DV_Category_ID int=0,
	@DV_CreatedBy uniqueidentifier= null--,
	-
	
)
As
Begin


	Declare @SQL varchar(Max), @Columns varchar(Max), @quote char(1)=char(39),@unq uniqueidentifier
	
	set @unq=NEWID()

	Create Table #MPR_RejectionReason
	(
		ApplicationNo nvarchar(50) Not Null,
		DocRejectionReason_ID int not Null
	)

	Insert Into #MPR_RejectionReason
	select * from @MPR_RejectionReason

	Create Table #MPR_ScrutinyDocument
	(
		ApplicationNo nvarchar(50) Not Null,
		[ScrutinyDocument_ID] int not Null,
		IsExist bit null,
		Invalid bit null,
		Remarks nvarchar(max) null
	)

	Insert Into #MPR_ScrutinyDocument
	select * from @MPR_ScrutinyDocument


	Select @Columns = N'Set DV_Education_ID =' + convert(varchar(20),@DV_Education_ID) + ',
		DV_Type_ID ='+ convert(varchar(20),@DV_Type_ID) +',
		DV_Elligible_NonElligible ='+  convert(varchar(20),@DV_Elligible_NonElligible) +',
		DV_FromNaxaliteArea ='+  convert(varchar(20),@DV_FromNaxaliteArea)+',
		DV_Hsc_Ssc_7th_Marks ='+ convert(varchar,@DV_Hsc_Ssc_7th_Marks) +',
		DV_Cast =' + @quote + @DV_Cast + @quote +',
		DV_Category_ID ='+ convert(varchar(20),@DV_Category_ID) --+',
		--DV_CreatedBy =
  --      (
  --          CASE DV_CreatedBy
  --              WHEN
  --                  DV_CreatedBy is null
  --              THEN
  --                  '+@quote+convert(varchar(50),@DV_CreatedBy)+@quote+'
  --              ELSE
  --                  DV_CreatedBy
  --          END
  --      )'+',

		--DV_CreatedOn=
		--(
  --          CASE DV_CreatedOn
  --              WHEN
  --                  DV_CreatedOn is null
  --              THEN
  --                  getdate()
  --              ELSE
  --                  DV_CreatedOn
  --          END
  --      ),

		


		--DV_UpdatedBy=
  --          (CASE 
  --              WHEN
  --                  DV_CreatedBy is null
  --              THEN
  --                 DV_UpdatedBy
  --              ELSE
  --                '+@quote+ convert(varchar(50),@DV_CreatedBy)+@quote+'
		--		END'
  --      +',

		--DV_UpdatedOn=
  --         ( CASE 
  --              WHEN
  --                  CreatedOn is null
  --              THEN
  --                 DV_UpdatedOn
  --              ELSE
  --                getdate()
  --          END
  --      )'
		

		
		Select @SQL=N'/*Main Table*/
				Update ' + TableName + ' ' + @Columns + ' 
				Where AppNo=' + @quote + @AppNo + @quote + '

				/*Delete Tran tables*/
				Delete From ' + TableName + '_RejectionReason
				Where AppNo=' + @quote + @AppNo + @quote + '
				
				Delete From ' + TableName + '_ScrutinyDocument
				Where AppNo=' + @quote + @AppNo + @quote + '

				/*Insert Tran tables*/
				INSERT INTO ' + TableName + '_RejectionReason
				select * from #MPR_RejectionReason '+


				'INSERT INTO ' + TableName + '_ScrutinyDocument
				select * from #MPR_ScrutinyDocument' +

				'/*Insert History tables*/'+

               
				'Insert INTO MPR_DocVeriHistory(Unq,AppNo,Unit,Post,DV_Education_ID,DV_FromNaxaliteArea,DV_Hsc_Ssc_7th_Marks,DV_CreatedBy,DV_CreatedOn) values'+
				'('+@quote+convert(varchar(50),@unq) + @quote+',N'+@quote+@AppNo+@quote+','+@quote+@Unit+@quote+','+@quote+@Post+@quote+','+@DV_Education_ID+','+@DV_FromNaxaliteArea+','+@DV_Hsc_Ssc_7th_Marks+','+@quote+convert(varchar(50),@DV_CreatedBy)+@quote+',getdate())'+


				
				'Insert INTO MPR_ScrutinyDocumentHistory(Unq, AppNo, ScrutinyDocument_ID, IsExist, Invalid, Remarks)
				select'+@quote +convert(varchar(50),@unq) + @quote+', AppNo, ScrutinyDocument_ID, IsExist, Invalid, Remarks from ' + TableName + '_ScrutinyDocument
				Where AppNo=' + @quote + @AppNo + @quote +

				'Insert INTO MPR_RejectionReasonHistory(Unq, AppNo, DocRejectionReason_ID)
				select'+@quote +convert(varchar(50),@unq) + @quote+', AppNo, DocRejectionReason_ID from ' + TableName + '_RejectionReason
				Where AppNo=' + @quote + @AppNo + @quote

 
	   	from MPR_TableList where Unit='01' and Post= '01'
		print @Columns
		print @SQL
	Exec(@SQL)
	DROP table #MPR_RejectionReason
	DROP table #MPR_ScrutinyDocument

End
Posted
Updated 6-Mar-17 11:15am
Comments
Afzaal Ahmad Zeeshan 5-Mar-17 6:11am    
Why do you want to convert nvarchar to bit?

Somehow, you can try to convert nvarchar to int, then to bit. That would work.
Graeme_Grant 5-Mar-17 6:34am    
nvarchar(max) to bit? Bit is a Yes/No, On/Off, True/False state value. You can't stuff an elephant into your back pocket.

What are you trying/wanting to do?
Tomas Takac 5-Mar-17 7:53am    
You should first find out which statement is failing.
Kornfeld Eliyahu Peter 5-Mar-17 8:09am    
What is the value of that NVARCHAR you try to convert?
Bryian Tan 5-Mar-17 17:08pm    
which line is throwing the error?

1 solution

First of all you should change the row

SQL
DV_FromNaxaliteArea ='+  convert(varchar(20),@DV_FromNaxaliteArea)+',

to
SQL
DV_FromNaxaliteArea ='+  convert(bit,@DV_FromNaxaliteArea)+',

Also ensure that the content of @DV_FromNaxaliteArea is something that can be converted to bit which may only contain 'NULL', '0' or '1'.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900