We help IT Professionals succeed at work.

INSERT INTO Paradox Table with Special Character on the Primary Key Field

Jesus Rodriguez
Jesus Rodriguez asked
on
We have a paradox Table with the following fields (Acct#,ClientName,LastName) where Acct# is the Primary Key and WE CANNOT CHANGE THE STRUCTURE OF THE TABLE. Acct# is a type A in Paradox that is like a VARCHAR type in SQL, you cna insert Numbers and Letters and we already double checked that the primary key is not duplicated.

We can Update, Delete or Modify any record but at the time that we want to insert a new record, we get the following error
"ERROR [HYS22] [Microsoft][ODBC Paradox Driver] The INSERT INTO statement contains the following unknown field name: 'Acct#'.  Make sure you have typed the name correctly, and try the operation again."

The query is as follows  --  INSERT INTO status([Acct#]) values('123123123'). We are almost sure that is because of the Special Character on the Acct# field.

Does nyone have a work around or a solution for this problem that can help us? We are migrating from Paradox to SQL and are struggling with this problem. Any help would be appretiated.
Comment
Watch Question

if you are using sql server then
[acct#]
or "acct#"

should do it

Author

Commented:
Nope, neither both of this work

Author

Commented:
We already try without lucky before we post this.
how about trying to create a view that will assign this column a different name and then try inserting into the view?
@momi_sabag,
Did you read the part about it being a Paradox database? ;-)
@k-designers,
From the error message, would I be correct in assuming that this is during an insert into the Paradox table or are you trying to use the Paradox driver to access SQL Server?
If it is the former, I'll have to check some Paradox manuals I have at home (yeah, I go that far back ;-).
If it is the latter, then you probably need to set up a different ODBC connection.  
In the mean time, check to make sure that your column is, indeed, "Acct#" and not, for instance "ACCT#".  Also, you might try using double quotes instead of square brackets.
However, as an aside, it is about time you moved off of Paradox. ;-)

Author

Commented:
I'm not using linked server. I'm using ODBC as this:

                Qp1 = "INSERT INTO status(Acct#)"
                Qp2 = " values('123123123')
                Qry = Qp1 + Qp2
                Dim T As New DataTable
                Dim adt As New OdbcDataAdapter(Qry, Me.sConnString)
                adt.SelectCommand.CommandTimeout = 6000
                T.Clear()
                adt.Fill(T)

sConnString if the Connection String with Paradox and works find for update, select and delete.
Note:  Your query as shown in the following is not the same as you indicated originally.
  Qp1 = "INSERT INTO status(Acct#)"
                Qp2 = " values('123123123')
                Qry = Qp1 + Qp2
                Dim T As New DataTable
                Dim adt As New OdbcDataAdapter(Qry, Me.sConnString)
                adt.SelectCommand.CommandTimeout = 6000
                T.Clear()
                adt.Fill(T)
 
Silly comment box posted instead of inserting a space! :-/
Qp1 = "INSERT INTO status(Acct#)"
               Qp2 = " values('123123123')
               Qry = Qp1 + Qp2
               Dim T As New DataTable
               Dim adt As New OdbcDataAdapter(Qry, Me.sConnString)
               adt.SelectCommand.CommandTimeout = 6000
               T.Clear()
               adt.Fill(T)
 
That will result in Qry having "INSERT INTO status(Acct#) values('123123123')".  Try the following:
Qp1 = "INSERT INTO status([Acct#])"
               Qp2 = " values('123123123')
               Qry = Qp1 + Qp2
               Dim T As New DataTable
               Dim adt As New OdbcDataAdapter(Qry, Me.sConnString)
               adt.SelectCommand.CommandTimeout = 6000
               T.Clear()
               adt.Fill(T)
 
 
Kent OlsenData Warehouse / Database Architect

Commented:
Hi K,

I thought that VB strings were delimited with single quotes.

                Qp1 = 'INSERT INTO status("Acct#")'
                Qp2 = ' values(''123123123'')'
                Qry = Qp1 + Qp2
                Dim T As New DataTable
                Dim adt As New OdbcDataAdapter(Qry, Me.sConnString)
                adt.SelectCommand.CommandTimeout = 6000
                T.Clear()
                adt.Fill(T)

Note the double-quotes on line 1, and paired single-quotes on line 2.


Kent

Author

Commented:
Ok let me be more specific

Neither
             Qry = "INSERT INTO status([Acct#]) values('123123123')"
              Dim T As New DataTable
              Dim adt As New OdbcDataAdapter(Qry, Me.sConnString)
              adt.SelectCommand.CommandTimeout = 6000
              T.Clear()
              adt.Fill(T)
or
             Qry = "INSERT INTO status("Acct#") values('123123123')"
              Dim T As New DataTable
              Dim adt As New OdbcDataAdapter(Qry, Me.sConnString)
              adt.SelectCommand.CommandTimeout = 6000
              T.Clear()
              adt.Fill(T)
or
              Qry = "INSERT INTO status(Acct_) values('123123123')"
              Dim T As New DataTable
              Dim adt As New OdbcDataAdapter(Qry, Me.sConnString)
              adt.SelectCommand.CommandTimeout = 6000
              T.Clear()
              adt.Fill(T)

Works, we try all this combination

Kent OlsenData Warehouse / Database Architect

Commented:

Try this one:


             Qry = "INSERT INTO status(""Acct#"") values('123123123')"
              Dim T As New DataTable
              Dim adt As New OdbcDataAdapter(Qry, Me.sConnString)
              adt.SelectCommand.CommandTimeout = 6000
              T.Clear()
              adt.Fill(T)

Author

Commented:
Nope, with double quotes doesn't work neither. As I told before, think that is a problem with the special Character and the key field. It's a nightmare. I saw some comments on a microsoft public group and still no one get a reply neither.

the link for this comment was here http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.data.odbc&tid=05a4ccec-93f3-4a7f-b32b-9c69ea4f31c5&cat=&lang=&cr=&sloc=&p=1
Just out of curiosity, there appears to have been data inserted in the past . . . how did it get into the tables?
Just for clarification purposes, this is an insert into the Paradox tables, isn't it?
Thanks to all for your help but we found the error. Was generated when they have required fields on the table. It doesn't matter that you have the primary key inserted correctly if you have other fields that are required.

 We didn't realize the required fields existed until we tried to modify the table structure directly.
k-designers,
So, after posting the error message and our working based on that (but without your table definition) under the assumption that you knew what your table's definition looks like, it turns out you didn't? ;-)
Okay, then.
A recommendation regarding the first step in debugging an INSERT SQL statement:
Look at the table definition and compare your INSERT statement to the table definition so that you can make sure that you are actually inserting data into all required columns and not trying to insert data into Identity columns.  (It also pays to make sure that column names, tablenames, and datatypes are correctly referenced. ;-)