Years ago, I used to do a lot of programming with MS Access. Somewhere along the way I saw an interesting code sample of how to write a custom query. I have used the methodology described ever since. There may be better solutions available, and I am open to them, but I have used variations of the following code in all types of web pages and programs.
First Step:
- Create Search Form
- Code The Search ButtonPrivate Sub cmdSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSearch.Click
‘On Error GoTo ActError‘Query which searchs by user input
Dim strwhere As String‘Parcel Query Defaults
strOwner = gtxtOwner
strHouseNum = gtxtHouseNum
strStreetName = gtxtStreetName
strMap = gtxtMap
strGroup = gtxtGroup
strParcel = gttxtParcel
strParcelFile = gtxtParcelFile
strParcelID = gtxtParcelID
With cmdSearchstrwhere = “‘”
If Len(txtOwnerSearch.Text) > 0 Then
strwhere = ” and ” & strOwner & ” like ‘” & txtOwnerSearch.Text & “%’”
End IfIf Len(txtCMAPSearch.Text) > 0 Then
strwhere = strwhere & ” and ” & strMap & ” = ‘” & txtCMAPSearch.Text & “‘”
End If
If Len(txtGPSearch.Text) > 0 Then
strwhere = strwhere & ” and ” & strGroup & ” = ‘” & txtGPSearch.Text & “‘”
End If
If Len(txtParcelSearch.Text) > 0 Then
strwhere = strwhere & ” and ” & strParcel & ” = ” & txtParcelSearch.Text
End If
If Len(txtHouseNumSearch.Text) > 0 Then
strwhere = strwhere & ” and ” & strHouseNum & ” = ” & txtHouseNumSearch.Text
End If
If Len(txtStreetNameSearch.Text) > 0 Then
strwhere = strwhere & ” and ” & strStreetName & ” like ‘” & txtStreetNameSearch.Text & “%’”
End IfEnd With
strwhere = UCase$(Mid$(strwhere, 6))
txtSearch.Text = strwhere
Call sSearch(strwhere)
Me.ParcelsTableAdapter.Fill(Me.CurvDBDataSet.parcels)
End SubExplanation:
I will attempt to explain the code. It we look at:If Len(txtOwnerSearch.Text) > 0 Then
strwhere = ” and ” & strOwner & ” like ‘” & txtOwnerSearch.Text & “%’”
End IfIn the If…Then statement we test to see if txtOwnerSearch.text has a value. I have been told that checking to see if the length of txtOwnerSearch.text is greater than 0 is slightly quicker than checking to see if txtOwnerSearch.text has a value. If the length of txtOwnerSearch.text is greater than 0 and therefore has a value we begin to build the query statement.
You will notice that all query statements begin with an AND regardless if it is the first statement. This is by design and will be handled.
strwhere = ” and ” & strOwner & ” like ‘” & txtOwnerSearch.Text & “%’”
strwhere= Combined Query String
strOwner=Column to be queried
” like ‘” & txtOwnerSearch.Text & “%’”=Search operator(Like), value(txtOwnerSearch.Text) and wildcard(%)Results:
“and NAME_1=’Wilson M’”Handling the initial AND:
strwhere = UCase$(Mid$(strwhere, 6))
UCase$ = Capitalizes the entire string
Mid$= Cuts off the first 6 characters, ” and “Results:
strWhere=”NAME_1=’Wilson M’”Call sSearch(strwhere) – Call the search Subroutine
- Building the Search Sub:
Sub sSearch(ByVal pstrWhere As String)
‘Set mouse cursor as this can take some time with large datasets
On Error GoTo ErrorHandler
‘app.MousePointer = vbHourglass
Dim intCount As Integer
‘Get layer to query
Dim pARMap As ARMap
pARMap = frmMain.conARMap.ARPageLayout.FocusARMapCall sSetIndexes(strParcelFile)
pARLayer = pARMap.ARLayer(gintGroupIndex).ChildARLayer(gintLayerIndex) ‘m_LayersIndex(cboLayers.ListIndex)‘Build the ARSearchDef
Dim pARSearchDef As ArcReaderSearchDef
pARSearchDef = New ArcReaderSearchDef‘Build WhereClause that meets search criteria
Dim sWhereClause As String
‘Remove quotes from WhereClause if search is numeric
sWhereClause = pstrWhere
pARSearchDef.WhereClause = sWhereClause
m_pFeatureCursor = pARLayer.SearchARFeatures(pARSearchDef)
intCount = fCreateParcelDS(m_pFeatureCursor)If intCount > 0 Then
lblMeets.Text = “Features MEETING the search criteria: ” & intCount
Else
lblMeets.Text = “Features MEETING the search criteria: 0″
End If
Exit SubErrorHandler:
Debug.Print(Err.Number)
Select Case Err.Number
Case -2147219885
MsgBox(“Check Field Names under Program Options”)
‘Me.MousePointer = vbDefault
Exit Sub
End SelectEnd Sub
Explanation:
This subroutine is used to process the query string. The code here is slanted to work with ArcReader Datasets and calls several custom functions which can be ignored. The relevant section where the query is applied is:Call sSetIndexes(strParcelFile) – Subroutine to set variables for search layer indexes
pARLayer = pARMap.ARLayer(gintGroupIndex).ChildARLayer(gintLayerIndex) ‘m_LayersIndex(cboLayers.ListIndex) – Set the layer to be searched
‘Build the ARSearchDef
Dim pARSearchDef As ArcReaderSearchDef
pARSearchDef = New ArcReaderSearchDef‘Build WhereClause that meets search criteria
Dim sWhereClause As String
‘Remove quotes from WhereClause if search is numeric
sWhereClause = pstrWhere
pARSearchDef.WhereClause = sWhereClause - Apply the search string
m_pFeatureCursor = pARLayer.SearchARFeatures(pARSearchDef)- Build the Querried features
intCount = fCreateParcelDS(m_pFeatureCursor) - Custom function to process the records and provide record count
Summary:
The methodology described above is extremely flexable. I have used it in VB, VBA, .NET, PHP, and ASP projects. I hope it helps you.
Posted by wilsongis 
Posted by wilsongis 
Posted by wilsongis 