Information générale
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Titre:
SQL Pass Thru, ADO or Remote Views?
Windows 2000, VFP 6.0 with SP 5 Installed, using SQL Server 2000.
Experience: Many reports requiring one table have been created and the users really like them.
Desired: Create a SQL statement using 6 tables to be used in a more complex report.
Tried: SQL Pass through.
Results: Only able to use one table.
Scenario 1.
Code:
*Simplified.
Load:
STORE SQLSTRINGCONNECT('Driver={SQL SERVER};Server=N103;Database=Development;uid=User;pwd=User');
TO tConnection
IF tConnection < 0
= MESSAGEBOX('Cannot make connection', 16, 'SQL Connect Error')
ENDIF
Thisform.pConnection = tConnection
Method:
*Simplified.
*A filter is built called lcFilter
thisform.grdData.recordsource = SPACE(0)
tConnection = Thisform.pConnection
SQLEXEC(tConnection, 'SELECT ReworkNumber, SupplierCode, SupplierName, ;
PartNumber, PartName, Kanban, Problem, ;
Model, DateCreated ;
FROM Tally ;
&lcFilter ORDER BY ReworkNumber DESC ', 'rvLineCall')
*This works fine. It you precede more than one field name with the *table name it bombs.
*If you try to use more than one table it bombs.
*It works great for one table!
Next thought: Use VFP with ADO. I have done this for a number of years again with one table.
Scenario 2.
Code:
*Simplified.
Load:
STORE SQLSTRINGCONNECT('Driver={SQL SERVER};Server=N103;Database=Development;uid=User;pwd=User');
TO tConnection
IF tConnection < 0
= MESSAGEBOX('Cannot make connection', 16, 'SQL Connect Error')
ENDIF
Thisform.pConnection = tConnection
Method:
*Simplified.
*A filter is built called sString
thisform.grdData.recordsource = SPACE(0)
tConnection = Thisform.pConnection
# DEFINE adOpenStatic 3
# DEFINE adLockOptimistic 3
* Create object...
oRecordSet = CreateObject("adodb.RecordSet")
oRecordSet.Open(sString, tConnection, adOpenStatic,adLockOptimistic)
*Create a Cursor
*Fill Cursor from a For Loop.
Scenario 2 has worked in the past with single tables using ODBC but this does not work. I have considered a remote view but will save that till last.
Thank you for any thoughts or ideas.
Tom
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement