如何引用包含引号的SQL列?

huangapple go评论62阅读模式
英文:

How to reference SQL columns containing quotes?

问题

I am having trouble referencing the "current" column. It was originally named using quotes, and I am not sure how to reference it.
I have tried:

colName = ""current"";
colName = "'current'";
colName = "current";

Using the query:
query = "SELECT " + colName + " FROM myTable";
cmd = new SQLCommand(query,Conn);
cmd.CommandText = query;
adpt = new SQLDataAdapter(query,Conn);
table = new DataTable();
adpt.Fill(table);

All to no avail. It keeps saying the column does not belong to the table, I can easily reference any other column successfully.

CREATE TABLE dbo.myTable
(
id int identity NOT NULL,
puffid int,
summaryid int,
serial_number int,
puff_date_time datetime,
x_angle numeric(8,3) DEFAULT ((0)),
y_angle numeric(8,3) DEFAULT ((0)),
z_angle numeric(8,3) DEFAULT ((0)),
voltage numeric(8,3) DEFAULT ((0)),
"current" numeric(8,3) DEFAULT ((0)));
GRANT DELETE, REFERENCES, UPDATE, SELECT, INSERT ON dbo.myTable TO dbo;
I really have no idea how to reference this thing. I can't change the column name or it will break multiple other parts of the project.

英文:

I am having trouble referencing the "current" column. It was originally named using quotes, and I am not sure how to reference it.
I have tried:

 colName = "\"current\"";
 colName = "\'current\'";
 colName = "current";

 Using the query:
 query = "SELECT" + colName "FROM myTable";
 cmd = new SQLCommand(query,Conn);
 cmd.CommandText = query;
 adpt = new SQLDataAdapter(query,Conn);
 table = new DataTable();
 adpt.Fill(table);

All to no avail. It keeps saying the column does not belong to the table, I can easily reference any other column successfully.

 CREATE TABLE dbo.myTable
(
   id              int identity    NOT NULL,
   puffid          int,
   summaryid       int,
   serial_number   int,
   puff_date_time  datetime,
   x_angle         numeric(8,3)    DEFAULT ((0)),
   y_angle         numeric(8,3)    DEFAULT ((0)),
   z_angle         numeric(8,3)    DEFAULT ((0)),
   voltage         numeric(8,3)    DEFAULT ((0)),
   "current"       numeric(8,3)    DEFAULT ((0)));
   GRANT DELETE, REFERENCES, UPDATE, SELECT, INSERT ON dbo.myTable TO dbo;

I really have no idea how to reference this thing. I can't change the column name or it will break multiple other parts of the project.

答案1

得分: 2

你需要将它们包裹在 [] 中,并且它们周围需要有空格:

 colName = "\"current\"";

 使用查询语句:
 query = "SELECT [" + colName + "] FROM myTable";
 cmd = new SQLCommand(query,Conn);
 cmd.CommandText = query;
 adpt = new SQLDataAdapter(query,Conn);
 table = new DataTable();
 adpt.Fill(table);
英文:

You will need to wrap them into [ and ] and you need spaces around them as well:

 colName = "\"current\"";

 Using the query:
 query = "SELECT [" + colName + "] FROM myTable";
 cmd = new SQLCommand(query,Conn);
 cmd.CommandText = query;
 adpt = new SQLDataAdapter(query,Conn);
 table = new DataTable();
 adpt.Fill(table);

答案2

得分: 2

Honestly, if you can, I suggest you rename the column to current. This can be done with sp_rename:

EXEC sys.sp_rename N'dbo.myTable."current"',N'current','COLUMN';

If you can't, then you'll need to escape the double quotes that are part of the name or use T-SQL's delimit identifier too. Either """current""" or ["current"] would work to reference the column.

英文:

Honestly, if you can, I suggest you rename the column to current. This can be done with sp_rename:

EXEC sys.sp_rename N'dbo.myTable.["current"]',N'current','COLUMN';

If you can't, then you'll need to escape the double quotes that are part of the name or use T-SQL's delimit identifier too. Either """current""" or ["current"] would work to reference the column.

huangapple
  • 本文由 发表于 2023年4月13日 22:39:48
  • 转载请务必保留本文链接:https://go.coder-hub.com/76006752.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定