How do I add ASPNET user to SQLExpress DB?

I am going though the ASP.NET QuickStart tutorials. One of the lessons is to add a datagrid. I am able to do that with VWD and am able to connect to the database in VWD. However when I try to execute the application I get a "Login failure" message because the user pcName/ASPNET cannot login.

How do I add the ASPNET user to the SQLExpress database? There don't seem to be any administration tools that were installed with SQL Server Express 2005.

Thanks in advance for the help!

There are two permissions to SQL Server the Server permissions under security in Management in Management Studio and the new security section within the database. The management tools are a separate download. Hope this helps.

http://msdn.microsoft.com/vstudio/express/sql/compare/default.aspx

|||Thank you so much! It worked!|||

the link you posted is taking me to sqlexpress home page. And I don't see anything that is related to our discussion. Could you help little more and send me what I need to do in order to add aspnet user to sqlexpress??

thanks

Cemal

How can you use SQL Full Text Search CONTAINS() with an asp.net 2.0 ObjectDataSource using

How can you use SQL Full Text Search CONTAINS() with an asp.net 2.0 ObjectDataSource using @.Parameters?

MSDN says something like this, but only works directly using like the Query from SQL Manager:

USE TestingDB;
GO
DECLARE @.SearchWord NVARCHAR(30)
SET @.SearchWord = N'performance'
SELECT TestText
FROM TestingTable
WHERE CONTAINS(TestText, @.SearchWord);

I tryed to mak something like that work with the DataSet DataAdapter Query Builder for the ObjectDataSource, but you can't use DECLARE or SET.

SELECT TestText
FROM TestingTable
WHERE CONTAINS(TestText, @.SearchWord);

But again it says @.SearchWord not a valide SQL Construct

Is there anyway to make a DataSet.DataApater.ObjectDataSource work with an SQL FTS CONTAINS() with @.Parameters?

Have you tried putting it into a stored procedure?

|||

No, I habe not tried using a stored procedure, thanks for the reminder, I will try that, if it works out great I will tell you, thanks.

|||

In the query window I do:

DECLARE @.SearchWord NVARCHAR(128)

SET @.SearchWord = N'Water'

SELECT Answers.BusinessId, Answers.BusinessName, BusinessType.TypeEN, BusinessType.TypeFR, BusinessMainType.TypeEN AS MainTypeEN, BusinessMainType.TypeFR AS MainTypeFR, Answers.CallBackTypeId, Answers.Q1b, Answers.IsOwnerFrench, Answers.Q2aId

FROM Answers

INNER JOIN BusinessType ON Answers.BusinessTypeId = BusinessType.BusinessTypeId

INNER JOIN BusinessMainType ON Answers.BusinessMainTypeId = BusinessMainType.BusinessMainTypeId

WHERE

(Answers.CallBackTypeId = 1) AND CONTAINS(Answers.BusinessName, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessType.TypeEN, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessType.TypeFR, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessMainType.TypeEN, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessMainType.TypeFR, @.Keywords)

ORDER BY Answers.BusinessName

END

It works great and I get the values returned that I should, but only in the SQL Manager 2005, or a query in visual studio 2005, and not in the DataSet Builder for ObjectDataSource.

When I do this as a stored procedure as:

CREATE PROCEDURE SP_Keywords

@.Keywords NVARCHAR(128)

AS

BEGIN

SELECT Answers.BusinessId, Answers.BusinessName, BusinessType.TypeEN, BusinessType.TypeFR, BusinessMainType.TypeEN AS MainTypeEN, BusinessMainType.TypeFR AS MainTypeFR, Answers.CallBackTypeId, Answers.Q1b, Answers.IsOwnerFrench, Answers.Q2aId

FROM Answers

INNER JOIN BusinessType ON Answers.BusinessTypeId = BusinessType.BusinessTypeId

INNER JOIN BusinessMainType ON Answers.BusinessMainTypeId = BusinessMainType.BusinessMainTypeId

WHERE

(Answers.CallBackTypeId = 1) AND CONTAINS(Answers.BusinessName, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessType.TypeEN, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessType.TypeFR, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessMainType.TypeEN, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessMainType.TypeFR, @.Keywords)

