winform c# Error while inserting data (DateTime) into SQL

Question

    SqlConnection con = new SqlConnection("Data Source=RANJEETMAURYA;Initial    Catalog=Project;Integrated Security=True");
    con.Open();

    DateTime current = DateTime.Now;

    //DateTime CurrentDate;
    //CurrentDate = Convert.ToDateTime(DateTime.Now.ToString("dd-MMM-yyyy"));
    current = Convert.ToDateTime(DateTime.Now.ToString("MM/dd/yyyy hh:mm"));

    SqlCommand cmd = new SqlCommand(@"INSERT INTO CustomerDetails
                      (Date, Name, Gender, Address, Contact_No, Email_ID)
VALUES        ('" +current+ "','" + txtName.Text + "','" + Gender + "','" +     txtAddress.Text + "','" + txtContact.Text + "','" + txtEmail.Text + "')", con);
    cmd.ExecuteNonQuery();
    con.Close();
    MessageBox.Show("Customer Information Added Successfully.", "Dairy  Management System", MessageBoxButtons.OK, MessageBoxIcon.Information);
    SQLFunctions.Refresh(this.dataGridCustomerDetails);

this is the error please help me out for what reason it is running some times, it is not running some times.

 System.FormatException was unhandled
  HResult=-2146233033
Message=String was not recognized as a valid DateTime.
Source=mscorlib
StackTrace:
   at System.DateTimeParse.Parse(String s, DateTimeFormatInfo dtfi, DateTimeStyles  styles)
   at System.Convert.ToDateTime(String value)
   at IndianDiary.frmCustomerDetails.btnAddNew_Click(Object sender, EventArgs e) in

much propbably it has something to do with DateTime format of your server. Check if datetime format of your SQL and your program matches up. — paroxit, Mar 13 '14 at 10:18
Converting the DateTime into user-defined string format is the reason behind the error. — Atanu Roy, Mar 13 '14 at 10:32

score 4 · Answer 1 · edited May 23 '17 at 12:05

You are converting current time to string and then parsing string back to DateTime:

DateTime current = DateTime.Now;
current = Convert.ToDateTime(DateTime.Now.ToString("MM/dd/yyyy hh:mm"));

What is the point of this? Just use DateTime.Now. Also use command parameters.

string sql = @"INSERT INTO CustomerDetails
               (Date, Name, Gender, Address, Contact_No, Email_ID)
               VALUES (@date, @name, @gender, @address, @contactNo, @emailId)";

SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@date", DateTime.Now);
cmd.Parameters.AddWithValue("@name", txtName.Text);
cmd.Parameters.AddWithValue("@gender", Gender);
cmd.Parameters.AddWithValue("@address", txtAddress.Text);
cmd.Parameters.AddWithValue("@contactNo", txtContact.Text);
cmd.Parameters.AddWithValue("@emailId", txtEmail.Text);

See How does SQLParameter prevent SQL Injection?

Also use App.config to store connection string:

<connectionStrings>
  <add name="ranjeet" 
       connectionString="Data Source=RANJEETMAURYA;Initial Catalog=Project;Integrated Security=True" 
       providerName="System.Data.EntityClient" />
</connectionStrings>

Then you will be able to get it with ConfigurationManager.

Also wrap connection and command into using statement to dispose them automatically:

using (SqlConnection con = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand(sql, con))
{
    // add parameters
    con.Open();
    cmd.ExecuteNonQuery();
}

score 0 · Answer 2 · answered Mar 13 '14 at 10:21

0

Why are You converting it to string just send DateTime.Now to database and while retrieving it from database use this

retrievedDate= DateRetrieved.ToString("MM/dd/yyyy hh:mm");

answered Mar 13 '14 at 10:21

Rajeev Ranjan

1,006
8
18

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated. it is showing this error – user3327117 Mar 13 '14 at 11:29

score 0 · Answer 3 · answered Mar 13 '14 at 10:28

As Reference to the answer of Sergey Berezovskiy you can also pass the parameter as:

string sql = @"INSERT INTO CustomerDetails
               (Date, Name, Gender, Address, Contact_No, Email_ID)
               VALUES (@date, @name, @gender, @address, @contactNo, @emailId)";

SqlCommand cmd = new SqlCommand(sql);
cmd.Parameters.Add("@date", SqlDbType.Date).Value = DateTime.Now;
cmd.Parameters.Add("@name", SqlDbType.Varchar, 50).Value = txtName.Text;
cmd.Parameters.Add("@gender", SqlDbType.Varchar, 10).Value = Gender;
cmd.Parameters.Add("@address", SqlDbType.Varchar, 50).Value =txtAddress.Text;
cmd.Parameters.Add("@contactNo", SqlDbType.Varchar, 25).Value = txtContact.Text;
cmd.Parameters.Add("@emailId", SqlDbType.Varchar, 35).Value =txtEmail.Text;

winform c# Error while inserting data (DateTime) into SQL

3 Answers3