Conversion failed when converting the varchar value 'Send' to data type bit.

Question

Conversion failed when converting the varchar value 'Send' to data type bit.

X0r1 1

In Sql Server Management Studio 15.0 cannot edit table with field name "Send" and data type bit

Error on edit another field :
Microsoft SQL Server Management Studio
No row was updated.

The data in row 1 was not committed.
Error Source: .Net SqlClient Data Provider.
Error Message: Conversion failed when converting the varchar value 'Send' to data type bit.

Correct the errors and retry or press ESC to cancel the change(s).

but if I change column name "Send" to "Send_" work successfully

90041-dbotest2-microsoft-sql-server-management-studio.png

90014-dbotest-microsoft-sql-server-management-studio.png

Example :
In table "test" I cannot edit row, but in table "test2" I can edit and insert data.

![CREATE TABLE [dbo].[test](  
 [id] [int] IDENTITY(1,1) NOT NULL,  
 [desc] [varchar](max) NOT NULL,  
 [Send] [bit] NOT NULL,  
 CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED   
(  
 [id] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  
  
/*---------------------------------------------------------------------*/  
  
CREATE TABLE [dbo].[test2](  
 [id] [int] IDENTITY(1,1) NOT NULL,  
 [desc] [varchar](max) NOT NULL,  
 [Send_] [bit] NOT NULL,  
 CONSTRAINT [PK_test2] PRIMARY KEY CLUSTERED   
(  
 [id] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

7 answers

Your answer

Answer 1

Michael Taylor 60,161

I tested this and it appears to be a bug in SSMS. The issue, I believe, is that desc is a keyword in T-SQL and send is part of a larger statement.

However it is actually inserting (twice in my case) but the UI is reporting the error. If you try to edit in the UI, let it error, cancel the edit, close the table and reopen it you might see it actually added it twice.

You should report this as a bug to the SSMS team.

X0r1 1 Reputation point

2021-04-21T17:16:15.287+00:00

sorry, I use "desc" column to simplify de example, in my table don't have this column.

@Michael Taylor , correctly, after error, in new record, I select table and data was inserted. This increases my concern. records have been inserted without knowing it.

When you edit an existing record it doesn't happen, record are unchanged.

thanks you, very much
Michael Taylor 60,161 Reputation points

2021-04-21T17:25:04.447+00:00

Yes it is a bug in the UI, not SQL. Use the Help\Technical Support menu in SSMS to report the issue to the team so they can research it. I believe the issue is with the send column as that is the start of a SQL command.

Answer 2

Hi @X0r1 ,
Thanks for your feedback. After some testing, I also encountered the same issue as you.
Currently, you can use INSERT INTO statement to insert new records in a table and use UPDATE statement to update records. You also can right-click the Table->Script Table as-> INSERT To/UPDATE To to help you insert or update records.
Sorry for the inconvenience.
In addition, if you find any post in the thread is helpful, you could kindly accept it as answer. This would benefit the community, and encourage the community member to keep working on your issues.

Best Regards,
Amelia

Answer 3

Michael Taylor 60,161

You cannot store a text value in a bit column. The error is pretty obvious. But I'm really confused as to what actual value you're using. The column name is Send but you said the error says error converting 'Send' to bit. That would mean you are trying to set the Send column with the value Send which wouldn't work as it is a bit column. The only values you can put into a bit column is 0 or 1.

X0r1 1 Reputation point

2021-04-21T14:45:48.12+00:00

I don't know but the only difference between the two tables is the name of the column.

In two examples I insert 0/1, but when row is commit management studio change value to True/False.

I have tried to enter 1/0/true/false/True/false but the same error appears

If I use T-Sql to insert/update values work fine. the problem is when I use Edit Table Rows from Sql Server Management Studio

Answer 4

Guoxiong 8,206

AS cooldadtx mentioned, only values you can use for the bit column are 0 or 1 since you set the column NOT NULL. The error should not depend on the column name. For example, if you run the following query, you will get the error:

Msg 245, Level 16, State 1, Line 7
Conversion failed when converting the varchar value 'Send' to data type bit.

DECLARE @Test TABLE (
 [Id] int IDENTITY(1,1) NOT NULL,
 [Desc] varchar(max) NOT NULL,
 [Send_] bit NOT NULL
);

INSERT INTO @Test
SELECT 'Test1', 'Send';

X0r1 1 Reputation point

2021-04-21T14:52:36.243+00:00

the problem is not in the data, in the two tables I enter the same value and the only difference is the name of the column
Guoxiong 8,206 Reputation points

2021-04-21T15:23:28.053+00:00
I am using SSMS v18.8 and SQL server 2019. I tested it and both INSERT statements throwed the same error:

INSERT INTO [dbo].[test] SELECT 'Test1', 'Send'; INSERT INTO [dbo].[test2] SELECT 'Test1', 'Send';

If I run the following queries, they are successful:

INSERT INTO [dbo].[test] SELECT 'Test1', 1; INSERT INTO [dbo].[test2] SELECT 'Test1', 1;

I also can update the data in [dbo].[Test2]:

UPDATE [dbo].[test] SET [Send] = 0;
X0r1 1 Reputation point

2021-04-21T18:00:32.537+00:00

@Guoxiong this error occurs on edit table rows and you insert 1 or 0 in "send" column.

In Insert or update (T-SQL) statements it is normal that the error is thrown. you can't insert a string value into the bit column.

Send is a column name not value

Answer 5

Viorel 122.6K

Sound like an issue, similar to this: https://learn.microsoft.com/en-us/troubleshoot/sql/ssms/error-use-ssms-update-row-table. I think that it can be re-reported, probably here: https://feedback.azure.com/forums/908035-sql-server.

Share via

Conversion failed when converting the varchar value 'Send' to data type bit.

7 answers

Your answer