Last post Jun 19, 2015 11:28 AM by PatriceSc
Jun 18, 2015 01:26 PM|maggiemays|LINK
I have a field varchar(200) that I want to contain strings of numbers with dashes like this: 12-001,12-003,12-004, but when I try to update , I get the error:
Conversion failed when converting the varchar value '12-123' to data type int. I tried to cast the number to a varchar, but it still gives me the same error.
I am going to need to update this field through c#, but I wanted to populate the data with some values for testing purposes.
Also, when I try to set this field to '0', it generates this error:
Conversion failed when converting the varchar value '02-255' to data type int.
How can I fix this?
I tried doing this, but I got the same errror:
set field = cast ( '12-001,12-004,12-004', as varchar(max)) ...
Jun 18, 2015 02:05 PM|PatriceSc|LINK
And the SQL statement is? As told by the message you are trying to convert a string to an integer. Are you sure the column is a varchar? Do you really try to update the right column? If the error seems unrelated to your code, could it be that you have a
trigger that fails?
Jun 18, 2015 02:45 PM|maggiemays|LINK
hi, the statement is: update table
set field = '12-111,12-130'
Yes, the field I'm trying to update is really varchar(200)
Jun 18, 2015 02:49 PM|PatriceSc|LINK
Don't you have table triggers then? Especially as my understanding is that when using a value the error message shows some other value ??
Jun 18, 2015 02:55 PM|maggiemays|LINK
No, I don't have triggers.
Jun 18, 2015 03:50 PM|limno|LINK
Can you generate your table script to show us?
(Right click on your table from SSMS>>Script Table as >>Create to >>... )
Jun 18, 2015 04:52 PM|maggiemays|LINK
Hi, yes, here it is: The field I'm having problems with is Notices. Thank you.
CREATE TABLE [dbo].[notice](
[ID] [varchar](6) NOT NULL,
[noticeDate] [smalldatetime] NOT NULL,
[noticeTime] [varchar](20) NOT NULL,
[Approved_date] [smalldatetime] NULL,
[Code] [varchar](2) NULL,
[Notices] [varchar](200) NULL,
CONSTRAINT [PK_notice] PRIMARY KEY CLUSTERED
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[notice] ADD CONSTRAINT [DF_notice_DateCreatedq] DEFAULT (getdate()) FOR [DateCreated]
ALTER TABLE [dbo].[notice] ADD CONSTRAINT [DF_notice_CreatedBy] DEFAULT ('') FOR [CreatedBy]
ALTER TABLE [dbo].[notice] ADD CONSTRAINT [DF_notice_DateModified] DEFAULT (getdate()) FOR [DateModified]
ALTER TABLE [dbo].[notice] ADD CONSTRAINT [DF_notice_ModifiedBy] DEFAULT ('') FOR [ModifiedBy]
Jun 19, 2015 01:53 AM|Edwin Guru Singh|LINK
The field I'm having problems with is Notices.
insert into notice(id,noticeDate,noticeTime,Approved_date,Code,Notices) select 1,CAST(GETDATE() as smalldatetime),'10am',CAST(GETDATE() as smalldatetime),'xx','12-111,12-130';----insert into notice(id,noticeDate,noticeTime,Notices) select 2,CAST(GETDATE() as smalldatetime),'10am','12-111,12-130';
update notice set Notices='12-111,12-130' where id=1
it was working fine..
you have to double check your update statement that you are passing the values to right column or not.
Jun 19, 2015 10:57 AM|limno|LINK
This is not the whole code.
Can you post the whole thing without stripping anything out? Thanks.
Jun 19, 2015 11:22 AM|maggiemays|LINK
I will check again. thanks Edwin.
Jun 19, 2015 11:28 AM|PatriceSc|LINK
For now there is just no INT column at all. So if when running an update and you do have a "Conversion failed when converting the varchar value '12-123' to data type
int" then you have something else you don't show such as a CAST inside your statement or some other table is involved etc...
If you confirm that something like :
UPDATE Notice SET Notices='0' WHERE Id='123' (noticed the ID is a varchar, could it be part of the problem ?) you have an error message showing an entirely unrelated value such as '02-255' then there is really something you are hidden from us ;-)
What is the EXACT update statement is ? And show the EXACT corresponding error message as well please.