Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

insert error: Column not allowed here

Posted on 2005-05-15
18
Medium Priority
?
246 Views
Last Modified: 2010-04-05
I have to insert row in Oracle table

I use AdoCommand1.Text:
insert into TP_DETPTK Values (1051, 105120020100000025365, 105120020100000022888, '+Quotedstr(Date(Now()))+', PT, 99, W, '+Quotedstr(DateTime(Now()))+')

I tried:
insert into TP_DETPTK Values (1051, 105120020100000025365, 105120020100000022888, Upper(12/05/2005), Upper(PT), 99, Upper(V), Upper(12/05/2005))

But i got same problem
How to solve this error?
0
Comment
Question by:selas
  • 7
  • 6
  • 3
  • +1
18 Comments
 
LVL 6

Expert Comment

by:vadim_ti
ID: 14005970
i am not sure but may be you have a problem with quotes

lets say you use "somestring" and you need to use 'somestring'
0
 
LVL 22

Expert Comment

by:Mohammed Nasman
ID: 14006212
try to quote the letters with double single quotation

', '''PT'', ''99'', ''W'', '

or use Quotedstr
0
 

Author Comment

by:selas
ID: 14006610
i try this:
insert into TP_DETPTK Values (''1051'', ''105120020100000025365'', ''105120020100000022888'', ''12/05/2005'', ''PT'', ''99'', ''V'', ''12/05/2005'')

Now i get: mising comma
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 6

Expert Comment

by:vadim_ti
ID: 14006622
what is your TP_DETPTK  table structure?
0
 
LVL 22

Expert Comment

by:Mohammed Nasman
ID: 14006623
could you show the table structure?
and are you using (") or (' ') without space
0
 
LVL 22

Expert Comment

by:Mohammed Nasman
ID: 14006631
vadim_ti, you faster in second ;)
0
 

Author Comment

by:selas
ID: 14010468
tp_detptk structure:

id_casino      integer
id_cli            varchar2(21)
id_typpts      char(21)
dat_ses        date
cod_typmvt   varchar2(5)
ptsnbr          number(14,4)
boo_ok        char(1)
lstchg_ts      date

i use ' ' without space
0
 
LVL 22

Expert Comment

by:Mohammed Nasman
ID: 14010592
try

insert into TP_DETPTK Values (1051, ''105120020100000025365'', ''105120020100000022888'', ''12/05/2005'', ''PT'', 99, ''V'', ''12/05/2005'')
0
 
LVL 6

Expert Comment

by:vadim_ti
ID: 14010602
if this
insert into TP_DETPTK Values (''1051'', ''105120020100000025365'', ''105120020100000022888'', ''12/05/2005'', ''PT'', ''99'', ''V'', ''12/05/2005'')

is a text you enter to AdoCommand1.Text property try it with a single '

if you make it in run time

make this way

s := 'insert into TP_DETPTK Values (''1051'', ''105120020100000025365'', ''105120020100000022888'', ''12/05/2005'', ''PT'', ''99'', ''V'', ''12/05/2005'')';

AdoCommand1.Text := s;
0
 

Author Comment

by:selas
ID: 14011943
if i try
insert into TP_DETPTK Values (1051, ''105120020100000025365'', ''105120020100000022888'', ''12/05/2005'', ''PT'', 99, ''V'', ''12/05/2005'')

i get missing comma
0
 
LVL 22

Expert Comment

by:Mohammed Nasman
ID: 14012028
mmm, could you try to test it using Sql plus?
i created the table as you describe and tested it, worked fine with me
0
 
LVL 22

Expert Comment

by:Mohammed Nasman
ID: 14012088
ok, seems you trying to test using Sql plus
when you using Sql plus you have to single qoutation as:

insert into TP_DETPTK Values (1051, '105120020100000025365', '105120020100000022888', '12/05/2005', 'PT', 99, 'V', '12/05/2005')
0
 

Author Comment

by:selas
ID: 14012473
I know the answer

  ShortDateFormat := 'yyyy/mm/dd';
  AdoCommand1.CommandText := '';
  AdoCommand1.CommandText := 'insert into TP_DETPTK Values (1051, '+LabeledEdit2.Text+', '+DBlookupComboBox2.Text+', '''+DatetoStr(now())+''', ''PT'', '+LabeledEdit5.Text+', ''Y'', '''+DateTimetoStr(now())+''')';
  AdoCommand1.Execute;

I dont know why, but dateformat in oracle must be different :/
0
 

Author Comment

by:selas
ID: 14012501
but now i have second problem: I CANT INSERT SECOND ROW.... WHY ???

Constrain:

DROP TABLE TP_DETPTK CASCADE CONSTRAINTS ;

CREATE TABLE TP_DETPTK (
  ID_CASINO   INTEGER       NOT NULL,
  ID_CLI      VARCHAR2 (21)  NOT NULL,
  ID_TYPPTS   CHAR (21)     NOT NULL,
  DAT_SES     DATE          NOT NULL,
  COD_TYPMVT  VARCHAR2 (5)  NOT NULL,
  PTSNBR      NUMBER (14,4),
  BOO_OK      CHAR (1)      DEFAULT '0',
  LSTCHG_TS   DATE,
  CONSTRAINT PK_TP_DETPTK
  PRIMARY KEY ( ID_CASINO, ID_CLI, ID_TYPPTS, DAT_SES, COD_TYPMVT ) ) ;
0
 
LVL 22

Accepted Solution

by:
Mohammed Nasman earned 2000 total points
ID: 14012577
seems you violate your key Primary Key

you can't post the same values twice

and to know your date format in server, try to run this statement from sql plus and see the results

select sysdate from dual;
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 14017450
>I dont know why, but dateformat in oracle must be different :/
u can use the oracle TO_DATE function to transform "any" stringdate into a valid date

like

...,TO_DATE('17.05.2005','DD.MM.YYYY'),.....

meikl ;-)
0
 

Author Comment

by:selas
ID: 14017458
but i can't change id_casino to diferent number
so i can only add one row?
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 14017506
as far as i see your PK is
>PRIMARY KEY ( ID_CASINO, ID_CLI, ID_TYPPTS, DAT_SES, COD_TYPMVT )

this means all this fields makes a record unique (-> no duplicates),
so u can keep the id_casino to the same value as before,
if another field becomes another value

meikl ;-)
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Screencast - Getting to Know the Pipeline
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question