topic Re: Problem with WhereClause/DefinitionExpression - SQL statement (VBA) in ArcObjects SDK Questions

Problem with WhereClause/DefinitionExpression - SQL statement (VBA)

Anonymous User — Sun, 12 Dec 2021 16:52:52 GMT

Original User: koboldmaquis@yahoo.de
Original Date: 2012-06-08T18:03:01-0600

Hi,

my WhereClause/DefinitionExpression - SQL statement isn't working. I'm getting the values for the SQL statement out of the ComboBox3 (UserForm) via the variable StrPLZ = ComboBox3.Value. I'm trying to use this variable in the SQL statement, but i get in ArcMap the following error:

ArcMap Drawing Error
One or more layers failed to draw:
POI: An invalid SQL statement was used.

My guess is, that the programm cant query/compare the string variable (StrPLZ) with the long-datatype field of the attribute table (PLZ) (see highlighted code below, Screenshot of Table).

Can anybody please help me

'Definition Query: Suche von POI Ã¼ber die Postleitzahl (Name des Buttons im GUI: POI suchen)
Private Sub CommandButton3_Click()

'Connect Schnittstellen
Dim pMxDocument As IMxDocument
Set pMxDocument = ThisDocument
Dim pMap As IMap
Set pMap = pMxDocument.FocusMap
Dim pLayer0 As ILayer
Set pLayer0 = pMap.Layer(0)

'Schnittstelle wechseln (QueryInterface),
'um fÃ¼r die Abfrage notwendige Eigenschaften und Methoden zu erhalten
Dim pDefQuery As IFeatureLayerDefinition
Set pDefQuery = pLayer0

'Anwendereingaben aus der ComboBox3, ComboBox4 und ComboBox5 in Variable schreiben
'benÃ¶tigt, damit das Programm 'weiÃ?', welche Features (POI) angezeigt werden sollen
Dim StrPLZ As String
Dim StrOberKat As String
Dim StrUnKat As String
StrPLZ = ComboBox3.Value
StrOberKat = ComboBox4.Value
StrUntKat = ComboBox5.Value

'Variable fÃ¼r Message Boxen deklarieren (Name des PLZ-Bereichs)
Dim StrText As String
StrText = TextBox1.Text

'gegebenenfalls vorhandene Auswahl entfernen
pDefQuery.DefinitionExpression = ""

'Definition Query durchfÃ¼hren
If StrPLZ = "Bitte Postleitzahl wÃ¤hlen" Then
MsgBox "Sie haben keine Auswahl getroffen. Alle POI werden angezeigt", vbOKOnly + vbExclamation, "Auswahl treffen"
pDefQuery.DefinitionExpression = ""
ElseIf StrUntKat = "Optional: Unterkategorie wÃ¤hlen" Then
MsgBox "Alle POI in der Oberkategorie '" & StrOberKat & "' im Postleitzahlbereich '" & StrPLZ & "' " & StrText & "werden angezeigt", vbOKOnly, "Hinweis"
pDefQuery.DefinitionExpression = "PLZ = '" & StrPLZ & "'"
End If


'Refresh
pMxDocument.ActiveView.Refresh
pMxDocument.UpdateContents

End Sub

ComboBox3.Clear
ComboBox3.Value = "Bitte Postleitzahl wÃ¤hlen"
ComboBox3.AddItem "44795"   'Bochum-Weitmar
ComboBox3.AddItem "44797"   'Bochum-Weitmar
ComboBox3.AddItem "44799"   'Bochum
ComboBox3.AddItem "44801"   'Bochum-Querenburg
ComboBox3.AddItem "44803"   'Bochum-Steinkuhl
ComboBox3.AddItem "45527"   'Hattingen
ComboBox3.AddItem "58454"   'Witten
ComboBox3.AddItem "58455"   'Witten
ComboBox3.AddItem "58456"   'Witten-Herbede
ComboBox3.AddItem "0"       'Keine PLZ Zuordnung mÃ¶glich

