Parsing Date from VB.NET for SQL INSERT

Question

I have a web app with a form that I am trying to pass to an ASP.NET server (using VB.NET) and then on to a MS SQL Server table. The form uses a jQuery datepicker in several textboxes and formats them as MM/dd/yyyy. The form fields are then passed through a PageMethod to the web server which takes the various field values and combines them into a SQL UPDATE command.

I am constantly getting the following error whenever I try to execute the SQL command:

Conversion failed when converting date and/or time from character string.

Here is the code on the server:

Using myConn As New System.Data.SqlClient.SqlConnection(CString.ConnectionString)
    myConn.Open()

    Dim cmd As New System.Data.SqlClient.SqlCommand("UPDATE table " & _
                             "SET type = '" & type & "', " & _ 
                                 "target = '" & "@target" & "', " & _
                                 "patient = '" & patient & "', " & _
                                 "dob = '" & "@dob" & "' " & _   
                             "WHERE serial = '" & serial & "'", myConn)


    cmd.Parameters.Add(SqlParameter("@target", Data.SqlDbType.Date))       
    cmd.Parameters.Add(SqlParameter("@dob", Data.SqlDbType.Date))

    If target = "" Then
        cmd.Parameters("@target").Value = Data.SqlTypes.SqlDateTime.Null
    Else
        cmd.Parameters("@target").Value = target
    End If

    If dob = "" Then
        cmd.Parameters("@dob").Value = Data.SqlTypes.SqlDateTime.Null
    Else
        cmd.Parameters("@dob").Value = dob
    End If

    cmd.ExecuteNonQuery()

End Using

Note: I've tried about twenty different ways of parsing the dates, converting them to dates, changing around the formats and none of it has worked.

Note 2: The conditional statements at the end are simply to prevent empty date fields from being stored in the SQL DB as "1/1/1900", but rather as an actual SQL NULL value. From debugging though, it seems that this is not the issue - it is when there is an actual value that the error is fired.

If anyone can see what I'm doing wrong and how I might fix it, it would be greatly appreciated. Thanks in advance for your help!

Cade Roux · Accepted Answer · 2012-02-01 14:08:49Z

1

You are mixing up your parameterized and non-parameterized parts (why aren't you parameterizing everything?)

Dim cmd As New System.Data.SqlClient.SqlCommand("UPDATE table " & _
                         "SET type = '" & type & "', " & _ 
                             "target = @target, " & _
                             "patient = '" & patient & "', " & _
                             "dob = @dob " & _   
                         "WHERE serial = '" & serial & "'", myConn)

answered Feb 1, 2012 at 14:08

Cade Roux

90.1k42 gold badges186 silver badges268 bronze badges

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

3 Comments

mbeasley Over a year ago

I wasn't sure that that made a difference. But you just solved my problem. I was putting single quotes around the parameters still (which were originally non-parameters, just stored string variables). I just changed it and it worked like a charm. I think I'll also parameterize everything. Thanks for your help!

Cade Roux Over a year ago

@mbeasley You can mix them, but target = '@target' is setting target equal to a literal string containing "@target", because SQL sees that as a literal. @ parameters are not insertion points which just get replaced.

mbeasley Over a year ago

Right. That's what I ended up fixing and it worked out perfectly. Foolish mistake on my end, but lesson learned. I appreciate your help.

Bill Martin · Accepted Answer · 2012-02-01 14:10:52Z

0

Are you including time? DateTime fields require date and time.

answered Feb 1, 2012 at 14:10

Bill Martin

4,96310 gold badges57 silver badges87 bronze badges

Collectives™ on Stack Overflow

Parsing Date from VB.NET for SQL INSERT

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related