Problem with Insert query to Paradox table using C#

Question

I have Paradox 5.x tables i need to connect to in order to select and update. I am using OLEDBConnection.

selecting from the tables i have no problem. while trying to insert into the tables i met a problem when i entered hardcoded the fields namesi got an error: The INSERT INTO statement contains the following unknown field name: ... so i used OleDbDataReader.GetName(...) to get fields names. now i use the fields names recived from table in order to insert into table and i still get the same error.

I think the problem is with the field name: 'Truck #1 Serial Number' Table name: 'Vehicles'

I tried using [], ."", .[] and .[""].

I have read about the need of Borland engine but im not sure this is the issue. Thanks upfront.

-=Noam=-

p.s I cannot change name of tables since its a castumer DB i need to connect.

Im adding the C# code i use:

private static string createInsertQueryVehicle(string i_VehicleNumber, string i_VehicleMFG, string i_Truck1SerialNo, string i_Truck2SerialNo, string i_Truck3SerialNo)
        {
            string tryout = string.Format("INSERT INTO {0} ([{6}], [{7}], [{8}], [{9}], [{10}]) VALUES(RIGHT('{1}',10),'{2}','{3}','{4}','{5}')",
                TableName, Vnum, Vinfo, T1Serial, T2Serial, T3Serial, VnumFieldName, VinfoFieldName, T1SerialFieldName
                T2SerialFieldName,T3SerialFieldName);
            return tryout;
        }

at end tryout holds:

INSERT INTO Vehicles ([Vehicle Number], [Vehicle Mfg], [Truck #1 Serial Number], [Truck #2 Serial Number], [Truck #3 Serial Number]) VALUES(RIGHT('000000010001525',10),'קרונות משא','ר40011_1','ר40011_2','')

EDIT: Just wanted to add my solution at end: At the end the best solution i could get was to use accesses as the connection point using linked tablse to the paradox tables, at end handling it as an acceses DB..... Hope it helps someone.

This may be what you are looking for: stackoverflow.com/questions/116163/… — Kell
Kell: I dont know if its apply also for insert querys. I have seen this post and tried this version unfortunatly I still get the same Exception. — Bjorkson
Gabe i added the C# function and the query result. Exception recived: "The INSERT INTO statement contains the following unknown field name: 'Truck #1 Serial Number'. Make sure you have typed the name correctly, and try the operation again."} System.Data.Common.DbException {System.Data.OleDb.OleDbException}. I remind the the fields names are taken from the table with a query. — Bjorkson

Will Marcouiller Will Marcouiller · Accepted Answer · 2010-12-06T15:19:27

You need to use the quoted identifiers while having special character in field or table names. The double-quote (") should be what you're looking for.

Besides, I do believe that the Borland Database Engine is required in order to work against a Borland database such as Paradox. At least, that what I have always been told to, though I have never yet experienced such architecture, since I was using Delphi when working with Paradox.

Problem with Insert query to Paradox table using C#

3 Answers