DB列名称的保留字会导致数据适配器更新崩溃 [英] reserved word for DB column name crashes data adapter update

问题描述

我正在VB.net中连接到SQL Server 2000数据库的项目

我无法修改

我创建了一个数据集与DB相同的模式。

当尝试使用dataAdapter.update

sub从数据集更新数据库时，我收到错误。

问题似乎是其中一个字段被命名为''desc''这是一个

为SQL保留字。

我已经能够解决这个设计漏洞，直到现在，但看起来好像

update sub无法处理它。

我使用命令构建器来创建更新，插入和删除脚本。

跟踪是这样的：


发生了'System.Data.SqlClient.SqlException'类型的未处理异常
system.data.dll


附加信息：系统错误。

未处理的异常：System.Data.SqlClient.SqlException：语法不正确

靠近keyw System.Data.Common.DbDataAdapter.Update（DataRow [] dataRows，

DataTableMapping tableMapping）

ord''desc''。

在System.Data.Common.DbDataAdapter.Update（DataTable dataTable）


有没有办法解决这个问题？

解决方案

>我正在VB.net中的一个项目连接到SQL Server 2000

数据库

我无法修改
我创建了一个数据集使用与DB相同的模式。
当尝试使用dataAdapter.update
sub从数据集更新数据库时，出现错误。


如果你无法修改数据库，为什么还要尝试更新它呢？这个

将是我的第一个红旗。

我已经能够解决这个设计缺陷''到现在为止它看起来像更新了
sub无法处理它。

每隔一段时间，有人在数据库中创建一个列或其他对象

其名称是一个保留字。如果你将
将对象的标识符括在方括号中，SQL Server将允许你引用它：


SELECT [Desc] FROM t_SomeTable


有时候，如果您符合条件，SQL Server将理解您的意思

您指的是：


SELECT t_SomeTable .Desc FROM t_SomeTable


你可能会考虑另一件事，虽然我个人不喜欢这样：


SET QUOTED_IDENTIFIER ON

SELECT" Desc" FROM t_SomeTable

SET QUOTED_IDENTIFIER OFF


一般来说，SQL Server查询分析器是你最好的朋友

找出正确的SQL语法与应用程序代码无关。一旦你获得了SQL语法在查询分析器中做正确的事情，然后在你的应用程序代码中尝试使用

。如果它不起作用，还有其他东西

出错，比如运行时权限或其他棘手的应用程序。

-

Peace&快乐的计算，


Mike Labosh，MCSD MCT

所有者，vbSensei.Com

Escriba coda ergo sum。 - vbSensei

当我说我不能修改DB时，我的意思是结构，而不是数据。


我知道[]工作，但它看起来不像VB.net那样。

更新代码由VB.net生成，因为我使用了命令构建器（don '$

浪费你的时间告诉我我应该自己编写而不是使用

命令构建器。

看起来好像你使用保留字，VB.net不会采取任何

预防措施，以避免在处理数据库时出错。


有没有办法纠正这个问题问题？


" Mike Labosh" <毫升***************** @ vbsensei.com>在消息中写道

news：ur ************** @ TK2MSFTNGP11.phx.gbl ...

我正在VB.net中的一个项目连接到SQL Server 2000数据库

我无法修改
我创建了一个模式相同的数据集DB。
当尝试使用
dataAdapter.update子从数据集更新数据库时，我收到错误。

如果你不能修改数据库，为什么还要尝试更新呢？这将是我的第一个红旗。

我已经能够解决这个设计漏洞了，直到现在它看起来像b $ b更新sub无法处理它。

每隔一段时间，有人在

数据库中创建一个列或其他对象，其名称是一个保留字。如果
将对象的标识符括在方括号中，SQL Server将允许您引用它：

SELECT [Desc] FROM t_SomeTable

有时，SQL如果您符合您所指的
项目，服务器将理解您的意思：

SELECT t_SomeTable.Desc FROM t_SomeTable

您可能会考虑的另一件事，尽管我个人不喜欢它是
这个：
SET QUOTED_IDENTIFIER ON
SELECT" Desc"从t_SomeTable
SET QUOTED_IDENTIFIER关闭

一般来说，SQL Server查询分析器是您最好的朋友，无论应用程序代码如何，都能确定正确的SQL语法。一旦
你得到SQL语法在查询分析器中做正确的事情，然后在你的应用程序代码中尝试它。如果它不起作用，还有其他的东西出错了，例如运行时权限或其他棘手的应用程序
。 -
Peace&快乐的计算，