ORDER BY Answers.BusinessName

END

It prompts me for the value of the @.Keywords, I get 0 rows returned, it does execute properly no errors, and it is connecting to the right DB/Tables so on.

I'm new to stored procedures, I normaly just use SQL Queries, but I see all the advantages of using Stored Procedures.

Can you help me if you can please and thank you?

|||

Your queries are bad code. You cannot mix AND with OR like you have done. It is afundamental logic error.

You must use ( ) around the various units of code that go together.

Example

WHERE ( Answers.CallBackTypeId = 1
AND CONTAINS(Answers.BusinessName, @.Keywords)
)
OR ( Answers.CallBackTypeId = 1
AND CONTAINS(BusinessType.TypeEN, @.Keywords)
)
OR ...

END

WHERE Answers.CallBackTypeId = 1
AND ( CONTAINS(Answers.BusinessName, @.Keywords)
OR CONTAINS(BusinessType.TypeEN, @.Keywords)
OR ...
)

|||

I knew that, I actualy wrote the script the way you did, but like always visual studio likes to change code auto formatting crap even do that option is off, and it converted it to what I posted.

Yes the script still works even do it's mixed like it is with AND/OR, I tested it both ways, comes out with the same results, it's just the script is longer that's all, no bad.

So let me restate my problem, lets not go off track since the script works.

In the query window I do:

DECLARE @.SearchWord NVARCHAR(128)

SET @.SearchWord = N'Water'

SELECT Answers.BusinessId, Answers.BusinessName, BusinessType.TypeEN, BusinessType.TypeFR, BusinessMainType.TypeEN AS MainTypeEN, BusinessMainType.TypeFR AS MainTypeFR, Answers.CallBackTypeId, Answers.Q1b, Answers.IsOwnerFrench, Answers.Q2aId

FROM Answers

INNER JOIN BusinessType ON Answers.BusinessTypeId = BusinessType.BusinessTypeId

INNER JOIN BusinessMainType ON Answers.BusinessMainTypeId = BusinessMainType.BusinessMainTypeId

WHERE

(Answers.CallBackTypeId = 1) AND CONTAINS(Answers.BusinessName, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessType.TypeEN, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessType.TypeFR, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessMainType.TypeEN, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessMainType.TypeFR, @.Keywords)

ORDER BY Answers.BusinessName

END

It works great and I get the values returned that I should, but only in the SQL Manager 2005, or a query in visual studio 2005, and not in the DataSet Builder for ObjectDataSource.

When I do this as a stored procedure as:

CREATE PROCEDURE SP_Keywords

@.Keywords NVARCHAR(128)

AS

BEGIN

SELECT Answers.BusinessId, Answers.BusinessName, BusinessType.TypeEN, BusinessType.TypeFR, BusinessMainType.TypeEN AS MainTypeEN, BusinessMainType.TypeFR AS MainTypeFR, Answers.CallBackTypeId, Answers.Q1b, Answers.IsOwnerFrench, Answers.Q2aId

FROM Answers

INNER JOIN BusinessType ON Answers.BusinessTypeId = BusinessType.BusinessTypeId

INNER JOIN BusinessMainType ON Answers.BusinessMainTypeId = BusinessMainType.BusinessMainTypeId

WHERE

(Answers.CallBackTypeId = 1) AND CONTAINS(Answers.BusinessName, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessType.TypeEN, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessType.TypeFR, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessMainType.TypeEN, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessMainType.TypeFR, @.Keywords)

ORDER BY Answers.BusinessName

END

It prompts me for the value of the @.Keywords, I get 0 rows returned, it does execute properly no errors, and it is connecting to the right DB/Tables so on.

I'm new to stored procedures, I normaly just use SQL Queries, but I see all the advantages of using Stored Procedures.

Can you help me if you can please and thank you?

|||

cdmlb:

In the query window I do:

DECLARE @.SearchWord NVARCHAR(128)

SET @.SearchWord = N'Water'

