CODEWITHSUNDEEP
c#asp.net
Ajay Mirge
Ajay Mirge

Reputation: 102

how to insert a date from textbox to database

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.

Upvotes: 3

Views: 24699

Answers (3)

Anand Zambre
Anand Zambre

Reputation: 1

 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();

Upvotes: 0

FarligOpptreden
FarligOpptreden

Reputation: 5043

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.

Upvotes: 0

Pranay Rana
Pranay Rana

Reputation: 176896

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

Upvotes: 1

Related Questions