INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Accessing Data in a Cursor

Accessing Data in a Cursor

(OP)
Newbie question: How do I access the data in VFP 9.0 from the following SQL?

SELECT SUM(reinsprem) AS CurrVal FROM reinstrn WHERE date >= DATE(2017,08,01) AND Policy Like PolNum AND code NOT like 'MBR1' INTO CURSOR A1

All I need to do is get the returned value of CurrVal in the cursor A1

Thanks so much for any help

RE: Accessing Data in a Cursor

lnCurvall = A1.CurrVal 

If this value is all you need, this approach is much easier:

SELECT SUM(reinsprem) FROM reinstrn WHERE date >= DATE(2017,08,01) AND Policy Like PolNum AND code NOT like 'MBR1' INTO Array laCurVal
lnCurvall = laCurVal && You don't need this line, only used here for demonstration. Just call the array directly
*Since the array will only have one element, you don't need to specify any index. 

RE: Accessing Data in a Cursor

(OP)
Okay so I've updated my SQL to look as follows:
SELECT SUM(reinsprem) FROM reinstrn WHERE date >= DATE(2017,08,01) AND Policy Like PolNum AND code NOT like 'MBR1' INTO Array laCurVal
and have tested with this line:
MESSAGEBOX(laCurVal + CHR(10) + NewVal)
The program fails there on the test line, I get an operator/operand type mismatch

RE: Accessing Data in a Cursor

Because, the value is numeric! Try
MESSAGEBOX(Transform(laCurVal) + CHR(10) + NewVal) 

RE: Accessing Data in a Cursor

Hi Shawn,

The problem here is that your laCurVal is numeric, whereas CHR(10) is a character string. NewVal might also be numeric - that's not clear from your code. You can't add a numeric value to a character string - hence the error.

The solution is to convert laCurVal (and possibly NewVal) to character fields. You can do that by wrapping them in a TRANSFORM().

But keep in mind that this only affects your message box, which is only there for testing purposes. The error doesn't mean that there is anything wrong with your SQL.

You could also simply do a separate message box for laCurVal and NewVal respectively, without the CHR(10). That would avoid the error message you are seeing.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: Accessing Data in a Cursor

(OP)
Great that worked!. Now when I use that number in an SQL statement to update another table will I have to use the transform function

RE: Accessing Data in a Cursor

Transform converts more or less any data type into text. Whether that's necessary or not in order to update, depends on the data type in the target table. If the target is numeric then no conversion is necessary.

RE: Accessing Data in a Cursor

(OP)
Right that makes sense. Thanks guys for the help.

RE: Accessing Data in a Cursor

Just notice a query INTO ARRAY only is simpler in case of having such a one value result. It could also be used for a whole result set with multiple rows and columns, but addressing a cursor normally is simpler, especially since a cursor actually is a local albeit temporary DBF, you have exclusive access to.

A cursor also is kept in memory like an array. A cursor crsResult has a filename DBF("crsResult"), but that is just virtual as long as the cursor memory doesn't outgrow allocated memory.

And the best advantage is you can do further queries on that cursor, which isn't possible with an array. You can also update the resulting cursor when you query INTO CURSOR crsResult READWRITE. Otherwise, it is a read-only result with one exception, which would be leading too far off for now. It won't happen with a calculated result value anyway.

So INTO CURSOR is the usual target for a query. An array has the advantage of random access of row/col, of course, but VFP won't create associated arrays as PHP does, which you could access with field names instead of array element index, but a cursor simply has the access like a record object with cursorname.fieldname - what could be simpler? The fieldname only? Yes, that is an option, too.

So finally, the simplest answer to your question is CurrVal.

Bye, Olaf.

RE: Accessing Data in a Cursor

Just to add to what Olaf posted .... Arrays are especially useful with queries that only return a single value. For example:

SELECT COUNT(*) FROM MyTable INTO ARRAY laResult

This will result in an array containing just one cell (one row, one column). So you can retrieve the result by referencing laResult(1), for example:

MESSAGEBOX(laResult(1))


I generally find that easier than using INTO CURSOR in cases like that.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: Accessing Data in a Cursor

Mike,

as I wrote earlier, for single row single column arrays, like the one returned from this Select statement, you don't need to specify the index. MESSAGEBOX(laResult() is enough.

RE: Accessing Data in a Cursor

You would probably want another right hand bracket though

happy shades

Regards

Griff
Keep Smileing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

RE: Accessing Data in a Cursor

(OP)
After reading all of the responses, I have decided to go with the Cursor Method that Olaf posted because it makes the most sense. There is only 1 value that I am pulling and there wouldn't be a need to an array. Thanks again to all who helped.

RE: Accessing Data in a Cursor

And that was also my very first suggestion.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close