RE: Use of CASE with Microsoft OLE DB Provider for Visual FoxPro
- From: Arlyn_L <Arlyn_L@xxxxxxxxxxxxxxxx>
- Date: Mon, 5 May 2008 14:58:02 -0700
Wen Yuan;
I am developing with .net c#. I need to create a pivot result from a many to
many relationship involving 3 tables in an existing FoxPro database. I tried
the ICase() approach and it is not recognized by the OLE DB provider for VFP.
The provider returns "Unrecognized command/keyword.
The SQL I tried was:
string _query =
"SELECT MAX(ICASE(ppv.type = 1, pd.value)) AS
addressee), " +
" MAX(ICASE(ppv.type = 2, pd.value)) AS detail, " +
" MAX(ICASE(ppv.type = 3, pd.value)) AS city, " +
" MAX(ICASE(ppv.type = 4, pd.value)) AS county, " +
" MAX(ICASE(ppv.type = 5, pd.value)) AS state, " +
" MAX(ICASE(ppv.type = 6, pd.value)) AS country, " +
" MAX(ICASE(ppv.type = 7, pd.value)) AS postal, " +
" MAX(ICASE(ppv.type = 8, pd.value)) AS latlon, " +
" MAX(ICASE(ppv.type = 9, pd.value)) AS phone, " +
" MAX(ICASE(ppv.type = 10, pd.value) AS temple, " +
" p.RECNO, p.StyleId, p.DsId, " +
" p.StartYesr, p.EndYear, p.Comment, p.ShortPlace
" +
"FROM {0}_P AS p INNER JOIN " +
" {0}_PPV AS ppv ON p.RECNO = ppv.Recno INNER JOIN
" +
" {0}_PD AS pd ON ppv.Uid = pd.Uid " +
"GROUP BY p.RECNO, p.StyleId, p.DsId, p.StartYesr,
p.EndYear, p.Comment, p.ShortPlace";
....
string _querySQL = string.Format(_query, _project);
OleDbCommand command = new OleDbCommand(_querySQL, _connection);
// If connection is closed open it
if (_connection.State == ConnectionState.Closed)
{
_connection.Open();
_needsClose = true;
}
OleDbDataReader reader = command.ExecuteReader();
I an not a foxpro programmer so I am not sure how to create a stored proc or
how to access it with .net.
I am slow to respond to you because I have not been able to access the
managed discussion group for several days, but hopefully that is fixed now.
Regards;
Arlyn_L
""Wen Yuan Wang [MSFT]"" wrote:
Hello Arlyn,.
Thanks for your waiting.
Do you develop a .net application? I'm afraid to say "DO CASE... ENDCASE"
command cannot work fine in .net application by OLEDB Provider for VFP. We
recommend you can use ICASE() function. This is a new in-line function
supported by VFP 9.0. Alternative way is that you can create a Stored
Procedure in VFP and in the SP you can use DO CASE ENDCASE. After that,
you can call that SP in your client application.
Hope this helps. Please feel free to let m know if you have any more
concern on this. We are glad to assist you.
Have a great day,
Best regards,
Wen Yuan
Microsoft Online Community Support
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
msdnmg@xxxxxxxxxxxxxx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
- Follow-Ups:
- RE: Use of CASE with Microsoft OLE DB Provider for Visual FoxPro
- From: "Wen Yuan Wang [MSFT]"
- RE: Use of CASE with Microsoft OLE DB Provider for Visual FoxPro
- Prev by Date: RE: ODBC/OLEDB failure accessing MS Access "Attachment" field in q
- Next by Date: RE: Use of CASE with Microsoft OLE DB Provider for Visual FoxPro
- Previous by thread: RE: ODBC/OLEDB failure accessing MS Access "Attachment" field in q
- Next by thread: RE: Use of CASE with Microsoft OLE DB Provider for Visual FoxPro
- Index(es):
Relevant Pages
|
|
