DAVID/ASPNET Sql connection problem!

I am having a problem with .net ms sql connections...

My situation: I log onto my computer (Windows XP Pro) using the administrator account.
I have MSDE installed, with NT authorization.
I created a quick connection in ASP.net Web Matrix, and ran the code using the ASP.NET web matrix server - It ran perfectly, so i know the connection string is good, and so is all the other code i used for the connection... I know that it used the administrator account to access the sql server.

Then i copied and pasted the exact working code i had in web matrix to Visual Studio.net, and tried to run it using IIS, but it gave the following error:

Server Error in '/' Application.

Login failed for user 'DAVID\ASPNET'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'DAVID\ASPNET'.

Source Error:

Line 11: Dim myCommand As New SqlCommand(CommandText, myConnection)
Line 12:
Line 13: myConnection.Open()
Line 14:
Line 15: ' Create Instance of Connection and Command Object

I only have MSDE installed on my PC, so i ran the following in DOS:

cd "c:\Program Files\Microsoft SQL Server\80\Tools\Binn"
osql -E -S DAVID -Q "sp_grantlogin 'DAVID\ASPNET'"
osql -E -S DAVID -d myDB -Q "sp_grantdbaccess 'DAVID\ASPNET'"
osql -E -S DAVID -d myDB -Q "sp_addrolemember 'db_owner', 'DAVID\ASPNET'"
pause

So now ASPNET should have full access to the sql server. (Stored Procedures ran fine, and the roles and accounts were created successfully.)

So i ran the ASP.net code in IIS again, but still the same error!