SELECT Answers.BusinessId, Answers.BusinessName, BusinessType.TypeEN, BusinessType.TypeFR, BusinessMainType.TypeEN AS MainTypeEN, BusinessMainType.TypeFR AS MainTypeFR, Answers.CallBackTypeId, Answers.Q1b, Answers.IsOwnerFrench, Answers.Q2aId

FROM Answers

INNER JOIN BusinessType ON Answers.BusinessTypeId = BusinessType.BusinessTypeId

INNER JOIN BusinessMainType ON Answers.BusinessMainTypeId = BusinessMainType.BusinessMainTypeId

WHERE

(Answers.CallBackTypeId = 1) AND CONTAINS(Answers.BusinessName, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessType.TypeEN, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessType.TypeFR, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessMainType.TypeEN, @.Keywords) OR

(Answers.CallBackTypeId = 1) AND CONTAINS(BusinessMainType.TypeFR, @.Keywords)

ORDER BY Answers.BusinessName

END

Well, the obvious thing to ask you is why you are setting @.SearchWord and searching for @.Keywords in the query studio version. The query in the query studio and in the stored procedure are not the same!

How can take Back-Up and restore database from a web form wsing asp.net(vb.net)

I want to give a facility to backup database restore database to the customer in my project.
Database is stored in sql server 2000.
From the website the browser can take backup by clicking BACKUP button and
restore database by clicking RESTORE button.
is it possiable in asp.net programatically ?

In SQL Server you backup a database using the "backup database" command. You restore a database using the "restore database" commands. You can lookup the complete syntax to these commands.

You issue these SQL commands from asp.net just like you would any other SQL command. The backup command will work even if others are using the database at the moment you do the backup. The biggest problem would be the restore. If you are going to restore a database over an existing database (overwrite it) then that database must not be in use or you will get an error.

Brian

|||i do not really recommend this approach whether you can or not !!!!
this is an admin job and it is better not to have a wide access to do this kind of operation..... i am not sure about if you can do it or not ... !!|||Its definitely possible, take a look at this page on MSDNhttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_35ww.asp This details the TSQL commands that are necessary to perform the backup/restore. Once you create the commands just execute them with SqlClient.
Hope This Helps

How can I use SQL Report in ASP.NET?

Hi,

Can I use SQL Reports in ASP.NET? Please help me how it is working. Can I use more than one tables in query?

Arun.

You can use reportviewer from VS in your ASP.NET. You may have it for the express version through SP1. You also can use multiple tables in your query through JOINS for example. If you mean for the ReportViewer datasource, you can use multple datasources for the reportviwer. I hope I answer your question in a generic way. If you have specific questions, I will try to share what I know.

How can I use Sql Express instead of Sql Server 2005 for asp.net providers

Hi all,

Since I activated the aspnet_regsql.exe tool, it has been a bad day - here's why:

Originally I installed VS2005 Pro (without Sql Express) and then Sql Server 2005 Dev. I then started to learn asp.net from the ".NET FRAMEWORK 2.0 Web-Based client Development" Training kit from Micorsoft. I have compiled and run every exercise up till Chapter 9 lesson 2 exercise 1, without the need to start the Sql Server 2005. In exercise 2 I should learn about asp.net profilers and the exercise showed me to use the aspnet_regsql.exe tool if I was using Sql Server 2005 - and so I did, and exercise 2 worked fine after a couple of adjustment in the connection string, because it was configured to use the SQLEXPRESS server by default.

However - in Chapter 9 lesson 3, I should not use asp.net providers, so I shut down the Sql Server 2005, because I don't have that much memory to spend. Now the trouble comes - every website I have made afterwards require that the Sql Server 2005 is running or else I get some errors.

How can I get back to the way it was before I activated the aspnet_regsql.exe, so I don't have to have the Sql Server 2005 running every time I want to make a website using aspx?

Thanks in advance

From your description, it sounds like you need to change the connection strings to use the SQL Express instance instead of the SQL Developer instance.|||

My connection string in both the machine.config and web.config, located in <Win dir>\Microsoft.NET\Framework\v2.0.50727\CONFIG, looks like this:

<connectionStrings>

<add name="LocalSqlServer" connectionString="data source=.\SQLEXPRESS;

Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;UserInstance=true"

providerName="System.Data.SqlClient" />

