Problems with OleDbDataReader VFPOLEDB

Hello experts,

can anyone tell me what's wrong with the following?

//***************************************************************************************//
// read data from database via OleDbDataReader 'rs' into DataTable 'table'
String connStr = "Provider=VFPOLEDB;Data Source=K:/Faktura/Data;Collating Sequence=general;";

OleDbConnection con = new OleDbConnection(connStr);
con.Open();

OleDbCommand cmd = new OleDbCommand();
cmd.Connection = con;
cmd.CommandText = "SELECT Artikel.zusaetze2, Artikel.art_bez1, Artikel.hinweis, " +
"Artikel.picture, CAST(NULL AS MEMO) AS pictures, Artikel.werbetext, Artikel.zusaetze5, " +
"Artikel.zusaetze3, Artikel.bemerkung, Artikel.zusaetze4, " +
"CAST(('A_' + Artikel.art_nummer) AS CHARACTER(50)) AS art_nummer, " +
"Artikel.min_verkf, " +
"ALLTRIM(CAST(((SELECT STR(Lager.bestand) FROM Lager WHERE Lager.art_nummer = Artikel.art_nummer) + ' ' + ARTIKEL.Einheit) AS CHARACTER(50))) AS bestand " +
"FROM ARTIKEL " +
"WHERE  Artikel.zusaetze1 = ( 'JA' ) AND  DELETED() = ( .F. )" +
"UNION ALL " +
"SELECT MASCH.zusaetze2, MASCH.fabrikat + ' ' + MASCH.gerate_typ AS art_bez1, MASCH.Hinweis, MASCH.picture, MASCH.pictures, MASCH.werbetext, CAST(YEAR(MASCH.baujahr) AS CHARACTER(40)) AS zusaetze5, MASCH.zusaetze3, MASCH.bemerkung, MASCH.zusaetze4, CAST(('M_' + MASCH.gerate_nr) AS CHARACTER(50)) AS art_nummer, MASCH.minverkf AS min_verkf, CAST(NULL AS CHARACTER(50)) AS bestand FROM MASCH WHERE MASCH.zusaetze1 = 'JA' AND DELETED() = .F.";

/*
"SELECT Artikel.zusaetze2, Artikel.art_bez1, Artikel.hinweis, " +
"Artikel.picture, CAST(.NULL. AS M) AS pictures, Artikel.werbetext, " +
"Artikel.zusaetze5, Artikel.zusaetze3, Artikel.bemerkung, " +
"Artikel.zusaetze4, " +
"CAST(("A_"+Artikel.art_nummer) AS CHARACTER(50)) AS art_nummer, " +
"Artikel.min_verkf, " +
"ALLTRIM(CAST((STR(Lager.bestand)+" "+Artikel.einheit) AS CHARACTER(50))) AS bestand " +
"FROM " +
"ARTIKEL " +
"LEFT OUTER JOIN LAGER " +
"ON  (Artikel.art_nummer + Artikel.art_bez1) = (Lager.art_nummer + Lager.art_bez1) " +
"WHERE Artikel.zusaetze1 = ("JA") " +
"AND DELETED() = (.F.)"
*/

cmd.CommandType = CommandType.Text;

rs = cmd.ExecuteReader();
rs.Close();
rs = cmd.ExecuteReader();
table.Load(rs);     // 'table' = 'abzubildende Artikel'CommandBehavior.CloseConnection
rs.Close();

//***************************************************************************************//

Why does the 'outer join'-query work in the FoxPro sql-editor but not in my code? In my code the result is always zero records.

Why do I have to run 'cmd.ExecuteReader();' twice in order to get the expected result? Otherwise the result is always zero records.

The Foxpro data source is free tables, not a database.

Thanks in advance

Thomas
tkremsAsked:
Who is Participating?
 
pcelbaConnect With a Mentor Commented:
Do you have deleted records in your tables? If yes (and I am sure you have) then you should remove
"AND DELETED() = .F."   and   "AND DELETED() = (.F.)"   parts from your queries.

Such filters should not be used when JOIN is present because it could cause the empty result set as in your case.

Note: If you would like to retrieve deleted records then you have to execute SET DELETED OFF command.

The query (with DELETED condition) can work in VFP IDE. The result depends on the DELETED() status of the actual record in currently selected work area.
0
 
IrogSintaCommented:
Are you saying that it if you only have ExecuteReader in there once just like below, it doesn't work:
cmd.CommandType = CommandType.Text;
rs = cmd.ExecuteReader();
table.Load(rs);     // 'table' = 'abzubildende Artikel'CommandBehavior.CloseConnection
rs.Close();

Open in new window

Or were you closing the recordset prior to loading it into the table?
0
 
tkremsAuthor Commented:
Yes, if I have ExecuteReader only once, it doesn't work. The Result is zero records, and that's definitely wrong. No, the recordset is not closed prior to loading it into the table. The join query doesn't work, too. Maybe a FoxPro issue because of two tables in the queries? I have no idea what's going on there.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Olaf DoschkeSoftware DeveloperCommented:
ALLTRIM(CAST(((SELECT STR(Lager.bestand) FROM L....

SELECT-SQL returns a result set, not a value, you can't make such sub queries in foxpro. I know it's possible in T-SQL to select a single field or expression and work with it as a scalar, but that doesn't work in vfp.

You can always move that into a normal join and do expressions on the joined fields.

Bye, Olaf.
0
 
pcelbaCommented:
Olaf wrote:
that doesn't work in vfp.
tkrems wrote:
Why does the 'outer join'-query work in the FoxPro sql-editor but not in my (P.C. note: C#) code?

Olaf, why don't you believe tkrems? When did you test it the last time?

You are right up to certain VFP version only.

VFP 9 supports correlated subselects and subqueries. If it would return more rows then error 1860 is generated in this case.
0
 
Olaf DoschkeSoftware DeveloperCommented:
pavel,

I just took a glimpse, yes, I didn't test.

But there is something embedded similar to

cast (select str(field) from table as c(50))

And that doesn't work, and I tested it this way. It gives a syntax error. You can't do a subselect within a cast, which expects a single scalar value and not a result set. Do you see that now?

Bye, Olaf.

PS: It would help pretty formatting the  foxpro query to see what it's supposed to do and rewrite it. Others are also right about DELETED(), this is something you might be able to use in queries about single tables, but not here. As you just filter for DELETED() = .F. you can simply skip that, it's a default condition anyway, unless you don't connect with a parameter saying you also want to be able to see deleted records not yet purged by a PACK.
0
 
pcelbaCommented:
Of course, subselects must be enclosed in parentheses otherwise syntax and other various errors can be reported. Even T-SQL requires these parentheses (but I did not test it now :-).

And tkrems follows this rule.

So you can do the subselect inside the CAST in VFP if it returns scalar value.

Just try:
cast ((select str(field) from table WHERE RECNO()=1) as c(50))  && The WHERE condition is used to ensure the scalar value on output.
0
 
Olaf DoschkeSoftware DeveloperCommented:
Ok, accepted. I still would never use that. The where clause used inside is just filtering for the record related to the current article, which is the main table selected from. So this can be turned to a simple join and would run faster already.

Bye, Olaf.
0
 
pcelbaCommented:
Who knows how correlated subqueries and subselects are handled in VFP... I don't see any major difference between this one and join. Both of them need same index for optimization and the speed difference can be marginal.
0
 
tkremsAuthor Commented:
Sorry for my late answer.

Thanks a lot pcelba. You are perfectly right!

Thomas
0
 
tkremsAuthor Commented:
Thank you very much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.