1

I am not able to insert date from text box which is in DD/MM/YYYY format. I am using Visual Studio 2008 on Windows 7.

my language and regional settings are "ENGLISH(United Kingdom)", and my system is showing today's date like 12/12/2013. The same date i have get in text box via form On load event.

private void Form1_Load(object sender, EventArgs e)
{
   textBox1.Text = DateTime.Now.ToShortDateString();
}

which results into 12/12/2013.

I have placed one button to store date in Table "Table1" which contain only one field Date and its data type is DATETIME. The one click event contains following lines.

SqlConnection c = new SqlConnection();
c.ConnectionString ="Data Source=.\\SQLEXPRESS;AttachDbFilename='D:\\Documents\\Visual Studio 2008\\Projects\\test\\test\\Database1.mdf';Integrated Security=True;User Instance=True";
c.Open();
DateTime d;
d = Convert.ToDateTime(textBox1.Text);
string q = "insert into table1 values('" + d + "')";
SqlCommand cmd = new SqlCommand(q, c);
cmd.ExecuteNonQuery();
MessageBox.Show("record inserted");

Whenever i edit date Say today is 12/12/2013(DD/MM/YYYY) and i typed 13/12/2013 , it shows the error Sql exception was unhandeled.

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

and when i type 12/13/2013 (MM/DD/YYYY)

it shows String was not recognized as a valid DateTime.

Any Suggestion Friends..???

CocLn
  • 742
  • 10
  • 15
Flanker
  • 89
  • 2
  • 13
  • Try to format the value into YYYY/MM/DD. your region might be in different date format.. – Jade Dec 12 '13 at 06:09
  • I want DD/MM/YYYY format only. – Flanker Dec 12 '13 at 06:17
  • using SqlParameter for DateTime type, will do the conversion automatically. Also whenever You convert a string To DateTime like Convert.ToDateTime(textBox1.Text), Always specify format. Similarly in case of Parsing and DateTimeObj.ToString(). Other wise the code will run on some machine and will break on another machine. – Priyank Dec 12 '13 at 07:18
  • @ priyank Thnks A lot – Flanker Dec 13 '13 at 05:37

4 Answers4

3

You should use proper parameters instead of concatanating strings:

    string q = "insert into table1 values (@dateparam)";
    SqlCommand cmd = new SqlCommand(q, c);
    cmd.Parameters.AddWithValue("@dataparam", d);
    cmd.ExecuteNonQuery();

This will help you with type conversion as well as mitigating SQL injection.

Szymon
  • 42,577
  • 16
  • 96
  • 114
  • Sir I am not aware about this format. Will you please show me exactly what i should do or send me link of Simple insert select queries. i will b greatfull – Flanker Dec 12 '13 at 06:19
  • 2
    Read more about SqlParameter class and how it's used. There's lots information on that topic. – Szymon Dec 12 '13 at 06:31
  • 1
    @Flanker [here is the MSDN page](http://msdn.microsoft.com/en-us/library/yy6y35y8%28v=vs.110%29.aspx) that gives a overview of Parameters – Scott Chamberlain Dec 12 '13 at 06:33
  • Its working sir......... Thanks @Szymon. i have learnt a new way to deal with Sql Commands. – Flanker Dec 13 '13 at 06:54
0

use this

d=DateTime.ParseExact(text, "dd/MM/yyyy hh:mm", CultureInfo.InvariantCulture);

C# DateTime.ParseExact

Parse string to DateTime in C#

Hope this helps

Community
  • 1
  • 1
A.K.
  • 3,321
  • 1
  • 15
  • 27
-1

Your code:

d = Convert.ToDateTime(textBox1.Text);

will make d as System.DateTime data type. If you want the format as mm/dd/yyyy - you will have to convert it back to string with the format type

for ex:

string q = "insert into table1 values('" + d.ToString("MM/dd/yyyy") + "')";

In my opinion - you don't have to convert your text box to date time especially if your sql data type is date time. If you do that for validation - then you should validate the format of text in text box.

Sudhakar Tillapudi
  • 25,935
  • 5
  • 37
  • 67
juds
  • 69
  • 1
  • 2
-1

Simply change your code,

Convert.ToDateTime(textbox.text, CultureInfo.InvarientCulture);

or add this in your 'site.Master.cs' page load event.

// to override the datetime format to "dd-MM-yyyy"(in Site Master)

            CultureInfo cl = (CultureInfo)CultureInfo.CurrentCulture.Clone();
            cl.DateTimeFormat.ShortDatePattern = "dd-MM-yyyy";
            cl.DateTimeFormat.LongTimePattern = "";
            Thread.CurrentThread.CurrentCulture = cl;

now you can use your code like this..

Convert.ToDateTime(textbox.text, CultureInfo.CurrentCulture); Now if you publish your code in any local server it will over ride the date format to 'dd-MM-yyyy'.

Ratheesh Raveendran Pillai
  • 43
  • 6