Problem with WhereClause/DefinitionExpression - SQL statement (VBA)

3625
6
Jump to solution
06-08-2012 05:03 PM
by Anonymous User
Not applicable
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]
0 Kudos
1 Solution

Accepted Solutions
by Anonymous User
Not applicable
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.

View solution in original post

0 Kudos
6 Replies
MauriceSchoenert
New Contributor
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
0 Kudos
by Anonymous User
Not applicable
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
0 Kudos
MauriceSchoenert
New Contributor
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 😞
0 Kudos
MauriceSchoenert
New Contributor
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!
0 Kudos
by Anonymous User
Not applicable
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.
0 Kudos
MauriceSchoenert
New Contributor
yes, very confusing

correct:

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

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