INSERT INTO Paradox Table with Special Character on the Primary Key Field
We have a paradox Table with the following fields (Acct#,ClientName,LastName
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.
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.
ASKER
Nope, neither both of this work
ASKER
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. ;-)
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. ;-)
ASKER
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.CommandT
T.Clear()
adt.Fill(T)
sConnString if the Connection String with Paradox and works find for update, select and delete.
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.CommandT
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.CommandT
T.Clear()
adt.Fill(T)
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.CommandT
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.CommandT
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.CommandT
T.Clear()
adt.Fill(T)
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.CommandT
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.CommandT
T.Clear()
adt.Fill(T)
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.CommandT
T.Clear()
adt.Fill(T)
Note the double-quotes on line 1, and paired single-quotes on line 2.
Kent
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.CommandT
T.Clear()
adt.Fill(T)
Note the double-quotes on line 1, and paired single-quotes on line 2.
Kent
ASKER
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.CommandT
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.CommandT
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.CommandT
T.Clear()
adt.Fill(T)
Works, we try all this combination
Neither
Qry = "INSERT INTO status([Acct#]) values('123123123')"
Dim T As New DataTable
Dim adt As New OdbcDataAdapter(Qry, Me.sConnString)
adt.SelectCommand.CommandT
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.CommandT
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.CommandT
T.Clear()
adt.Fill(T)
Works, we try all this combination
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.CommandT
T.Clear()
adt.Fill(T)
ASKER
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
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?
Just for clarification purposes, this is an insert into the Paradox tables, isn't it?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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. ;-)
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. ;-)
[acct#]
or "acct#"
should do it