如何在vb.net中通过SQL向MS-Access添加'Boolean'列 [英] How to add a 'Boolean' column to ms-access via SQL in vb.net
问题描述
我正在尝试使用SQL在ms-access中的表中添加Boolean
列.我正在使用JET,这是我尝试过的SQL查询.
I am trying to add a Boolean
column to a table in ms-access using SQL. I am using JET, here are the the SQL queries I have tried.
Query = "ALTER TABLE tabDatafiveMinutely ADD CON0001 BOOLEAN DEFAULT FALSE"
Query = "ALTER TABLE tabDatafiveMinutely ADD CON0001 BOOLEAN"
我得到的错误是字段定义中的语法错误"
The error I am getting is 'Syntax error in field definition'
感谢您的帮助
我现在要设置默认的null
而不是false
.我已经尝试过default null
,但这仍然给了我false
,有人可以帮忙吗?
I would now like to make the default null
rather than false
. I have tried default null
and this still gives me false
, can anyone help with this?
结果:
ms-access数据库只能使用true
和false
,不能为null.因此,我决定使用和integer
代替.
An ms-access database can only take true
and false
and not null. Therefore I have decided to use and integer
instead.
推荐答案
Yes/No
列的等效SQL类型是BIT
The equivalent SQL type of a Yes/No
column is BIT
ALTER TABLE tabDatafiveMinutely
ADD COLUMN CON0001 BIT DEFAULT 0 NOT NULL
注意
只能通过Jet OLE DB提供程序和ADO执行DEFAULT语句.如果通过Access SQL View用户界面使用它将返回错误消息.
Note
The DEFAULT statement can be executed only through the Jet OLE DB provider and ADO. It will return an error message if used through the Access SQL View user interface.
@Pere指出,Jet Engine(Access查询引擎)不会将DEFAULT值应用于现有行.更改表后,您必须运行UPDATE语句.
As @Pere points out, Jet Engine (Access' query engine) does not apply the DEFAULT value to existing rows. You must run an UPDATE statement after altering the table.
UPDATE tabDatafiveMinutely SET CON0001 = 0 WHERE CON0001 IS NULL
这篇关于如何在vb.net中通过SQL向MS-Access添加'Boolean'列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!