Creating a View from ASP.Net page (Problems)

Hello,

I am having an error when using the CREATE VIEW statement and trying to execute it from an ASP.net page. I get an error that says something along the lines of 'dbo' schema does not exist or you do not have permissions to access it. I have checked and the user has been granted every permission I can think of (including setting it to the DB owner), but it does not work.

I am able to execute all the Select, Update, Insert statements that I wish to, and when I copy the statement into the SQL 2005 Management studio query menu it works perfectly, it just gives the error message from the ASP.net page.

Here is an example (or as close as I can remember at this point) of some code I tried:

myCommand.Connection = myConnection
myCommand.Open()
myCommand.CommandText = "Create View TestView as Select 1, 2"
myCommand.ExecuteNonQuery()
myCommand.Close()

Any help would be appreciated.

Thanks,
StuporMan

I dont think that is a valid SQL statement.|||

You are correct, but it can be made correct by changing the statement to:

"Create View TestView as Select 1 as test1, 2 as test2"

(you can verify this by running in SQL Server management studio).

Andthis does not change the ASP.net error message.

You can do thefollowing test to show what I mean.

Create an ASP.net page with 2buttons.

One button click runs this code:

myCommand.Connection = myConnection
myCommand.Open()
myCommand.CommandText = "Select 1 as test1, 2 as test2"
myCommand.ExecuteNonQuery()
myCommand.Close()

(I have left the definitions and SQL connection string up to you)

On the other button click run this:

myCommand.Connection = myConnection
myCommand.Open()
myCommand.CommandText = "Create View TestView as Select 1 as test1, 2 as test2"
myCommand.ExecuteNonQuery()
myCommand.Close()

And you should produce the same error message as I am getting.

Thanks,

StuporMan

|||

StuporMan:

I am able to execute all the Select, Update, Insert statements that I wish to, and when I copy the statement into the SQL 2005 Management studio query menu it works perfectly, it just gives the error message from the ASP.net page.

I've tried to run your command in SQL Server Management Studio:

Create View TestView as Select 1, 2

and it generates this error message:

Msg 4511, Level 16, State 1, Procedure TestView, Line 1
Create View or Function failed because no column name was specified for column 1.

|||

Ok, I had mistyped the SQL I was using as I had to do it from memoryat the time. Here is a valid Create View statement that produces thesame error in ASP.net:

"Create View TestView as Select 1 as test1, 2 as test2"

This should run in SQL Management studio. If it does not try"Create View TestView as Select * from <insert table name here>",which should also be valid. In either case, ASP.net should produce thesame error message.

Thanks,

StuporMan

|||

I am still having problems even after correcting the SQL string.Tryany valid CREATE VIEW statement from ASP.net and see if it will work. Ihave not been able to get any to work to date.

|||Presumably whatever context your web application is under doesn't have the needed security. Are you sure you elevated the permissions of the correct account? (Just guessing here, as I've never tried creating views from a web application myself.)|||

Thanks for the response, you prompted me to again check mypermissions. The user I was logging into the SQL server with DIDhave the correct permission set. The ASPNET user did not. Giving ASPNETthe permission did the trick.

Thanks,

StuporMan