Visual FoxPro SQL Queries - View definition has been changed

Author

View definition has been changed

r.knipscheer@home.nl

2006-02-08, 9:25 am

Hi,

a lot is been written already about this subject, but most of the
problems mentioned were caused by concatenations. I use a very simple
view. I use a filter on this view so i just see the records which
hasn't been processed yet, Retouren.verwerkt = ( 'N' ).

On my form i have a process button wich appends all the lines to a
file, and replaces retouren.verwerkt with 'Y'. After adding a line i
requery and after processing the lines i also requery. When i open my
form, there is no data with verwerkt is 'N'. After i add a line and
requery i get the above error. When i close and reopen, i see the one
added line and i can add all other lines without any problems. When i
process the lines and replace verwerkt with 'Y' and requery i again get
the error 'view definition has been changed.'

I guess the solution is somewhere in the fact that at first no data is
fetched, and there will be somekind of null values in the view wich are
been replaced with real values after adding a line, causing vfp to
thing that the view definition has been changes, same when processing
the lines and no data is returned.
As there is no data before and after, NVL will not work, becouse there
is no data. I also tried the nodataonload, same result.
This is my view select:

SELECT Retouren.id, Retouren.ean, Retouren.titel, Retouren.aantal,;
Retouren.datum_invoer, Retouren.verwerkt;
FROM ;
retouren Retouren;
WHERE Retouren.verwerkt = ( 'N' );
ORDER BY Retouren.id

Thanks!
Rotsj

Cindy Winegarden

2006-02-09, 3:24 am

<r.knipscheer@home.nl> wrote in message
news:1139406442.918709.126980@g43g2000cwa.googlegroups.com...

Hi Rotsj,

> ..... I use a very simple
> view. I use a filter on this view so i just see the records which
> hasn't been processed yet, Retouren.verwerkt = ( 'N' ).

> This is my view select:
>
> SELECT Retouren.id, Retouren.ean, Retouren.titel, Retouren.aantal,;
> Retouren.datum_invoer, Retouren.verwerkt;
> FROM ;
> retouren Retouren;
> WHERE Retouren.verwerkt = ( 'N' );
> ORDER BY Retouren.id

Which buffering scheme are you using?

> On my form i have a process button wich appends all the lines to a
> file, and replaces retouren.verwerkt with 'Y'. After adding a line i
> requery and after processing the lines i also requery. When i open my
> form, there is no data with verwerkt is 'N'.

I assume this is the expected result since you have changed all the "Y"
values to "N".

> After i add a line and
> requery i get the above error. When i close and reopen, i see the one
> added line and i can add all other lines without any problems. When i
> process the lines and replace verwerkt with 'Y' and requery i again get
> the error 'view definition has been changed.'

If you stop between changing verwerkt to "Y" and the requery and query the
underlying data is this "Y" value updated back to the source data?

> I guess the solution is somewhere in the fact that at first no data is
> fetched, and there will be somekind of null values in the view wich are
> been replaced with real values after adding a line, causing vfp to
> thing that the view definition has been changes, same when processing
> the lines and no data is returned.
> As there is no data before and after, NVL will not work, becouse there
> is no data. I also tried the nodataonload, same result.

Would you be willing to create a small project and a small amount of data
that duplicates the problem and send it to me? I'm really curious about this
one.

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn
.com www.cindywinegarden.com

Cindy Winegarden

2006-02-10, 3:24 am

Rotsj sent me part of his project so I could take a look. I was under the
impression that the view was local, but it was actually a remote view.
Remote views are interesting in that the Fox data types have to be properly
matched to the data types of the back end database.

He included a local copy of some of the data and two fields that caught my
attention. One was dates stored in a character field with some stored with
the year first and some with the year last. That makes it impossible to do
good date searches. The other field was N(18) and had numbers that were ISBN
or EAN numbers to identify books. These numbers should be entered to a
character field, not a numeric field. I looked at the view definition (of
the actual remote view) he included and the N(18) field was marked as C(13).

My guess is that the problem lies in the back end database trying to convert
the EAN/ISBN numbers between numeric and character values.

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn
.com www.cindywinegarden.com

"Cindy Winegarden" < cindy_winegarden@msn
.com> wrote in message
news:OP4nORSLGHA.744@TK2MSFTNGP09.phx.gbl...

> Would you be willing to create a small project and a small amount of data
> that duplicates the problem and send it to me? I'm really curious about
> this one.

r.knipscheer@home.nl

2006-02-10, 9:24 am

Hi Cindy,

thank you again to take the time to look at this problem. I fixed the
points mentioned. I found out that another problem was caused by
integer values on my database. The database uses integers in
auto-increment fields. It appears that these integer values causes
problems. I now have changed these into numeric and handle the
increment myself.

A bit of a workaround but it solved the problem.

Rotsj.

Sponsored Links