I have even tried re-installing asp.net!
I havent tried re-installing MSDE because i dont want to loose my data, but if i have to, then i guess i have to... :(

What do i do??I'm sure you understood, that your SQL Server did not accept DAVID\ASPNET as a login name?! Either there is indeed not such a user, or maybe you took the back-slash instead the slash. Could you post your connection string?|||I will get the connection string to you asap - its at home, not at work, so only tomorrow...

When installing the .NET framework, the user ASPNET is automatically added. ASP.NET uses this user for all the interaction it makes with the server (in most cases). So anyway - the ASPNET user is there, and my computer name is DAVID, and so is my SQL server instance called DAVID.

Ill try with the forward slash instead of using a back slash, but im pretty sure that i did it the right way... I copied it exactly as the asp error page had it...

DATEDIFF question

Uses: SQL Server 2000 Personal Edition with SP3, Windows XP Pro;

Hi,

Is there any possible way that I can differentiate 2 different dates and get in a format inclusive of hh, mm, ss part altogether, rather than only getting one part?

Like this:

if

StartDateTime = 10:15:01 AM and

LastDateTime = 11:20:01 AM

then the difference of LastDateTime and StartDateTime will be = 1hour and 05 Minutes and 00 seconds (I need like this). So using DATEDIFF function I will be only getting one part either hour, Minute and Second. I would like to know is there anyway either by using DATEDIFF function or other to get in the format inclusive of hours, Minutes and Seconds.

Regards,

Hifni

You can do something like below. Note that the smalldatetime or datetime data types all contain a date value. So you just need to use same date value.

declare @.newstrt datetime, @.newend datetime

-- the expression below strips date part out and sets it to 1900/01/01

set @.newstart = cast('1900-01-01' + right(convert(varchar, @.start, 126), 13) as datetime)

set @.newend = cast('1900-01-01' + right(convert(varchar, @.end, 126), 13) as datetime)

-- now you can just use - operator like:

select convert(varchar, @.newend - @.newstart, 114)

Note that this does assume that the time interval is within a day.

|||

Hi,

Thanks for the reply, but that is not what I'm looking for. Actually my emphasis is on the time rather than the date. Actually it's like this:

Assume that there is a person, who called at Time X of a day and he spoked until time'Y'. So the duration of the call he took would be = Y - X. I want the result to be how many hours, minutes and seconds he took to the call. So the duration should illustrated as = hh: mm: ss. In DATEDIFF, you only get either hours part, minutes part or the seconds parts, however I want it in all inclusive. Is there a way to get it?

Regards,

|||

declare @.date1 datetime
declare @.date2 datetime
select @.date1 = '2002-03-07 04:15:03.000'
select @.date2= '2002-03-07 13:21:04.000'
declare @.hour int
declare @.min int
declare @.s int

select @.hour=datediff(hour,@.date1,@.date2)
select @.min=datediff(mi,@.date1,@.date2)-@.hour*60
select @.s=datediff(s,@.date1,@.date2)-@.hour*60*60-@.min*60

select @.hour ,@.min,@.s

|||The last line in the script I posted will give you exactly the output you are looking for i.e, only time formatted as hh:mm:ss. As long as you have the date part same between the values you can simply store it in smalldatetime and datetime. I am not using DATEDIFF instead I am using the - operator which produces different results. Run the script with some input and see.|||

Hi

There is no built in method in SQL Server which can produce the result u need.

u can do it using T-SQL by programmatically.

just create your own function in SQL Server and pass two dates to that function

just like this

Create function dbo.fun_TimeDiff(@.StDate datetime, @.EnDate datetime)
Returns Varchar(10) AS
Begin
declare @.hor int
declare @.min int
declare @.sec int

Set @.hor=datediff(hour,@.StDate,@.EnDate)
Set @.min=datediff(mi,@.StDate,@.EnDate)-@.hor*60
Set @.sec=datediff(s,@.StDate,@.EnDate)-@.hor*60*60-@.min*60

Return convert(char(2),@.hor) +':'+ convert(char(2),@.min) + ':'+ convert(char(2),@.sec)
End

select dbo.Fun_TimeDiff('2006/01/03 07:30:00.000','2006/01/03 17:41:10.000')

it will give u the result

10:11:10

DATEDIFF question

Uses: SQL Server 2000 Personal Edition with SP3, Windows XP Pro;

Hi,

Is there any possible way that I can differentiate 2 different dates and get in a format inclusive of hh, mm, ss part altogether, rather than only getting one part?

Like this:

if

StartDateTime = 10:15:01 AM and

LastDateTime = 11:20:01 AM

then the difference of LastDateTime and StartDateTime will be = 1hour and 05 Minutes and 00 seconds (I need like this). So using DATEDIFF function I will be only getting one part either hour, Minute and Second. I would like to know is there anyway either by using DATEDIFF function or other to get in the format inclusive of hours, Minutes and Seconds.

Regards,

Hifni

You can do something like below. Note that the smalldatetime or datetime data types all contain a date value. So you just need to use same date value.

declare @.newstrt datetime, @.newend datetime

-- the expression below strips date part out and sets it to 1900/01/01

set @.newstart = cast('1900-01-01' + right(convert(varchar, @.start, 126), 13) as datetime)

set @.newend = cast('1900-01-01' + right(convert(varchar, @.end, 126), 13) as datetime)

-- now you can just use - operator like:

select convert(varchar, @.newend - @.newstart, 114)

Note that this does assume that the time interval is within a day.

|||

Hi,

Thanks for the reply, but that is not what I'm looking for. Actually my emphasis is on the time rather than the date. Actually it's like this:

Assume that there is a person, who called at Time X of a day and he spoked until time'Y'. So the duration of the call he took would be = Y - X. I want the result to be how many hours, minutes and seconds he took to the call. So the duration should illustrated as = hh: mm: ss. In DATEDIFF, you only get either hours part, minutes part or the seconds parts, however I want it in all inclusive. Is there a way to get it?

Regards,

|||

declare @.date1 datetime
declare @.date2 datetime
select @.date1 = '2002-03-07 04:15:03.000'
select @.date2= '2002-03-07 13:21:04.000'
declare @.hour int
declare @.min int
declare @.s int

select @.hour=datediff(hour,@.date1,@.date2)
select @.min=datediff(mi,@.date1,@.date2)-@.hour*60
select @.s=datediff(s,@.date1,@.date2)-@.hour*60*60-@.min*60

select @.hour ,@.min,@.s

|||The last line in the script I posted will give you exactly the output you are looking for i.e, only time formatted as hh:mm:ss. As long as you have the date part same between the values you can simply store it in smalldatetime and datetime. I am not using DATEDIFF instead I am using the - operator which produces different results. Run the script with some input and see.|||

Hi

There is no built in method in SQL Server which can produce the result u need.

u can do it using T-SQL by programmatically.

just create your own function in SQL Server and pass two dates to that function

just like this

Create function dbo.fun_TimeDiff(@.StDate datetime, @.EnDate datetime)
Returns Varchar(10) AS
Begin
declare @.hor int
declare @.min int
declare @.sec int

Set @.hor=datediff(hour,@.StDate,@.EnDate)
Set @.min=datediff(mi,@.StDate,@.EnDate)-@.hor*60
Set @.sec=datediff(s,@.StDate,@.EnDate)-@.hor*60*60-@.min*60

Return convert(char(2),@.hor) +':'+ convert(char(2),@.min) + ':'+ convert(char(2),@.sec)
End

select dbo.Fun_TimeDiff('2006/01/03 07:30:00.000','2006/01/03 17:41:10.000')

it will give u the result

10:11:10