Alpha Video Training
Results 1 to 10 of 10

Thread: ADO vs Alpha DAO

  1. #1
    Member
    Real Name
    Jon Vitello
    Join Date
    Oct 2007
    Posts
    7

    Default ADO vs Alpha DAO

    I am stumped -

    I have been sucesfull building a web app using ADO that displays SQL data from a Pervasive database. To date I have not been able to update the table. I receive an error message that the table was not updated.

    I have been unsuccesful using Alpha DAO. I connect to the same sql server. When I attempt to run a live preview I receive the following error mesage displayed across the top of the header

    [Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface]Invalid column name: 'BKCM_ACCT_REM 1'. SQL State is: 42S22 [Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface]Error in expression: BKCM_ACCT_REM 1 SQL State is: HY000

    I use the same DSN for each.

    1. Should I just use ADO and try to correct the update issue?
    2. What recommendations are there for getting Alpha DAO to run?

    Thanks in advance

  2. #2
    "Certified" Alphaholic Melvin Davidson's Avatar
    Real Name
    Melvin Davidson
    Join Date
    Apr 2000
    Location
    Parker, CO
    Posts
    1,197

    Default Re: ADO vs Alpha DAO

    Quote Originally Posted by jvitello View Post
    Invalid column name: 'BKCM_ACCT_REM 1'. SQL State is: 42S22 [Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface]Error in expression: BKCM_ACCT_REM 1 SQL State is: HY000
    I doubt you will be successful with either ADO. The problem is the space in the column name. Generic SQL does not like that.

    BKCM_ACCT_REM 1
    .........................^

  3. #3
    Member
    Real Name
    Jon Vitello
    Join Date
    Oct 2007
    Posts
    7

    Default Re: ADO vs Alpha DAO

    Thanks for the reply - That is interesting, I have been using this same driver and DSN with Paradox, (BDE) for years and not had a problem.

    Is it the "space" creating the update error when I hit submit from the ADO form?

    Thanks again

    Jon

  4. #4
    "Certified" Alphaholic Melvin Davidson's Avatar
    Real Name
    Melvin Davidson
    Join Date
    Apr 2000
    Location
    Parker, CO
    Posts
    1,197

    Default Re: ADO vs Alpha DAO

    Funny you should ask, as I thought that is what I said. I will be more specific.
    You cannot use a space in an Identifier/Column Name. So YES, the space is the problem. It does not follow the SQL standard.

    Here are a couple of reference pages:

    http://www.dbcenter.cise.ufl.edu/tri...fm.html#144132

    http://www.postgresql.org/docs/8.0/s...AX-IDENTIFIERS
    (click on Identifiers and keywords in above)

    The problem with Paradox is that it allows deviation from the SQL standard with regard to identifiers.

    You might, however, try a quoted column name:
    EG:
    Instead of
    SELECT BKCM_ACCT_REM 1 FROM some_table;

    try
    SELECT "BKCM_ACCT_REM 1" FROM some_table;


    No guarantee it will work though.

  5. #5
    "Certified" Alphaholic Melvin Davidson's Avatar
    Real Name
    Melvin Davidson
    Join Date
    Apr 2000
    Location
    Parker, CO
    Posts
    1,197

    Default Re: ADO vs Alpha DAO

    I had another thought regards your problem.

    Is the column name really "BKCM_ACCT_REM 1"

    Or did you really mean to do
    SELECT BKCM_ACCT_REM, 1 FROM some_table;

    IOW, did you leave out the comma separator?

  6. #6
    Member
    Real Name
    Jon Vitello
    Join Date
    Oct 2007
    Posts
    7

    Default Re: ADO vs Alpha DAO

    I need to caution that I am not a programer - Only a hack. I developed the grid using the wsiwyg editor. I had a look at the actual DB using a pervasive editor. The field is: BKCM.ACCT.REM

    The field constructed by the editor is:
    tmpl.ADO.SQL = "SELECT BKCM_ACCT_CODE, BKCM_ACCT_OLDCD, BKCM_ACCT_ALPHA, BKCM_ACCT_NAME, BKCM_ACCT_ADD1, BKCM_ACCT_ADD2, BKCM_ACCT_ADD3, BKCM_ACCT_CITY, BKCM_ACCT_STATE, BKCM_ACCT_ZIP, BKCM_ACCT_CNTRY, BKCM_ACCT_CONT1, BKCM_ACCT_TITLE, BKCM_ACCT_PHONE, BKCM_ACCT_FAX, BKCM_ACCT_REP, BKCM_ACCT_DLOAD, BKCM_ACCT_SICCD, BKCM_ACCT_CUST, BKCM_ACCT_LEAD, BKCM_ACCT_START, BKCM_ACCT_TERR, \"BKCM_ACCT_REM[ 1]\", \"BKCM_ACCT_REM[ 2]\", \"BKCM_ACCT_FONE[ 1]\", \"BKCM_ACCT_FONE[ 2]\", \"BKCM_ACCT_FONE[ 3]\", \"BKCM_ACCT_FTWO[ 1]\", \"BKCM_ACCT_FTWO[ 2]\", \"BKCM_ACCT_FTWO[ 3]\", \"BKCM_ACCT_FTHRE[ 1]\", \"BKCM_ACCT_FTHRE[ 2]\", BKCM_ACCT_FTIME, BKCM_ACCT_CCARD, BKCM_ACCT_CNUM, BKCM_ACCT_CEXP, BKCM_ACCT_CMPNM, BKCM_ACCT_PNAME, BKCM_ACCT_EXTRA, BKCM_ACCT_EMAIL, BKCM_ACCT_EMPS FROM BKCMACCT_B ORDER BY BKCM_ACCT_CODE"

    I assume these fields BKCM.ACCT.REM and BKCM.ACCT.FONE etc are arrays.

    Can I manually edit the SQL statement to remove these fields?
    If yes how do I enter the editor?

    Thank you very much

    Jon

  7. #7
    "Certified" Alphaholic Melvin Davidson's Avatar
    Real Name
    Melvin Davidson
    Join Date
    Apr 2000
    Location
    Parker, CO
    Posts
    1,197

    Default Re: ADO vs Alpha DAO

    Quote Originally Posted by jvitello View Post
    I need to caution that I am not a programer - Only a hack. I developed the grid using the wsiwyg editor. I had a look at the actual DB using a pervasive editor. The field is: BKCM.ACCT.REM

    The field constructed by the editor is:
    tmpl.ADO.SQL = "SELECT BKCM_ACCT_CODE, BKCM_ACCT_OLDCD, BKCM_ACCT_ALPHA, BKCM_ACCT_NAME, BKCM_ACCT_ADD1, BKCM_ACCT_ADD2, BKCM_ACCT_ADD3, BKCM_ACCT_CITY, BKCM_ACCT_STATE, BKCM_ACCT_ZIP, BKCM_ACCT_CNTRY, BKCM_ACCT_CONT1, BKCM_ACCT_TITLE, BKCM_ACCT_PHONE, BKCM_ACCT_FAX, BKCM_ACCT_REP, BKCM_ACCT_DLOAD, BKCM_ACCT_SICCD, BKCM_ACCT_CUST, BKCM_ACCT_LEAD, BKCM_ACCT_START, BKCM_ACCT_TERR, \"BKCM_ACCT_REM[ 1]\", \"BKCM_ACCT_REM[ 2]\", \"BKCM_ACCT_FONE[ 1]\", \"BKCM_ACCT_FONE[ 2]\", \"BKCM_ACCT_FONE[ 3]\", \"BKCM_ACCT_FTWO[ 1]\", \"BKCM_ACCT_FTWO[ 2]\", \"BKCM_ACCT_FTWO[ 3]\", \"BKCM_ACCT_FTHRE[ 1]\", \"BKCM_ACCT_FTHRE[ 2]\", BKCM_ACCT_FTIME, BKCM_ACCT_CCARD, BKCM_ACCT_CNUM, BKCM_ACCT_CEXP, BKCM_ACCT_CMPNM, BKCM_ACCT_PNAME, BKCM_ACCT_EXTRA, BKCM_ACCT_EMAIL, BKCM_ACCT_EMPS FROM BKCMACCT_B ORDER BY BKCM_ACCT_CODE"

    I assume these fields BKCM.ACCT.REM and BKCM.ACCT.FONE etc are arrays.

    Can I manually edit the SQL statement to remove these fields?
    If yes how do I enter the editor?

    Thank you very much

    Jon

  8. #8
    "Certified" Alphaholic Melvin Davidson's Avatar
    Real Name
    Melvin Davidson
    Join Date
    Apr 2000
    Location
    Parker, CO
    Posts
    1,197

    Default Re: ADO vs Alpha DAO

    Quote Originally Posted by jvitello View Post
    I assume these fields BKCM.ACCT.REM and BKCM.ACCT.FONE etc are arrays.

    Can I manually edit the SQL statement to remove these fields?

    Jon
    Yes, they are arrays and I don't believe the ADO handles them very well.
    I would not play with the query. Just recreate the grid and do not include those fields.

  9. #9
    Member
    Real Name
    Jon Vitello
    Join Date
    Oct 2007
    Posts
    7

    Default Re: ADO vs Alpha DAO

    Quote Originally Posted by Melvin Davidson View Post
    Yes, they are arrays and I don't believe the ADO handles them very well.
    I would not play with the query. Just recreate the grid and do not include those fields.
    Thanks again - I will need a bit more help. How do I filter out the fields?. They are already set to not display.

  10. #10
    Member
    Real Name
    Jon Vitello
    Join Date
    Oct 2007
    Posts
    7

    Default Re: ADO vs Alpha DAO

    I think I have it. I was building the grid based upon a table. I will rebuild using an sql querry and only include the fields I require.

    Thanks again - your help is much appreciated.

    Jon

Similar Threads

  1. ODBC / ADO access to Alpha dbf
    By Steve Wood in forum Alpha Five Version 7
    Replies: 20
    Last Post: 02-07-2007, 06:19 PM
  2. ADO Import working Well-Thanks Alpha
    By crimmelcp in forum Alpha Five Version 7
    Replies: 0
    Last Post: 01-22-2006, 02:29 PM
  3. Importing Data into Alpha from SQL/ADO Reports
    By David Brown in forum Web Application Server v6
    Replies: 11
    Last Post: 10-19-2005, 11:37 AM
  4. ADO from the outside....
    By Blake in forum Alpha Five Version 6
    Replies: 0
    Last Post: 07-08-2005, 04:47 AM
  5. DAO, ADO and all that stuff
    By Roy Lasris in forum Alpha Five Version 4
    Replies: 2
    Last Post: 01-14-2001, 05:07 PM

Bookmarks

Posting Permissions

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