Hi Everyone,
I am trying to use a list of keys, mukeys, to search for these mukey within the mukey field in the component table and create a query table, CompTable. I keep getting an error, field not found at the line, WhereClause within the QueryDef. How do I write the WhereClause correctly using a the mukeys list? Here is my script below:
//there is more script above, but I just included the script section related to this question.
using (Row row = rowCursor.Current)
{
mukeyCount++;
var mukey = Convert.ToString(row["MUKEY"]);
mukeys.Add(mukey);
}
...
QueryDef queryDef = new QueryDef
{
Tables = "component",
WhereClause = "mukey= mukeys[mukeycount]", //error occurs here
SubFields = "component.cokey,component.mukey,component.comppct_r"
};
QueryTableDescription queryTableDescription = new QueryTableDescription(queryDef)
{
Name = "CompTable",
PrimaryKeys = geodatabase.GetSQLSyntax().QualifyColumnName("component", "cokey")
};
Table queryTable = geodatabase.OpenQueryTable(queryTableDescription);
Thanks everyone!
WhereClause = "mukey= mukeys[mukeycount]", //error occurs here
Because the WhereClause is looking for the field name as mukeys[mukeycount] that is not available. I am guessing that you are trying to find a field name in mukeys[mukeycount] which can be fetched as:
WhereClause = $"mukey= {mukeys[mukeycount]}"
@Aashis is correct your 'where' clause is not properly constructed. I think your intend was to construct a where clause using the 'in' clause, for example:
mukey in (100, 101, 205)
The 'in' clause allows you to specify a list of possible matches. Depending on the datatype of mukey you might have to surround the 'in' clause items in single quotes.
Here is a sample that worked for me:
protected override void OnClick()
{
var mukeys = new List<string>();
mukeys.Add("Test 1");
mukeys.Add("Test 3");
var inClause = string.Join("','", mukeys.Select(i => i.Replace("'", "''")));
_ = QueuedTask.Run(() =>
{
QueryDef queryDef = new QueryDef
{
Tables = "testlines",
WhereClause = $@"description in ('{inClause}')",
SubFields = "objectid,description"
};
System.Diagnostics.Trace.WriteLine($@"Where: {queryDef.WhereClause}");
var defaultGdb = new FileGeodatabaseConnectionPath(new Uri(CoreModule.CurrentProject.DefaultGeodatabasePath));
using (Geodatabase geodatabase = new Geodatabase(defaultGdb))
{
QueryTableDescription queryTableDescription = new QueryTableDescription(queryDef)
{
Name = "testlines",
PrimaryKeys = geodatabase.GetSQLSyntax().QualifyColumnName("testlines", "objectid")
};
using (var queryTable = geodatabase.OpenQueryTable(queryTableDescription))
{
using (var cursor = queryTable.Search ())
{
while (cursor.MoveNext())
{
using (var row = cursor.Current)
{
System.Diagnostics.Trace.WriteLine($@"Found: {row["objectid"]} {row["description"]}");
}
}
}
}
}
});
}
The diagnostic output of this snippet looks like this:
Where: description in ('Test 1','Test 3')
Found: 9 Test 1
Found: 11 Test 3