Error converting data type varchar to numeric

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

I have the below table and I am trying to insert the values but it is giving me the error

[Check_In](
	[Room_Account] [varchar](50) NULL,
	[Group_Account] [varchar](50) NULL,
	[Type_Id] [varchar](50) NULL,
	[Room_No] [int] NULL,
	[Guest_Id] [varchar](50) NULL,
	[Guest_Name] [varchar](250) NULL,
	[Group_Id] [int] NULL,
	[Checkin_Type] [varchar](50) NULL,
	[Reservation_No] [int] NULL,
	[Contact_Name] [varchar](250) NULL,
	[Arrival_Date] [datetime] NULL,
	[Departure_Date] [datetime] NULL,
	[No_Of_Days] [int] NULL,
	[No_Of_Adults] [int] NULL,
	[No_Of_Childs] [int] NULL,
	[Arrival_source] [varchar](50) NULL,
	[Company_id] [varchar](500) NULL,
	[Special_Request] [varchar](1000) NULL,
	[Billing_Instruction] [varchar](1000) NULL,
	[Payment_Type] [varchar](250) NULL,
	[Card_Type] [varchar](250) NULL,
	[Card_No] [varchar](250) NULL,
	[Holder_Name] [varchar](250) NULL,
	[Expiry_Date] [datetime] NULL,
	[Bank_Name] [varchar](250) NULL,
	[Bank_SlipNo] [varchar](250) NULL,
	[Rate_Code] [varchar](500) NULL,
	[Amount] [money] NULL,
	[Deposit_Amount] [money] NULL,
	[ResvSource_Id] [varchar](250) NULL,
	[Market_Segment] [varchar](250) NULL,
	[CheckIn_Status] [varchar](250) NULL,	
	[User_Shift] [varchar](50) NULL,
	[User_Name] [varchar](250) NULL,	
	[Arrival_time] [varchar](50) NULL,	
	[GuestNameReal] [varchar](1000) NULL,
	[Guest_Title] [varchar](50) NULL,
        [Deposit_Required] [money] NULL,
        [Remarks] [varchar](3000) NULL,
        [Group_Leader] [varchar](50) NULL,
        [Type_ratecode] [varchar](50) NULL,
        [Bill_pref] [varchar](50) NULL,
        [Credit_Limit] [decimal](18, 2) NULL,
	[ActualCredit_Limit] [money] NULL,
	[grpcode] [varchar](250) NULL,
	[weekdayrateonly] [int] NULL,
	[weekendamount] [money] NULL,

What I have tried:

inserting the below query

insert into check_in(room_account,group_account,type_id,room_no,guest_id,guest_name,group_id,checkin_type,reservation_no,contact_name,arrival_date,departure_date,no_of_days,no_of_adults,no_of_childs,company_id,special_request,billing_instruction,payment_type,Card_type,Card_no,expiry_date,bank_name,bank_slipno,rate_code,amount,deposit_amount,resvsource_id,market_segment,checkin_status,user_shift,user_name,folio_count,Checkin_Form,Arrival_Time,GuestNameReal,Guest_Title,Deposit_Required,Remarks,Group_Leader,type_ratecode,bill_pref,credit_limit,actualcredit_limit,weekendamount,weekdayrateonly)values('2506201817043086-1','','STUDIOT','1704','12794','NOOR SHAWAL BIN NASRI','','Individual','1434','',cast('25-Jun-2018'as smalldatetime),cast('27-Jun-2018'as smalldatetime),'2','2','0','','','','CreditCard','VISA CARD','4460 3100 3037 3359',cast('01-Jul-2020'as smalldatetime),'','','PROMOTION 158',cast('158'as money),cast('0.0000'as money),'PHONE','PROMOTION','CheckIn','1','sa',3086,'CheckIn','10:09:48 AM','','', cast('316'as money),'' ,'','RTCode','','','300','cast('158' as money),'0')

I have tried to resolve but I am not getting where the problem is
can someone please help me

Posted 27-Jun-18 18:59pm

Member 13142345

Updated 27-Jun-18 19:21pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2018-06-27T19:23:00

Match your fields up with the data you are entering:

room_account             '2506201817043086-1'                 
group_account            ''                                   
type_id                  'STUDIOT'                            
room_no                  '1704'                               
guest_id                 '12794'                              
guest_name               'NOOR SHAWAL BIN NASRI'              
group_id                 ''                                   
checkin_type             'Individual'                         
reservation_no           '1434'                               
contact_name             ''                                   
arrival_date             cast('25-Jun-2018'as smalldatetime)  
departure_date           cast('27-Jun-2018'as smalldatetime)  
no_of_days               '2'                                  
no_of_adults             '2'                                  
no_of_childs             '0'                                  
company_id               ''                                   
special_request          ''                                   
billing_instruction      ''                                   
payment_type             'CreditCard'                         
Card_type                'VISA CARD'                          
Card_no                  '4460 3100 3037 3359'                
expiry_date              cast('01-Jul-2020'as smalldatetime)  
bank_name                ''                                   
bank_slipno              ''                                   
rate_code                'PROMOTION 158'                      
amount                   cast('158'as money)                  
deposit_amount           cast('0.0000'as money)               
resvsource_id            'PHONE'                              
market_segment           'PROMOTION'                          
checkin_status           'CheckIn'                            
user_shift               '1'                                  
user_name                'sa'                                 
folio_count              3086                                 
Checkin_Form             'CheckIn'                            
Arrival_Time             '10:09:48 AM'                        
GuestNameReal            ''                                   
Guest_Title              ''                                   
Deposit_Required          cast('316'as money)                 
Remarks                  ''                                   
Group_Leader             ''                                   
type_ratecode            'RTCode'                             
bill_pref                ''                                   
credit_limit             ''                                   
actualcredit_limit       '300'                                
weekendamount            'cast('158' as money)                
weekdayrateonly          '0'

And then look at the columns that are not stored as VARCHAR:

[Room_No]            [int] NULL,
[Group_Id]           [int] NULL,
[Reservation_No]     [int] NULL,
[Arrival_Date]       [datetime] NULL,
[Departure_Date]     [datetime] NULL,
[No_Of_Days]         [int] NULL,
[No_Of_Adults]       [int] NULL,
[No_Of_Childs]       [int] NULL,
[Expiry_Date]        [datetime] NULL,
[Amount]             [money] NULL,
[Deposit_Amount]     [money] NULL,
[Deposit_Required]   [money] NULL,
[Credit_Limit]       [decimal](18, 2) NULL,
[ActualCredit_Limit] [money] NULL,
[weekdayrateonly]    [int] NULL,
[weekendamount]      [money] NULL,

Then look at what you are putting in them:

[Room_No]               '1704'                              
[Group_Id]              ''                                  
[Reservation_No]        '1434'                              
[Arrival_Date]          cast('25-Jun-2018'as smalldatetime) 
[Departure_Date]        cast('27-Jun-2018'as smalldatetime) 
[No_Of_Days]            '2'                                 
[No_Of_Adults]          '2'                                 
[No_Of_Childs]          '0'                                 
[Expiry_Date]           cast('01-Jul-2020'as smalldatetime) 
[Amount]                cast('158'as money)                 
[Deposit_Amount]        cast('0.0000'as money)              
[Deposit_Required]      cast('316'as money)              
[Credit_Limit]          ''                                  
[ActualCredit_Limit]    '300'                               
[weekendamount]         'cast('158' as money)               
[weekdayrateonly]       '0'

All of those look like appropriate data, except the "empty" values:

[Group_Id]              ''                                  
[Credit_Limit]          ''

So try explicitly setting them to NULL and see if that helps:

SQL

insert into check_in(room_account,group_account,type_id,room_no,guest_id,guest_name,group_id,checkin_type,reservation_no,contact_name,arrival_date,departure_date,no_of_days,no_of_adults,no_of_childs,company_id,special_request,billing_instruction,payment_type,Card_type,Card_no,expiry_date,bank_name,bank_slipno,rate_code,amount,deposit_amount,resvsource_id,market_segment,checkin_status,user_shift,user_name,folio_count,Checkin_Form,Arrival_Time,GuestNameReal,Guest_Title,Deposit_Required,Remarks,Group_Leader,type_ratecode,bill_pref,credit_limit,actualcredit_limit,weekendamount,weekdayrateonly)values('2506201817043086-1','','STUDIOT','1704','12794','NOOR SHAWAL BIN NASRI',NULL,'Individual','1434','',cast('25-Jun-2018'as smalldatetime),cast('27-Jun-2018'as smalldatetime),'2','2','0','','','','CreditCard','VISA CARD','4460 3100 3037 3359',cast('01-Jul-2020'as smalldatetime),'','','PROMOTION 158',cast('158'as money),cast('0.0000'as money),'PHONE','PROMOTION','CheckIn','1','sa',3086,'CheckIn','10:09:48 AM','','', cast('316'as money),'' ,'','RTCode','',NULL,'300','cast('158' as money),'0')

But do yourself a favour: don't put quotes around numeric data!