使用ADO.NET创建视图 [英] Create View with ADO.NET

问题描述

是否有人尝试使用CREATE VIEW命令创建SQL7视图

和ADO.NET？如果是这样，当试图创建视图时，是否存在捕获SQL错误的技巧？我有一个VB.NET应用程序，可以使用其他的b
，可以根据现有的表和

列名等价表创建视图。如果我意外地引入了一个与另一列相同的列名

，那么我最终会得到一个错误的CREATE

VIEW SQL语句，如果你运行它在查询分析器

窗口中，您会收到如下错误：


服务器：消息4506，级别16，状态1，过程BAR，行2

每个视图中的列名必须是唯一的。视图中的列名''FOO''

''BAR''被指定多次。


ExecuteNonQuery正在Try / Catch中完成但是Catch

无法抓住它'


有什么想法吗？

解决方案

Zack，

您的代码是什么样的，特别是CREATE VIEW语句

本身？


你可以在执行

ExecuteNonQuery方法之前打印CREATE VIEW语句吗？


希望这有帮助

Jay


" Zack Sessions" < ZC ******** @ visionair.com>在消息中写道

新闻：db ************************* @ posting.google.co m ... < blockquote class =post_quotes>是否有人尝试使用CREATE VIEW命令和ADO.NET创建SQL7视图？如果是这样，在尝试创建视图时是否存在捕获SQL错误的技巧？我有一个VB.NET应用程序，在其他东西，可以创建基于现有表和
列名称等效表的视图。如果我意外地引入了与另一列相同的列名
，如果在查询分析器中运行它，我最终会遇到错误的CREATE
VIEW SQL语句
窗口中出现如下错误：

服务器：消息4506，级别16，状态1，过程BAR，行2
每个视图中的列名必须是唯一的。视图中的列名''FOO''
''BAR''被指定不止一次。

ExecuteNonQuery正在Try / Catch中完成但是Catch
ain '抓住''它!!!

任何想法？

错误在你的CREATE VIEW命令中，而不是在ADO.NET中。尝试在查询分析器中运行CREATE VIEW命令


" Zack Sessions"写道：

有没有人尝试使用CREATE VIEW命令和ADO.NET创建SQL7视图？如果是这样，在尝试创建视图时是否存在捕获SQL错误的技巧？我有一个VB.NET应用程序，在其他东西，可以创建基于现有表和
列名称等效表的视图。如果我意外地引入了与另一列相同的列名
，如果在查询分析器中运行它，我最终会遇到错误的CREATE
VIEW SQL语句
窗口中出现如下错误：

服务器：消息4506，级别16，状态1，过程BAR，行2
每个视图中的列名必须是唯一的。视图中的列名''FOO''
''BAR''被指定不止一次。

ExecuteNonQuery正在Try / Catch中完成但是Catch
ain '抓住''它!!!

任何想法？

" Jay B. Harlow [MVP - Outlook ]" < JA ************ @ msn.com>在消息新闻中写道：<＃5 ************** @ TK2MSFTNGP11.phx.gbl> ...

Zack，
你的是什么代码看起来像，特别是CREATE VIEW语句本身？

您可以在执行
ExecuteNonQuery方法之前打印CREATE VIEW语句吗？


试图调试这个问题的时候，我添加了一个Debug.WriteLine来支持
转储SQL命令以创建我用来剪切和粘贴的视图

进入查询分析器窗口。示例创建视图命令看起来像

类似：


创建视图viewname AS

SELECT col1，

col2，

col3 as NewName，

col4，

col5 as NewName，

col6

FROM tablea


注意错误是col3和col5都有相同的别名。

在Query中运行此命令时分析器窗口，你得到你期望的

错误，有两个具有相同名称的字段。当我尝试使用ADO.NET从VB.NET运行命令时，没有错误返回
，即不会引发Try / Catch中的Catch。






Zack Sessions < ZC ******** @ visionair.com>在消息中写道
新闻：db ************************* @ posting.google.co m ...

是否有人尝试使用CREATE VIEW命令和ADO.NET创建SQL7视图？如果是这样，在尝试创建视图时是否存在捕获SQL错误的技巧？我有一个VB.NET应用程序，在其他东西，可以创建基于现有表和
列名称等效表的视图。如果我意外地引入了与另一列相同的列名
，如果在查询分析器中运行它，我最终会遇到错误的CREATE
VIEW SQL语句
窗口中出现如下错误：

服务器：消息4506，级别16，状态1，过程BAR，行2
每个视图中的列名必须是唯一的。视图中的列名''FOO''
''BAR''被指定不止一次。

