topic Re: WhereClause Street name , House number, Postal code in ArcObjects SDK Questions

WhereClause Street name , House number, Postal code

SigurdurGear — Mon, 16 Oct 2017 13:55:02 GMT

I have 3 Textbox and I need to search for Street name , House number, Postal code

House Number and Postal code can be empty

I am try to search using just part of street name or full name

Street name = Data Type Text

House number = Data Type Numeric

Postal code = Data Type Numeric

I can search for street name with House number but if I have no House number I get nothing

pQueryFilter.WhereClause = "Street = '" + Street.Text + "' AND Number = '" + number.Text + "'"

Re: WhereClause Street name , House number, Postal code

DuncanHornby — Mon, 16 Oct 2017 14:04:21 GMT

That makes sense street = 'XYZ' AND Number = empty string. You do not state what format your data source is and how "Nothing" is represented. In databases nothing is usually a NULL value. Edit your existing question to include this information and a sample of your data where is passes and fails because at the moment your question is lacking sufficient information for anyone to help you.

Re: WhereClause Street name , House number, Postal code

SigurdurGear — Sat, 11 Dec 2021 19:37:34 GMT

this is my code

Dim pMap As IMap
Dim pMxDoc As IMxDocument
Dim pFeatLayer As IFeatureLayer
Dim pFeatSel As IFeatureSelection
Dim pQueryFilter As IQueryFilter
pMxDoc = CType(My.ArcMap.Application.Document, IMxDocument)
pMap = pMxDoc.FocusMap
' Find Parcels Layer in TOC
Dim sLayerName As String
sLayerName = Layer_name
Dim i As Integer
For i = 0 To pMap.LayerCount - 1
If pMap.Layer(i).Name = sLayerName Then
pFeatLayer = pMap.Layer(i)
Exit For
End If
Next i
'Check to See if Parcels is in TOC
If pFeatLayer Is Nothing Then
MsgBox(Layer_name & " Error")
Exit Sub
End If


pQueryFilter = New QueryFilter
pQueryFilter.WhereClause = "Street = '" + Street.Text + "' AND Number = '" + number.Text + "'"

Dim pFeaCursor As IFeatureCursor = pFeatLayer.Search(pQueryFilter, False)
Dim pFeature As IFeature = pFeaCursor.NextFeature

Do Until pFeature Is Nothing
Dim index As Integer = pFeature.Fields.FindField("STREETNAME") 
Dim sName As String = pFeature.Value(index)
MsgBox(sName.ToString)
Dim pdata As keep_data = New keep_data()
pdata.Name = sName
pdata.ObjectID = pFeature.OID
If IsDBNull(pFeature.Value(pFeature.Fields.FindField("POSTALCODE"))) = False Then
pdata.postalcode = pFeature.Value(pFeature.Fields.FindField("POSTALCODE"))
Else
pdata.postalcode = ""
End If
If IsDBNull(pFeature.Value(pFeature.Fields.FindField("STREETNAME"))) = False Then
pdata.streetname = pFeature.Value(pFeature.Fields.FindField("STREETNAME"))
Else
pdata.streetname = ""
End If
If IsDBNull(pFeature.Value(pFeature.Fields.FindField("HOUSENUMBER"))) = False Then

Dim valueStr As String = pFeature.Value(pFeature.Fields.FindField("HOUSENUMBER"))
Dim valueIntConverted As Integer = CInt(valueStr)
pdata.housenumber = valueIntConverted
Else
pdata.housenumber = "0" 'Nothing
End If
pFeature = pFeaCursor.NextFeature

Loop‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: WhereClause Street name , House number, Postal code

DuncanHornby — Mon, 16 Oct 2017 15:02:28 GMT

If you debug the code and look at the whereclause you had built does it look like:

"Street = 'xyz' AND Number = ''"

As you don't state what your dataset format is... do you have the correct field delimiters? Would help everyone if you provided a screen shot of a sample of data?

Re: WhereClause Street name , House number, Postal code

KenBuja — Sat, 11 Dec 2021 19:37:37 GMT

You should construct the whereclause in response to whether a text box has been selected

Dim clause as String =  "Street = '" + Street.Text + "'"
If number.Text <> "" then
  clause += " AND Number = '" + number.Text + "'"
End If
QueryFilter.WhereClause = clause
‍‍‍‍‍‍

Re: WhereClause Street name , House number, Postal code

TedKowal — Mon, 16 Oct 2017 15:24:43 GMT

Just a side comment, the query (WhereClause) you are setting is an exact match. Typically, in experience, in dealing with text types of searches, the user either cannot spell, don't know the whole name or only has partial information. An exact matching whereclause will not handle this type of situation. An old reference I used, much of which is still valid which explains how to use the like operator and wild cards within your WhereClause.

"STREET_NAME" LIKE '%Blue Bird%'

IQueryFilter.WhereClause Property

Just a side note!

Re: WhereClause Street name , House number, Postal code

SigurdurGear — Mon, 16 Oct 2017 15:26:48 GMT

Duncan Hornby and Ken Buja thanks for your Replay

I managed to fix this This work for me

I can Search for Street name With or without House number, and Postal code

I only use House number and Postal code to narrow the search

English is not my mother Language sorry if I can't Explain it better or wrong spell

pQueryFilter.WhereClause = "STREETNAME LIKE '" & Street.Text & "%' and HOUSENUMBER LIKE '" & number.Text & "%' and POSTALCODE LIKE '" & Pn.Text & "%' ORDER BY STREETNAME,HOUSENUMBER,POSTALCODE Asc"‍‍