Thread: Conversion failed when converting the varchar error

hi

below is my procedure i m getting conversion error .how to resolve this?

Code:

CREATE PROCEDURE pr_ProductGLCategoryData_Import

@vnMemberID INT, 

@vnSupplierID INT,

@vsProductCode VARCHAR(50),

@vnIsStock bit=0,

@vsGLCategoryCode VARCHAR(1000),

@vsResult VARCHAR(1000) OUTPUT

AS

 

SET NOCOUNT ON

SET @vnIsStock = IsNull(@vnIsStock,0)

Set @vsResult = ''

Set @vsResult = 'Supplier Code / ' + cast(IsNull(str(@vnSupplierID),'<blank>') AS Varchar) + CHAR(10)

getting this error

Msg 245, Level 16, State 1, Procedure pr_ProductGLCategoryData_Import, Line 63

Conversion failed when converting the varchar value 'Supplier Code / 11

' to data type int.

try this:

Code:

SET @vsResult = 'SupplierCode /' + CAST(ISNULL(@vnSupplierID,'<blank>') As VarChar) + CHAR(10)

I tested the above and worked fine for me.

thanks for reply.actaully problem was with Return statement.if i use like this
Return @vsResult then it give error.if i use only return statement then it works.

so now its working.

Originally Posted by guddu

thanks for reply.actaully problem was with Return statement.if i use like this
Return @vsResult then it give error.if i use only return statement then it works.

so now its working.

Ahhh that's because you can only Return integers, thus having to set @vsResult as an OUTPUT parameter.

yes u are rite.but now i m facing a new problem in front end.
'SUPPLIER CODE / ' + ISNULL(CONVERT(VARCHAR,@vnSupplierID),'<BLANK>')

procedure returns this string well butin my output from front end <BLANK> tag is not showing.instead it show space i meant not showing <blank> tag.

why?

Originally Posted by guddu

yes u are rite.but now i m facing a new problem in front end.
'SUPPLIER CODE / ' + ISNULL(CONVERT(VARCHAR,@vnSupplierID),'<BLANK>')

procedure returns this string well butin my output from front end <BLANK> tag is not showing.instead it show space i meant not showing <blank> tag.

why?

Try using Server.HtmlEncode on the output and see if it displays then.

woh again sorry jmh.its in vb dear.

Okay, looking at this again, the only way <blank> will be returned is if @vnSupplierID is NULL. So, if this isn't being passed to the procedure as NULL, then it won't return <blank> in the string.

that is no an issue.actuall when i execute procedure at analyzer its working and in case of null it returns <blank> tag.but in my vb output it does not showing that tag.

Are you sure that the variable that you are passing to the procedure in VB actually is a NULL value? If the procedure works as expected, then this is no longer a SQL problem. Post a question in the Visual Basic forum with the relevant code.