ExecuteNonQuery正在Try / Catch中完成但是Catch
ain '抓住''它!!!

任何想法？

Has anyone tried to create a SQL7 view using the CREATE VIEW command
and ADO.NET? If so, is there a trick in trapping a SQL error when
trying to create the view? I have a VB.NET app that, amoung other
things, can create views based on existing tables and a table of
column name equivalents. If I accidently introduce a column name
equivalent that is the same as another column, I end up with a CREATE
VIEW SQL statement that is in error, if you run it in a query analyzer
window you get an error like the following:

Server: Msg 4506, Level 16, State 1, Procedure BAR, Line 2
Column names in each view must be unique. Column name ''FOO'' in view
''BAR'' is specified more than once.

The ExecuteNonQuery is being done inside a Try/Catch but the Catch
ain''t catchin'' it!!!

Any ideas?

解决方案

Zack,
What does your code look like, specifically the CREATE VIEW statement
itself?

Can you print the CREATE VIEW statement, just before you execute the
ExecuteNonQuery method?

Hope this helps
Jay

"Zack Sessions" <zc********@visionair.com> wrote in message
news:db*************************@posting.google.co m...

Has anyone tried to create a SQL7 view using the CREATE VIEW command
and ADO.NET? If so, is there a trick in trapping a SQL error when
trying to create the view? I have a VB.NET app that, amoung other
things, can create views based on existing tables and a table of
column name equivalents. If I accidently introduce a column name
equivalent that is the same as another column, I end up with a CREATE
VIEW SQL statement that is in error, if you run it in a query analyzer
window you get an error like the following:

Server: Msg 4506, Level 16, State 1, Procedure BAR, Line 2
Column names in each view must be unique. Column name ''FOO'' in view
''BAR'' is specified more than once.

The ExecuteNonQuery is being done inside a Try/Catch but the Catch
ain''t catchin'' it!!!

Any ideas?

The error is in your CREATE VIEW command, not in ADO.NET. Try run the CREATE VIEW command in Query Analyzer

"Zack Sessions" wrote:

Has anyone tried to create a SQL7 view using the CREATE VIEW command
and ADO.NET? If so, is there a trick in trapping a SQL error when
trying to create the view? I have a VB.NET app that, amoung other
things, can create views based on existing tables and a table of
column name equivalents. If I accidently introduce a column name
equivalent that is the same as another column, I end up with a CREATE
VIEW SQL statement that is in error, if you run it in a query analyzer
window you get an error like the following:

Server: Msg 4506, Level 16, State 1, Procedure BAR, Line 2
Column names in each view must be unique. Column name ''FOO'' in view
''BAR'' is specified more than once.

The ExecuteNonQuery is being done inside a Try/Catch but the Catch
ain''t catchin'' it!!!

Any ideas?

"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message news:<#5**************@TK2MSFTNGP11.phx.gbl>...

Zack,
What does your code look like, specifically the CREATE VIEW statement
itself?

Can you print the CREATE VIEW statement, just before you execute the
ExecuteNonQuery method?
WHile attempting to debug this issue, I added a Debug.WriteLine to
dump the SQL command to create the view which I used to cut and paste
into a Query Analyzer window. A sample create view command would look
like:

CREATE VIEW viewname AS
SELECT col1,
col2,
col3 as NewName,
col4,
col5 as NewName,
col6
FROM tablea

Note the error is that col3 and col5 both have the same alias name.
When you run this command in the Query Analyzer window, you get the
error you expect, that there are two fields with the same name. When I
try to run the command from VB.NET using ADO.NET, no error is
returned, ie, the Catch in a Try/Catch is not raised.










"Zack Sessions" <zc********@visionair.com> wrote in message
news:db*************************@posting.google.co m...

Has anyone tried to create a SQL7 view using the CREATE VIEW command
and ADO.NET? If so, is there a trick in trapping a SQL error when
trying to create the view? I have a VB.NET app that, amoung other
things, can create views based on existing tables and a table of
column name equivalents. If I accidently introduce a column name
equivalent that is the same as another column, I end up with a CREATE
VIEW SQL statement that is in error, if you run it in a query analyzer
window you get an error like the following:

Server: Msg 4506, Level 16, State 1, Procedure BAR, Line 2
Column names in each view must be unique. Column name ''FOO'' in view
''BAR'' is specified more than once.

The ExecuteNonQuery is being done inside a Try/Catch but the Catch
ain''t catchin'' it!!!

Any ideas?

这篇关于使用ADO.NET创建视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！