Needed: User, Login, Connection advice.

HI,
I'm still on the steep side of the learning curve with ASP.NET. I've looked through a number of threads on this forum, and have gotten pieces of the answer, but need help getting past a roadblock.

I haven't been able to get a simple test application to connect to the Pubs database loaded on my system running MSDE. The only control in the application is a WebDataForm created by the Wizard. Everything works well (even the Preview Data form the Data menu loads and displays the correct data), except when the form is viewed in a browser. Click the load button and an error:Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.

Similar stories are common on this forum, and I tried to address the problem. I'm quite sure it's a autherization or authentication problem. I have Windows Server 2003 (with IIS 6.0), VS.NET 03 and MSDE as the SQL server on the same box. The MSDE server is using Windows Security mode. I Created a new user called ASPNET on the Windows Server. I added a login to the SQL Server 'WinServerName\ASPNET' using windows auth. Still get the same login failed message.

Is there something I'm missing? Do I have to add a new user to IIS?

For the record, what users/settings do I need to have in Windows, SQL-Server and IIS, get past this login problem.

Thanks for answering this basic question - one more time.Hi,
An Addendum to my message:

I found out that WINDOWS SERVER 2003 in native mode (default), running IIS 6.0 Uses "NETWORK SERVICE" rather than "ASPNET" as the default login user name for ASP.NET applications. I had not seen this in the messages I looked through in this forum.

When I, after a number of dead ends, created a new Login for my SQL Server - (as I'm using MSDE, I used The Web Data Administrator rather than Enterprise Manager or OSQL to create the new login) - with the name 'NT AUTHORITY\NETWORK SERVICE', the application was able to access the database through the VS and IE browswers.

As this test application is only used locally, I'm not concerned with the security ramifications of this method of access. However, I can see that it will be a big concern, and a lot of work to establish the appropriate methods when I have a production application to deploy.

I still would welcome advice on what strategies you have used to provide appropriate access privilages to ASP.NET applications and users that will need to access data from SQL Server databases in production environments.

I know that this is asking a lot - as there are so many permutations to consider. What I would hope you could provide, is a base line of "must" and "must never" stepsfor establishing data connections to data sources using ASP.NET.

If this is too open ended a question - let me know your thoughts on that as well. In looking through the messages of the past 3 months on this forum, I know that many have been frustrated by the complxity of getting a database connection established. Yes, the information to get it done is out there, but the sheer volume can be daunting.

Thanks for all you help with my questions.

need urgent help

Hi,

I am creating a attendance sheet software for inhouse use.

my data is like this:-

-----------------------------
| name | login time | logout
time |
-----------------------------
| a | 2007-11-10 12:00:00 | 2007-11-10
16:00:00 |
-----------------------------
| b | 2007-11-10 15:00:00 | 2007-11-10
18:00:00 |
-----------------------------

My requirement:-

I want to generate an hourly report like this:-
----------------------------
date time range total people logged
in
----------------------------
2007-11-10 0 -2 0
----------------------------
2007-12-10 2-4 0
----------------------------
..
..
----------------------------
2007-11-10 12-14 1
----------------------------
2007-11-10 14-16 2
----------------------------
2007-11-10 16-18 1
-----------------------------
..
..
----------------------------
2007-11-10 22-24 0
----------------------------

This is what I want to creat , but I don't know how can I generate
such kind of report.

Can you please guide me for the same. Please reply urgently.

Thanks & Regards,
BhishmHi Bhishm,

I'm afraid you will need to supply a lot more information than this.
What table(s) exist for storing these details? What technology are you
using to design the report? What does the "Time Range" value in the
report represent (looks like hours?) Do you simply need a SQL
Statement to prepare data in the "report" format you specified?

If I simply assume that everything un-said is as I imagine it, I guess
the solution might be something like:
/* Initialise data table */
CREATE TABLE tblLog (LogName nvarchar(50), LogInTime datetime,
LogOutTime datetime)

INSERT INTO tblLog (LogName, LogInTime, LogOutTime)
SELECT 'personA', '2007-11-10T12:00:00', '2007-11-10T16:00:00'
UNION ALL
SELECT 'personB', '2007-11-10T15:00:00', '2007-11-10T18:00:00'
UNION ALL
SELECT 'personC', '2007-11-10T11:00:00', '2007-11-10T14:00:00'

/* Create supporting table */
CREATE TABLE HrInDay (HrMin INT, HrMax INT, TimeRange VARCHAR(10))

DECLARE @.i INT, @.Date VARCHAR(8)

SET @.i = 0
SET @.Date = '20071110' -- Date parameter for "Report"

WHILE @.i < 24
BEGIN
INSERT INTO HrInDay (HrMin, HrMax, TimeRange)
VALUES (@.i, @.i + 2, CAST(@.i AS VARCHAR) + ' - ' + CAST(@.i + 2 AS
VARCHAR))
SET @.i = @.i + 2
END

/* Select from a derived table so it's sorted - there is probably a
better way to do this but I'm too lazy to find it :) */
SELECT LogDate, TimeRange, NoPplLogged
FROM (
SELECT @.Date AS LogDate,
MAX(TimeRange) AS TimeRange,
HrMin,
COUNT(DISTINCT LogName) AS NoPplLogged
FROM tblLog
RIGHT JOIN HrInDay
ON DATEPART(hh, LogInTime) < HrMax
AND DATEPART(hh, LogOutTime) HrMin
AND CONVERT(VARCHAR(8), LogInTime, 112) = @.Date
GROUP BY HrMin
) AS Report

DROP TABLE HrInDay
DROP TABLE tblLog

Good luck!
J|||Thanks a lot :) JhofM for showing the way.

