question

X0r1 avatar image
0 Votes"
X0r1 asked AmeliaGu-msft answered

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

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]


sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

cooldadtx avatar image
0 Votes"
cooldadtx answered X0r1 edited

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·
GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered X0r1 commented

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';


· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·

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;


0 Votes 0 ·
X0r1 avatar image X0r1 GuoxiongYuan-7218 ·

@GuoxiongYuan-7218 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

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered X0r1 commented
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you, I will try to report on the forum.

I think the problem is in the SSMS

0 Votes 0 ·
cooldadtx avatar image
1 Vote"
cooldadtx answered cooldadtx commented

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

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

90043-select-test.png

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

thanks you, very much


0 Votes 0 ·
select-test.png (9.2 KiB)

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.

1 Vote 1 ·
X0r1 avatar image
0 Votes"
X0r1 answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

X0r1 avatar image
0 Votes"
X0r1 answered

I found a partial solution:

If I remove "Send" column on Sql Pane and update the record work successfully

90064-solution-dbotest-microsoft-sql-server-management-s.png



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
1 Vote"
AmeliaGu-msft answered

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.