Urgent! How to Query GeoDatabase Layer

[Gordon1]

I am trying to run a sql query through a file geo database layer. but i keep getting an exception thrown. 

Here is my Code 

Please any help is greatly appreciated

Thanks

Gordon

public string SearchAddresses(Map map, GeoCollection<object> args)
       {
 
           SessionHandler.StreetNumber = args[0].ToString();
           SessionHandler.PreDir = args[1].ToString();
           SessionHandler.PreType = args[2].ToString();
           SessionHandler.StreetName = args[3].ToString();
           SessionHandler.SufDir = args[4].ToString();
           SessionHandler.City = args[5].ToString();
           float mapWidth = 0f;
           float mapHeight = 0f;
           float.TryParse(args[6].ToString(), out mapWidth);
           float.TryParse(args[7].ToString(), out mapHeight);
 
           StringBuilder sbSQL = new StringBuilder();
           string jsonresults;
 
           if (!string.IsNullOrEmpty(SessionHandler.ActiveCounty))
           {
 
               LayerOverlay ovlFindResults = (LayerOverlay)map.CustomOverlays[SessionHandler.WebAddressPointsName];
              FileGeoDatabaseFeatureLayer lyrFindResults = ovlFindResults.Layers[0] as FileGeoDatabaseFeatureLayer;
 
 
               string sqlWhereCont = " WHERE “;
               sbSQL.Append(“SELECT “);
               //sbSQL.Append(SessionHandler.AddressPointsPointsIDColumn + " AS PointsID,”);
               sbSQL.Append(“ObjectID As ObjectID,”);
               //sbSQL.Append(“POINTS_ID as PointsID,”);
               sbSQL.Append(SessionHandler.AddressPointsStreetNumberColumn + " AS StreetNumber,”);
               sbSQL.Append(SessionHandler.AddressPointsSufTypeColumn + " AS SufType,”);
               sbSQL.Append(SessionHandler.AddressPointsPreDirColumn + " AS PreDir,");
               sbSQL.Append(SessionHandler.AddressPointsStreetNameColumn + " AS StreetName,");
               sbSQL.Append(SessionHandler.AddressPointsLabelColumn + " AS Address “);
               //sbSQL.Append(SessionHandler.AddressPointsCityColumn + " AS City”);
               sbSQL.Append(" FROM [" + SessionHandler.WebAddressPointsArcLayerName + “]”);
               if (!string.IsNullOrEmpty(SessionHandler.StreetNumber))
               {
                   sbSQL.Append(sqlWhereCont + SessionHandler.AddressPointsStreetNumberColumn + " = ‘" + SessionHandler.StreetNumber + "’");
                   sqlWhereCont = "AND ";
               }
               if (!string.IsNullOrEmpty(SessionHandler.PreDir))
               {
                   sbSQL.Append(sqlWhereCont + “UCASE(” + SessionHandler.AddressPointsPreDirColumn + “) = '” + SessionHandler.PreDir.ToUpper() + “’”);
                   sqlWhereCont = "AND ";
               }
               if (!string.IsNullOrEmpty(SessionHandler.AddressPointsPreTypeColumn))
               {
                   if (!string.IsNullOrEmpty(SessionHandler.PreType))
                   {
                       sbSQL.Append(sqlWhereCont + “UCASE(” + SessionHandler.AddressPointsPreTypeColumn + “) = '” + SessionHandler.PreType.ToUpper() + “’”);
                       sqlWhereCont = "AND ";
                   }
               }
               if (!string.IsNullOrEmpty(SessionHandler.StreetName))
               {
                   sbSQL.Append(sqlWhereCont + “UCASE(” + SessionHandler.AddressPointsStreetNameColumn + “) LIKE '” + SessionHandler.StreetName.ToUpper() + “%’”);
                   sqlWhereCont = "AND ";
               }
               if (!string.IsNullOrEmpty(SessionHandler.SufType))
               {
                   sbSQL.Append(sqlWhereCont + “UCASE(” + SessionHandler.AddressPointsSufTypeColumn + “) LIKE '” + SessionHandler.SufType.ToUpper() + “%’”);
                   sqlWhereCont = "AND ";
               }
               if (!string.IsNullOrEmpty(SessionHandler.SufDir))
               {
                   sbSQL.Append(sqlWhereCont + “UCASE(” + SessionHandler.AddressPointsSufDirColumn + “) = '” + SessionHandler.SufDir.ToUpper() + “’”);
                   sqlWhereCont = "AND ";
               }
               if (!string.IsNullOrEmpty(SessionHandler.City))
               {
                   sbSQL.Append(sqlWhereCont + “UCASE(” + SessionHandler.WebCitiesColumn + “) = '” + SessionHandler.City.ToUpper() + “’”);
                   sqlWhereCont = "AND “;
               }
 
               sbSQL.Append(” ORDER BY ");
               //sbSQL.Append( SessionHandler.AddressPointsCityColumn + “,”);
               sbSQL.Append(SessionHandler.AddressPointsStreetNameColumn + “,”);
               sbSQL.Append(SessionHandler.AddressPointsSufTypeColumn + “,”);
               sbSQL.Append(SessionHandler.AddressPointsPreDirColumn + “,”);
               sbSQL.Append(SessionHandler.AddressPointsStreetNumberColumn);
 
               lyrFindResults.Open();
               dtFindResults = lyrFindResults.QueryTools.ExecuteQuery(sbSQL.ToString());
               var x = dtFindResults.Rows.Count;
 
               //GetJson(dtFindResults);
 
               jsonresults = GetJson(dtFindResults);
 
               lyrFindResults.Close();
 
               return jsonresults;
           }
           return string.Empty;
       }

[Troy]

Hi Gordon,



Would you append the exception message here so we can see more details? and also the sql string?



Currently, I only assume there might be some issues on the sql statement.



Thanks,

Troy

[Gordon1]

SQL string = "SELECT ObjectID As ObjectID,HNO,STS AS SufType,PRD AS PreDir,RDLABEL AS Address  FROM [Addresses] WHERE HNO = ‘101’ ORDER BY RDSTS,PRD,HNO 
  
 The exception that i am getting is “Exception has been thrown by the target of an invocation.”

[Troy]

Hi Gordon,



I tried a similar T-sql statement with a gdb file but didn’t encounter your exception, here is the file and the test code. 

FileGeoDatabaseFeatureSource.GetTableNames("…/…/data/ExecuteSQL.gdb");
FileGeoDatabaseFeatureSource featuresource = new FileGeoDatabaseFeatureSource("…/…/data/ExecuteSQL.gdb", “Cities”);
featuresource.Open();
DataTable dt = featuresource.ExecuteQuery(“select ObjectID as objectID,CITY_NAME as name,STATE_NAME as state_name from Cities order by POP1990,CITY_NAME”);

Would you please attached your gdb file or send to us if the size is large? 



Thanks,

Troy

ExecuteSQL.gdb.zip (159 KB)