Mike Labosh，MCSD MCT
所有者，vbSensei.Com
Escriba coda ergo sum。 - vbSensei

Benoit：


如果有值得战斗的战斗，它''更改列名是

保留字。您是否尝试使用[]更改其上的别名？我已经告诉许多人，除非你使用访问权限，否则它应该可以工作。如果你没有b $ b不能做到这一点，为什么不根据表创建一个View并给它

真实姓名。无论谁坚持使用Desc而不让你改变它是

非常短视，但他/她不可能禁止你

创建一个视图并使用它相反。

" Benoit Martin" < BM ********* @ hotmail.com>在留言中写道

新闻：％2 **************** @ TK2MSFTNGP10.phx.gbl ...

当我说我不能修改数据库，我的意思是结构，而不是
数据。
我知道[]工作，但它看起来不像VB.net那样做。
更新代码由VB.net生成，因为我使用命令构建器
（不要浪费你的时间告诉我我应该自己编写而不是使用
命令构建器）
看起来如果你使用保留字，VB.net将不会采取任何预防措施来避免在处理数据库时出错。

有没有办法纠正这个问题？

Mike Labosh <毫升***************** @ vbsensei.com>在消息中写道
新闻：ur ************** @ TK2MSFTNGP11.phx.gbl ...

I ''我正在VB.net上的一个项目连接到SQL Server 2000

数据库

我无法修改
我创建了一个数据集一个与数据库相同的模式。
当尝试使用dataAdapter.update子从数据集更新数据库时，我收到一个错误。

如果你不能修改数据库，为什么还要尝试更新呢？这将是我的第一个红旗。

我已经能够解决这个设计缺陷''到现在为止它看起来像更新子可以''处理它。

每隔一段时间，有人在

数据库中创建一个列或其他对象

其名称是一个保留字。 SQL Server将允许您参考这个，如果

你

将对象的标识符括在方括号中：

SELECT [Desc] FROM t_SomeTable

有时，如果您符合

项目

，SQL Server将理解您的意思：

SELECT t_SomeTable.Desc FROM t_SomeTable

你可能会考虑的另一件事，虽然我个人不喜欢它

这：

SET QUOTED_IDENTIFIER ON
SELECT" Desc"从t_SomeTable
SET QUOTED_IDENTIFIER关闭

一般来说，无论应用程序代码如何，SQL Server查询分析器都是您最好的朋友
，用于确定正确的SQL语法。一旦

你

获取SQL语法在查询分析器中做正确的事情，然后在你的应用程序代码中尝试
。如果它不起作用，还有其他的东西出错了，例如运行时权限或其他棘手的应用程序

东西。

-
Mike Labosh，MCSD MCT
所有者，vbSensei.Com
Escriba coda ergo sum。 - vbSensei

I''m working on a project in VB.net connecting to a SQL Server 2000 database
that I can''t modify
I created a dataset with a schema identical to the DB.
When trying to update the DB from the dataset using the dataAdapter.update
sub, I get an error.
The problem seems to be that one of the fields was named ''desc'' which is a
reserved word for SQL.
I''ve been able to work around this design flaw ''till now but it looks like
the update sub can''t handle it.
I used the command builder to create the Update, Insert and Delete scripts.
The trace is like this:

An unhandled exception of type ''System.Data.SqlClient.SqlException'' occurred
in system.data.dll

Additional information: System error.
Unhandled Exception: System.Data.SqlClient.SqlException: Incorrect syntax
near the keyword ''desc''.
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)

Is there a way around this?

解决方案

> I''m working on a project in VB.net connecting to a SQL Server 2000
database

that I can''t modify
I created a dataset with a schema identical to the DB.
When trying to update the DB from the dataset using the dataAdapter.update
sub, I get an error.
If you cannot modify the database, why bother trying to update it? This
would be my first red flag.
I''ve been able to work around this design flaw ''till now but it looks like
the update sub can''t handle it.

Every once in a while, someone makes a column or other object in a database
whose name is a reserved word. SQL Server will let you refer to this if you
enclose the object''s identifier in square brackets:

SELECT [Desc] FROM t_SomeTable

Sometimes, SQL Server will understand what you mean if you qualify the item
you are referring to:

SELECT t_SomeTable.Desc FROM t_SomeTable

Another thing you might consider, though I personally don''t like it is this:

SET QUOTED_IDENTIFIER ON
SELECT "Desc" FROM t_SomeTable
SET QUOTED_IDENTIFIER OFF

