Re: C0000005 errors: ODBC driver problem?

From: miocolano (anonymous_at_discussions.microsoft.com)
Date: 05/10/04 

Date: Mon, 10 May 2004 11:06:07 -0700

Carsten,
Your suggestion to use APPEND FROM solved the problem. There must be a bug in the Excel ODBC driver I was using, because when I entirely stopped using SQLConnect() and SQLexec() for Excel and instead used

APPEND FROM filename TYPE XL8 SHEET sheetname

the problems all disappeared.

The code is simpler, that way, too!

I had not created any objects, as you described in your second reply.

Just one tip for anyone interested: I found that I needed to create and import into a temp dbf with a field for every spread*** column, including ones I didn't want (except maybe unwanted ones to the right of all the desired ones). For unwanted columns I used placeholder fields represented with names such as Fn, type c, length 1.

Dates in the spread*** came in as numbers, and the receiving field had to be either character or numeric type. The trick is that after appending, the number in the date column is the number of days after 12/30/1899. To arrive at the correct date, use

actual_date= ctod("12/30/1899")+number_imported_from_date_column

I upgraded to VFP version 8 from 6 only because I thought that was how to fix the Fatal Exceptions, but it must have been the Excel driver all along!

Thanks very very much!

-Mark Iocolano

     ----- Carsten Bonde wrote: -----
     
     Mark,
     
     if the problem is an unreleased object, then is it probably created with one
     of the functions:
     - CreateObject()
     - NewObject()
     - GetObject()
     
     Usually you release every object you create with one of the above functions.
     
     If you are not holding objects (created with one of the above functions) in
     the array bound to the comboboxes, i doubt that they are the reason.
     
     What happens if you just start your form, and quits again (without using any
     of your functions). Will it still crash? In that case you might take a look
     at the code at the INIT-Event and LOAD-Event, are you creating objects here?
     
     What does your progress-dialog do? What happens if you remove it from your
     form (better make a backup first <s>)?
     
     VFP automatically releases the objects (Controls) contained on the form, so
     it should'nt be nessecary "manually" to release them.
     
     Cheers
     Carsten
     
     
     "miocolano" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag
     news:AEB7E1F8-EFE6-4B58-ABB7-8DCA46062333@microsoft.com...
> Carsten,
> First, thanks for taking the time to read my lengthy post.
>> I think it's more likely the unreleased object-reference you mentioned.
     The form has four combo boxes that use the same public array as their
     source. There are four text boxes that contain the Excel path/filenames.
     There are four "Browse" buttons that simply trigger the getfile() function
     to populate the respective text box. An edit box is updated with a running
     progress dialog. Finally, there is a "Start" button and a "Close" button.
>> All the functions and procedures are in the main .prg that calls the form.
     (Except for code that MUST be in the object methods, such as
     thisform.release).
>> The combo boxes and text boxes have global variables as their control
     sources. No controls are bound to data sources.
>> The edit box is simply updated using
> form1.edtStatus.value = "new text" + chr(13)+chr(10) +
     form1.edtStatus.value
> I've removed the edit box and related code for debugging, with no effect.
>> What could the unreleased object-reference be?
>> Should I explicitly release all the objects in the form's destroy method
     or in the click method of the close button?
>> I'll try the APPEND FROM you suggested, too.
>> Thanks,
> -Mark Iocolano
>>> ----- Carsten Bonde wrote: -----
>> miocolano,
>> i have no experience with Excel and ODBC, but have you considered
     using VFPs
> built-in-function "APPEND FROM ? TYPE XLS"?
> Another alternative way to get the data could be using automation
     (please
> check googles newsgroup-search for examples).
>> The problem you mention, that the program crashes when the form is
     released,
> is usually because some object-reference is hanging unreleased
     around.
>> Cheers
> Carsten
>>>> "miocolano" <anonymous@discussions.microsoft.com> schrieb im
     Newsbeitrag
> news:7D488887-2620-4CDB-8FB0-3EAAC171F064@microsoft.com...
>> I have written a very simple program with one form. Form is called
     from a
> prg using
>> DO FORM Form1
>> READ EVENTS
>>> VFP8 sp1, WinXP Professional sp1.
>>> Names of Excel spread***(s) are input on the form. "Start"
     button reads
> them via ODBC SQLexec(), creates temporary dbfs containing the data
     from
> certain columns. The Excel data is then placed into a SQL database
     using the
> functions of the API for the application that uses the SQL database.
>>> "Close" button click method does
>> thisform.release
>> CLEAR EVENTS
>>> Program usually crashes on the READ EVENTS line when the form is
     released.
>>> Before importing each Excel row, data is fetched from the SQL
     database,
> again via ODBC, to see if that record was previously imported. My
     program,
> in other words, uses ODBC connections and SQLexec() for Excel and for
     SQL
> 2000.
>>> It also sometimes crashes on lines having to do with Selecting
     data from
> the SQLresult cursors into temporary tables (the raw data extracted
     from
> Excel or SQL needs to be manipulated--for example, all the text Excel
> columns are retrieved as memo fields). Also crashes sometimes when
     issuing
> USE near the end of the program to close the temporary dbfs. Also
     sometimes
> crashes when indexing the temp dbfs.
>>> I have upgraded to VFP8, SP1. Closed and removed the FOXUSER file
     (not
> used by the program, anyway). Even tried replacing the form with
> old-fashioned SAY..GET..READ CYCLE code using old Foxpro2.6 screen
> techniques. (With that, it crashes sometimes on the READ statement)
>>> I have checked my housekeeping: issued SQLdisconnect() when done
     with the
> connection, closed and erased temp files, etc.
>>> To test, I removed code to load and call the functions of the
> application's API. That didn't appear to be the cause. (The API is
> well-tested and has been in use for a long time.)
>>> I am beginning to think there is something wrong with the
     temporary tables
> created using SQLexec(). Perhaps an ODBC driver bug? Excel ODBC
     driver is
> v4.00.6019.00. SQL ODBC driver is v2000.81.9042.00.
>>> I have spent days on this and cannot deliver the program in this
> condition. Any suggestions would be appreciated!
>>