?
Solved

Problems with OleDbDataReader VFPOLEDB

Posted on 2012-09-09
11
Medium Priority
?
1,904 Views
Last Modified: 2012-09-11
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
0
Comment
Question by:tkrems
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38380452
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
 

Author Comment

by:tkrems
ID: 38380484
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
 
LVL 43

Accepted Solution

by:
pcelba earned 2000 total points
ID: 38380619
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 38380741
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
 
LVL 43

Expert Comment

by:pcelba
ID: 38380807
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
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 38381015
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
 
LVL 43

Expert Comment

by:pcelba
ID: 38381027
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
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 38381138
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
 
LVL 43

Expert Comment

by:pcelba
ID: 38381228
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
 

Author Comment

by:tkrems
ID: 38386124
Sorry for my late answer.

Thanks a lot pcelba. You are perfectly right!

Thomas
0
 

Author Closing Comment

by:tkrems
ID: 38386128
Thank you very much!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question