topic Re: SELECT WHERE statement in ArcPad Questions

SELECT WHERE statement

JayKappy — Mon, 16 Aug 2010 16:40:18 GMT

I can get this query to work...but its quering a Double Field (ANGLE):

' Build Query Statement
Dim varQuery
varQuery = "SELECT OBJECTID FROM SIGNS_STANDALONETABLE WHERE ANGLE = 270"

' Set up recordset query
Dim  pRS6
Set pRS6 = pDS6.Execute(varQuery)

' Move first
pRS6.MoveFirst
        
Do While Not pRS6.EOF 
 Dim MaxID, MaxID2   
 MaxID = pRS6.Fields("OBJECTID").Value
 MaxID2 = MaxID2 & ", " & MaxID
 pRs6.MoveNext 
Loop

msgbox "Max ID: " & MaxID2

But I need to query a string field and I cant get that to work....this is what I was trying:
ID is a text field in the table

varQuery = "SELECT OBJECTID FROM SIGNS_STANDALONETABLE WHERE ID = "01NW-001" "

I then wrote it to a variable and tried this:

Dim varUniqueID 
varUniqueID = "01NW-001"
varQuery = "SELECT OBJECTID FROM SIGNS_STANDALONETABLE WHERE ID = " & varUniqueID & " "

neither works....any thoughts?

THanks in advance....

Re: SELECT WHERE statement

PaulSteinmeyer — Mon, 16 Aug 2010 16:53:50 GMT

I'm not an expert in queries, but I think the literal quotation mark might be messing it up.

Instead of

varQuery = "SELECT OBJECTID FROM SIGNS_STANDALONETABLE WHERE ID = "01NW-001" "

try

varQuery = "SELECT OBJECTID FROM SIGNS_STANDALONETABLE WHERE ID =  " & chr(34) & "01NW-001" & chr(34) "

-Paul

Re: SELECT WHERE statement

JayKappy — Tue, 17 Aug 2010 12:50:43 GMT

THANK YOU FOR YOUR COMMENTS....

But Yea no go....I get another error

Unterminated String Constant
Souce Text Unaviable
varQuery = "SELECT etc etc

I cant figure out how I can get the query to work for numeric fields and not text fields....
ugggg

Re: SELECT WHERE statement

PaulSteinmeyer — Tue, 17 Aug 2010 13:37:59 GMT

Aaack, I can't type (or edit). The last " is the problem, line should read

varQuery = "SELECT OBJECTID FROM SIGNS_STANDALONETABLE WHERE ID =  " & chr(34) & "01NW-001" & chr(34)

Again, I know little about queries but I do know that putting literal quotes in a quoted string can be a pain. This gets around that by inserting the ASCII value of the quotation mark for when you want the literal character to actually work in the string.

-Paul

Re: SELECT WHERE statement

JayKappy — Tue, 17 Aug 2010 14:04:26 GMT

Thanks again....going nuts here...went after two different fields to make sure it wasnt the field...but errors on both ....

Error:
The Column Name is not valid
Source Text Unavailable

varQuery = "SELECT OBJECTID FROM SIGNS_STANDALONETABLE WHERE ID = " & chr(34) & "01NW-001" & chr(34)

varQuery = "SELECT OBJECTID FROM SIGNS_STANDALONETABLE WHERE DESCRIPTIO = " & chr(34) & "STOP" & chr(34)

I cant figure this out....whats going on here...

This works great for a numeric value....I agree the Literal strigns in qoutes etc can be a real pain...

varQuery = "SELECT OBJECTID FROM SIGNS_STANDALONETABLE WHERE ANGLE = 270"

THanks Again...hopefully we can figure this out....

Anyone?

Re: SELECT WHERE statement

RuiGe — Tue, 17 Aug 2010 17:42:44 GMT

instead of using ", use ' for strings

varQuery = "SELECT OBJECTID FROM SIGNS_STANDALONETABLE WHERE ID = '01NW-001' "

Re: SELECT WHERE statement

JayKappy — Tue, 17 Aug 2010 20:05:22 GMT

I try this:

varQuery = "SELECT OBJECTID FROM SIGNS_STANDALONETABLE WHERE ID = '01NW-001' "

and when I do a message box on varQuery I get

SELECT OBJECTID FROM SIGNS_STANDALONETABLE WHERE ID = '01NW-001'

and nothing happens, ie the query is not returning anything...
Thoughts?

Thanks everyone for yoru help...its very appreciated...this is so simple dont know why its so tough

Re: SELECT WHERE statement

JayKappy — Tue, 17 Aug 2010 20:23:44 GMT

Whats happening with this is that the query is completly bypassing my Do While loop when I try for a string value:
varQuery = "SELECT OBJECTID FROM SIGNS_STANDALONETABLE WHERE Suuport_ID1 = '01NW-001'"

