how to insert a date from textbox to database

Question

please help me to insert a date from a text box in dd-mm-yyyy format to sql server. my code is as follows:-

        int prio = Convert.ToInt32(Priority.Text);
        string stdate = planstart.Text;
        string endate= planend.Text;

        string actst = actualstart.Text;
        string acten = actualend.Text;



            SqlConnection myconnection = new SqlConnection(constring);
            SqlCommand mycommand = new SqlCommand();
            DataSet mydataset = new DataSet();
            SqlDataAdapter mydataadapter = new SqlDataAdapter();

            myconnection.Open();
            mycommand.Connection = myconnection;
            mycommand.CommandText = " insert into project_status.dbo.Project_Status_Report values('" + projectcode.Text + "','" + projectname.Text + "',(select P_Code from project_status.dbo.Project_Type where Project_Type = '" + projecttype.Text + "')," + prio + ",'" + stdate + "','" + endate + "','" + actst + "','" + acten + "','" + currentstatus.Text + "','" + remark.Text + "','no');";

            mycommand.CommandType = CommandType.Text;
            mycommand.ExecuteNonQuery();

and it is throwing an exception saying:- Conversion failed when converting date and/or time from character string.

1) Look into **parameterized queries** to protect yourself from SQL Injection. 2) If your date should be a date, don't treat it as a string. Validate that it is a valid date, convert it, then provide it as a parameter in the query. — Anthony Pegram, Feb 10 '12 at 06:15
can you tell me how to convert it.cause i have tried but failed to convert. — Ajay Mirge, Feb 10 '12 at 06:19

Pranay Rana · Accepted Answer · 2012-02-10T06:23:52.243

You need to convert data according to you sql server formate that way you can resolve issue ..

Try

String UrDate = "27/12/2011"; 
System.Globalization.DateTimeFormatInfo dateInfo = new System.Globalization.DateTimeFormatInfo(); 
dateInfo.ShortDatePattern = "dd/MM/yyyy"; 
DateTime validDate= Convert.ToDateTime(toDate, dateInfo);

or

Format String For Dates

// String to DateTime
 String MyString;
 MyString = "1999-09-01 21:34 PM";
 //MyString = "1999-09-01 21:34 p.m.";  //Depends on your regional settings

 DateTime MyDateTime;
 MyDateTime = new DateTime();
 MyDateTime = DateTime.ParseExact(MyString, "yyyy-MM-dd HH:mm tt",
                                      null);

Make use of Paramerize query to avoid SQL INJECTION...make code less error pron Walkthrough: Displaying Data in a Windows Form Using a Parameterized Query

actually i am taking date in a format dd/mm/yyyy.and i have tried the above code but it is throwing an exception saying string is not in a valid date fomat.as i am new i am not aware of these things please help me.thank you.. — Ajay Mirge, Feb 10 '12 at 06:23

score 0 · Answer 2 · answered Jun 28 '15 at 16:27

 con.Open();
 string query = "insert_demo";
    /* date  fromat Stored*/
 TextBox2.Text = DateTime.Now.ToLongDateString();
 SqlCommand com = new SqlCommand(query, con);


 com.CommandType = CommandType.StoredProcedure;

 com.Parameters.AddWithValue("@Name", TextBox1.Text.ToString());
 com.Parameters.AddWithValue("@Date", TextBox2.Text.ToString());

 com.ExecuteNonQuery();

FarligOpptreden · Answer 3 · 2012-02-10T06:29:57.590

0

Just a word of caution - you need to sanitize that query to prevent SQL injection attacks. Consider using parameterised queries. Read up about it, it's not really the scope of this answer.

You should create strongly typed DateTime objects first and then format them the way you need to insert. Consider the following modification to your code:

string stdate = DateTime.Parse(planstart.Text).ToString();
string endate = DateTime.Parse(planend.Text).ToString();

string actst = DateTime.Parse(actualstart.Text).ToString();
string acten = DateTime.Parse(actualend.Text).ToString();

EDIT

I removed the string parameter from the ToString() so you can get a valid DateTime string that's usable by SQL Server.

edited Feb 10 '12 at 06:29

answered Feb 10 '12 at 06:18

FarligOpptreden

5,013
22
23

thank you.but i am still getting an error:-String was not recognized as a valid DateTime. – Ajay Mirge Feb 10 '12 at 06:26
That might be the regional settings on your database then. If the data type is datetime, you need to actually pass through a VALID datetime object. The format in which it gets displayed depends on the regional settings. Consider removing the string parameter from the ToString() method - it should take regional settings into account and return a valid DateTime string. – FarligOpptreden Feb 10 '12 at 06:28

how to insert a date from textbox to database

3 Answers3

Linked