I got a lot of help from it in solving it.

Now I am looking into possilities and will let you know my results.

Thanks a lot again, it's of great help

need urgent help

Hi,
I am creating a attendance sheet software for inhouse use.
my data is like this:-
-----
| name | login time | logout
time |
-----
| a | 2007-11-10 12:00:00 | 2007-11-10
16:00:00 |
-----
| b | 2007-11-10 15:00:00 | 2007-11-10
18:00:00 |
-----
My requirement:-
I want to generate an hourly report like this:-
----
date time range total people logged
in
-----
2007-11-10 0 -2 0
----
2007-12-10 2-4 0
----
.
.
-----
2007-11-10 12-14 1
-----
2007-11-10 14-16 2
----
2007-11-10 16-18 1
-----
.
.
-----
2007-11-10 22-24 0
----
This is what I want to creat , but I don't know how can I generate
such kind of report.
Can you please guide me for the same. Please reply urgently.
Thanks & Regards,
Bhishm> This is what I want to creat , but I don't know how can I generate
> such kind of report.
An idea is to create a temp table, and filling it op with one row at a time.
The rows can be foud by declaring a datetime variable, and assigning it the
lowest datetime you need to have in your report.
Then you kan do something like this:
create table #temptable (...)
declare startdate datetime
set @.startdate '2007-11-20 00:00:00.000'
while @.startdate < getdate()
begin
insert into #temptable
select @.startdate as time, count(*)
from loggingtable
where login_time between @.startdate and dateadd(hh, 2, @.startdate)
group by login_time
set @.startdate = dateadd(hh,2,@.startdate)
end
The above is not tested, but my idea should shine through, so you can
continue your own work.
/Sjang|||Brishim
Untested
create table #t (name char(1),login datetime,logout datetime)
insert into #t values ('a', '2007-11-10 12:00:00','2007-11-10 16:00:00')
insert into #t values ('b', '2007-11-10 15:00:00','2007-11-10 18:00:00')
select '12-14' [time range] ,
count(case when convert(char(2),login,108) >= 12 and
convert(char(2),login,108)< 15
or convert(char(2),logout,108) >= 12 and convert(char(2),logout,108)<
15
then 1 end) [total people logged] from #t
union all
select '14-16' [time range],
count(case when convert(char(2),login,108) >= 14 and
convert(char(2),login,108)< 17
or convert(char(2),logout,108) >= 14 and convert(char(2),logout,108)<
17
then 1 end)
from #t
"Bhishm" <bhishms@.gmail.com> wrote in message
news:f74d745f-661a-4a57-8b18-a685b8658c43@.i37g2000hsd.googlegroups.com...
> Hi,
> I am creating a attendance sheet software for inhouse use.
> my data is like this:-
> -----
> | name | login time | logout
> time |
> -----
> | a | 2007-11-10 12:00:00 | 2007-11-10
> 16:00:00 |
> -----
> | b | 2007-11-10 15:00:00 | 2007-11-10
> 18:00:00 |
> -----
> My requirement:-
> I want to generate an hourly report like this:-
> ----
> date time range total people logged
> in
> -----
> 2007-11-10 0 -2 0
> ----
> 2007-12-10 2-4 0
> ----
> .
> .
> -----
> 2007-11-10 12-14 1
> -----
> 2007-11-10 14-16 2
> ----
> 2007-11-10 16-18 1
> -----
> .
> .
> -----
> 2007-11-10 22-24 0
> ----
>
> This is what I want to creat , but I don't know how can I generate
> such kind of report.
> Can you please guide me for the same. Please reply urgently.
> Thanks & Regards,
> Bhishm

