CODEWITHSUNDEEP
c#asp.netdate-conversion
Learner
Learner

Reputation: 43

Conversion Error during database UPDATE

I am entering the current date to a SQL database using an ASP.NET form. The conversion failed when converting date time from string. This is the code I'm using.

txt_OrderDate.Text = DateTime.Now.ToShortDateString();

Upvotes: 0

Views: 276

Answers (2)

andrey.shedko
andrey.shedko

Reputation: 3238

  1. This is dangerouse way to do like that. Suggest to use Sql Parameters instead.

  2. Use its this way.

    DateTime dt = DateTime.ParseExact(txt_OrderDate.Text, "yyyy-MM-dd HH:mm", CultureInfo.InvariantCulture);

  3. Then pass dt value to SQL Parameter.

  4. Example of using Sql Parameter from MSDN:

    private static void UpdateDemographics(Int32 customerID, string demoXml, string connectionString) { // Update the demographics for a store, which is stored // in an xml column. string commandText = "UPDATE Sales.Store SET Demographics = @demographics " + "WHERE CustomerID = @ID;";

        using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand(commandText, connection);
        command.Parameters.Add("@ID", SqlDbType.Int);
        command.Parameters["@ID"].Value = customerID;

        // Use AddWithValue to assign Demographics.
        // SQL Server will implicitly convert strings into XML.
        command.Parameters.AddWithValue("@demographics", demoXml);

        try
        {
            connection.Open();
            Int32 rowsAffected = command.ExecuteNonQuery();
            Console.WriteLine("RowsAffected: {0}", rowsAffected);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
}

Upvotes: 1

Gaurav Jain
Gaurav Jain

Reputation: 444

    SqlConnection con = new SqlConnection(@"Data Source=BG9;Initial Catalog=Northwind;Integrated Security=True");
    con.Open();
    SqlDataAdapter ad1 = new SqlDataAdapter();
    ad1.InsertCommand = new SqlCommand("insert into test_date values (CONVERT(VARCHAR(10),'" + TextBox1.Text+"',120))", con);
    ad1.InsertCommand.ExecuteNonQuery();
    con.Close();

Upvotes: 1

Related Questions