Data could not be loaded - Parameter index out of range...

[Used2BeAnOwl]

Hello,
I'm trying to create a form containing a subform using the form wizard, and I'm getting an error when I try to open the form. I've pasted the error messages and information below that are provided when I load the form. I'm connecting to a MySQL database via the JDBC driver provided by mysql.com, and forms created by the wizard work fine when trying to connect to individual tables. I'm running OOo version 3.3.0 on Mac OS X 10.7 ...not sure what other information might be relevant.

The only other similar post I found on a similar topic doesn't seem to address the use of the wizard and I'm not sure how the reported solution would apply in this case (http://user.services.openoffice.org/en/ ... rm#p182437).

Thanks!
Randy

Error: The data content could not be loaded.
Error: SQL Status: S1009
Parameter index out of range (1 > number of parameters, which is 0).
Info: The SQL command leading to this error is:
SELECT * FROM `accStuff`.`MonthlyReportsData` WHERE ( `district2` = :link_from_district )

[Villeroy]

Bind the subform to table MonthlyReportsData and connect the district2 to the related field in the main form.

[Used2BeAnOwl]

Under Form Properties for the subform I have <Content> pointing to the MonthlyReportsData table, <Link master fields> defined as "district" from the MonthlyReports table and <Link slave fields> defined as "district2" from the MonthlyReportsData table. I looked at the Form Properties of the MainForm, but didn't see anything else to edit. These were already defined by the wizard, is there something else I'm missing?

[Villeroy]

No need to do anything in the main form. I should have written "link district2 to the main form's related field".
You don't need any parameter queries for simple subfrom links where TableX.A=TableY.B

[Used2BeAnOwl]

I have the following constraint in the MonthlyReportsData table definition, but that doesn't seem to help. Is there some other way to link them on the OOo side?

ALTER TABLE MonthlyReportsData ADD CONSTRAINT `fk_MonthyReportsData2MonthlyReports` FOREIGN KEY (`district2`) REFERENCES `MonthlyReports` (`district`)

[Villeroy]

So what is your problem with that relation?

[rudolfo]

I have seen similar problems with a SQLite database. But my main form was based on a group by query. The grid for the main form was filled with data, but when the subform should be filled I got a:
SQL Status: HY000, Error code: 1, "parameter marker count incorrect" and the statement showing the problem was: SELECT * FROM "expenses" WHERE ( "shop" = :link_from_shop ). The :link_from_ seems to be what the Master-Detail Relation uses internally.
I thought it would work better with two tables behind the forms and created a view in SQLite with the SQL statement from the query ... with the same frustrating results

Because I had only one table "expenses" I gave it a go and created an embedded HDBSQL database with the same content. And both forms were filled with the expected data, no need for a view, the Base query for the Master form did not cause any problems.

My temporary conclusion is that the problem is related with the type of the column that is used to link the master and slave form. Base was showing wtext in case of SQLite, but varchar in case of HDBSQL. SQLite doesn't use strict types for database columns, this could always be a problem. And MySQL should be easier to handle, but it seems like text or varchar fields as linking fields can cause a problem in Base. Well, I am guessing here that your district column is a string.
Maybe you give it a go with an additional (lookup) table that uses an integer ID for your districts and use that in your MonthlyReportsData and MonthlyReports to link between master and slave form.

[Tubal]

I have a related issue.

My problem is that if I have multiple levels, i.e.

Code: Select all

Form1
   Subform2
      Subform3

If there is no data in Subform2 when the form opens, then the link between Subform2 and Subform3 is broken, and won't work for the rest of the session. If there is data in Subform2 when the form opens, there is no problem.

This seems to only happen if I connect to an external datasource. I attemped to create an HSQL example, but I didn't experience this problem.

The error I'm getting is:

Code: Select all

SQL Status: 42883
ERROR: operator does not exist: integer = character varying
  Hint: No operator matches the given name and argument type( s ). You might need to add explicit type casts.
  Position: 53

The SQL command leading to this error is:

SELECT * FROM "public"."sys_pos" WHERE ( ( "sys_id" = :link_from_sys_id ) AND ( "model_id" = :link_from_model_id ) ) AND ( 0 = 1 )

I'm thinking that the query is running and looking for :link_from_sys_id, but since there is no data, there is no link, and it breaks the query. (This query is not something I created, it appears to be internal to Base to populate the subform).

Is there a workaround to this?

[RoryOF]

Why not put in a dummy link, something that is utterly out of range or marked simply as a placeholder for housekeeping purposes?

[Tubal]

Why not put in a dummy link, something that is utterly out of range or marked simply as a placeholder for housekeeping purposes?

Could you give me an example?

I've attached my current form tree and linking fields.