How do i insert a value to a DateTime datafield in SQL database?

I use this code:

protected void InsertButton_Click(object sender, EventArgs e)
        {
            string msg = MsgTextArea.Value;
            DateTime start = Convert.ToDateTime(StartTextBox.Text);
            DateTime end = Convert.ToDateTime(StartTextBox.Text);

            db.insertSpecialClosing(msg, start, end);
        }

 
public void insertSpecialClosing(string msg, DateTime start, DateTime end)
        {
            query("INSERT INTO SpecialClosing (Text, SpecialClosingStart, SpecialClosingEnd) VALUES('" + msg + "', '"+start+"', '"+end+"')");
        }

And i get this error: 

+  $exception {"The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.\r\nThe statement has been terminated."} System.Exception {System.Data.SqlClient.SqlException}

What am i doing wrong?

Try Removing  "' "         singl quote from msg

Try to call like this:

db.insertSpecialClosing(msg, start.Date, end.Date);

 Thank you for your replies, unfortunaly none of them works. Do you have any other suggentions?

 User Paremeterized Queries.

http://aspnet101.com/aspnet101/tutorials.aspx?id=1

It will not only solve your problem, but it is also a defense against SQL injections...

Hi,

 Try replaceing start by start.ToShortDateString() and End by end.ToShortDateString() in the query

Thanks

Hi,

try this 

FormatDateTime(txtdate.text,DateFormat.ShortDate)

("INSERT INTO SpecialClosing (Text, SpecialClosingStart, SpecialClosingEnd) VALUES('" + msg + "', '"+start.ToString("MM/dd/yyyy")+"', '"+end.ToString("MM/dd/yyyy")+"')");   

Use Query like this. 

 I tried all of your suggentions and they work as far of putting data into the database, but it doesn't insert the correct date. For example if a put in the date 24/06/2009 i get this date in the database: 29-06-1894 00:00:00. Why is that?

Hi,

Before inserting data into DB, try to call this

set dateformat 'dmy'.

in DB

This will set the dateformat in your DB and then call the insert statement.

Thanks

Poku:

I tried all of your suggentions and they work as far of putting data into the database

Did you try a parameter query also?

hi try site http://codeincsharp.blogspot.com/2008/05/different-date-format-in-c.html

Try to use Parameterized query as suggested by someone. It will handle the date formats for you.

Like:

string strQuery;

SqlCommand cmd;

strQuery = "insert into customers (CustomerID, CompanyName) values(@CustomerID, @CompanyName)";

cmd = new SqlCommand(strQuery);

cmd.Parameters.AddWithValue("@CustomerID", "A234");

cmd.Parameters.AddWithValue("@CompanyName", "DCB");

InsertUpdateData(cmd);

Refer to:

http://aspnet101.com/aspnet101/tutorials.aspx?id=1

http://www.aspsnippets.com/post/Parameterized-Queries-ADONet.aspx

http://www.4guysfromrolla.com/webtech/092601-1.shtml

Never use inline queries like this. This will cause SQL Injection attacks.

Refer to:

http://csharp-codesamples.com/2009/05/preventing-sql-injection-using-linq/

http://unixwiz.net/techtips/sql-injection.html

http://www.sitepoint.com/article/sql-injection-attacks-safe/

http://www.acunetix.com/websitesecurity/sql-injection.htm

http://www.owasp.org/index.php/SQL_injection