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.

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.

Can someone say "Sql Injection"? Or ';DROP TABLE Contacts;--? — Joel Coehoorn
– Joel Coehoorn, Commented May 7, 2010 at 20:37
It's probably your Age column that is wrong, but as others said, this should be re-written as a parameterized query. — D'Arcy Rittich
– D'Arcy Rittich, Commented May 7, 2010 at 20:39
this kind of coding is incredibly dangerous. I see Justin has an answer coming that does not involve dynamic sql, take it under serious consideration. Your current implementation is very easy to manipulate and cause serious headaches and data loss. — Matthew Jones
– Matthew Jones, Commented May 7, 2010 at 20:40
Another issue: it looks like you do not have a primary key on that table. What happens when you have two contacts with the same name? — D'Arcy Rittich
– D'Arcy Rittich, Commented May 7, 2010 at 20:42

Joel Coehoorn · Accepted Answer · 2010-05-07 21:04:53Z

4

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

edited May 7, 2010 at 21:04

Joel Coehoorn

418k114 gold badges581 silver badges818 bronze badges

answered May 7, 2010 at 20:38

Justin Niessner

246k40 gold badges416 silver badges546 bronze badges

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

Comments

Joel Coehoorn · Accepted Answer · 2010-05-07 21:03:50Z

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.

But, you never know how long phone numbers could be one day :)

Dave · Accepted Answer · 2010-05-07 20:56:15Z

0

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.

answered May 7, 2010 at 20:56

Dave

1,24413 silver badges24 bronze badges

Collectives™ on Stack Overflow

VB.NET SqlException Was Unhandled

3 Answers 3

Comments

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Related