Solved

Calendar Control and ASP.net

Posted on 2013-01-18
6
643 Views
Last Modified: 2013-01-19
I'm trying to use the Calendar control within Web Developer Express and output the result to a datetime field in SQL 2005, however I keep getting the same error:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

I'm new to this so my code is probably well wrong, can someone please help:

strDate = Format(CDate(Calendar1.SelectedDate), "dd-mm-yyyy HH:mm:ss")
0
Comment
Question by:ndr-itsolutions
6 Comments
 
LVL 19

Expert Comment

by:Rikin Shah
ID: 38792222
Hi,

You can either use Convert.ToDateTime() or DateTime.TryParseExact() try using them.
0
 
LVL 6

Expert Comment

by:esolve
ID: 38792257
You could also try

DateTime.ParseExact(yourdate, "dd/MM/yyyy", CultureInfo.InvariantCulture)

More Info here:
http://msdn.microsoft.com/en-us/library/w2sa9yss.aspx

Just remember that depending on the datetime settings on your machine it expects the date in a specific order. For example if your machine is localized in China it would have the format dd/MM/YYYY. Usually the problem with converting is because the day and month values is swopped around which causes the error.

Also if you're trying to convert by including timeformats (HH:mm:ss) as well you will have to use the ParseExact mentod.

For normal conversions you can use date and time styles. More info here:

http://msdn.microsoft.com/en-us/library/ms187928.aspx
0
 

Author Comment

by:ndr-itsolutions
ID: 38792366
So, strDate is what I am passing the date selected to , then to be written into the database, how would I change the syntax to do that ?

My code is:
Dim SelDate = Calendar1.SelectedDate

Open in new window



sqlcon.Open()
        Dim strcommand As String
     
        strcommand = "Insert into Release_Tracker(Environment,Current_Version,Component,SubComponent,Resource,Status,DateCreated,Comments,Project_name,Software_Location) values ('" + strEnv + "','" + strBuild + "','" + strComponent + "','" + strSubComponent + "','" + strResource + "','" + strStatus + "','" + SelDate + "','" + strComments + "','" + strProject + "','" + strSoftware + "')"
        Dim sqlcomm As New SqlCommand(strcommand, sqlcon)
        Dim o As String = sqlcomm.ExecuteNonQuery()

Open in new window

0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 6

Assisted Solution

by:esolve
esolve earned 250 total points
ID: 38792422
You don't

The problem you're having is you're using inline SQL. With this approach you are unable to set values in the database. What I mean by this is if you have a stored proc you can specify the dateformat like this in the procedure:

DATEFORMAT DMY

Also using a procedure is a better approach as what your currently doing because your current code allows for sql injection.

CREATE PROCEDURE p_insert_release_tracker
(
 @Param1 NVARCHAR,
 @Param2 datetime
)
AS
SET DATEFORMAT DMY
...insert statement here

....
GO

Oh another thing. Do not try to convert the date. Just send the datetime value through.

Dim dtDate as datetime()
dtDate = Calendar.Selecteddate

then you create a sqlparameter of type datetime and add it to the parameters collection of the sqlcommand object.

Do you know how to do this?


// Create the connection.
    using (SqlConnection connection = new SqlConnection(@"Data Source=..."))
    {
        // Open the connection.
        connection.Open();

        // Create the command.
        using (SqlCommand command = new SqlCommand("p_insert_release_tracker", connection))
        {
            // Set the command type.
            command.CommandType = System.Data.CommandType.StoredProcedure;

            // Add the parameter.
            SqlParameter parameter = command.Parameters.Add("@dt",
                System.Data.SqlDbType.DateTime);

            // Set the value.
            parameter.Value = DateTime.Now;

            // Make the call.
            command.ExecuteNonQuery();
        }
    }
0
 
LVL 20

Accepted Solution

by:
informaniac earned 250 total points
ID: 38794487
Does this help?
strDate = Format(CDate(Calendar1.SelectedDate), "MM-dd-yyyy HH:mm:ss") 

Open in new window

0
 

Author Closing Comment

by:ndr-itsolutions
ID: 38796235
As a quick fix to my issue informaniac has provided the solution, but for long term, I want to start using the code provided by esolve. Thanks Guys !
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question