CODEWITHSUNDEEP
sqlms-accesssyntax-error
nickycdk
nickycdk

Reputation: 53

MS-Access: SQL UPDATE syntax error, but why?

I'm getting a syntax error in this SQL, and can't seem to figure out why?

The SQL UPDATE returns this on the error: 

UPDATE Tankstationer 
SET Long='12.5308724', Lat='55.6788735' 
WHERE Id = 2;

Here's my code:

foreach (var row in reader)
{
   var id = reader.GetInt32(0);
   var adress = reader.GetString(1);
   var zip = reader.GetDouble(2);
   var city = reader.GetString(3);
   var adressToParse = adress + " " + zip + " " + city;

   GMapGeocoder.Containers.Results result = Util.Geocode(adressToParse, key);
   foreach (GMapGeocoder.Containers.USAddress USAdress in result.Addresses )
   {
      var google_long = convertNumberToDottedGoogleMapsValid(USAdress.Coordinates.Longitude);
      var google_lat = convertNumberToDottedGoogleMapsValid(USAdress.Coordinates.Latitude);
      Message.Text = "Lattitude: " + google_long + System.Environment.NewLine;
      Message.Text = "Longitude: " + google_lat + System.Environment.NewLine;
      string updatesql = "UPDATE Tankstationer SET Long='" +google_long+ "', Lat='" +google_lat+ "' WHERE Id = " +id+"";
      OleDbCommand update = new OleDbCommand();
      update.CommandText = updatesql;
      update.Connection = conn;
      reader = update.ExecuteReader();
      Message.Text = "Done";
   }
}

Upvotes: 2

Views: 2510

Answers (4)

RolandTumble
RolandTumble

Reputation: 4703

"Long" is a reserved word in Access. If you can't change the schema to call that column something else, put it in brackets:

UPDATE Tankstationer
  SET [Long]='12.5308724', Lat='55.6788735'
  WHERE Id = 2;

Upvotes: 0

Darryl Peterson
Darryl Peterson

Reputation: 2270

Try:

"id" is being set to Int32 (var id = reader.GetInt32(0);) but you are concatenating it to a string (WHERE Id = " +id+"";). Make sure that id is cast as a string value and not an int.

Upvotes: 0

mslliviu
mslliviu

Reputation: 1138

try using update.ExecuteNonQuery() instead of reader.

Saw other comments too late.

I don't use access often, but mine it's using <"> for text delimiter, not <'>

Upvotes: 0

cjk
cjk

Reputation: 46425

The error is probably because you are executing a reader, but your query does not return anything. Call update.ExecuteNonQuery() instead.

Upvotes: 3

Related Questions