Date in SQL string from DateTimePicker

Question

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)?

As far as I remember, DateTimePicker control in Windows Form has a property Value which returns the DateTime selected in control. Use this property so you don't need to convert String into DateTime — sallushan
– sallushan, Commented Aug 13, 2014 at 8:07
It does, but this was not playing well when used in the SQL statement. It seemingly converted the Value to a string within the SQL string, giving me something on the lines of Date=#3/4/12# which didnt work in a connection string, hence my switch to using .Text which gives the date in a more recognizable text format for SQL — bmgh1985
– bmgh1985, Commented Aug 13, 2014 at 8:16
I think all you need is a space before the "WHERE". " WHERE (Code = '" & PCode & "')" — Pradeep Kumar
– Pradeep Kumar, Commented Aug 13, 2014 at 8:51
Amended. Also, that would not cause the error as described above — bmgh1985
– bmgh1985, Commented Aug 13, 2014 at 9:01

Matthew Haugen · Accepted Answer · 2014-08-13 17:13:13Z

5

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.

edited Aug 13, 2014 at 17:13

answered Aug 13, 2014 at 7:49

Matthew Haugen

13.4k5 gold badges41 silver badges55 bronze badges

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

7 Comments

bmgh1985 Over a year ago

Was hoping that wasn't the solution, but knew deep down it probably was. It's only a simple form for one person at work to use, so code injection wasn't really something I was considering combating, but it probably is the best way to get round it. Will test and let you know, thanks.

Matthew Haugen Over a year ago

@bmgh1985 As a general rule, every little bit of code you write should take every reasonable step to be secure. You just never know. Even if only one person uses it, I'd deem this a more-than-reasonable step to take. I'm sure you could control the format via DateTime.ToString(string) to make it something SQL would understand, but there's really no reason to bother when the best solution is this easy.

bmgh1985 Over a year ago

OK, finally got through doing that (like I said, that was just a snippet; I actually had around 50 lines to edit and add parameters for!), but I am still getting an error. Will amend OP with it

Matt Wilko Over a year ago

Worth noting that if the input is from a DateTimePicker then you should just use the .Value property when calling AddWithValue which will avoid the Convert.ToDateTime

bmgh1985 Over a year ago

@MattWilko Done that now and it worked this time. Thanks

|

Pradeep Kumar · Accepted Answer · 2014-08-13 07:58:06Z

0

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.

answered Aug 13, 2014 at 7:58

Pradeep Kumar

7,0294 gold badges24 silver badges50 bronze badges

Collectives™ on Stack Overflow

Date in SQL string from DateTimePicker

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related