greets again folks,
The values LastLoginDate and LastActivityDate in my SQl Express membership dBase are always off.
The date is usually correct but the time is always hours off.
Is there some way to get the time part of the DateTime to be correct?
Do I have to write code to set the time when the user logs in?
Thanks a mil!
Hi,
I'm inserting a datetime values into sql server 2000 from c#
SQL server table details
Table nameate_test
columnname datatype
No int
date_t DateTime
C# coding
SqlConnection connectionToDatabase = new SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=testdb;Integrated Security=SSPI");
connectionToDatabase.Open();
DataTable dt1 = new DataTable();
dt1.Columns.Add("no",typeof(System.Int16));
dt1.Columns.Add("date_t", typeof(System.DateTime));
DataRow dr = dt1.NewRow();
dr["no"] = 1;
dr["date_t"] = DateTime.Now;
dt1.Rows.Add(dr);
for(int i=0;i<dt1.Rows.Count;i++)
{
string str=dt1.Rows["no"].ToString();
DateTime dt=(DateTime)dt1.Rows["date_t"];
string insertQuery = "insert into date_test values(" + str + ",'" + dt + "')";
SqlCommand cmd = new SqlCommand(insertQuery, connectionToDatabase);
cmd.ExecuteNonQuery();
MessageBox.Show("saved");
}
When I run the above code, data is inserted into the table
The value in the date_t column is 2007-07-09 22:10:11 000.The milliseconds value is always 000 only.I need the millisecond values also in date_t column.
Is there any conversion needed for millisecond values?
thanks,
Mani
I have a database which was automatically created in VWD (final release
version). I want to design this database using the Management
Studio software. How can I access the database from this
tool? I am able to connect to the sql server running on my
machine however the database created for the website is not listed
there.
Hi
can somebody explain me how I can assign a NULL value to a datetime type field in the script transformation editor in a data flow task.
In the script hereunder, Row.Datum1_IsNull is true, but still Row.OutputDatum1 will be assigned a value '0001-01-01' which generates an error (not a valid datetime). All alternatives known to me (CDate("") or Convert.ToDateTime("") or Convert.ToDateTime(System.DBNull.Value)) were not successful.
Leaving out the ELSE clause generates following error: Error: Year, Month, and Day parameters describe an un-representable DateTime.
If Not Row.Datum1_IsNull Then
Row.OutputDatum1 = Row.Datum1
Else
Row.OutputDatum1 = CDate(System.Convert.DBNull)
End If
Any help welcome.
Hi, I am using express database and tools. I have an aspnetdb express database because I am using the login controls, personalisation...Can I use this database for my private tables also? Or do I need a separate express database for my private tables? Will using aspnetdb database, which contains special asp.net tables, for my private website tables create any kinds of problems in using my website? I want to use a single express database for my website, not two different express databases.
Regards, Sandy
Could anyone please advise on a good hosting company that DOES support the above applications and any advice on the Login Controls for the uploaded database would be greatly appreciated.
View 1 Replies View RelatedI just spent the better par of 3 days creating a prototype in ASP.Net 2.0 and SQL Server Express only to discover that nobody from outside can see it...
ERROR with impersonation=true
User does not have permission to perform this action.
ERROR with impersonation=false
Unable to open the physical file "c:inetpubwwwroot------.mdf". Operating system error 5: "5(Access is denied.)".An attempt to attach an auto-named database for file c:inetpubwwwroot-----.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
What makes this so difficult?
What am I missing?
How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?
View 5 Replies View RelatedI am getting the error:
Cannot open database "aspnetdb" requested by the login. The login failed.
When I browse to my ASP.NET 3.5 LINQ web application on the IIS 6.0 server on Server 2003.
I imagine this is because while I granted SQL Server 2005 login and permissions to my database that the application stores its data in, I did NOT grant any rights to the service account the IIS Application Pool uses for its identity to the aspnetdb database on SQL Server which is where all my roles information is stored at.
My question is what are the MINIMUM permissions needed for this database so it can perform its roles related functions?
I'm using Windows Authentications with the SQL Role provider for authorization.
Thank you.
EDIT: I think I only need to open the aspnetdb database and add my login to the aspnet_Roles_FullAccess role. Is that correct?
Hi,
I just installed Visual web developper 2008 express with all options including SQL srvr express 2005!
This is from default iso image on the official website.
I create my first project and when I want to do ASP.NET configuration from web site menu, i got this error:
"Cannot open database "aspnetdb" requested by the login. The login failed."
I am just trying to learn here so i even did not create any dB or something, it is 1st time I run configuration to add security roles and users for testing!
I also un-installed all and re-installed, still the same...
pleas help and give me details as i am new.
kind regards,Golgot
I used aspnet_regsql to put membership tables, etc on sql server 2005.
Is there a tool to help me build the web.config statement to point to these tables?
I found the code below at http://www.c-sharpcorner.com/UploadFile/dsdaf/104092006022507AM/1.aspx?ArticleID=bc2ca2b0-9e3d-42ad-b4b9-0dce96231ce6 but it throws this error: Parser Error Message: Unrecognized configuration section membership.
<membership>
<providers>
<remove name="AspNetSqlMembershipProvider" />
<add connectionStringName="MyDBConnectionString" enablePasswordRetrieval="false"
enablePasswordReset="true" requiresQuestionAndAnswer="true" applicationName="/"
requiresUniqueEmail="false" passwordFormat="Hashed" maxInvalidPasswordAttempts="5"
minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="1"
passwordAttemptWindow="10" passwordStrengthRegularExpression=""
name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
</providers>
</membership
Am I barking up the right tree?
thanks, steve
guys this is kind of a biggie for me.
i've only used sql server to compare the Date Portion of date(s), not the whole string (mm/dd/yyyy hh:mi).
i have a table called Time_Check which has two fields with timestamps,
"TimeLastRun & TimeNextRun". i was trying to compare the Current TimeStamp that i pass from a vb.net form to a stored procedure and compare that DateTime to the TimeNextRun. I'd like to then Update the two Time Columns if rows are found. Should i maybe be using the DateDif Func if i know ahead of time how much time should be elapsed(say five minutes)?
Any Code help is appreciated
Create Procedure dbo.My_Time_Check
@myDate smalldatetime
as
select alertnumber,alertname,TimeLastRun,TimeNextRun
from time_check
where timeNextRun >= @myDate
--would like to update these two columns if rows are found
thanks again
this forum is the best
rik
hello all,
i have table - compmail in that i have compmailid, compmaildetail and compmaildate - columns
compmailid compmaildetail compmaildate
-------------------------------------------
94341 comp mailing list 2008-1-23 16:41:17.037
94445 comp mailing vital 2008-1-23 23:05:18.152
94592 comp mailing items 2008-1-24 10:48:08.077
108229 comp mailing sales 2008-1-27 15:13:37.207
108740 comp mailing usages 2008-1-27 16:44:17.517
108261 comp mailing stats 2008-1-27 14:42:55.780
so i want maxcompmailid for each date
so output will be like:
compmailid compmaildetail compmaildate
-------------------------------------------
94445 comp mailing vital 2008-1-23 23:05:18.152
94592 comp mailing items 2008-1-24 10:48:08.077
108740 comp mailing usages 2008-1-27 16:44:17.517
if i m doing:
select max(compmailid),compmaildetail,max(compmaildate) from compmail
group by compmaildetail
not giving me correct results which i want
can anyone have idea?
How can i return how much of the timespan in table2 is in the timespan in table1?
table 1 - email
-------
Login time: 2007-12-12 13:14:26.363
Logout time: 2007-12-12 14:15:58.803
table2 - phone
------
Login time: 2007-12-12 12:11:08.343
Logout time: 2007-12-12 14:13:10.847
How Can I BCP in datetime values in nondefault format, wich is "mdy" in U.S. English.
Thanks in advance, for any help.
Hi, does anyone know a SQL statement that will take two DATETIME values and list all minutes in between them like so:
START VALUE: 5/22/2008 10:00
END VALUE: 5/22/2008 10:10
RESULT:
5/22/2008 10:00
5/22/2008 10:01
5/22/2008 10:02
5/22/2008 10:03
5/22/2008 10:04
5/22/2008 10:05
5/22/2008 10:06
5/22/2008 10:07
5/22/2008 10:08
5/22/2008 10:09
5/22/2008 10:10
Thanks!
Hi,
I have two tables TABLEA and TABLEB. They both have the same columns. I need to insert data from TABLEA to TABLEB based on the following conditions
TABLEA Structure with Sample Data
PKEY
Attribute
Value
Updated_DateTime
1
A
2.2
08:30AM
2
A
3.3
08:40AM
3
B
3.2
08:40AM
4
C
1.1
08:00AM
5
C
1.2
08:43PM
6
D
1.01
08:42AM
This is the sample data for TABLEB
PKEY
Attribute
Value
01
A
2.2
02
F
3.3
03
B
3.2
04
D
1.01
Now..When inserting data into TABLE B ,
->I need to check if the Attribute already exists in TABLE B .
-> If it already exists then I must update the Value feild with the latest value from TABLEA based on updated_datetime.
(Ex: Attribute 'A' already exists in tableB so i need to update the VALUE feild in TABLE B to 3.3 which is the latest updated value in TABLEA at 08:40AM)
-> If the Attribute doesnt exist then I simply need to insert that row into TABLEB
Can some one please post the query or the procedure that I can use for the above requirement.
Any help greatly appreciated.This is a urgent requirement for me.Please help
Thanks
Hello all,I'm kind of new to ASP.NET and I've hit my first unsolvable roadblock. I'm trying to create a formview insertcommand that allows me to place the current date into a DateTime field but I continuously get an Input string was not in a correct format error. I may be using the wrong functions, I'm unsure. I've messed with DateTime.Now.ToString() as well as GETDATE() but I have no idea how to get it to work. I've also tried to switch the variable type of the field to a String type as well as DateTime. I am running visual basic .net with Microsoft SQL Server 2005. Here is my code right now: Experiencing problems with technology? Just fill out the form below and your problem will be forwarded to the Information Technology department.</em></strong><br /> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TechticketdbConnectionString %>" SelectCommand="SELECT [CRDL#] AS column1, [Employee ID] AS Employee_ID, [Problem description] AS Problem_description, [Date submitted] AS Date_submitted, [Ticket number] AS Ticket_number FROM [Tickets]" DeleteCommand="DELETE FROM [Tickets] WHERE [Ticket number] = @Ticket_number" InsertCommand="INSERT INTO [Tickets] ([CRDL#], [Employee ID], [Problem description], [Date submitted]) VALUES (@column1, @Employee_ID, @Problem_description, GETDATE() )" UpdateCommand="UPDATE [Tickets] SET [CRDL#] = @column1, [Employee ID] = @Employee_ID, [Problem description] = @Problem_description, [Date submitted] = @Date_submitted WHERE [Ticket number] = @Ticket_number"> <DeleteParameters> <asp:Parameter Name="Ticket_number" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="column1" Type="Int32" /> <asp:Parameter Name="Employee_ID" Type="Int32" /> <asp:Parameter Name="Problem_description" Type="String" /> <asp:Parameter Name="Date_submitted" Type="String" /> <asp:Parameter Name="Ticket_number" Type="Int32" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="column1" Type="Int32" /> <asp:Parameter Name="Employee_ID" Type="Int32" /> <asp:Parameter Name="Problem_description" Type="String" /> <asp:Parameter Name="Date_submitted" Type="String" /> </InsertParameters> </asp:SqlDataSource> I've left out the EditItemTemplate because it isn't going to be used for this form. Here is the InsertItemTemplate: <InsertItemTemplate> column1: <asp:TextBox ID="column1TextBox" runat="server" Text='<%# Bind("column1") %>'> </asp:TextBox><br /> Employee_ID: <asp:TextBox ID="Employee_IDTextBox" runat="server" Text='<%# Bind("Employee_ID") %>'> </asp:TextBox><br /> Problem_description: <asp:TextBox ID="Problem_descriptionTextBox" runat="server" Text='<%# Bind("Problem_description") %>'> </asp:TextBox><br /> <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert" Text="Insert"> </asp:LinkButton> <asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel"> </asp:LinkButton> </InsertItemTemplate> <ItemTemplate> column1: <asp:Label ID="column1Label" runat="server" Text='<%# Bind("column1") %>'></asp:Label><br /> Employee_ID: <asp:Label ID="Employee_IDLabel" runat="server" Text='<%# Bind("Employee_ID") %>'> </asp:Label><br /> Problem_description: <asp:Label ID="Problem_descriptionLabel" runat="server" Text='<%# Bind("Problem_description") %>'> </asp:Label><br /> Date_submitted: <asp:Label ID="Date_submittedLabel" runat="server" Text='<%# Bind("Date_submitted") %>'> </asp:Label><br /> Ticket_number: <asp:Label ID="Ticket_numberLabel" runat="server" Text='<%# Eval("Ticket_number") %>'> </asp:Label><br /> <asp:LinkButton ID="EditButton" runat="server" CausesValidation="False" CommandName="Edit" Text="Edit"> </asp:LinkButton> <asp:LinkButton ID="DeleteButton" runat="server" CausesValidation="False" CommandName="Delete" Text="Delete"> </asp:LinkButton> <asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" CommandName="New" Text="New"> </asp:LinkButton> </ItemTemplate> </asp:FormView> I would like to get the value formatted as a DateTime but a String will do just fine if a DateTime variable won't work. Any help would be greatly appreciated. Thanks!
View 2 Replies View RelatedIf myDateTimeColumn contains a <NULL> value. How do you handle that when reading into a DateTime object in your code?DateTime myDate = Convert.ToDateTime(dr["myDateTimeColumn"]);Does not work, it throws: System.InvalidCastException: Object cannot be cast from DBNull to other types.
I am curious as to what others are doing to handle this?
Hi,
When I try to insert a new record into a table that has a datetime field that allows nulls, a default 01/01/1900 date is inserted instead of null. I recreated the table and set the datatype to smalldatetime and I still get the error. What have I missed?
Hi all...
how I can obtain the sum of a datetime field as aggregate function?
Given a set of records I need to calculate the number of records (count (*)) and the sum of a field of type datetime.
Is this possible? how?
Thanks..
Massimo
I want to count persons for non overlapping intervalls by room number. Here the data:
SET DATEFORMAT mdy;
DECLARE @PersonTime TABLE
(
ID INT,
Person INT,
Room INT,
Coming DATETIME,
Going DATETIME
[code]....
am making a CV program and i need a way to count the experience the user has:
i have his begin date and end Date as datetime in an sql server.
i can do it programicly but i prefer to do it at the sql side
the question:
how can i get how much exp he has aka :
SUM(DATEDIFF(year , JobApExp.BeginDate , JobApExp.EndDate ))
but for all the datarow
(he has more than one BeginDate and EndDate (for each job he has one))
P.S i want to be able to use it in a where clause :
select * from jobap
where -- or HAVING
JobAp.ind = JobApExp.JobAp AND
SUM(DATEDIFF(year , JobApExp.BeginDate , JobApExp.EndDate )) > CONVERT(int,@Exp)
thanks in advance
Hi
I have a really simple query which i can't figure out why its not working. I have a table called 'ADMIN' which has a datetime field called 'date_edited'. Because the majority of records have never been edited, i have allowed null values and they are filled with 'NULL' in each record. How ever, when i try:
SELECT * FROM ADMIN WHERE date_edited = NULL
I get no records, but i can see and know i have hundreds! I know i'm doing somthing really stupid, but for life of me can't figure it out! :eek:
thanks
Hello:
I've recently discovered the MS CRM 3.0 stamps any datetime field in SQL as UTC (aka GMT). Even though the end user may select 12:00 noon on the CRM gui interface, the datetime is stamped in SQL as 16:00 (I'm in EST).
So, is there an easy way to read the 16:00 back to local EST, taking into account daylightsavings (DST)? Apparently in VB or C# this conversion is a trivial matter of using ToLocalTime(), but I don't believe SQL 2005 has such a convenient function.
I'm currently building a SQL view that I wish to use to support multiple reports, and it would be ideal for the view to translate the UTC datetime fields (even if via a user-defined function) to EST rather than coding every report or custom app to translate the UTC.
Any suggestions or links to code will be greatly appreciated!
Thanks,
Pete
I would like to convert a UTC datetime value to any Local Time Zone. Below is some code I'm trying to ensure works correctly. Anyone care to comment?
I have used the following lines before the function to obtain the 2nd parameter:
SELECT GETUTCDATE()
SELECT GETDATE()
SELECT DATEDIFF(hh,'2008-05-20 20:08:01.020', '2008-05-20 16:08:01.020')
DECLARE @UTC DATETIME
EXECUTE @UTC = UTCtoLocalDate '2008-05-20 20:08:01.020', -4
-- =============================================
-- Description: <Converts local datetime values to UTC datetime values, using the built-in GETUTCDATE() function.
-- <Parameter two (@TZ) represents the time zone difference from UTC/GMT. To obtain this value
-- <run GETUTCDATE() and GETDATE(), then determine the timezone from the difference between the two.>
-- =============================================
CREATE FUNCTION [dbo].[UTCtoLocalDate] (@UTCDate DATETIME, @TZ INT)
RETURNS DATETIME AS
BEGIN
IF ( DATEPART(hh, @UTCDate) <> 0 )
BEGIN
DECLARE @LocalDate DATETIME
DECLARE @UTCDelta INT
DECLARE @thisYear INT
DECLARE @DSTDay INT
DECLARE @NormalDay INT
DECLARE @DSTDate DATETIME
DECLARE @NormalDate DATETIME
SET @thisYear = YEAR(@UTCDate)
IF (@thisYear < 2007 )
BEGIN
SET @DSTDay = ( 2 + 6 * @thisYear - FLOOR(@thisYear / 4) ) % 7 + 1
SET @NormalDay = ( 31 - ( FLOOR( @thisYear * 5 / 4) + 1) % 7)
SET @DSTDate = '4/' + CAST(@DSTDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'
SET @NormalDate = '10/' + CAST(@NormalDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'
END
ELSE
BEGIN
SET @DSTDay = ( 14 - ( FLOOR( 1 + @thisYear * 5 / 4 ) ) % 7 )
SET @NormalDay = ( 7 - ( FLOOR ( 1 + @thisYear * 5 / 4) ) % 7 )
SET @DSTDate = '3/' + CAST(@DSTDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'
SET @NormalDate = '11/' + CAST(@NormalDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'
END
IF ((@UTCDate > @DSTDate) AND (@UTCDate < @NormalDate))
BEGIN
SET @UTCDelta = @TZ + 1
END
ELSE
BEGIN
SET @UTCDelta = @TZ
END
-- now convert utc date to local date
SET @LocalDate = DATEADD(Hour, @UTCDelta, @UTCDate)
END
ELSE
BEGIN
SET @LocalDate = @UTCDate
END
RETURN(@LocalDate)
END
GO
Hi All
I am working on a query to get all the datetime values in a column in a table into a comma separated text.
eg.
ColumnDate --------------------------- 2005-11-09 00:00:00.0002005-11-13 00:00:00.0002005-11-14 00:00:00.0002005-11-16 00:00:00.000
I wanted to get something like
2005-11-09, 2005-11-13, 2005-11-14, 2005-11-16
Have just started SQL and hence am getting confused in what I think should be a relatively simple query. Any help will be much appreciated. Thanks
I am importing a couple SAS datasets to SQL Server 2008 for a project. The dates are in a float format, they show up as DT_R8 in SSIS. How can I convert these values to SQL server datetime? I have tried dozens of methods I found on-line with no success, I keep getting 'Arithmetic overflow error converting expression to data type datetime.' errors.
View 0 Replies View RelatedIs there a way that I can do this at the table level to automatically handle the rounding of seconds, etc. down to the minute automatically without having to use a trigger?
Here is a very basic example of what I am trying to do:
--example: '09-22-2007 15:07:18.850' this is the value inserted into the table by the code
select getdate()
--example: '2007-09-22 15:07:00.000' this is the value I want to store in the table
select dateadd(mi, datediff(mi, 0, getdate()), 0)
Is it possible to have a DateTime field in a row automatically generated when a row is created?
ie:
INSERT INTO [TableName] ([DateTimeStamp], [UserId], [Title], [Explanation]) VALUES (DateTime.Now(), @UserId, @Title, @Explanation)
or something like that?
hi ,
this is my first post on MS Forums .
Q: my question is that , i want to format a date column .. d/m/yyyy .. but there is no any format facility ..
anybody can help me here ??
Hello,I have a foreign key constraint between two tables (Appointments andMissedAppointmentReasons) and I'd like to allow null values in Appointmentstable for the field containing the MissedAppointmentReason, but currently, Iget a Foreign Key Constraint Error when I try to add a record to theAppointments table.Any ideas how I can have the null values but still maintain the Foreign KeyConstraint?Thanks!Rick
View 3 Replies View RelatedI'm getting error:
String was not recognized as a valid DateTime.
my insert parameter:
<asp:Parameter Name="LastModified" Type="DateTime" DefaultValue= "<%=DateTime.Now.ToString() %>"
my insert command:
InsertCommand="INSERT INTO [Product] ([Enabled], [ProductCode], [ProductName], [ProductAlias], [CarrierId], [DfltPlanId], [DoubleRating], [DoubleRateProductId], [ConnCharges], [StartDate], [EndDate], [Contracted], [BaseProductId], [LastModified], [LastUser]) VALUES (@Enabled, @ProductCode, @ProductName, @ProductAlias, @CarrierId, @DfltPlanId, @DoubleRating, @DoubleRateProductId, @ConnCharges, @StartDate, @EndDate, @Contracted, @BaseProductId, @LastModified, @LastUser)"
LastModified is a datetime field.
Running sql2005