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

creating a table dynamically in SQL Server Express with asp.net and vb

Hi,

Is there a way to dynamically create a new table in sql server express using the code behind with vb on a Page_Load event?

Thanks Matt

sure you can. Create a command object, sent the text as

CREATE

TABLE [dbo].[userdata](

[userID] [int]

IDENTITY(1,1)NOTNULL,

[varchar]

(50)COLLATE SQL_Latin1_General_CP1_CI_ASNULL,

[name] [varchar]

(50)COLLATE SQL_Latin1_General_CP1_CI_ASNULL

)

ON [PRIMARY]

execute the command you will get your table in the database,

Hope this help.

|||

Thank You. That works great. It is exactly what I was looking for.

Matt

Creating a Database using ASP.NET

Hi All,

Can anybody help me/does anybody have the code to create a database on ms sql server 2000 using asp.net?

This needs to be done without being asked for the sql server login credentials (i.e the sa username/password can be inserted in the code)Using Microsoft Data Access Application Blocks, I would do the following (C#):
SQLHelper.ExecutNonQuery("Connection string", CommandType.Text, "Create Database...");
That's a simple brute force way to do it, you'll obviously need to play and tweak it, but hopefully that will get you started.

http://www.able-consulting.com/dotnet/adonet/Data_Providers.htm#SQLClientManagedProvider
is a link to where the connection strings for ADO.NET are.

HTH|||Thanks for the pointer.

Not being much of a .net coder, do you know of any complete solutions (web based database administration tools) or even better, do you know anybody that would be able to create the script for me...i'll pay of course!|||http://weblogs.asp.net/ashben/archive/2003/09/28/29469.aspx is a link to some web based datbase administration tools. Let us know if that is what you need or if you actually need someone to do this for you.|||Hi ericlandes,

Thanks for the link. I do still require someone to code an asp.net page for me to create databases.

* The page must be available to anonymous users (anyone can create a database) - So maybe the 'sa' user credentials are coded into the asp.net form?

* The form will allow the user to specify a database name to create and create an sql username/password with permissions to that database

* When the database is created, it will have certain options set (i.e maximum database size of 2mb)|||So will each person not only create the database, but create different fields, with different types, etc, or will each person basically generate a new 'template' database, all of them having the same fields/datatypes, with the only difference being the name of the database, it's username & password?|||The basic concept is:

User visits page, enters the name of the database they want to create, and also picks a username/password to create which will have access this new database.

Basic checks will need to be made to ensure that the database and username do not already exist.

The database will be created totaly blank, with the maximum size of the database set to 2mb|||anyone got any ideas?|||Hello xmsms,

It sounds to me like you are creating a web-hosting environment with data-access.

Even if this is not your intended useage, I'll bet that if you look for hosting tools for SQL-Server then you will be on the right track to finding the tool, or the right code for your needs.

Did you ever find your coder to do the job for you? If not and you are still stuck, I will be able to look into your question further after June 20th.

CreateUserWizard - Stored Procedure

Im trying to make an "createaccount-page" and followed this guide:http://aspnet.4guysfromrolla.com/articles/070506-1.aspx

The problem is that I want to use a stored procedure instead...But Iam getting this message when pushing the create-button:

"Procedure or function usp_MyProcedure has too many arguments specified"

My stored procedure look like this: (maybe something is wrong here?)

ALTER PROCEDURE

usp_MyProcedure

(

@.UserID

uniqueidentifier OUTPUT,

@.FName

varchar(50),

@.LName

varchar(50),

)

AS

INSERT INTO MyTable (UserID, FName, LName,)Values

(@.UserID, @.FName, @.LName)

RETURN

My sqldatasource:

<asp:SqlDataSourceID="InsertExtraInfo"runat="server"ConnectionString="<%$ ConnectionStrings:ASPNETDBConnectionString %>"ProviderName="<%$ ConnectionStrings:ASPNETDBConnectionString.ProviderName %>"InsertCommand="usp_MyProcedure"InsertCommandType="StoredProcedure"><InsertParameters><asp:ControlParameterName="FName"Type="String"ControlID="tboxFName"PropertyName="Text"/><asp:ControlParameterName="LName"Type="String"ControlID="tboxLName"PropertyName="Text"/></InsertParameters></asp:SqlDataSource>The code behind-file is the same as the one in the article...

Ok..Now I dont understand...because I have the same sql-code as they use...(I wait with the stored procedure until it first works)....

Everything is saved in the database, but I get this message: "The variable name '@.UserId' has already been declared. Variable names must be unique within a query batch or stored procedure"

My sqldatasource look like this now:

<

asp:SqlDataSourceID="InsertExtraInfo"runat="server"ConnectionString="<%$ ConnectionStrings:ASPNETDBConnectionString %>"ProviderName="<%$ ConnectionStrings:ASPNETDBConnectionString.ProviderName %>"InsertCommand="INSERT INTO [MyTable] (UserId, FName, LName) VALUES (@.UserID, @.FName, @.LName)"><InsertParameters><asp:ControlParameterName="FName"Type="String"ControlID="tboxFName"PropertyName="Text"/><asp:ControlParameterName="LName"Type="String"ControlID="tboxLName"PropertyName="Text"/></InsertParameters></asp:SqlDataSource>|||

Where is your code to get the value for your UserId InsertParameter?

You need something like this in your code for inserting:

...

InsertExtraInfo.InsertParameters.Add("UserId", System.GUID.NewGuid().ToString());
InsertExtraInfo.Insert();

...

|||

Because I didnt get this to work, I choosed to use profiles instead...I use this code in my code-behind-file:

Protected

Sub CreateUserWizard1_CreatedUser(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles CreateUserWizard1.CreatedUser

' Create an empty Profile for the newly created userDim ProfileAs ProfileCommon =CType(ProfileCommon.Create(CreateUserWizard1.UserName,True), ProfileCommon)' Populate some Profile properties off of the create user wizard

Profile.FirstName =

CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("tboxFNamn"), TextBox).Text

Profile.LastName =

CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("tboxLNamn"), TextBox).Text' Save the profile - must be done since we explicitly created this profile instance