ComboBox4.Clear
ComboBox4.Value = "Optional: Oberkategorie wÃ¤hlen"
ComboBox4.AddItem "Ã?ffentliche Einrichtungen"
ComboBox4.AddItem "Wohnheime"
ComboBox4.AddItem "Nahversorgung"
ComboBox4.AddItem "Dienstleistungen"
ComboBox4.AddItem "Gesundheitswesen"
ComboBox4.AddItem "Freizeiteinrichtungen/Sport/Wellness"
ComboBox4.AddItem "Gastronomie"
ComboBox4.AddItem "Kultur/Unterhaltung"
ComboBox4.AddItem "Sonstige"

[ATTACH=CONFIG]15079[/ATTACH]

[ATTACH=CONFIG]15080[/ATTACH]

Re: Problem with WhereClause/DefinitionExpression - SQL statement (VBA)

MauriceSchoenert — Sun, 10 Jun 2012 11:45:08 GMT

ok i found a solution...

just add a new field in the attribute table of your data, set the datatype to TEXT, fill the column with the numbers from the LONG field.. u get now string numbers which u can compare in the vba sql statement

Re: Problem with WhereClause/DefinitionExpression - SQL statement (VBA)

Anonymous User — Mon, 11 Jun 2012 12:23:21 GMT

Original User: Neil

You don't need a new field, you just need to write the definition query using the proper syntax:

pDefQuery.DefinitionExpression = "PLZ = '" & StrPLZ & "'"

If PLZ is a string field then this query is correct. If PLZ is a numeric field then remove the single quotes around the value:

pDefQuery.DefinitionExpression = "PLZ = " & StrPLZ

Re: Problem with WhereClause/DefinitionExpression - SQL statement (VBA)

MauriceSchoenert — Mon, 11 Jun 2012 16:04:16 GMT

You don't need a new field, you just need to write the definition query using the proper syntax:

pDefQuery.DefinitionExpression = "PLZ = '" & StrPLZ & "'"

If PLZ is a string field then this query is correct. If PLZ is a numeric field then remove the single quotes around the value:

pDefQuery.DefinitionExpression = "PLZ = " & StrPLZ

Mmmh, thx, I'll try it. It seems so easy, if u know it.. i was searching the inet and ArcGIS help pages for hours

Is that SQL Syntax (in VBA) basic knowledge or why is there no reference in ArcGIS Desktop Help Center 😞

Re: Problem with WhereClause/DefinitionExpression - SQL statement (VBA)

MauriceSchoenert — Mon, 11 Jun 2012 17:12:31 GMT

Ok, the statement

pDefQuery.DefinitionExpression = "PLZ = " & StrPLZ

works,

but what is the syntax for:

pDefQuery.DefinitionExpression = "PLZ = '" & StrPLZ & "' AND Kategorie = '" & StrOberKat & "'"

pDefQuery.DefinitionExpression = "PLZ = '" & StrPLZ & "' AND Kategorie = '" & StrOberKat & "' AND Unterkategorie = '" & StrUntKat & "'"

?
I tried something like this: pDefQuery.DefinitionExpression = "PLZ = " & StrPLZ AND Kategorie = '" & StrOberKat & "'" which isnt working

PLZ is numeric, StrPLZ is string, Kategorie is string, StrOberKat is string, Unterkategorie is string, StrUntKat is string

I'm sorry, I'm not familiar with sql in vba, thank u for ur support!

Re: Problem with WhereClause/DefinitionExpression - SQL statement (VBA)

Anonymous User — Mon, 11 Jun 2012 17:33:24 GMT

Original User: Neil

If the field type is text then the value must be enclosed in single quotes. If the field is numeric then the value is not enclosed in single quotes.

Re: Problem with WhereClause/DefinitionExpression - SQL statement (VBA)

MauriceSchoenert — Mon, 11 Jun 2012 18:31:23 GMT

yes, very confusing

correct:

pDefQuery.DefinitionExpression = "Kategorie = '" & StrOberKat & "' AND PLZ = " & StrPLZ & ""

pDefQuery.DefinitionExpression = "PLZ = " & StrPLZ & " AND Kategorie = '" & StrOberKat & "' AND Unterkategorie = '" & StrUntKat & "'"