need urgent help

Hi,
I am creating a attendance sheet software for inhouse use.
my data is like this:-
----
--
| name | login time | logout
time |
----
--
| a | 2007-11-10 12:00:00 | 2007-11-10
16:00:00 |
----
--
| b | 2007-11-10 15:00:00 | 2007-11-10
18:00:00 |
----
--
My requirement:-
I want to generate an hourly report like this:-
----
--
date time range total people logged
in
----
--
2007-11-10 0 -2 0
----
--
2007-12-10 2-4 0
----
--
.
.
----
--
2007-11-10 12-14 1
----
--
2007-11-10 14-16 2
----
--
2007-11-10 16-18 1
----
--
.
.
----
--
2007-11-10 22-24 0
----
--
This is what I want to creat , but I don't know how can I generate
such kind of report.
Can you please guide me for the same. Please reply urgently.
Thanks & Regards,
Bhishm> This is what I want to creat , but I don't know how can I generate
> such kind of report.
An idea is to create a temp table, and filling it op with one row at a time.
The rows can be foud by declaring a datetime variable, and assigning it the
lowest datetime you need to have in your report.
Then you kan do something like this:
create table #temptable (...)
declare startdate datetime
set @.startdate '2007-11-20 00:00:00.000'
while @.startdate < getdate()
begin
insert into #temptable
select @.startdate as time, count(*)
from loggingtable
where login_time between @.startdate and dateadd(hh, 2, @.startdate)
group by login_time
set @.startdate = dateadd(hh,2,@.startdate)
end
The above is not tested, but my idea should shine through, so you can
continue your own work.
/Sjang|||Brishim
Untested
create table #t (name char(1),login datetime,logout datetime)
insert into #t values ('a', '2007-11-10 12:00:00','2007-11-10 16:00:00')
insert into #t values ('b', '2007-11-10 15:00:00','2007-11-10 18:00:00')
select '12-14' [time range] ,
count(case when convert(char(2),login,108) >= 12 and
convert(char(2),login,108)< 15
or convert(char(2),logout,108) >= 12 and convert(char(2),logout,108)<
15
then 1 end) [total people logged] from #t
union all
select '14-16' [time range],
count(case when convert(char(2),login,108) >= 14 and
convert(char(2),login,108)< 17
or convert(char(2),logout,108) >= 14 and convert(char(2),logout,108)<
17
then 1 end)
from #t
"Bhishm" <bhishms@.gmail.com> wrote in message
news:f74d745f-661a-4a57-8b18-a685b8658c43@.i37g2000hsd.googlegroups.com...
> Hi,
> I am creating a attendance sheet software for inhouse use.
> my data is like this:-
> ----
--
> | name | login time | logout
> time |
> ----
--
> | a | 2007-11-10 12:00:00 | 2007-11-10
> 16:00:00 |
> ----
--
> | b | 2007-11-10 15:00:00 | 2007-11-10
> 18:00:00 |
> ----
--
> My requirement:-
> I want to generate an hourly report like this:-
> ----
--
> date time range total people logged
> in
> ----
--
> 2007-11-10 0 -2 0
> ----
--
> 2007-12-10 2-4 0
> ----
--
> .
> .
> ----
--
> 2007-11-10 12-14 1
> ----
--
> 2007-11-10 14-16 2
> ----
--
> 2007-11-10 16-18 1
> ----
--
> .
> .
> ----
--
> 2007-11-10 22-24 0
> ----
--
>
> This is what I want to creat , but I don't know how can I generate
> such kind of report.
> Can you please guide me for the same. Please reply urgently.
> Thanks & Regards,
> Bhishm

