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='[email protected]',@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','[email protected]','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')

Both error messages are pretty straight forward and clear. What do you expect us to do about it? — Zohar Peled
– Zohar Peled, Commented Oct 22, 2017 at 10:27
Hi Zohar Peled, i just want to know that why these errors are coming, as when i am inserting data with insert command in sql server through c#, then the same data inserted successfully. — Mandeep Singh
– Mandeep Singh, Commented Oct 22, 2017 at 10:32
23.90 can't be converted to int, and whatever string you are using can't be converted to date or datetime. Do you expect us to do the debugging for you? — Zohar Peled
– Zohar Peled, Commented Oct 22, 2017 at 10:34
Hi, When inserting data by insert command , record inserted successfully. please check insert command at last of question. — Mandeep Singh
– Mandeep Singh, Commented Oct 22, 2017 at 10:39

Gottfried Lesigang · Accepted Answer · 2017-10-22 11:23:20Z

2

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...

edited Oct 22, 2017 at 11:23

answered Oct 22, 2017 at 11:00

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Gottfried Lesigang Over a year ago

Not exactly the same, but very related: sqlblog.org/2009/10/10/…

Mandeep Singh Over a year ago

thanks Shnugo, you find the exact cause. now it is working fine.

Mandeep Singh Over a year ago

I accepted your answer, But my votes are not displaying publicly.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related