CODEWITHSUNDEEP
c#asp.netsql-serverasp.net-mvc
wajira000
wajira000

Reputation: 389

DateTime.Now Throw exception in SQL saying it's not valid datetime value

I'm new to ASP.NET and I need your expert help to solve this. I'm using ASP.NET MVC 5 and SQL Server. I try to insert some data to database using SQL this is my query

INSERT INTO tem (Telephone, Status, CreateDate) 
VALUES ('" + telephone + "','"+ status +"','" + DateTime.Now + "');

It's working fine in localhost but after I deploy to server it showing an error message:

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

My DateTime.Now value is like 28/02/2016 8:45:45 AM.

If I add 2016-02-28 8:45:45 AM by hard-coding then it's working.

Upvotes: 0

Views: 1718

Answers (3)

vahid kargar
vahid kargar

Reputation: 794

as @marc_s says in first comment, you shouldn't use SQL statement, instead of your code, do something like this:

conn2 = new SqlConnection();
        conn2.ConnectionString = ConfigurationManager.ConnectionStrings["connSpionshopString"].ConnectionString;
 SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn2;
        cmd.CommandText = "INSERT INTO tem (Telephone, Status, CreateDate)    VALUES(@param1,@param2,@param3)";

        cmd.Parameters.Add("@param1", "telephone ");
        cmd.Parameters.Add("@param2", "status ");
        cmd.Parameters.Add("@param3", DateTime.Now);

    cmd.ExecuteNonQuery(); 

    conn2.Close();

Upvotes: 2

marc_s
marc_s

Reputation: 755471

You need to use parametrized queries that solve both your SQL injection vulnerability, and also avoid having to convert your DateTime to a string which often leads to problems.

Use code something like this:

// define connection string and **PARAMETRIZED** insert query 
string connectionStirng = ".....";
string insertQry = "INSERT INTO tem (Telephone, Status, CreateDate) VALUES (@Telephone, @Status, @CreateDate);";

// set up connection and command in using blocks
using (SqlConnection conn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand(insertQry, conn))
{
    // define the parameters
    cmd.Parameters.Add("@Telephone", SqlDbType.VarChar, 50).Value = telephone;
    cmd.Parameters.Add("@Status", SqlDbType.VarChar, 20).Value = status;
    cmd.Parameters.Add("@CreateDate", SqlDbType.DateTime).Value = DateTime.Now;

    // open connection, execute query, close connection
    conn.Open();
    int rowsInserted = cmd.ExecuteNonQuery();
    conn.Close();
}

Upvotes: 5

TomTom
TomTom

Reputation: 62159

get the basics straight. DatetimeNow has NOTHING to do with SQL.

My DateTime.Now value is like 28/02/2016 8:45:45 AM.

No. Your datetime.Now value is a double actually, internally. The STRING representation is what is generated by your UI Settings as you do not define a culture or a format string. YOu should do so if you want a specific format.

If your culture and the server's culture do not match up - naturally the datetime will not parse correctly.

This is why you should use parameters (and they avoid sql injection) and not deal with strings.

Upvotes: 1

Related Questions