Alpha DevCon 2018
Results 1 to 10 of 10

Thread: .AddSheetFromResultSet generates an exception

  1. #1
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,190

    Default .AddSheetFromResultSet generates an exception

    Code:
    dim result as p
    result=http_get("https:......................")
    dim Doc as Office::Excel2003Document
    Doc.Load("C:\Users\..............\xyz.xls")
    doc.AddSheetFromResultSet(result,"sheet1",.t.)
    Doc.Save("C:\Users\..............\xyz.xls")
    delete Doc
    Exception.jpg
    Why?

  2. #2
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    24,846

    Default Re: .AddSheetFromResultSet generates an exception

    Probably already has a sheet1.
    There can be only one.

  3. #3
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,190

    Default Re: .AddSheetFromResultSet generates an exception

    Thanks Stan, but I tried with and w/out sheet1 with the same exception

  4. #4
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    24,846

    Default Re: .AddSheetFromResultSet generates an exception

    May be a problem with whatever is being returned by result=http_get("https:......................").

    doc.AddSheetFromResultSet(result,"sheet1",.t.) 'result is supposed to be a SQL resultset derived something like

    Code:
    dim conn as SQL::Connection
    dim rs as SQL::ResultSet
    dim connString as C
    dim select_exp as C
    connString = "{A5API='Access', FileName='c:\program files\a5v8\mdbfiles\alphasports.mdb'}"
    select_exp = "select * from customer where bill_state_region = 'ny'"
    if .not. conn.open(connString)
        ui_msg_box("Error", conn.CallResult.text)
        end
    end if
    if .not. conn.execute(select_exp)
        ui_msg_box("Error", conn.CallResult.text)
        conn.close()
        end
    end if
    rs = conn.ResultSet
    conn.close()
    No error when I used such a resultset and specified sheet7 for a test.
    There can be only one.

  5. #5
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,190

    Default Re: .AddSheetFromResultSet generates an exception

    Quote Originally Posted by Stan Mathews View Post
    May be a problem with whatever is being returned by result=http_get("https:......................").
    The URL has a spreadsheet published online which is returned as a blob.
    The interesting thing is if I take that blob and paste it to an Excel spreadsheet, Excel parses the blob into a spreadsheet flawlessly.
    The trouble is trying to parse that blob in alpha.

  6. #6
    "Certified" Alphaholic
    Real Name
    Govindan Gandhi
    Join Date
    Aug 2008
    Location
    New York, NY
    Posts
    4,294

    Default Re: .AddSheetFromResultSet generates an exception

    dim result as p
    result=http_get("https:......................")
    since you are using http_get, it is returning a pointer, but actually the result.body is the data.
    may be you could try,
    Code:
    dim Doc as Office::Excel2003Document
    Doc.Load("C:\Users\..............\xyz.xls")
    doc.AddSheetFromResultSet(result.body,"sheet1",.t.)
    Doc.Save("C:\Users\..............\xyz.xls")
    delete Doc
    thanks for reading

    gandhi

    version 11 3381 - 4096
    mysql backend
    http://www.alphawebprogramming.blogspot.com
    ggandhi344@gmail.com
    Skype:ggandhi344@gmail.com
    1 914 924 5171

  7. #7
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,190

    Default Re: .AddSheetFromResultSet generates an exception

    You are correct, it's a pointer with http_get() and a blob with http_get_page2().
    I have been working with all of them,
    Anyway, this is part of a project and I figured an easier more straight forward way to get it in alpha and abandoned this approach altogether. Thanks

  8. #8
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,190

    Default Re: .AddSheetFromResultSet generates an exception

    This is part of a mini project to get data from spreadsheet from a web page directly into an alpha table.
    It would have been nice if I would have been able to take the blob (either from response.body or directly from http_get_page2()) and parse it directly in alpha. But that did not work. Not sure why. Could be a version issue.

    So, I resorted to using Excel as an intermediary.
    I tried multiple different ways in Excel with success. Now I am just trying to find the most efficient way. So if anyone has some ideas, please let me know. Or, if I manage somehow to parse the blob directly into the table, that would be best.

    But using Excel comes with a lot of nuances, as the documentation on the subject is sparse and repeatedly states that "Excel does not like this, or Excel does not like that..".
    I should perhaps, time permitting, start a new thread on the subject, having noticed multiple quirks when using pointers to Excel.

    Some of the difficulties I am running into with Excel might have to do with the alpha version I am using. V11 (can't remember the build). That might be part of it. With my time being so limited right now, I couldn't spend much time debugging it. But I will provide examples in a separate thread to see if it's a version issue or something else.
    Thanks

  9. #9
    "Certified" Alphaholic
    Real Name
    Govindan Gandhi
    Join Date
    Aug 2008
    Location
    New York, NY
    Posts
    4,294

    Default Re: .AddSheetFromResultSet generates an exception

    i am not proficient in desktop side but here is something i tried seems so far so good,
    i used http_download function to download the xls file to c:\temp directory and then used operation to import data into a dbf table with the names. all worked good.
    can this be done in coded version, i do not know yet.
    you probably can you know way better than i.
    thanks for reading

    gandhi

    version 11 3381 - 4096
    mysql backend
    http://www.alphawebprogramming.blogspot.com
    ggandhi344@gmail.com
    Skype:ggandhi344@gmail.com
    1 914 924 5171

  10. #10
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,190

    Default Re: .AddSheetFromResultSet generates an exception

    That's one of the ways I already tried using Excel and it worked just fine.

Similar Threads

  1. Live Preview generates unnecessary dialog
    By richardbarth in forum Application Server Version 11 - Web/Browser Applications
    Replies: 0
    Last Post: 07-04-2013, 09:17 PM
  2. Sub-browse generates extra records
    By burtnco in forum Alpha Five Version 10 - Desktop Applications
    Replies: 1
    Last Post: 04-27-2010, 05:43 PM
  3. what action scripting generates this code?
    By Mike Wilson in forum Alpha Five Version 9 - Desktop Applications
    Replies: 2
    Last Post: 11-20-2009, 05:32 PM
  4. print.preview generates error
    By carterweslock in forum Alpha Five Version 5
    Replies: 5
    Last Post: 02-11-2003, 06:50 AM
  5. APPEND wizard generates wrong syntax
    By videomuger in forum Alpha Five Version 5
    Replies: 5
    Last Post: 02-05-2003, 08:31 PM

Posting Permissions

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