Programming with dBASE - invalid parameter error

Author

invalid parameter error

Linda C. Baker

2006-02-17, 7:24 am

I have a report with a query object with a long sql statement.
See below.

It works fine for me and for most of the users, but someone called me today that said they get the following error and the report will not run:

Database engine error

Invalid parameter C:Program Files\ IN State Board of
File: receiptrpt.rep
Routine: RECEIPTRPTREPORT::RE
CEIPTTRPTREPORT
Line: 43

I tried having them reinstall the system and that didn't work. Line 43 is the line below that says "active=true".

I am at my wit's end as to why it works for me and they are getting this error. Since it works for me, I can not test it and diagnose the problem. Does anyone have any idea what the problem might be or how to correct it? Could it be something with thei
r data file? I had them email their files to me and they worked for me.

Thanks.

Linda

this.RECSORT1 = new QUERY()
this.RECSORT1.parent = this
with (this.RECSORT1)
left = 0.7396
top = 0.7396
sql = 'SELECT Receipts.TYPE, Receipts.AMOUNT, Receipts.UNITACCTNO, Receipts.TITLE, Begbal.UNITFUNDNO, Class.TITLE CLASSTITLE, Begbal.TITLE FUNDTITLE, Receipts.COUNTY, Receipts."YEAR", Receipts.UNITTYPE, Receipts.IDNO, Receipts.FUNDCODE, Receipts.ACC
TTYPE, Receipts.CLASS, Receipts.CATCODE, Receipts.CODE ;
FROM "receipts.dbf" Receipts INNER JOIN "begbal.dbf" Begbal ON (Receipts.TYPE = Begbal.TYPE) ;
AND (Receipts.FUNDCODE = Begbal.FUNDCODE) INNER JOIN "class.dbf" Class ON (Receipts.ACCTTYPE = Class.ACCTTYPE) AND (Receipts.CLASS = Class.CLASS) ;
ORDER BY Receipts.TYPE, FUNDTITLE, Receipts.CLASS, Receipts.TITLE'
requestLive = false
active = true
endwith

Jan Hoelterling

2006-02-17, 7:24 am

Hi Linda,

are you using parameters in the SQL statement? Is it possible that this user
gets a NULL value in there? If the program fails on the "active = true"
line, that usually has two reasons:
1) The SQL statement is somehow incorrect, syntactically or logically
2) The resulting rowset is read-only and you have "requestlive = true" (to
make things more complicated, this scenario results in the meaningless error
message "Operation not applicable" - go figure!?!)

You could throw in a
msgbox(SQL,"SQL=")
before the
active = true
to get an idea what the real SQL looks like. That may give you a clue... My
money is on some issue with the SQL statement...

Hope this helps,

Jan

Linda C. Baker

2006-02-20, 3:23 am

Thanks. I will check that.

Linda

Jan Hoelterling Wrote:

> Hi Linda,
>
> are you using parameters in the SQL statement? Is it possible that this user
> gets a NULL value in there? If the program fails on the "active = true"
> line, that usually has two reasons:
> 1) The SQL statement is somehow incorrect, syntactically or logically
> 2) The resulting rowset is read-only and you have "requestlive = true" (to
> make things more complicated, this scenario results in the meaningless error
> message "Operation not applicable" - go figure!?!)
>
> You could throw in a
> msgbox(SQL,"SQL=")
> before the
> active = true
> to get an idea what the real SQL looks like. That may give you a clue... My
> money is on some issue with the SQL statement...
>
> Hope this helps,
>
> Jan
>
>
>

Linda C. Baker

2006-02-21, 11:23 am

The SQL statement is below:

'SELECT DISTINCT Igtable.IGCODE, Igtable.TITLE, Igtable.IGTYPE, Intergov.IGCODE, Intergov.AMOUNT, Intergov.COUNTY, Intergov.UNITTYPE, Intergov.IDNO, Intergov."YEAR" FROM "igtable.dbf" Igtable INNER JOIN "intergov.dbf" Intergov ON (Igtable.IGCODE = Interg
ov.IGCODE) GROUP BY Igtable.IGTYPE, Igtable.IGCODE, Igtable.TITLE, Intergov.IGCODE, Intergov.AMOUNT, Intergov.COUNTY, Intergov.UNITTYPE, Intergov.IDNO, Intergov."YEAR" ORDER BY IGTYPE'

The user did call today and say they installed the system on another computer and it worked. Just like it worked on mine also. I have had a couple of others with this problem out of several hundred. Would there be something about a specific computer (o
perating system, other software, etc) that would cause an error like this? You mentioned the rowset may be read-only. How would it become read-only? Is that something I can have the user check. I was wondering if it could be some type of file access p
roblem.

Thanks.

Jan Hoelterling Wrote:

