error converting varchar to numeric in update string

Question

I am having an issue with my update code, error converting varchar to numeric is creating an exception. It is dealing with two tables.

table1(WEB_ADDRESS) consists of address information That is sent to a geocoding API service.

|PKID | ADDRESS_ID | ADDRESS_1 | CITY | etc...

table2(WEB_ADDRESS_GEO) consists lattitudes and longitudes recieved from the API service.

ADDRESS_ID | Lat | Lng

I need to update the rows of data in table 2 with lats and lngs to match the address data in table 1.

here is the code here

  using (SqlConnection myConnection = new SqlConnection(context))
  {
      myConnection.Open();
      string strQueryUpdate = "UPDATE WEB_ADDRESS_GEO SET Lat = '" + strLat + "', Lng = '" + strLng + "'" + "WHERE ADDRESS_ID=" + row.ADDRESS_ID;
      SqlCommand myCommandUpdate = new SqlCommand(strQueryUpdate, myConnection);
      myCommandUpdate.ExecuteNonQuery();

the column ADDRESS_ID is of type VarChar, Lat and Lng are of type decimal.

Note : sql injection is avoided as there will never be user input.

You will feel the wrath of gods of SQL injection by doing that string concatenation instead of using prepared statements/parameters... — Patryk Ćwiek
– Patryk Ćwiek, Commented Dec 11, 2014 at 15:09
Note: sql injection is not the only reason to use parameterized commands. For example parameters have explicit types so you can avoid type problems such as you are having here (or at least they are easier to see). — Chris
– Chris, Commented Dec 11, 2014 at 15:10

Soner Gönül · Accepted Answer · 2014-12-11 15:20:47Z

2

If ADDRESS_ID is VarChar, you need to use single quotes with it like;

"WHERE ADDRESS_ID= '" + row.ADDRESS_ID + "'"

If Lat and Lng are decimal, you don't need to use single quotes with them.

SET Lat = " + strLat + ", Lng = " + strLng

As a better way, use parameterized queries. This kind of string concatenations are open for SQL Injection attacks.

using(SqlConnection myConnection = new SqlConnection(context))
using(SqlCommand myCommandUpdate = conn.CreateCommand())
{
   myCommandUpdate.CommandText = @"UPDATE WEB_ADDRESS_GEO SET Lat = @lat, Lng = @lng 
                                  WHERE ADDRESS_ID = @address";
   myCommandUpdate.Parameters.Add(@lat, SqlDbType.Decimal).Value = strLat;
   myCommandUpdate.Parameters.Add(@lng, SqlDbType.Decimal).Value = strLng;
   myCommandUpdate.Parameters.Add(@address, SqlDbType.VarChar).Value = row.ADDRESS_ID;
   myConnection.Open();
   myCommandUpdate.ExecuteNonQuery();
}

But from your variable names, strLat and strLng really sounds like some character values, not numerical values. It is the opposite your row.ADDRESS_ID value. Mostly the variable contains ID as a name used in numeric values, not characters.

edited Dec 11, 2014 at 15:20

answered Dec 11, 2014 at 15:09

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

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

7 Comments

Chris Over a year ago

Even if not worrying about SQL injection (because you 100% trust the source) parameterized queries are better at dealing with types because you don't need to worry about whether you are quoting correctly and such like.

Chris Over a year ago

NB. I think you should remove the ADDRESS_ID comments. IDs are usually numeric and there is no reason to suspect that it isn't in this case and it obscures the real issue that you mention next.

RyeNyeTheComputerScienceGuy Over a year ago

It looks like the best answer for the question is really just to use parametrized queries. So I will do that.

Soner Gönül Over a year ago

@RyeNyeTheWebSiteGuy What is the error exactly? Can you please be more specific?

Soner Gönül Over a year ago

@RyeNyeTheWebSiteGuy This doesn't look to me like a numeric value. Using VarChar type will be good choice.

|

Collectives™ on Stack Overflow

error converting varchar to numeric in update string

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related