PRB: Table Names Using Quoted Identifiers Do Not Open Properly

ID: Q194010

The information in this article applies to:

SYMPTOMS

When you open, or design against, existing tables in the Visual InterDev Data View, one of the following error occurs:

-Table 'tablename' no longer exists in the database.
-[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'tablename'.
-Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '<tablename with space>'. /<project>/_ScriptLibrary/Recordset.ASP, line 466


CAUSE

This occurs when you attempt to view or open a table with a space in its name in Design View, or if the "Use ANSI quoted identifiers" option is not selected when you create the data connection to the database.


RESOLUTION

To avoid this problem, you can use one of the following workarounds:

  1. Delete the existing data connection from the project.


  2. Ensure that the option "Use ANSI quoted identifier" is selected in the Add data connection wizard, and then add a new Data Connection to the database.


-or-
  1. Recreate the table with a valid SQL Server name (no spaces).



STATUS

This behavior is by design.


MORE INFORMATION

Steps to Reproduce Behavior

  1. Create a new Database Project.


  2. Add a new SQL Server data connection. Make sure that the option "Use ANSI quoted identifiers" is not selected. You can find this option in the Add Data Connection wizard, on the pane that has the option "Change the Default Database to:".


  3. In the Data View tab of Visual InterDev, locate or create a table with a space in its name.


  4. Right-click on the table and click Design or Open.


-or-
  1. Create a new non-database project and add an ASP page, or add an ASP page to an existing non-database project.


  2. Drag a recordset Design-Time control (DTC) to the <BODY> portion of the page. Right-click on the DTC and click Properties. Set the Connection to the DSN where the option "Use ANSI quoted identifiers" is not selected.


  3. Set the Database Object to TABLES, and set the Object Name to the name of the table that contains a space.


  4. Save the page and use the View in Browser feature. The following error appears: 
    
      Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
      [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect
      syntax near '<tablename with space>'.
      /<project>/_ScriptLibrary/Recordset.ASP, line 466


Additional query words: 


Keywords          : kberrmsg kbCtrl kbEEdition kbide kbSQLServ kbVisID600 kbGrpASP 
Version           : WINDOWS:6.0; winnt:6.5
Platform          : WINDOWS winnt 
Issue type        : kbprb

Last Reviewed: May 27, 1999