Datetime problem in VB 2005

Question

I am using VB2005 and SQL SERVER 2000.

PVAR_SQL_STR = "INSERT INTO GLR_US_PERIOD (ORG5_CODE,PERIOD_YEAR,PERIOD_CODE," _
    "PERIOD_NO,FROM_DATE,TO_DATE,INSERT_USER,INSERT_DATE) VALUES " _
    & "('" & PVAR_COMPANY_CODE & "' ,'" & TextBox1.Text & "','" & Serial1.Text & _
    "'," & TextBox2.Text & ", '" + DateTimePicker1.Value.ToString("D") + "' ,'" + _
    DateTimePicker2.Value.ToString("D") + "','" & PVAR_USER_CODE & "','" + _
    Now.ToString("F") + "')"

Syntax error converting datetime from character string because of this part only:

Now.ToString("F")

Why, I do not know but when I change into

Now.ToString("D")

it works well but it saves the date only. I want to insert date and time.

Answer 1

The simple answer is not to try to build it all into the SQL statement at all. Use a parameterised query instead, and set the parameter value to DateTime.Now (or DateTime.UtcNow) instead.

Parameterised queries are also an effective guard against SQL injection attacks. Inserting general data (especially when given by users) into SQL statements directly is a recipe for disaster.

See the docs for SqlCommand.Parameters for more information - or consult just about any decent tutorial or book on ADO.NET.