Microsoft KB Archive/169500: Difference between revisions

From BetaArchive Wiki
(importing KB archive)
 
m (Text replacement - """ to """)
 
(2 intermediate revisions by the same user not shown)
Line 64: Line 64:
NOTE: In Microsoft Visual FoxPro 5.0(a), driver-specific functions are included in the Functions drop-downs and the correct syntax is pasted into the Expression edit box (for example, '{fn FUNCTIONNAME()}').<br />
NOTE: In Microsoft Visual FoxPro 5.0(a), driver-specific functions are included in the Functions drop-downs and the correct syntax is pasted into the Expression edit box (for example, '{fn FUNCTIONNAME()}').<br />
<br />
<br />
In Microsoft Visual FoxPro 3.0b, the driver-specific functions return the incorrect syntax. The Expression Builder omits &quot;{fn }&quot; from the driver specific-functions.
In Microsoft Visual FoxPro 3.0b, the driver-specific functions return the incorrect syntax. The Expression Builder omits "{fn }" from the driver specific-functions.
=== Example ===
=== Example ===


This example requires access to the Microsoft SQL Server sample database called &quot;Pubs.&quot;<br />
This example requires access to the Microsoft SQL Server sample database called "Pubs."<br />
<br />
<br />
Modify the following code to point to your Microsoft SQL Server, with appropriate user ID (UID) and password (PWD) specified:
Modify the following code to point to your Microsoft SQL Server, with appropriate user ID (UID) and password (PWD) specified:
<pre class="codesample">  CREATE DATABASE TESTSQL
<pre class="codesample">  CREATE DATABASE TESTSQL
   CREATE CONNECTION TESTCONN CONNSTRING ;
   CREATE CONNECTION TESTCONN CONNSTRING ;
       &quot;Driver=SQL Server;Server=MySQLServer;UID=UserName;PWD=StrongPassword;DATABASE=PUBS&quot;
       "Driver=SQL Server;Server=MySQLServer;UID=UserName;PWD=StrongPassword;DATABASE=PUBS"
   CREATE SQL VIEW TESTVIEW REMOTE CONNECTION TESTCONN
   CREATE SQL VIEW TESTVIEW REMOTE CONNECTION TESTCONN
                 </pre>
                 </pre>
Line 78: Line 78:
<br />
<br />
Running the above code will do the following:
Running the above code will do the following:
* Create the database &quot;TestSQL.&quot;
* Create the database "TestSQL."
* Create a connection &quot;TestConn.&quot;
* Create a connection "TestConn."
* Create a View using the connection TestConn and display the Remote View Designer.
* Create a View using the connection TestConn and display the Remote View Designer.


Line 86: Line 86:
# On the '''Fields''' tab, include several fields from the table.
# On the '''Fields''' tab, include several fields from the table.
# Select the '''Filter''' tab.
# Select the '''Filter''' tab.
# Drop down the list under '''Fields''' and select '''&lt;Expression&gt;'''.
# Drop down the list under '''Fields''' and select '''<Expression>'''.
# In the '''Expression''' edit box enter the following: <span class="kbd userinput"> {fn SUBSTRING(AUTHORS.PHONE,1,1)}</span>
# In the '''Expression''' edit box enter the following: <span class="kbd userinput"> {fn SUBSTRING(AUTHORS.PHONE,1,1)}</span>
# Click '''Verify''' and ensure that the expression is valid.
# Click '''Verify''' and ensure that the expression is valid.
# Click '''OK'''.
# Click '''OK'''.
# In the Criteria column, select '''='''.
# In the Criteria column, select '''='''.
# In the '''Example''' column, type <span class="kbd userinput"> &quot;9&quot;</span> (with the quotes).
# In the '''Example''' column, type <span class="kbd userinput"> "9"</span> (with the quotes).
# Run the view.<br />
# Run the view.<br />
<br />
<br />

Latest revision as of 10:04, 21 July 2020

Knowledge Base


How To Call Driver Specific Functions in a Remote View

Article ID: 169500

Article Last Modified on 6/29/2004



APPLIES TO

  • Microsoft Visual FoxPro 3.0b Standard Edition
  • Microsoft Visual FoxPro 5.0 Standard Edition
  • Microsoft Visual FoxPro 5.0a



This article was previously published under Q169500

SUMMARY

Calling driver-specific functions in a remote view requires special syntax when used in the filter expression of a remote view.

MORE INFORMATION

When calling any driver-specific functions in the filter of a remote view, you must use the following syntax:

   {fn FUNCTIONNAME()}
                

If the correct syntax is not used, then the following error is produced when returning from the expression builder and the expression is not returned:

Invalid Subscript Reference

NOTE: In Microsoft Visual FoxPro 5.0(a), driver-specific functions are included in the Functions drop-downs and the correct syntax is pasted into the Expression edit box (for example, '{fn FUNCTIONNAME()}').

In Microsoft Visual FoxPro 3.0b, the driver-specific functions return the incorrect syntax. The Expression Builder omits "{fn }" from the driver specific-functions.

Example

This example requires access to the Microsoft SQL Server sample database called "Pubs."

Modify the following code to point to your Microsoft SQL Server, with appropriate user ID (UID) and password (PWD) specified:

   CREATE DATABASE TESTSQL
   CREATE CONNECTION TESTCONN CONNSTRING ;
      "Driver=SQL Server;Server=MySQLServer;UID=UserName;PWD=StrongPassword;DATABASE=PUBS"
   CREATE SQL VIEW TESTVIEW REMOTE CONNECTION TESTCONN
                

Note The user, Username, must have permissions to perform these operations on the database.

Running the above code will do the following:

  • Create the database "TestSQL."
  • Create a connection "TestConn."
  • Create a View using the connection TestConn and display the Remote View Designer.

After running the above program perform the following steps:

  1. Add the Authors table and close the Add Table dialog box.
  2. On the Fields tab, include several fields from the table.
  3. Select the Filter tab.
  4. Drop down the list under Fields and select <Expression>.
  5. In the Expression edit box enter the following: {fn SUBSTRING(AUTHORS.PHONE,1,1)}
  6. Click Verify and ensure that the expression is valid.
  7. Click OK.
  8. In the Criteria column, select =.
  9. In the Example column, type "9" (with the quotes).
  10. Run the view.


One record will be returned from the Authors table in the Pubs database.


Keywords: kbhowto kbinterop kbautomation KB169500