Getting Error While Inserting Data to Table through Stored Procedure in C#

Question

Hi I have a table name customers, While i am inserting data in this table through Stored Procedure then i am getting error:

Stored Procedure is as follows:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

alter procedure [dbo].[customerinsert_new]
(@cid int, @full_name varchar(50),@gender varchar(20),@DOB date,@age varchar(50),@marital_status varchar(20),@email varchar(50),@phone varchar(20),
@Country varchar(50),@State varchar(50),@City Varchar(50),@address varchar(100),@Height varchar(50),@weight varchar(50),@BMI varchar(50),    @Subscription int,@referral varchar(50),@joining_date date,@due_date date,
@amount_reccived varchar(50),@amount_left varchar(50),
@c_photo varchar(100),@primary_facility varchar(50),
@occupation varchar(50),@Medical_aliments varchar(200),
@know_about_sculpt varchar(50),@workout_history varchar(100),
@Fitness_goal varchar(50),@no_days_train_weekly varchar(50),
@no_hours_train_daily varchar(50),@heart_condition varchar(50),
@chest_pain varchar(50),@chest_pain_last_month varchar(50),
@lose_consciousness varchar(50),@bone_joint_problem varchar(50),
@Doctor_currently_prescribing_drugs varchar(50),
@other_reason_not_do_physical_activity varchar(50),
@emergency_name varchar(50),@emergency_relation varchar(50),
@emergency_contact varchar(50),@bid varchar(50),
@device varchar(20),@payment_remarks varchar(50),@orderno varchar(50),
@discount varchar(10),@famt varchar(50))

  as
  begin
  insert into customers values
  (@cid,@full_name,@gender,@DOB,@age,@marital_status,@email,@phone,@Country,     @State,@City,@address,@Height,@weight,@BMI,@Subscription,@referral,@joining_date,@due_date,@amount_reccived,@amount_left,@c_photo,@primary_facility,@occupation,@Medical_aliments,@know_about_sculpt,@workout_history, @Fitness_goal,  @no_days_train_weekly,@no_hours_train_daily,@heart_condition,@chest_pain,
  @chest_pain_last_month,@lose_consciousness,@bone_joint_problem,  @Doctor_currently_prescribing_drugs,@other_reason_not_do_physical_activity,
  @emergency_name,@emergency_relation,@emergency_contact,@bid,@device, @payment_remarks,@orderno,@discount,@famt)
  end

and database design is as follows:

CREATE TABLE [dbo].[customers](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [cid] [int] NOT NULL,
    [full_name] [varchar](50) NOT NULL,
    [gender] [varchar](20) NOT NULL,
    [DOB] [date] NOT NULL,
    [marital_status] [varchar](20) NULL,
    [email] [varchar](50) NULL,
    [phone] [varchar](20) NULL,
    [Country] [varchar](50) NULL,
    [State] [varchar](50) NULL,
    [City] [varchar](50) NULL,
    [address] [varchar](200) NULL,
    [Height] [varchar](50) NULL,
    [weight] [varchar](50) NULL,
    [BMI] [varchar](50) NULL,
    [Subscription] [int] NULL,
    [referral] [varchar](50) NULL,
    [joining_date] [date] NULL,
    [due_date] [date] NULL,
    [amount_reccived] [varchar](50) NULL,
    [amount_left] [varchar](50) NULL,
    [c_photo] [varchar](500) NULL,
    [age] [varchar](50) NULL,
    [primary_facility] [varchar](50) NULL,
    [occupation] [varchar](50) NULL,
    [Medical_aliments] [varchar](200) NULL,
    [know_about_sculpt] [varchar](50) NULL,
    [workout_history] [varchar](100) NULL,
    [Fitness_goal] [varchar](50) NULL,
    [no_days_train_weekly] [varchar](50) NULL,
    [no_hours_train_daily] [varchar](50) NULL,
    [heart_condition] [varchar](50) NULL,
    [chest_pain] [varchar](50) NULL,
    [chest_pain_last_month] [varchar](50) NULL,
    [lose_consciousness] [varchar](50) NULL,
    [bone_joint_problem] [varchar](50) NULL,
    [Doctor_currently_prescribing_drugs] [varchar](50) NULL,
    [other_reason_not_do_physical_activity] [varchar](50) NULL,
    [emergency_name] [varchar](50) NULL,
    [emergency_relation] [varchar](50) NULL,
    [emergency_contact] [varchar](50) NULL,
    [bid] [varchar](50) NULL,
    [device] [varchar](20) NULL,
    [payment_remarks] [varchar](50) NULL,
    [orderno] [varchar](50) NULL,
    [discount] [varchar](10) NULL,
    [famt] [varchar](50) NULL,

I tried to get data by profiler, and i am able to see data is coming correct, but getting error, query at profiler:

exec customerinsert_new @cid='1079',@full_name='Testing',@gender='Male',@dob='2017-10-03',@age='25',@marital_status='Married',
@email='h@h.com',@phone='5',@country='India',@state='Haryana',@city='Asankhurd',@address='H.no. 720 Sector 9', @height='6.0',@weight='80',@BMI='23.92',@Subscription='3',@referral='Friend',@joining_date='2017-09-01',@due_date='2017-01-01',     @amount_reccived='1200',@amount_left='6800',@c_photo='10791.png',@primary_facility='Gym',@occupation='Na',@Medical_aliments='Na',
@know_about_sculpt='Friend',@workout_history='NA',@Fitness_goal='Fat Loss',@no_days_train_weekly='2',@no_hours_train_daily='1',     @heart_condition='No',@chest_pain='No',@chest_pain_last_month='No',@lose_consciousness='No',@bone_joint_problem='Yes',     @Doctor_currently_prescribing_drugs='No',@other_reason_not_do_physical_activity='No',@emergency_name='Mann',@emergency_relation='Na',     @emergency_contact='NA',@bid='2',@device='2',@payment_remarks='Cash',@orderno='INV2YQG2812OWX701XOE70112',@discount='0',@famt='8000'

in above query, like bmi is a varchar datatype field , but i am getting error:

Msg 245, Level 16, State 1, Procedure customerinsert_new, Line 52 Conversion failed when converting the varchar value '23.90' to data type int.

and for joining_date i am getting error which is date datatype:

Msg 241, Level 16, State 1, Procedure customerinsert_new, Line 52 Conversion failed when converting date and/or time from character string.

Following insert command insert data successfully:

insert into customers(cid,full_name,gender,DOB,marital_status,email,phone,Country,State,City,address,Height,weight,BMI,Subscription,referral,joining_date,
due_date,amount_reccived,amount_left,c_photo,age,primary_facility,occupation,Medical_aliments,know_about_sculpt,workout_history,Fitness_goal,no_days_train_weekly,
no_hours_train_daily,heart_condition,chest_pain,chest_pain_last_month,lose_consciousness,bone_joint_problem,Doctor_currently_prescribing_drugs,
other_reason_not_do_physical_activity,emergency_name,emergency_relation,emergency_contact,bid,payment_remarks,orderno,discount,famt)
 values('1079','Testing','Male','2017/10/23','Married','h@h.com','5','India','Haryana','Asankhurd','H.no. 720 Sector 9','6.0','80','23.90','3',
 'Friend','2017/09/11','2017/12/11','1200','6800','10791.png','25','Gym','Na','Na','Friend','NA','Fat Loss','2','1','No','No',
 'No','No','Yes','No','No','Mann','Na','NA','2','Cash','INV2YQG2812OWX701XOE70112','0','8000')

Answer 1

You should have a really good reason for your design... Using varchar(xy) for almost all your columns will be a great draw back in future... You should always place data in appropriate types!

The difference between the working command and your posted one is the explicitly stated column list. You should never ever rely on the columns to be in the expected order! No SELECT * and no INSERT INTO SomeTable [VALUES|SELECT]!

insert into customers values
(@cid,@full_name,@gender,@DOB,@age,@m

Place the column names in the correct order like in the later posted command:

insert into customers(col1, col2, ...) values
(@cid,@full_name,@gender,@DOB,@age,@m ...

I'm pretty sure, that order of columns of your table's DDL differs. After c_photo there's no age. You are not inserting the values to the columns you think you do.

Did you change your table recently? Your procedure might need a recompile in this case...