Executenonquery

[Guest]

I have wrote this code and my DB server is Sql Server 2000 SP4

Dim cnSql as new SqlClient.SqlConnection
Dim cmdSql As New SqlClient.SqlCommand

cnsql="Data Source=ced2; User ID=sa; Password=;Initial Catalog=NGN;Persist
Security Info=True;"

cnsql.open

cmdSql.CommandText = "create view VISTAGIACENZE as select
codice,sum(giacenza) as Giacenza from giacenze where magazzino='001' or
magazzino='500' group by codice"

cmsql.connection=cnsql

cmdSql.CommandText = strsql
cmdSql.ExecuteNonQuery()

When I try to execute method executenonquery Visual Studio return this error

System.Data.SqlClient.SqlException - Incorrect syntax near the keyword
'VIEW'.

It's very strange because executing the same string in query analyzer works
fine without errors.

Could you help me???

Thanks in advance.

Antonio.

 

[W.G. Ryan eMVP]

I tried it with a mock table and it's parsing. I only have Yukon on the
box i'm at though and VS2005 so I didn't run the commmand. However I'd run
profiler and see what's being sent to the DB. I was thinking there might be
a problem with the column alias being the same name as the table, but then I
realized it's just that my Italian isn't very good. I'd mention one other
thing.. checck the SqlErrors collection and make sure there aren't any
other errors in it.. sometimes when you get SqlExceptions, you can get stuff
back that's misleaeding unless you iterate the SqlErrors collection. This
is just a guess though.. b/c from QA everything is parsing correctly.

Can you post the create script for the table? i'll be home in about 30
mintues and I'll be glad to recreate the same table structure and see what I
can find out.

Thanks,

Bill

 

[ADF]

Bill, thank you very much.
The column alias and table name are different because giacenza is like
warehouse and giacenze is like warehouses. I have VS2003, .NET 1.1 SP1
and SQLSERVER 2K SP4.

View create script is the following:

create view [VISTAGIACENZE]
as
select codice,sum(giacenza) as Giacenza
from giacenze where magazzino='001' or magazzino='500' group by codice

and is built on this table

CREATE TABLE [GIACENZE] (
[Progressivo] [bigint] NOT NULL ,
[Magazzino] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Codice] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Giacenza] [decimal](18, 2) NULL ,
[Versamenti] [decimal](18, 2) NULL ,
[Consumi] [decimal](18, 2) NULL ,
[Acquisti] [decimal](18, 2) NULL
) ON [PRIMARY]

Now I try to parse SqlError collection. I will post results as soon as
possible.

Thank you.

Antonio.

W.G. Ryan eMVP ha scritto: