VB.NET SqlException Was Unhandled

Question

I am trying some SQL code but I get an error when I try this code.

    Main.database.ExecuteCommand("UPDATE Contacts SET first_name='" + c.first_name + _
                              "', middle='" + c.middle + _
                              "', last_name='" + c.last_name + _
                              "', age='" + c.age + _
                              "', mobile_phone='" + c.mobile_phone + _
                              "', home_phone='" + c.home_phone + _
                              "', work_phone='" + c.work_phone + _
                              "', home_street='" + c.home_street + _
                              "', home_city='" + c.home_city + _
                              "', home_state='" + c.home_state + _
                              "', home_zip='" + c.home_zip + _
                              "', work_street='" + c.work_street + _
                              "', work_city='" + c.work_city + _
                              "', work_state='" + c.work_state + _
                              "', work_zip='" + c.work_zip + _
                              "', home_www='" + c.home_www + _
                              "', work_www='" + c.work_www + _
                              "', home_email='" + c.home_email + _
                              "', work_email='" + c.work_email + _
                              "' WHERE first_name='" + c.first_name + _
                              "' AND last_name='" + c.last_name + "'")

I get the following error

Sql Exception was unhandled

The data types text and varchar are incompatible in the equal to operator.

Answer 1

Eeek!

Get rid of that dynamic SQL! It's completely unsafe. Use parameterized queries instead! Here's a little example:

Using conn As New SqlConnection(), _
      cmd As New SqlCommand("UPDATE Contacts SET first_name = @firstName", conn)
    conn.Open()
    cmd.Parameters.Add(new SqlParameter("firstName", c.first_name))
    cmd.ExecuteNonQuery()
End Using

Answer 2

As I alluded to in my comment, that code is completely unacceptable. Seriously, stop whatever you're doing right now and forget about this exception until you understand what sql injection is, why your code is vulnerable, and how to correctly use query parameters to protect it.

I mean it.

After you've done that and re-coded this to use parameterized queries, go back and modify your table in sql server so that your first_name, last_name, and most other text fields use the nvarchar data type instead.

The text datatype in sql server means external text, in that that the row itself only stores a pointer to the actual text. The text itself is kept in a completely different place. This is done to get around the 8192 byte size limit of rows in sql server, and it means that you can't modify or compare against fields of type text. You can only retrieve or replace the data in those fields. The "text" type is only intended for large (>1K) text fields.

Answer 3

All of the above, parameterization is manditory, I found this to be a wonderful how-to http://www.sqlservercentral.com/articles/.Net/workingwithsqlparameterinnet/2311/

One of my tricks is to dump the string and try it in a query window. The SQL Server interface will point to the offending code directly.

Answer 4

I tried the code revision using parameters

            Using conn As New SqlConnection(), _
            myCommand As New SqlCommand("UPDATE Contacts SET" + _
                                        "first_name=@first_name" + _
                                        "AND middle=@middle" + _
                                        "AND last_name=@last_name" + _
                                        "AND age=@age" + _
                                        "AND mobile_phone=@mobile_phone" + _
                                        "AND home_phone=@home_phone" + _
                                        "AND work_phone=@work_phone" + _
                                        "AND home_street=@home_street" + _
                                        "AND home_city=@home_city" + _
                                        "AND home_state=@home_state" + _
                                        "AND home_zip=@home_zip" + _
                                        "AND work_street=@work_street" + _
                                        "AND work_city=@work_city" + _
                                        "AND work_state=@work_state" + _
                                        "AND work_zip=@work_zip" + _
                                        "AND home_www=@home_www" + _
                                        "AND work_www=@work_www" + _
                                        "AND home_email=@home_email" + _
                                        "AND work_email=@work_email" + _
                                        "WHERE first_name=@first_name" + _
                                        "AND last_name=@last_name", conn)

            myCommand.Parameters.Add(New SqlParameter("@first_name", c.first_name))
            myCommand.Parameters.Add(New SqlParameter("@middle", c.middle))
            myCommand.Parameters.Add(New SqlParameter("@last_name", c.last_name))
            myCommand.Parameters.Add(New SqlParameter("@age", c.age))
            myCommand.Parameters.Add(New SqlParameter("@mobile_phone", c.mobile_phone))
            myCommand.Parameters.Add(New SqlParameter("@home_phone", c.home_phone))
            myCommand.Parameters.Add(New SqlParameter("@work_phone", c.work_phone))
            myCommand.Parameters.Add(New SqlParameter("@home_street", c.home_street))
            myCommand.Parameters.Add(New SqlParameter("@home_city", c.home_city))
            myCommand.Parameters.Add(New SqlParameter("@home_state", c.home_state))
            myCommand.Parameters.Add(New SqlParameter("@home_zip", c.home_zip))
            myCommand.Parameters.Add(New SqlParameter("@work_street", c.work_street))
            myCommand.Parameters.Add(New SqlParameter("@work_city", c.work_city))
            myCommand.Parameters.Add(New SqlParameter("@work_state", c.work_state))
            myCommand.Parameters.Add(New SqlParameter("@work_zip", c.work_zip))
            myCommand.Parameters.Add(New SqlParameter("@home_www", c.home_www))
            myCommand.Parameters.Add(New SqlParameter("@work_www", c.work_www))
            myCommand.Parameters.Add(New SqlParameter("@home_email", c.home_email))
            myCommand.Parameters.Add(New SqlParameter("@work_email", c.work_email))

            conn.Open()

            myCommand.ExecuteNonQuery()

            conn.Close()

        End Using

But I am still having a problem initializing the connection with this error

The ConnectionString property has not been initialized.