Profile.Save()

EndSub' Activate event fires when the user hits "next" in the CreateUserWizardPublicSub AssignUserToRoles_Activate(ByVal senderAsObject,ByVal eAs EventArgs)' Databind list of roles in the role manager system to a listbox in the wizard

AvailableRoles.DataSource = Roles.GetAllRoles

AvailableRoles.DataBind()

EndSub' Deactivate event fires when user hits "next" in the CreateUserWizardPublicSub AssignUserToRoles_Deactivate(ByVal senderAsObject,ByVal eAs EventArgs)' Add user to all selected roles from the roles listboxDim iAsInteger = 0DoWhile (i < AvailableRoles.Items.Count)If (AvailableRoles.Items(i).Selected =True)Then

Roles.AddUserToRole(CreateUserWizard1.UserName, AvailableRoles.Items(i).Value)

EndIf

i = (i + 1)

LoopEndSub

..The values from the textboxes is correctly saved in the table (aspnet_Profile), but the roles is not saved in aspnet_UsersInRoles..(for it should be saved there right?)...

Becuase the listbox called "AvailibleRoles" shows the all roles that the "new user" could use...And that role needs to be saved somewhere..(I think in the aspnet_UsersInRoles)...But its not saved there...

So what should I do to save it?

CreateSubscription service creates all the subscriptions as ASPNET user

I have a custom form which users can use to create subscriptions for specific
reprots. I am able to create subscriptions just fine but my issue is that all
the subscriptions are created as ASPNET user. Am I missing something in the
setup?
Thanks for your help.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1Hi,
Do you use Windows NT Authentication or anonymous access is enabled?
"akhan via SQLMonster.com" wrote:
> I have a custom form which users can use to create subscriptions for specific
> reprots. I am able to create subscriptions just fine but my issue is that all
> the subscriptions are created as ASPNET user. Am I missing something in the
> setup?
> Thanks for your help.
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1
>|||I am using Windows Authentication.
eralper wrote:
>Hi,
>Do you use Windows NT Authentication or anonymous access is enabled?
>> I have a custom form which users can use to create subscriptions for specific
>> reprots. I am able to create subscriptions just fine but my issue is that all
>> the subscriptions are created as ASPNET user. Am I missing something in the
>> setup?
>> Thanks for your help.
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1

CreateReport() method under ASP.NET

CreateReport throws an error saying that user IUSR_XXXXX doesn't have
permisssions to run this method.
So,
1) I went to ReportServices roles and anded a new group for IUSR_XXXXX
user
2) Assigned ReportserverSysadmin role for the new group.
3) Also set db_owner permissions for the IUSR_XXXXX user in
ReportService
related databases on
my local instance of MS SQL.
No results. Still can't call method CreateReport().
Any ideas ?
Thanks
Paul
PS. BTW this method works fine from *.rss script that is running under
local Admin account.There can be a few reasons that most likely cause this:
1. You have anonymous authentication enabled for "Reports" and
"ReportSerever" virtual folders. Go to IIS manager and make sure anonymous
access is disabled.
2. You are calling this method from within an ASP.NET application that
doesn't impersonate its user. It calls CreateReport using its own identity.
--
Dmitry Vasilevsky, SQL Server Reporting Services Developer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
---
"pTsy" <ptsygura@.bna.com> wrote in message
news:#pDOs2veEHA.2916@.TK2MSFTNGP12.phx.gbl...
> CreateReport throws an error saying that user IUSR_XXXXX doesn't have
> permisssions to run this method.
> So,
> 1) I went to ReportServices roles and anded a new group for IUSR_XXXXX
> user
> 2) Assigned ReportserverSysadmin role for the new group.
> 3) Also set db_owner permissions for the IUSR_XXXXX user in
> ReportService
> related databases on
> my local instance of MS SQL.
> No results. Still can't call method CreateReport().
> Any ideas ?
> Thanks
> Paul
> PS. BTW this method works fine from *.rss script that is running under
> local Admin account.
>