Conversion failed when converting the varchar value xx to int

Question

Post reply

Conversion failed when converting the varchar value xx to int

joe pinder

Valued Member

Points: 58
More actions
September 20, 2007 at 3:27 pm

#91349

Problem hope someone can help me.
I am getting this error message when trying to update a row through an asp page:
I am using visual studio 2005 and sql2005
Conversion failed when converting the varchar value '@JobLevel' to data type int.
However this doesnt make sense. joblevel parameter being passed in is int not varchar. Here is my code:
newParameter = newUpdateCommand.Parameters.Add(
"@CollegueNumber", SqlDbType.VarChar);
newParameter.Direction =
ParameterDirection.Input;
newParameter.SourceColumn =
"CollegueNumber";
newParameter = newUpdateCommand.Parameters.Add(
"@FirstName", SqlDbType.VarChar);
newParameter.Direction =
ParameterDirection.Input;
newParameter.SourceColumn =
"FirstName";
newParameter = newUpdateCommand.Parameters.Add(
"@LastName", SqlDbType.VarChar);
newParameter.Direction =
ParameterDirection.Input;
newParameter.SourceColumn =
"LastName";
newParameter = newUpdateCommand.Parameters.Add(
"@JobLevel", SqlDbType.Int);
newParameter.Direction =
ParameterDirection.Input;
newParameter.SourceColumn =
"JobLevel";
newParameter = newUpdateCommand.Parameters.Add(
"@AccountType", SqlDbType.VarChar);
newParameter.Direction =
ParameterDirection.Input;
newParameter.SourceColumn =
"AccountType";
sp is:
set
ANSI_NULLS ON
set
QUOTED_IDENTIFIER ON
go

ALTER
PROCEDURE [dbo].[UpdateCollegues]
@CollegueNumber
int,
@FirstName
varchar(20),
@LastName
varchar(20),
@JobLevel
int,
@AccountType
varchar(10)
AS
BEGIN
SET NOCOUNT ON;
update
Collegues set
FirstName
= '@FirstName',
LastName
= '@LastName',
JobLevel
= '@JobLevel',
AccountType
= '@AccountType'
where
CollegueNumber = '@CollegueNumber'
END
Can anyone help me here, not sure what I have done wrong, thanks!!

Regards

Joe

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

Take the quotes off:

FirstName = @FirstName,

LastName

= @LastName,

JobLevel

= @JobLevel,

AccountType

= @AccountType

where

CollegueNumber = @CollegueNumber

And then again, I might be wrong ...
David Webb

joe pinder Valued Member Points: 58 More actions · Answer 2

that working now, thanks for your v quick response!!!!!!!!!!!!

Regards

Joe

mengus Ten Centuries Points: 1187 More actions · Answer 3

joe pinder,

Was that stored procedure code generated, by say a tool like CodeSmith? If so, you may need to modify the template, so that in the future it can be use correctly.

Note, please also be aware of copying SQL code from SQL Server Profiler. When using a built-in OR Mapper, such as Wilson OR Mapper, there will be code generated as dynamic sql with parameters. However, the code will have to be verified, especially checking/removing the quotes and double quotes, which may be unsuitable for stored procedure DDL.

Regards,

Wameng Vang

MCTS