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 do i access the SQL server from a website.

How do I access the SQL server from a website, using my Machine Account, not the ASP.NET account?

thanks.

.intrino.On www.connectionstrings.com you can see all of the possible types of connection strings.

When you say "my machine account" do you mean a SQL Server Login, or using impersonation and your Windows account. If you want to use your Windows account, are both machines in the same Windows domain, and do you have a domain account?|||I want to use my account that is like so:

myDomain/Intrino

Instead the site connects as myDomain/ASPNET to the SQL server...and the connection string that i have set up is the SSPI and my IIS is setup as Windows Authentication and it setup as well in the web.config to use the Windows login.

I would like to connect as myDomain/Intrino...how do I transfer the login into the connection string?|||Anyone have any information on this?...|||You need to have ASP.NET run under a different account (your account).

In the Web.Config:

<identity impersonate="true" userName="domain\Jane" password="pass"/>|||So no way to the Web.Config dynammically to impersonate the user logged on?

Thanks for the answer.

.intrino.sql

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 it automatically generating a ordered number

hi,

i am a newcomer and a freshman in asp.net. i am now writing a web-based system for SME as my final year project. i am going to use sql server and asp.net in C# to perform my final year project.

as asp.net is new for me, i would have some simple problems to ask.

1. in the project, i would like the system can automatically generate the enquiry number for each new order input to the system. for example today is 05 July 2006, the enquiry number would like 2006211xxxx, where 2006 is year, 211 is the day count start from 1 Jan and xxxx is the random number/ ordered number. how can i implement this? i even don't know how to generate the ordered number. could anyone help me

2. if there is an unknown test sample in each order input. as the sample number for each order is different, how can i set a flexible table that can have different number of rows for user to input the test result.

thanks

Rgds, universe

1. I suggest you can have two columns: one used to record the inserted data for the row, another for row ID. So you can create a table as following:

create table tbl_test(id int identity(1,1),RecDate smalldatetime default getdate(), Description varchar(200))
create table tbl_test1(id uniqueidentifier default newid(),RecDate smalldatetime default getdate(), Description varchar(200))

insert into tbl_test(description) select 'This is a test row'

insert into tbl_test1(description) select 'This is a test row'

2. Sorry I'm not clear about this issue

How can I verify the availability of an SQL server

Hello everyone,

I have an ASP application mainly connected to one SQL database that works great but now I am trying to add some functionality that requires to connect to another remote SQL server. Till now all is fine except that the remote SQL server is not always online and of course when this happens my ASP application stops with the following error:

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80004005)
[DBNETLIB][ConnectionOpen (Connect()).]Specified SQL server not found.

in my Global.asa I setup my session variables for DSN connections and in my pages I call my SQL connection as follow:

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open Session("MySQL")

SQL = "my SQL statement here;"
Set RS = Conn.Execute(SQL)

Every time the page hits the Conn.Open line I get the error if the remote SQL is not online!!!

Is there a way I can check a sort of returned error code holding the connection status before getting to the Conn.Open line?

Any help would be greatly appreciated.

Thx in advance

You could put an error handler around it to catch the error if the server is not available

Check this site for some information on error handling: http://www.magictree.com/vbcourse/11design/errors.htm

So you could create a function to see if the server is online using the error handler of asp.

|||

Thank you Mark,

Well of course creating an error handling is what I am trying to do and the link you supplied is great, except it is for client side scriptting. ASP page are server side scrippting. In ASP.NET I could use it but not in pure old fashion ASP, I mean not that I know of!!!

For sure I need more info on how to!!! Please please please anyone!

The only other way I have found is to do it directly from my primary SQL server in a procedure that I can call from my ASP page but there too I need help so I rather follow one lead first and when exhausted the other.

Thank you again Mark

|||

AAAAAAAAAAHHHHHHHHH

Actually I am going to repeat myself but thank you Mark you put me on the right track and I got my solution.

Here it is:

<%
Response.Buffer = True
On Error Resume Next

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open Session("MySQL")

Set ConnRemote = Server.CreateObject("ADODB.Connection")
ConnRemote.Open Session("MySQLRemote")

If Err.Number <> 0 Then
Response.Clear
'set my code without remote SQL here
Else
'set my code with remote SQL here
End If
%>

Thank you so much... I knew I would get an answer in here!

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 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();
}
}

How can i use a report from Access 97

I was wondering if there is a way I can use a report from Access 97 to print a certificate using SQL Server and being displayed in ASP as well?We use Access 97 to create monthly reports and have them available for the Web. When we run the report we save it in a format called Snapshot, there is a viewer that can be used in an ASP to view Snapshot reports. They look just like they do in Access.

Here is an example of the ASP code:

