Error 1494

[michlm]

Has anyone come across Error 1494 ("The view cannot be re-queried. The view definition in the database has been changed. Close and reopen the view. To ensure that the view is not changed, open the database exclusively.&quot?

This error comes up when I try to open my three page form which has a parameterized view feeding it. The first page of the form has a grid on it with the view as its record source. The view has no data on entry but is requeried when the form is opened so it can set the parameters to have no value (set them = to " &quot. The view works on its own with no problem, but for some reason will no longer work in my form (it did at one point). Before the form is ever opened, I run a daily process that cleans up and merges some downloads and puts the info into a final table. The view then consists of this final table and two other tables.

I have tried many things such as totally rebuilding the view and also removing and readding the view to the form but NOTHING seems to work. I just don't know where else to look since rebuilding the view doesn't even work.

Can anyone tell me if they know when a message like this usually occurs? I'm pretty sure something in my daily process is causing it but I can't figure out which piece or where to start looking so if anyone can give me some ideas as to where they've seen this error, I might be able to pinpoint which piece of my daily process is causing it.

Thanks for any ideas you can give me!

Michelle

 

[michlm]

Ok, several hours of frustration later, I've determined that the problem is in 5 fields that are actually created in UDFs called by the view. The problem seems to be in the length of the numeric fields. An example would be if I run the view by itself, the Numeric is a length of 16 with 2 decimal places, but that same field in the view in the form lists a structure that is length 20 with 9 decimal places. The question is how can I set the length in my UDF so there isn't this conflict when the form uses the view? I tried to initialize my variable with the same length as what the form-view seems to be looking for but this didn't seem to work. I've tried using the VAL() after padding the numeric for the appropriate length but no luck. I must be missing something but I can't figure out what. Any ideas as to how I can guarantee the length of the field will stay the same when my UDFs run in the view?

Thanks.

Michelle

 

[michlm]

Ok, I solved my own problem (after only 3 days) so I figured I'd close out my own thread . What ended up happening is that in my UDFs being called by the view, I had to add code to account for the fact that the view was being loaded with nodata and then being requeried with a parameter. What I did was add an IF statement checking to see if one of my fields was NULL or EMPTY and if so, initialize my return variable to the length I wanted (i.e. 000000.000000). That way when the view creates the structure from the first record (because of the NODATA), the length is set correctly through the initializing of my return variable instead of setting the length through the evaluation of the other conditions. Once I added the IF statement to each of the UDFs, my problem was solved!

BTW, Thanks to Larry Miller who replied to a similar problem in the Visual MaxFrame Forum. It was his response that pointed me in the right direction . ...

Maybe this will help someone else at some point . .. .Happy Labor Day!

Michelle

 

[foxdev]

Michlm, another possibility might be to explicitly set the view's column properties within the View Designer. Your last message certainly pointed out a problem, although I never seem to have this problem with remote views (remote views go through ODBC, so behave differently, but still use the underlying VFP view structure).

 

[TomasDill]

I think following should be in all FAQ for VFP.
When you need to create SQL with secial numeric field size (say, for UDF in query or just expression), add constant value like '000.000' to the expression in query. This also will speed up query because do not need to check for empty values in UDF just to create view's structure. And this is very useful for reports, to do not re-format each field in report but just change SQL.
Following are sample queries:

SELECT *, sum(nMyNumber) + 000000.0000 as MySum from ....

MySum field in result will always have N(11,4) despite format of nMyNumber field.

SELECT *, MyFunction() + 000000.00 as MyField from ...

MyField will always have N(9,2) despite format of number returned by function.

Same rules work also for character fields, padr function is useful here. Unfortunately, I did not found a way to convert character fields and expressions into memo for query.
It DOES NOT works for currency or other field types. Use converting functions for other types.
I did not investigated speed penalties of above method. (Don't know whether '0' really added to result values taking a time, or just ignored.) Sure PADR function for character fields called always.

 

[jesylv]

Many Thanks to all.
This tread save me a lot of aggravation tonight.

Jean

 

[mbru]

I had a similar problem when dealing with a parameterized view. My view was used as a search engine. It had about 6 or 8 parameters feeding the search criteria in the WHERE clause of the view and it worked fine. I decided to add another search criteria, and hence, another parameter. Now I started getting the dreaded "View definition has changed' error. None of the fields being selected was changed in any way, only one more condition added to the WHERE clause. So I didn't see how or why the field length ideas mentioned in this thread would apply to my situation.

The view worked fine with the NoDataOnLoad property set to .F. When it was set to .T., the "view definition changed" error appeared. I tried restructuring the criteria in the WHERE clause. Many, many different ways. No success. Finally, in the command line mode, I used the view with and without the NODATA clause, each time listing the table structure to print. I compared the printouts. Lo and behold, one of the numeric fields changed size. Totally inexplicable to me. But I have my view working now as I wanted it to.

Thanks to the previous posters. Hope this might help someone else out.