need urgent help

Hi,
I am creating a attendance sheet software for inhouse use.
my data is like this:-
-----
| name | login time | logout
time |
-----
| a | 2007-11-10 12:00:00 | 2007-11-10
16:00:00 |
-----
| b | 2007-11-10 15:00:00 | 2007-11-10
18:00:00 |
-----
My requirement:-
I want to generate an hourly report like this:-
date time range total people logged
in
-----
2007-11-10 0 -2 0
2007-12-10 2-4 0
..
..
-----
2007-11-10 12-14 1
-----
2007-11-10 14-16 2
2007-11-10 16-18 1
-----
..
..
-----
2007-11-10 22-24 0
This is what I want to creat , but I don't know how can I generate
such kind of report.
Can you please guide me for the same. Please reply urgently.
Thanks & Regards,
Bhishm
Brishim
Untested
create table #t (name char(1),login datetime,logout datetime)
insert into #t values ('a', '2007-11-10 12:00:00','2007-11-10 16:00:00')
insert into #t values ('b', '2007-11-10 15:00:00','2007-11-10 18:00:00')
select '12-14' [time range] ,
count(case when convert(char(2),login,108) >= 12 and
convert(char(2),login,108)< 15
or convert(char(2),logout,108) >= 12 and convert(char(2),logout,108)<
15
then 1 end) [total people logged] from #t
union all
select '14-16' [time range],
count(case when convert(char(2),login,108) >= 14 and
convert(char(2),login,108)< 17
or convert(char(2),logout,108) >= 14 and convert(char(2),logout,108)<
17
then 1 end)
from #t
"Bhishm" <bhishms@.gmail.com> wrote in message
news:f74d745f-661a-4a57-8b18-a685b8658c43@.i37g2000hsd.googlegroups.com...
> Hi,
> I am creating a attendance sheet software for inhouse use.
> my data is like this:-
> -----
> | name | login time | logout
> time |
> -----
> | a | 2007-11-10 12:00:00 | 2007-11-10
> 16:00:00 |
> -----
> | b | 2007-11-10 15:00:00 | 2007-11-10
> 18:00:00 |
> -----
> My requirement:-
> I want to generate an hourly report like this:-
> ----
> date time range total people logged
> in
> -----
> 2007-11-10 0 -2 0
> ----
> 2007-12-10 2-4 0
> ----
> .
> .
> -----
> 2007-11-10 12-14 1
> -----
> 2007-11-10 14-16 2
> ----
> 2007-11-10 16-18 1
> -----
> .
> .
> -----
> 2007-11-10 22-24 0
> ----
>
> This is what I want to creat , but I don't know how can I generate
> such kind of report.
> Can you please guide me for the same. Please reply urgently.
> Thanks & Regards,
> Bhishm