Generally speaking, the SQL Server Query Analyzer is your best friend for
figuring out correct SQL syntax irrespective of application code. Once you
get the SQL syntax to do the right thing in Query Analyzer, then try it in
your application code. If it then doesn''t work, there is something else
going wrong, such as runtime permissions or other screwy application stuff.
--
Peace & happy computing,

Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSensei

when I said that I can''t modify the DB, I meant the structure, not the data.

I was aware of the [] work around but it doesn''t look like VB.net does it.
The update code is generated by VB.net as I used the command builder (don''t
waste your time telling me that I should write my own instead of using the
command builder)
It looks like if you use a reserved word, VB.net will not take any
precautions to avoid an error when dealing with the database.

Is there a way to correct that problem?

"Mike Labosh" <ml*****************@vbsensei.com> wrote in message
news:ur**************@TK2MSFTNGP11.phx.gbl...

I''m working on a project in VB.net connecting to a SQL Server 2000 database

that I can''t modify
I created a dataset with a schema identical to the DB.
When trying to update the DB from the dataset using the dataAdapter.update sub, I get an error.

If you cannot modify the database, why bother trying to update it? This
would be my first red flag.

I''ve been able to work around this design flaw ''till now but it looks like the update sub can''t handle it.

Every once in a while, someone makes a column or other object in a

database whose name is a reserved word. SQL Server will let you refer to this if you enclose the object''s identifier in square brackets:

SELECT [Desc] FROM t_SomeTable

Sometimes, SQL Server will understand what you mean if you qualify the item you are referring to:

SELECT t_SomeTable.Desc FROM t_SomeTable

Another thing you might consider, though I personally don''t like it is this:
SET QUOTED_IDENTIFIER ON
SELECT "Desc" FROM t_SomeTable
SET QUOTED_IDENTIFIER OFF

Generally speaking, the SQL Server Query Analyzer is your best friend for
figuring out correct SQL syntax irrespective of application code. Once you get the SQL syntax to do the right thing in Query Analyzer, then try it in
your application code. If it then doesn''t work, there is something else
going wrong, such as runtime permissions or other screwy application stuff. --
Peace & happy computing,

Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSensei

Benoit:

If there''s a battle worth fighting, it''s Changing column names that are
reserved words. Have you tried changing the Alias on it w/ the []? I''ve
been told by many that it should work unless you are using access. If you
can''t do any of that, why not create a View based on the table and give it
real names. Whoever insists on using Desc and not letting you change it is
being Very shortsighted, but he/she couldn''t possibly prohibit you from
creating a view and using it instead.
"Benoit Martin" <bm*********@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...

when I said that I can''t modify the DB, I meant the structure, not the data.
I was aware of the [] work around but it doesn''t look like VB.net does it.
The update code is generated by VB.net as I used the command builder (don''t waste your time telling me that I should write my own instead of using the
command builder)
It looks like if you use a reserved word, VB.net will not take any
precautions to avoid an error when dealing with the database.

Is there a way to correct that problem?

"Mike Labosh" <ml*****************@vbsensei.com> wrote in message
news:ur**************@TK2MSFTNGP11.phx.gbl...

I''m working on a project in VB.net connecting to a SQL Server 2000

database

that I can''t modify
I created a dataset with a schema identical to the DB.
When trying to update the DB from the dataset using the dataAdapter.update sub, I get an error.

If you cannot modify the database, why bother trying to update it? This
would be my first red flag.

I''ve been able to work around this design flaw ''till now but it looks like the update sub can''t handle it.

Every once in a while, someone makes a column or other object in a

database

whose name is a reserved word. SQL Server will let you refer to this if

you

enclose the object''s identifier in square brackets:

SELECT [Desc] FROM t_SomeTable

Sometimes, SQL Server will understand what you mean if you qualify the

item

you are referring to:

SELECT t_SomeTable.Desc FROM t_SomeTable

Another thing you might consider, though I personally don''t like it is

this:

SET QUOTED_IDENTIFIER ON
SELECT "Desc" FROM t_SomeTable
SET QUOTED_IDENTIFIER OFF

Generally speaking, the SQL Server Query Analyzer is your best friend for figuring out correct SQL syntax irrespective of application code. Once

you

get the SQL syntax to do the right thing in Query Analyzer, then try it in your application code. If it then doesn''t work, there is something else
going wrong, such as runtime permissions or other screwy application

stuff.

--
Peace & happy computing,

Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSensei

这篇关于DB列名称的保留字会导致数据适配器更新崩溃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！