PRB: DBGrid Does Not Insert Record If Field Name Contains Dash
This article was previously published under Q165432
This article has been archived. It is offered "as is" and will no longer be updated.
When you try to add a row to an SQL Server table using DBGrid bound to aRemote Data Control, you receive the following error message if one of thetable's field names contains a "-":
ODBC error: 37000 [Microsoft][SQL Server Driver]Line2: Incorrect syntax
This problem occurs because the Remote Data Objects code is creating a SQLINSERT statement that doesn't put quotation marks around field names. TheINSERT statement would resemble the following:
INSERT INTO x (id, e-mail) VALUES (1, 'Eric')
If RDO generated an INSERT statement that used double quotes around thefield name, then the INSERT would work. The following syntax would work:
INSERT INTO x (id, "e-mail") VALUES (1, 'Eric')
Either rename the field so that it doesn't contain a hyphen or do theupdates manually (where the developer creates the proper SQL syntax).
This is by design. SQL Server does not support the use of dashes in itsidentifiers, including server names, database names, tables, views,columns, indexes, triggers, procedures, defaults, and rules.
Steps to Reproduce Behavior
- In an SQL Server database, create a table with a field that has a dash in it. For example, use the following script with MSQuery:
CREATE TABLE x (id INTEGER, "e-mail" VARCHAR(10)) // Note the double quotes to avoid the error with the dash.
- Create a Data Source that points to the correct database.
- Make an MFC Dialog application.
- Add the Microsoft Remote Data Control (MSRDC) to the dialog box that is being created. Set the following properties of the MSRDC control:
DataSourceName to your data source CursorDriver 1-ODBC cursor LockType 3-Optimistic concurrency ResultType 1-Create a Keyset cursor SQL SELECT * FROM x
- Add a DBGrid to the dialog box and set the following properties:
AllowAddNew True AllowUpdates True DataSource the name of the MSRDC
- Build and run the application. While running the application, try to insert a record in the grid, that is (1, Eric). Move off the record and the error message occurs.
RDO DBGrid kbvc500 kbmfc kbdatabase kbodbc kbdao
Article ID: 165432 - Last Review: 10/26/2013 09:24:00 - Revision: 3.0
Microsoft Visual C++ 5.0 Enterprise Edition, Microsoft Visual C++ 5.0 Professional Edition
- kbnosurvey kbarchive kbprb KB165432