Spaces in Database Column - need SQL help 1

[CrashDome]

I am currently using Visual Studio .NET to create an application for an existing database we have.
It uses Pervasive v8 and I have successfully connected using the OleDBAdapters and writing to a dataset.
HOWEVER, the company that created the database (BTRIEVE) way back when used the term DATAFLEX RECNUM ONE for the primary keys in ALL of the tables.
When I try to SELECT that record (example: SELECT JOBS.DATAFLEX RECNUM ONE) I get an error
(Error in SELECT clause near 'RECNUM'.
Unable to parse query text.)
The statement was generated using the Query Builder and I tried to even add a [] around the column name to no avail.
If I remove the DATAFLEX RECNUM ONE from the statement (I have other columns selected) it works fine.
What can I do about the two spaces in the pk field name - short of renaming all of them? (whcih is not an option)

 

[CrashDome]

I forgot to mention:

I can successfully query the records using Pervasives SQL Query Analyser because it uses
SELECT "Dataflex Recnum One"
to tie the words together into one field name

 

[chiph]

Try surrounding the table name with square brackets:
[Dataflex Recnum One]

Chip H.

 

[CrashDome]

Qoute from first post:
"The statement was generated using the Query Builder and I tried to even add a [] around the column name to no avail."

 

[SHelton]

If the Query Analyser puts quotes around the column name, have you tried:

SELECT ""Dataflex RecNum One"" FROM...

or

SELECT ''Dataflex RecNum One'' FROM...

 

[CrashDome]

Yes... unfortunately I have and it didn't work either.

For some reason it is a problem only with Visual Studio. I know Pervasive was having some compatibility issues with previous versions and .Net. It looks like I may have to convert the server over to MySQL rather than Pervasive maybe? It may be that the Pervasive V8 OleDB Driver is causing my problem.

I could use ODBC, maybe that'll help? But I didn't want to have to set up an ODBC connection on every client either...
Looks like it'll be one of those weeks!

Thank you all for the help so far.

 

[JohnEfford]

Maybe try creating a view in pervasive which returns the same data as the table, if you need to update the column you could do this via stored procedures.

If you are using btrieve files and accessing them through pervasive SQL you will be using DDF files, it is possible to create another DDF for the table, which would then allow you to specify the column names. Go to New Table, place a tick in advanced options, name the table and then follow the wizard options to locate the Btrieve table.

Also report the problem to Pervasive, I reported a bug with thier OLE driver recently, Pervasive emailed me an updated version of the OLE driver within the fortnight.

Regards

 

[CrashDome]

I took your advice and set up a test view and test procedure.

Using the OleDB Provider, I could not see either the view or procedure. When I switched to the Microsoft OleDB provider for ODBC databases (and created an ODBC connection to the db) I could see the views and procedures, but I get errors all over the place when I try and use ODBC.

I wrote a messege in DevTalk at Pervasive and I hope it is a bug they can squish. If it is Visual Studio causing the issues I can imagine MS will tell me to change the column names (not possible because there are other applications currently using this database that I cannot change).

Thank you for the suggestion though...

 

[JohnEfford]

I have just double checked and my app does not use views so I cannot say if they work with the .net or not, I have tested them in visual basic version 6 though and they work ok, using OLE DB. All of my quieries in .net are perfomed using stored procedures. My only guess about what the problem could be is that you are not stating what the command type is, as shown below.

OLECommand.CommandType = CommandType.StoredProcedure

You may want to try my second suggestion to create a new DDF for the table, I know that this works as tested it out before making my post. Also if the applications you talk of are using the btrieve API, you may find that you can change the column names without impacting them (I currently have two P8 SQL tables which point to the same Btrieve file - both have different column names) as the pervasive SQL column names are stored in the DDF file for the table.

Good luck