Type error

[Naoise]

The line...

SET @SelString = 'INSERT INTO tblChartItems (ChartID, ProductItemID, ChartPosition, LastChartPosition, ProductTypeID) VALUES(' + @ChartID + ',' + @ProductID + ',' + @Position + ',' + 0 + ',' + @ProductTypeID +')'

The error...

Syntax error converting the varchar value 'INSERT INTO tblChartItems (ChartID, ProductItemID, ChartPosition, LastChartPosition, ProductTypeID) VALUES(' to a column of data type int.

Types...
@SelString = nchar(500)
@ChartID = Integer
@ProductID = nchar
@Position = Integer
@ProductTypeID = Integer

I have tried using convert(nchar(5),@ChartID) - on the types but the error remains. Do I even need
to convert these. I understand the error but how should I ammend it?

Thanks

 

[r937]

the problem lies not in the sql but in the scripting language you're using

you are trying to set @SelString equal to something

that something consists of a string added to an integer (and that's where the error occurs)

the string: 'INSERT ... + @Position + ','

the integer: 0


i think this should fix it --

SET @SelString = 'INSERT INTO tblChartItems (ChartID, ProductItemID, ChartPosition, LastChartPosition, ProductTypeID) VALUES(' + @ChartID + ',' + @ProductID + ',' + @Position + ', 0 ,' + @ProductTypeID +')'





r937.com | rudy.ca

 

[Naoise]

You are right when you say that the problem is a string and an integer being added and then a type error is thrown, however in the error message

Syntax error converting the varchar value 'INSERT INTO tblChartItems (ChartID, ProductItemID, ChartPosition, LastChartPosition, ProductTypeID) VALUES(' to a column of data type int.


you can see that it has a problem with the first value @ChartID (possibly other args further down also but @ChartID to start with) and this is where I was wondering what to do as I had cast it as an nchar like the @SelString and I still got the above error.

Tried your solution anyway and got the same error. It is definitely something to do with @ChartID and it's type.

Thanks for replies.

 

[r937]

oh yeah

okay, i don't know what scripting language that is, but you'll have a much better chance of getting an answer to your problem if you post the question in the appropriate forum for that language

this forum is for ANSI SQL, i.e. standard SQL that works in all database systems

if it helps, the ANSI SQL operator for concatenation is || not +



r937.com | rudy.ca

 

[Naoise]

I'm writing a stored procedure in MSSQL. || is not recognised, + only I'm afraid. I use asp to call the stored procedure but that not matters not seeing as I have converted the variabes in the stored proc. itself. Stumped.

 

[r937]

stumped --> forum183

r937.com | rudy.ca