CODEWITHSUNDEEP
sqlvb.net
bmgh1985
bmgh1985

Reputation: 789

Date in SQL string from DateTimePicker

I am trying to insert a date from a DateTimePicker in a Windows Form in VB.Net into an SQL string in such a way as it recognises it as a datetime (the field itself is set as datetime in SQL Server).

I have tried a few methods (Convert at SQL level, Format at VB.Net level) and am now using a variable stored as DateTime, however I still cannot get it to work. A snippet of my code is below:

Using sqlConn = New SqlConnection(My.Settings.sqlString)
        sqlConn.Open()
        Dim dte As DateTime = Convert.ToDateTime(Me.dateMain.Text)
        Dim cmd As SqlCommand
        cmd = sqlConn.CreateCommand
        cmd.CommandText = "Update Table1 " &
        "SET Person='" & Me.person.Text & "'," &
        "Date='" & dte & "' " &
        "WHERE (Code = '" & PCode & "')"
        cmd.ExecuteNonQuery()
        cmd = Nothing
End Using

EDIT: The following error (or slight variation of) is what I have got with almost every attempt I have tried. This error was received after the Parameterization answer submitted below

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

So it seems that even still, it is not recognising it as a datetime in SQL. I imagine I will need to try again with Convert in the SQL string, but my knowledge of the function is limited. Anyone know how I can use it to get this to work (if that is the solution)?

Upvotes: 2

Views: 1745

Answers (2)

Matthew Haugen
Matthew Haugen

Reputation: 13286

Sounds like a job for parameterization!

Using sqlConn = New SqlConnection(My.Settings.sqlString)
    sqlConn.Open()
    Dim cmd As SqlCommand
    cmd = sqlConn.CreateCommand
    cmd.CommandText = "Update Table1 " &
        "SET Person = @person, " &
        "Date = @dte " &
        "WHERE (Code = @code)"
    cmd.Parameters.AddWithValue("@person", Me.person.Text)
    cmd.Parameters.AddWithValue("@dte", Me.dateMain.Value)
    cmd.Parameters.AddWithValue("@code", PCode)
    cmd.ExecuteNonQuery()
    cmd = Nothing
End Using

And while you're at it, I don't want to change too much because I've never written a line of VB in my life (so this also may or may not be right, let me know if it doesn't compile or something, but this is the gist), but SqlCommand does implement IDisposable, so you should probably wrap that in a Using as well.

Parameterizing your queries will eliminate bugs like you've come across, promote code reuse, and arguably most importantly, stop SQL Injection attacks. Please always use parameters, for all our sakes.

Upvotes: 5

Pradeep Kumar
Pradeep Kumar

Reputation: 6969

You can use the ISO 8601 format (YYYY-MM-DDThh:mm:ss[.mmm] or YYYYMMDD hh:mm:ss[.mmm]) or use the unseparated format(YYYYMMDD hh:mm:ss[.mmm]). Both should work fine.

Parameterized query is still better though.

Upvotes: 0

Related Questions