Conversion failed when converting the varchar value xx to int

• joe pinder

  SSC Rookie

  Points: 28

  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

   

• David Webb-CDS

  SSCoach

  Points: 17398

  More actions

  September 20, 2007 at 4:20 pm

  #735087

  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

  SSC Rookie

  Points: 28

  More actions

  September 20, 2007 at 4:39 pm

  #735090

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

   

  Regards

  Joe

• mengus

  Ten Centuries

  Points: 1187

  More actions

  October 2, 2007 at 6:55 am

  #738115

  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