CODEWITHSUNDEEP
c#sql-serverasp.net-mvc-5
RyeNyeTheComputerScienceGuy
RyeNyeTheComputerScienceGuy

Reputation: 172

How to update multiple sql rows where table 1's ID is equal to table 2's ID

Application has no user input, nor will it be live on the web, so sql injection is not a threat

My Application

This application is supposed to send multiple addresses from 1 table to Google's geocoder API and store the returned latitudes and longitudes inside a second table for later use.The API has a maximum of 2500 requests per day.

What the problem is

When I run my program with no limmits, there are only a few rows (about 20) that get updated out of 2500 rows that are supposed to get updated. When I limit my program to only update 10 rows to avoid hitting the API limit, no changes happen. Most of the rows that do update when I allow 2500 requests are updated with the correct value with the exception of a few, I'm not to worried about that right now I think those are just bad results from the geocoder.

What I have so far

I have 2 strings strLat and strLong that hold lattitudes and longitudes retrieved from google geocoder API. 

string strLat = myCoordenates.Results[3].Geometry.Location.Lat.ToString();
string strLong = myCoordenates.Results[3].Geometry.Location.Lng.ToString();

I also have my attempt to update my database with them. This has been changed and manipulated many times, and i think the problem is likely to do with this part of my code. 

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

In my data base table 1 holds address data it has an Pk ID as well as a column called ADDRESS_ID, and then address is broken up like ADDRESS,CITY,ST,ZIP...ETC, table 2 holds the latitudes and longitudes and also has that same ADDRESS_ID table with the same equal values, currently most of the lats and lngs are 0.00 and it is of type decimal.

Table 1

|--PKid--| ADDRESS_ID| ADDRESS | CITY..etc
|124246| 70-00002913 | 112 bill st. | bob town

Table 2

| ADDRESS_ID | Lat | Lng |
| 70-00002913 | 0.00| 0.00|

Entire Update Controller

    //GET: /TABLE2/Update/

    public ActionResult Update(string id)
    {
        IEnumerable<TABLE1> table1 = db.TABLE1;
        IEnumerable<TABLE2> table2 = db.TABLE2;
        string context = "my database context here";

        foreach (var row in table1.AsEnumerable().Take(10)) 
        {

            string strAddr = row.ADDRESS + "," + row.CITY + "," + row.ZIP + "," + row.COUNTRY;

            GoogleMapsDll.GeoResponse myCoordenates = new GoogleMapsDll.GeoResponse();
            myCoordenates = GoogleMapsDll.GoogleMaps.GetGeoCodedResults(strAddr);
            if (myCoordenates.Results != null && myCoordenates.Results.Length > 3)
            {
                string strLat = myCoordenates.Results[3].Geometry.Location.Lat.ToString();
                string strLong = myCoordenates.Results[3].Geometry.Location.Lng.ToString();
                using (SqlConnection myConnection = new SqlConnection(context))
                    {
                        myConnection.Open();
                        string strQueryUpdate = "UPDATE WEB_ARENA_GEO SET Lat = '" + strLat + "', Lng = '" + strLong + "'" + "WHERE ADDRESS_ID='" + row.ADDRESS_ID + "'";
                        SqlCommand myCommandUpdate = new SqlCommand(strQueryUpdate, myConnection);
                        myCommandUpdate.ExecuteNonQuery();
                    }
                db.SaveChanges();
            }

        }
        return RedirectToAction("Index");

If any other info would be helpful please simply ask in the comments and i will do my best to provide

Upvotes: 0

Views: 845

Answers (1)

mreyeros
mreyeros

Reputation: 4379

It looks like your where condition is defined incorrectly. You are currently sending the following where:

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

This is basically comparing the actual id values against eachother. I believe that you need to modify your query to be as follows:

 string strQueryUpdate = "UPDATE TABLE2 SET Lat = " + strLat + ", Lng = " + strLong + 
      " WHERE ADDRESS_ID='" + row.ADDRESS_ID + "'";

UPDATE

 var pageSize = 10;
 var totalPages = Math.Ceiling(table1.Count() / pageSize);

 for(var i = 0; i < totalPages; i++) {
      var addressesToProcess = table1.Skip(i * pageSize).Take(pageSize);
      foreach (var address in addressesToProcess) 
      {
           string strAddr = address.ADDRESS + "," + address.CITY + "," + address.ZIP + "," + address.COUNTRY;

           GoogleMapsDll.GeoResponse myCoordenates = new GoogleMapsDll.GeoResponse();
           myCoordenates = GoogleMapsDll.GoogleMaps.GetGeoCodedResults(strAddr);
           if (myCoordenates.Results != null && myCoordenates.Results.Length > 3)
           {
                string strLat = myCoordenates.Results[3].Geometry.Location.Lat.ToString();
                string strLong = myCoordenates.Results[3].Geometry.Location.Lng.ToString();
                using (SqlConnection myConnection = new SqlConnection(context))
                {
                    myConnection.Open();
                    string strQueryUpdate = "UPDATE WEB_ARENA_GEO SET Lat = '" + strLat + "', Lng = '" + strLong + "'" + "WHERE ADDRESS_ID='" + address.ADDRESS_ID + "'";
                    SqlCommand myCommandUpdate = new SqlCommand(strQueryUpdate, myConnection);
                    myCommandUpdate.ExecuteNonQuery();
                }
                db.SaveChanges();
           }
      }
 }

Upvotes: 1

Related Questions