But when I go after a numeric value it works fine:
varQuery = "SELECT * FROM SIGNS_STANDALONETABLE WHERE ANGLE = 270"

With the string query above I never get to Message Box 3

msgbox "1"
' Set up recordset query
Dim pRS6
Set pRS6 = pDS6.Execute(varQuery)
msgbox "2"

' Move first
pRS6.MoveFirst
' Loop recordset and get number of records
Do While Not pRS6.EOF
   Dim Description, ObjectID, MaxID2
   Description = pRS6.Fields("DESCRIPTIO").Value
   ObjectID = pRS6.Fields("OBJECTID").Value
msgbox "3"
   msgbox "OBJECTID: " & ObjectID

      objEFPageOneControls2("Combo1").AddItem Description, ObjectID
   pRs6.MoveNext

Loop

msgbox "4"

Re: SELECT WHERE statement

RuiGe — Tue, 17 Aug 2010 21:16:48 GMT

I try this:

varQuery = "SELECT OBJECTID FROM SIGNS_STANDALONETABLE WHERE ID = '01NW-001' "

and when I do a message box on varQuery I get

SELECT OBJECTID FROM SIGNS_STANDALONETABLE WHERE ID = '01NW-001'

and nothing happens, ie the query is not returning anything...
Thoughts?

Thanks everyone for yoru help...its very appreciated...this is so simple dont know why its so tough

It seems no record is retured based on your query definition.
I am not sure what is exactly in your data. But if you open ArcPad Studio, then open the SIGNS_STANDALONETABLE table.
Delete the "SELECT * FROM [SIGNS_STANDALONETABLE]" in the data view window,
copy
SELECT OBJECTID FROM SIGNS_STANDALONETABLE WHERE ID = '01NW-001'
to the window and excute the command by click on "!" on toolbar.

If the SQL sentence is wrong, error warnings will appear;
if nothing is wrong, but no record returns, you need to make sure the Field is a string field and field value is exactly what you want to query for.

Re: SELECT WHERE statement

JayKappy — Wed, 18 Aug 2010 12:17:21 GMT

It seems no record is retured based on your query definition.
I am not sure what is exactly in your data. But if you open ArcPad Studio, then open the SIGNS_STANDALONETABLE table.
Delete the "SELECT * FROM [SIGNS_STANDALONETABLE]" in the data view window,
copy
SELECT OBJECTID FROM SIGNS_STANDALONETABLE WHERE ID = '01NW-001'
to the window and excute the command by click on "!" on toolbar.

If the SQL sentence is wrong, error warnings will appear;
if nothing is wrong, but no record returns, you need to make sure the Field is a string field and field value is exactly what you want to query for.

OK here is what I did....I went back into my data and did a TRIM([field]) on a couple text fields to make sure that there were no spaces..
I then re-exported to axf/apm using the ArcPAD data manager
went into ArcStudio and opened the table.
I replaced the query and just used one of the values I saw in the window...and it worked
I then tried my original value and it would not work...
I looked a bit further and it seems that only 164 records were exported to the axf/apm file (see attachement)

Is there a record limitation on standalone tables being exported to axf/apm?

This is a related table to the main feature class and I need all the values in it....THERE was data that was not exported to the axf/apm file

How do I get all the records to export?

Thanks

Re: SELECT WHERE statement

JayKappy — Wed, 18 Aug 2010 12:30:45 GMT

OH man do i feel stupid....OK the query seems to be workign now although back in ArcMap I am not zoomed to full extent therefore not all of my data is being exported...I think this is that last issue...

Re: SELECT WHERE statement

JayKappy — Wed, 18 Aug 2010 12:49:56 GMT

Last question...

How do I get this to work with a varaible...I tried this:

varQuery = "SELECT * FROM SIGNS_STANDALONETABLE WHERE Support_ID1 = " & varUniqueID & " "

and This

varQuery = "SELECT * FROM SIGNS_STANDALONETABLE WHERE Support_ID1 = ' & varUniqueID & ' "

and This

varQuery = "SELECT * FROM SIGNS_STANDALONETABLE WHERE Support_ID1 = ' " & varUniqueID & " ' "

varQuery = "SELECT * FROM SIGNS_STANDALONETABLE WHERE Support_ID1 = " & varUniqueID

Re: SELECT WHERE statement

JayKappy — Wed, 18 Aug 2010 14:12:04 GMT

I swear ArcpAD is really flaky....

varQuery = "SELECT * FROM SIGNS_STANDALONETABLE WHERE SUUPORT_ID1 = '"& varSupportID &"' "

This now works...and was one of my previous attempts that didnt.....

THANK YOU ALL FOR YOUR HELP....VERY APPRECIATED... maybe its the elimination of spaces??? '"& varSupportID &"' "

IT WAS THE SPACES THAT WAS MESSING THINGS UP....

Not working: ' " & variable & " '

Working: '"& variable &"'

Go figure