How to save Date and Time with my other data in database

Question

I am working on an inventory software in which I am placing order in my order panel, every thing is working absolutely fine, let me show you my database image:

enter image description here

and the code I used to enter data in DB is :

string sql = "";

int ProductQuantity = 0;
string ProductCode = "";
int ProductPrice = 0;
int totalPrice = 0;
string customerName;


for (int i = 0; i < lvProductInfo.Items.Count; i++)
{
    ProductCode = Convert.ToString(lvProductInfo.Items[i].SubItems[1].Text);
    ProductQuantity = Convert.ToInt32(lvProductInfo.Items[i].SubItems[2].Text);
    ProductPrice = Convert.ToInt32(lvProductInfo.Items[i].SubItems[3].Text);
    totalPrice = Convert.ToInt32(lvProductInfo.Items[i].SubItems[4].Text);
    customerName = lvProductInfo.Items[i].SubItems[5].Text;
    sql = "";
    sql = "INSERT INTO PurchaseLog (ProductCode,ProductQuantity,ProductPrice,TotalPrice,CustomerName)"
        + " VALUES ('" + ProductCode + "','" + ProductQuantity + "','" + ProductPrice + "','" + totalPrice + "','" + customerName + "')";

    clsConnection clsCn = new clsConnection();
    SqlConnection cn = null;
    SqlCommand cmd = new SqlCommand();

    clsCn.fnc_ConnectToDB(ref cn);

    cmd.Connection = cn;
    cmd.CommandText = sql;
    cmd.ExecuteNonQuery();
}

Now I want to add date and time also with all these data in my DB.

[SQL Injection](http://www.w3schools.com/sql/sql_injection.asp) detected — Sergey Berezovskiy, Jan 18 '14 at 20:03
Use `SqlParameters` to pass inserted values instead of concatenating strings which you get from UI — Sergey Berezovskiy, Jan 18 '14 at 20:05
@psnLoverCSharp Take a look http://www.codinghorror.com/blog/2005/04/give-me-parameterized-sql-or-give-me-death.html — Soner Gönül, Jan 18 '14 at 20:06
ok thanks, now can you guys let me know how can i date date and time ? — Ahsan Hussain, Jan 18 '14 at 20:08

score 3 · Answer 1 · answered Jan 18 '14 at 20:05

3

You have to create a new column in your database of type Datetime and add the date to your insert statement, passing a date such as DateTime.Now in case you want to add the server date.

Be careful, you are risking SQL Injection attack you your way of creating the insert statement. You should, at least, change you code to use SqlParameters (example)

answered Jan 18 '14 at 20:05

Dante

3,833
4
38
55

i know about adding column in my DB sir, but the point i am confused on how can i pick date and time and pass it to the DB – Ahsan Hussain Jan 18 '14 at 20:06
1

DateTime.Now gives you the current date time. You can append that to your insert statement, like you do to the rest of the fields. But you should change that to SqlParameters for security reasons... – Dante Jan 18 '14 at 20:09
It's better to use SQL type datetime2. here is a relevant post http://stackoverflow.com/questions/1334143/sql-server-datetime2-vs-datetime – potehin143 Jan 18 '14 at 20:15

score 2 · Accepted Answer · edited Jan 18 '14 at 21:10

2

Define a DateTime variable

DateTime dateTime;

If You want to insert Current Date & Time of your system(or server) then insert like that

cmd.Parameter.AddWithValue("@DateTime",dateTime.Now);

OR If You want to insert mannuly (use DateTimePicker control) then insert as

cmd.Parameter.AddWithValue("@DateTime",dateTimePicker1.Value);

edited Jan 18 '14 at 21:10

puretppc

3,232
8
38
65

answered Jan 18 '14 at 21:07

prograshid

876
3
12
32

How to save Date and Time with my other data in database

2 Answers2