> Hi Linda,
>
> are you using parameters in the SQL statement? Is it possible that this user
> gets a NULL value in there? If the program fails on the "active = true"
> line, that usually has two reasons:
> 1) The SQL statement is somehow incorrect, syntactically or logically
> 2) The resulting rowset is read-only and you have "requestlive = true" (to
> make things more complicated, this scenario results in the meaningless error
> message "Operation not applicable" - go figure!?!)
>
> You could throw in a
> msgbox(SQL,"SQL=")
> before the
> active = true
> to get an idea what the real SQL looks like. That may give you a clue... My
> money is on some issue with the SQL statement...
>
> Hope this helps,
>
> Jan
>
>
>

Paul Van House

2006-02-21, 1:23 pm

In article <R6P0uzvNGHA.2320@news-server>, lbaker@sboa.in.gov says...
> The SQL statement is below:
>
> 'SELECT DISTINCT Igtable.IGCODE, Igtable.TITLE, Igtable.IGTYPE, Intergov.IGCODE, Intergov.AMOUNT, Intergov.COUNTY, Intergov.UNITTYPE, Intergov.IDNO, Intergov."YEAR" FROM "igtable.dbf" Igtable INNER JOIN "intergov.dbf" Intergov ON (Igtable.IGCODE = Inte
rgov.IGCODE) GROUP BY Igtable.IGTYPE, Igtable.IGCODE, Igtable.TITLE, Intergov.IGCODE, Intergov.AMOUNT, Intergov.COUNTY, Intergov.UNITTYPE, Intergov.IDNO, Intergov."YEAR" ORDER BY IGTYPE'
>

Have you tried saving the SQL statement to a SQL file, then in your
query:
query.SQL = '@sql_file_name.sql'
query.active = true

YOu can store very long SQL statements in a SQL file, and it's easier to
maintain especially if you use the same statement in several forms.
Yours is 439 characters (can't remember the limit on statement length).

Rowsets become ReadOnly when you use an "order by" clause on a field
that is not part of an index key. After a little experimenting with a
basic table I found that even if the field used in "order by" is in an
index you sometimes still end up with a ReadOnly query.

Instead of using an Order By in the SQL, use the Rowset's index property
to set your order. This way it will NOT be ReadOnly and you are
guaranteed that the index SQL chooses is correct (since you're defining
it)

--
Paul Van House
remove ".removeme" for e-mail replies
Radio/TV Software and Baseball Stat Software:
http://www.binxsoftware.com
Family Home Page: http://vanhouse.binxsoftware.com
Church Home Page: http://www.ashfordumc.org

Linda C. Baker

2006-02-21, 1:23 pm

I will check that. Thanks.

Paul Van House Wrote:

> In article <R6P0uzvNGHA.2320@news-server>, lbaker@sboa.in.gov says...
tergov.IGCODE) GROUP BY Igtable.IGTYPE, Igtable.IGCODE, Igtable.TITLE, Intergov.IGCODE, Intergov.AMOUNT, Intergov.COUNTY, Intergov.UNITTYPE, Intergov.IDNO, Intergov."YEAR" ORDER BY IGTYPE'[color=darkre
d]
>
>
> Have you tried saving the SQL statement to a SQL file, then in your
> query:
> query.SQL = '@sql_file_name.sql'
> query.active = true
>
> YOu can store very long SQL statements in a SQL file, and it's easier to
> maintain especially if you use the same statement in several forms.
> Yours is 439 characters (can't remember the limit on statement length).
>
> Rowsets become ReadOnly when you use an "order by" clause on a field
> that is not part of an index key. After a little experimenting with a
> basic table I found that even if the field used in "order by" is in an
> index you sometimes still end up with a ReadOnly query.
>
>
> Instead of using an Order By in the SQL, use the Rowset's index property
> to set your order. This way it will NOT be ReadOnly and you are
> guaranteed that the index SQL chooses is correct (since you're defining
> it)
>
>
> --
> Paul Van House
> remove ".removeme" for e-mail replies
> Radio/TV Software and Baseball Stat Software:
> http://www.binxsoftware.com
> Family Home Page: http://vanhouse.binxsoftware.com
> Church Home Page: http://www.ashfordumc.org

Linda C. Baker

2006-02-25, 9:36 am

I emailed dbase support with this problem and found out that it has something to do with the user having a problem with the BDE. They told me to go to regedit and delete the Borland folder under HKEY_Local_Machine, Software and then reinstall the BDE. T
he user that had the problem did that and it works now. Thanks dbase support!

Linda C. Baker Wrote:

> I will check that. Thanks.
>
>
> Paul Van House Wrote:
>
Intergov.IGCODE) GROUP BY Igtable.IGTYPE, Igtable.IGCODE, Igtable.TITLE, Intergov.IGCODE, Intergov.AMOUNT, Intergov.COUNTY, Intergov.UNITTYPE, Intergov.IDNO, Intergov."YEAR" ORDER BY IGTYPE'[color=darkre
d]
>

Sponsored Links