</connectionStrings>

And I do not use an web.config at the website level.

I have also tried to reverse the action done by aspnet_regsql, by selecting the "Remove application services information from an existing database" option when starting aspnet_regsql.exe. Then detaching the aspnetdb database in Sql Server 2005 and deleted the aspnetdb files (mdb and log files) from Sql Server 2005 - all with out luck.

How can I use Reporting Services on ASP.NET Pages?

Hi all,

Could you tell the correct way to use Reporting Services on ASP.NET pages?

Now I wanna create an ASP.NET page to show some reports, and I hope to control all logic by program, so I need know the API between ASP.NET and Reporting Services.

For example, I wanna pass some data to Reporting Services via API, and then Reporting Services handle the data and return reports to me, and then I can show the reports on my web pages.

Please tell me how?

Hi Xinwen.

You can use the Report control and give hime the link to your report so it will show in side the control.

You don't interact with the report like you described - You can interact with the report with PARAMETERS passing through the URL.

So whan you give the report control the report url you can add to that url parameters that the report should get (like person id or project number) and also to change things in the report (like open it in diffrent ways, change the zoom, automaticlly export to pdf and so on) with the url command parameters.

for exm:
ttp://reportserver/myreportfolder/reportname.rdl?rc:format=PDF will export it to pdf.

search google about "report service url parameters"

good luck,
Roy.|||Thanks Roy!

How can I use Reporting Services on ASP.NET Pages?

Hi all,

Could you tell the correct way to use Reporting Services on ASP.NET pages?

Now I wanna create an ASP.NET page to show some reports, and I hope to control all logic by program, so I need know the API between ASP.NET and Reporting Services.

For example, I wanna pass some data to Reporting Services via API, and then Reporting Services handle the data and return reports to me, and then I can show the reports on my web pages.

Please tell me how?

Hi Xinwen.

You can use the Report control and give hime the link to your report so it will show in side the control.

You don't interact with the report like you described - You can interact with the report with PARAMETERS passing through the URL.

So whan you give the report control the report url you can add to that url parameters that the report should get (like person id or project number) and also to change things in the report (like open it in diffrent ways, change the zoom, automaticlly export to pdf and so on) with the url command parameters.

for exm:
ttp://reportserver/myreportfolder/reportname.rdl?rc:format=PDF will export it to pdf.

search google about "report service url parameters"

good luck,
Roy.
|||Thanks Roy!|||

Hi,

I tried with this but instead of prompting to save Excel File,it is displaying the report

http://localhost/Reports/Pages/Report.aspx?ItemPath=NameOfReport&rs:Command=Renderl&rs:Format=Excel

-Thanks,

Digant Desai

|||

In Visual studio 2005,there is a report veiwer contrlo to view your RDL reports.

U need to link report viewer control to corresponding report in Reporting service project.This u can do

by setting Propertis of report viewer.

You can also pass parameters form ASP page to rdl file.

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class Default2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
this.ReportViewer2.Visible = false;
}
protected void Button1_Click(object sender, EventArgs e)
{

this.ReportViewer2.Visible = true;
this.ReportViewer2.ServerReport.ReportServerUrl = new System.Uri("http://localhost/ReportServer");

string strReport = "/ReportProject14/Report1";

this.ReportViewer2.ServerReport.ReportPath = strReport;

Microsoft.Reporting.WebForms.ReportParameter[] RptParameters =
new Microsoft.Reporting.WebForms.ReportParameter[3];

string strComp = this.DropDownList1.SelectedItem.Value;
RptParameters[0] =
new Microsoft.Reporting.WebForms.ReportParameter("INTCOMPANYKEY", strComp);

string strFacility = this.DropDownList3.SelectedItem.Value;
RptParameters[1] =
new Microsoft.Reporting.WebForms.ReportParameter("INTFACILITYKEY", strFacility);

string strActivity = this.DropDownList2.SelectedItem.Value;
RptParameters[2] =
new Microsoft.Reporting.WebForms.ReportParameter("Activity", strActivity);

this.ReportViewer2.ServerReport.SetParameters(RptParameters);
this.ReportViewer2.ServerReport.Refresh();
}
}