Insert Date into sql table with Date column

Question

Hello and thanks for reading.

I'm trying to insert the current date into my table, but I can't figure out how to write it correctly.

Here is my C# code:

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString);
conn.Open();

string Comment = UserWriteComment.Text;
string ID = DetailedID.Text;
string Name = DetailedName.Text;
string UniqueID = lblID.Text;


string query = "INSERT INTO Comment(TicketID, Name, Comments, UserID, Date)" + "Values('" + ID + "', '" + Name + "', '" + Comment + "', '" + UniqueID + "', '" + Date + "')";
using (SqlCommand com = new SqlCommand(query, conn))
{
    com.ExecuteNonQuery();
    UserWriteComment.Text = "";
}

In the Query, There is a value called Date. This is here I like the Function to pass the current date into my Table.

I hope you can help me because I didnt managed to find the answer anywere.

Thanks:)

What is the problem? did you get any error or unexpected result? — Selman Genç
– Selman Genç, Commented Feb 12, 2014 at 22:10
Yes I got an error but that is most likely because I cant write the string thats needed correctly. — Carsten Løvbo Andersen
– Carsten Løvbo Andersen, Commented Feb 12, 2014 at 22:11

2 revs · Accepted Answer · 2014-02-13 06:24:59Z

7

Use DateTime.Now or (in the database via sql) GetDate(). But more important, use sql-parameters to prevent sql-injection and conversion/localization issues:

string insertSql = @"INSERT INTO Comment(TicketID, Name, Comments, UserID, Date)
                     Values(@ID, @Name, @Comment, @UniqueID, @Date)";
using (var conn = new SqlConnection("...."))
using (var com = new SqlCommand(insertSql, conn))
{
    com.Parameters.AddWithValue("@ID", ID);
    com.Parameters.AddWithValue("@Name", Name);
    com.Parameters.AddWithValue("@Comment", Comment);
    com.Parameters.AddWithValue("@UniqueID", UniqueID);
    com.Parameters.AddWithValue("@Date", DateTime.Now);
    conn.Open();
    com.ExecuteNonQuery();
}

The using-statement ensures that unmanaged resources like the connection will be disposed/closed even in case of an error.

edited Feb 13, 2014 at 6:24

community wiki

2 revs
Tim Schmelter

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 11:44:36Z

5

Use DateTime.Now instead of Date. i.e. update the INSERT line to the following.

string query = "INSERT INTO Comment(TicketID, Name, Comments, UserID, Date)" 
              + "Values('" + ID + "', '" + Name + "', '" + Comment + "', '" 
              + UniqueID + "', '" + DateTime.Now + "')";

P.S: You really should be using Parameterize statements to avoid a Bobby Tables situation.

enter image description here

To fix this, implement it as shown by @Tim in his answer:

edited May 23, 2017 at 11:44

CommunityBot

11 silver badge

answered Feb 12, 2014 at 22:12

Shiva

21.1k15 gold badges88 silver badges120 bronze badges

3 Comments

abhi Over a year ago

This also a valid option,

Dmitriy Khaykin Over a year ago

+1 for the comical approach to recommending SqlParameter usage :)

Carsten Løvbo Andersen Over a year ago

Awesome Thanks, didnt thought it was that "easy" as just using datatime.now in the Values parameter

Jag · Accepted Answer · 2014-02-12 22:15:11Z

0

Instead of Date, try using the following

DateTime.Now

Another function that can help you is

 GETDATE()

answered Feb 12, 2014 at 22:15

Jag

2911 silver badge4 bronze badges

1 Comment

Mark Giaconia Over a year ago

you could also consider using getdate() as the default field value in your table, unless you'll have timezone problems between appserver and database server.

Isaac G Sivaa · Accepted Answer · 2014-09-02 21:58:21Z

0

Date inserts for SQL Server is best used via :

GetDate()

or

Convert(Varchar, GetDate(), 101)

Note: converting the GetDate() value to varchar type 101 shortens the value to just the date w/o time stamp.

edited Sep 2, 2014 at 21:58

Isaac G Sivaa

1,2994 gold badges15 silver badges32 bronze badges

answered Sep 2, 2014 at 21:05

Sean

1

Collectives™ on Stack Overflow

Insert Date into sql table with Date column

4 Answers 4

Comments

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related