Passing Datetime in SQLCommand

I have to insert a row in my SQL Server via VS 2008 and my codes goes as follows;

conn = New SqlConnection
            conn.ConnectionString = String.Format("Data Source={0}; Integrated Security=True; Database={1};", Datasource, Database)

 sqlcmd = conn.CreateCommand()
            sqlcmd.CommandText = String.Format("Insert into WWF_Test (Name, Address, Quantity, CreditCardNo, LogMsg, Date_Time) values ({0}, {1}, {2}, {3}, {4}, {5})", _
                                               CustomerName, CustomerAdd, Quantity, CreditCardNum, Now, message)

            Using conn
                conn.Open()
                sqlcmd.ExecuteNonQuery()
                conn.Close()
            End Using

On run, I'm having an exception with the Date_Time column (of type datetime in SQL Server), whereby the time part is throwing an error. Like;

{"Incorrect syntax near '10'."} --> where Now = 11/11/2013 10:50:24

Am I missing a conversion? Considering that I need a datetime, I think it should have accepted it. Any help plz.

Jon Skeet
people
quotationmark

Don't use string.Format to put parameters into your SQL to start with.

Instead, use parameterized SQL, and specify the values for those parameters. This will avoid SQL Injection Attacks as well as helping avoid unnecessary and problematic string conversions.

So you want something like:

sqlcmd.CommandText = "Insert into WWF_Test (Name, Address, Quantity, CreditCardNo, LogMsg, Date_Time) values (@Name, @Address, @Quantity, @CreditCardNo, @LogMsg, @Date_Time)"

sqlcmd.Parameters.Add("@Name", SqlDbTypes.NVarChar).Value = CustomerName
sqlcmd.Parameters.Add("@Address", SqlDbTypes.NVarChar).Value = CustomerAdd
etc

... using SqlDbTypes.DateTime where appropriate. This way, you don't need to worry about how the DateTime value would be represented in SQL, because it isn't in SQL. It's possible that the driver will convert it into text in order to pass it along, but even if it does that means it's the driver's responsibility to get it right. It's more likely that it'll use a binary representation anyway.

people

See more on this question at Stackoverflow