Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

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

jimtut's profile image jimtut posted 5 years ago in General Permalink
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's profile image pmiller posted 2 years ago Permalink

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
web3dprint's profile image web3dprint posted 1 year ago Permalink

The problem of editing rows with BIT fields, which does not have a primary key, stiil persists. The query adds the b in front of the literal '0' or '1', making them b'0' or b'1', which I never seen before and from what I can see SQL Server does not support.

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