Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2004
    Posts
    8

    Unanswered: Scalar functions in SELECT (concat, left, if, etc.)

    I am writing Visual Basic code to access a Pervasive 2000 SQL database and want to use the CONCAT, IF, and LEFT functions in SELECT statements. So far, though, I seem to get an empty recordset when I use any of them. I don't get error messages when VB executes the project, though.
    If I go into the Pervasive Control Center and execute the queries from there, they work, but not in my VB code. If I go into SQL Builder in VB, it will say the sql code could be 'verified against the data source', but won't run--'data provider or other service returned an E_FAIL status'.

    EXAMPLE:

    strcnx = "Provider = PervasiveOLEDB;Data Source=StarTracer;Password=<pwd>;User ID=<uid>;Persist Security Info=True"

    <this statement works--rs.RecordCount shows records returned>
    rs.Open "select lastname AS LName, firstname as FName from people", strcnx, adOpenDynamic, adLockOptimistic, adCmdText

    <but this one doesn't--rs.RecordCount is zero, same if I use CONCAT or IF statements>
    rs.Open "select left(lastname, 5) AS LName, firstname as FName from people", strcnx, adOpenDynamic, adLockOptimistic, adCmdText

    MsgBox rs.RecordCount
    MsgBox Err.Number & ", " & Err.Description & ", " & Err.Source
    Last edited by zharrison; 09-16-04 at 12:51.

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    Do you actually get records but no record count? What are rs.EOF and rs.BOF set to? Can you iterate through the record set and get data?
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  3. #3
    Join Date
    Sep 2004
    Posts
    8
    Thanks for your reply.
    bof and eof are both true, and rs.movenext gives an error

  4. #4
    Join Date
    Sep 2004
    Posts
    8
    Another clue might be that I can use subtraction:

    SELECT (ComputerCharge - ActualCharge) AS RcptDiff FROM TxCash

  5. #5
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    If BOF and EOF are both true, then there's no records in the record set. What happens if you change from adOpenDyanmic to adOpenStatic?
    What happens if you change from the PervasiveOLEDB provider to ODBC (Change the Connect String to "DSN=<dsnname>;Pwd=jfltlc;UID=Master".
    Also, what version of 2000 are you using (easiest way is to view the version of W3ODBCCI.DLL or W3ODBCEI.DLL)?
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  6. #6
    Join Date
    Sep 2004
    Posts
    8
    Changing to ODBC from PervasiveOLEDB did the trick, Concat and Left work, anyway.
    My larger problem is that I'd still like to manipulate the data before passing it to the report object--I'm using a VB DataReport object and binding it to the recordset in question. Once you pass it to the DataReport, you don't have access to the records as you move through the recordset. If I want, for example, to display "Checkout" for a TxType of '1' and "Checkin" for a TxType of '2' and so on (for about 15 transaction types).
    Can I create some kind of temporary table for a set of several hundred to a thousand records and bind the DataReport object to that?

    Quote Originally Posted by mirtheil
    If BOF and EOF are both true, then there's no records in the record set. What happens if you change from adOpenDyanmic to adOpenStatic?
    What happens if you change from the PervasiveOLEDB provider to ODBC (Change the Connect String to "DSN=<dsnname>;Pwd=<pwd>;UID=<uid>".
    Also, what version of 2000 are you using (easiest way is to view the version of W3ODBCCI.DLL or W3ODBCEI.DLL)?

  7. #7
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    There's not an easy way to build temp tables and insert records. You would actually have to create the table, read the records from one table, insert them into the temp table then use the new table in the DataReport. You might try just building the query to return the values. For example, I have the following query in one of my apps:
    select i.id, i.title, m.media, i.price, i.notes from tblItem i, tblMedia m where i.media = m.id
    This displays media data and has things like "DVD" for the media rather than 1.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •