[Bug] Editing a row with bit fields causes strange results

jimtut posted 3 years ago in General
Several of my tables (on SQL Server) use bit(1) fields. Editing these rows causes strange results, whether I'm editing on the Data tab or in a Query tab. I get different results on different tables of mine, so let me generalize into 2 cases.

The simplest "error" is really a visual thing. Editing a text field in a row with multiple bits causes the bits to switch from displaying 0 or 1 to displaying "T,rue" or "Fa,lse". Yes, that's a comma in there! I found the root-cause for this - it has to do with how HeidiSQL displays numeric values (yes, numerics). If you have the preference "Local number format" turned on (and your local number format defines the use of comma as a thousands-separator), then "T,rue" and "Fa,lse" are displayed, as if they were numbers, not text (or bits). Could this be fixed?

Worse than the above visual error is that sometimes I can't edit rows that have bit values when the table does NOT have a Primary Key. Trying to commit the edit (Ctrl-Enter) causes an error "SQL Error (102): Incorrect syntax near '0'". (Of course, if there was a "1" in a bit field, it would say "Incorrect syntax near '1'".)

Turning on the log shows this SQL:

UPDATE TOP(1) "db-name"."dbo"."table-name" SET "Username"=N'foobarxx' WHERE  "ID"=3 AND "Username"='foobar' AND "UseGroups"=b'0';

This SQL fails in SQL Server Management Studio too. I think the correct SQL should be:

UPDATE TOP(1) "db-name"."dbo"."table-name" SET "Username"=N'foobarxx' WHERE  "ID"=3 AND "Username"='foobar' AND "UseGroups"=0;

Could HeidSQL be update to generate the correct SQL for updating records with bit values when the table does NOT have a primary key?
pmiller posted 2 months ago

I just encountered the same same issue while editing the value in an nvarchar(max) column using SQL Server. After the value was updated, the all bit column values on the row displayed as T,rue or Fa,lse instead of 1 or 0.

1 attachment(s):
  • t-rue-and-fa-lse

Please login to leave a reply, or register at first.