<OBJECT ID="SnapshotViewer" WIDTH=100% HEIGHT=97%
CODEBASE="/apps/CabFiles/Snapview.ocx"
CLASSID="CLSID:F0E42D60-368C-11D0-AD81-00A0C90DC8D9">
<PARAM NAME="_ExtentX" VALUE="16722">
<PARAM NAME="_ExtentY" VALUE="11774">
<PARAM NAME="_Version" VALUE="65536">
<PARAM NAME="SnapshotPath" VALUE="<%=reportName%>">
<PARAM NAME="Zoom" VALUE="0">
<PARAM NAME="AllowContextMenu" VALUE="-1">
<PARAM NAME="ShowNavigationButtons" VALUE="-1">
</OBJECT>


We only do this for monthly reports, I've created a Perl script that runs after hours to generate these reports. I'm not to sure of performance and concurrency if I had these reports generated on demand by the front-end.|||but you really should be using Access 2002 with Access Data Projects.

they are a lot faster for reporting than traditonal MDB.

How can i upload the files(any kinda) onto the database??

Hi everyone,

I am developing an web-database application using ASP.NET, C# and sql server 2000 .

In the application i want to upload different scanned files (pdf,doc,excel,jpeg,ttf,etc) onto the database.. how can i carry out this task :-?

If any one got the solution.. then, please let me know...

thanks in advance

Step 1: Use the ASP.NET file input control to let the user select a file. This is in the HTML section of the toolbox.

Step 2: Make sure that the is set correctly to support file uploads. This means settings its encType to multipart/form-data.

Step 3: Read the file details on the server side code from the Page.Request.Files property.

Step 4: From here you can then save the files to disk and just add an index to them in the database, or you can save their contents into the DB itself (as BLOBs).

Hope this helps.|||

Hey thanx man..

Will try it out.. and if any probs will ask for ur help

|||

Hi,

It really helped me.. but can u plz repeat the 2nd step.. coz.. theres a bit confusion reading that :(

|||In order to allow the uploading of files, you need to set the encType property of your form element.

Open the .aspx file in HTML view, locate the element and add

encType="multipart/form-data" inside the element.

e.g.

Essentially, this allows you to upload a combination of files and form variables.

Hope this helps.|||

Hey thanx U soo much man..

well i gonna try out this and will definitely tell u the out-put of the same.

Thanx again, and keep the good work going

|||

Hi,

I couldnt add the encType="multipart/form-data" into the file input :(

when i tried adding it to the input tag of the file, it gave an parse error,,

So, plz let me know the soln..

<form id="Form1" method="post" runat="server">
<asp:Label id="Label2" style="Z-INDEX: 102; LEFT: 386px; POSITION: absolute; TOP: 106px" runat="server"
ForeColor="Maroon" Font-Size="Medium" Font-Bold="True" Width="265px">Uploading new Employee Details</asp:Label><INPUT style="Z-INDEX: 121; LEFT: 494px; POSITION: absolute; TOP: 431px" type="file" id="File1" name="File1" runat="server">

I tried adding it in the input tag, but encountered an parse error,hope i will get the soln,,

thanks in advance

|||You need to add the encType attribute to the form element, not the input element.

How can I track the queries being issued against my sql server 2000 instance?

I have an ASP.NET app built on top of SQL Server 2000. My app is running slowly and I think I'm issuing too many queries to the database.

How can I track the queries, and when they are being issued, against my sql server 2000 instance? Is there a tool to view the queries, or is it all in a log file somewhere?

This will help me tune my ASP.NET caching strategy.

Any help is greatly appreciated!

Franco

Use SQL Server Profiler|||Choose: SQL Server, Tools, SQL Profile, File, New, Trace, Choose Server...

On the filters tab of the new window you see you can choose various filters. These include database name, application name, NT UserName, there are many to choose from.

SQL Profiler is an amazing tool for seeing whats happening "under the hood" I suggest you really read up on it in SQL Books Online as its extremely powerful.

Keep us all up to date with how you are getting on.

hth

Pace
|||Fantastic... just what I needed. Thanks!

How can I track the queries being issued against my sql server 2000 instance?

I have an ASP.NET app built on top of SQL Server 2000. My app is running slowly and I think I'm issuing too many queries to the database.

How can I track the queries, and when they are being issued, against my sql server 2000 instance? Is there a tool to view the queries, or is it all in a log file somewhere?

This will help me tune my ASP.NET caching strategy.

Any help is greatly appreciated!

Franco

Use SQL Server Profiler|||Choose: SQL Server, Tools, SQL Profile, File, New, Trace, Choose Server...

On the filters tab of the new window you see you can choose various filters. These include database name, application name, NT UserName, there are many to choose from.

SQL Profiler is an amazing tool for seeing whats happening "under the hood" I suggest you really read up on it in SQL Books Online as its extremely powerful.

Keep us all up to date with how you are getting on.

hth

Pace
|||Fantastic... just what I needed. Thanks!