Desperate help needed - INSERT issues. - Thread - go-pugleaf

Thread View: borland.public.cppbuilder.database.ado

4 messages

[T] Back to Threads [V] Tree View [G] Group Articles

4 total messages Started by Mike Collins Sat, 22 Mar 2008 02:07

Desperate help needed - INSERT issues.

#17

Author: Mike Collins
Date: Sat, 22 Mar 2008 02:07

39 lines
2631 bytes

Hi all, first off, happy Easter.

Secondly, i having major issues with a really basic database.  I'm trying to 
do an INSERT statement and i keep getting an database syntax exceptions 
raise, which looks like this:

Project SupportMIS.exe raised exception class EOleException with message 
'Syntax error in INSERT INTO statement'. Process stopped. Use Step or Run to 
continue.


Firstly, I'm using ADO, I have TADOConnection and a TADOQuery and i'm 
connecting to an MS Access 2003 database, using the Microsoft Jet 4.0 OLE 
Provider.  I have three tables, Staff, SEN and StaffSEN.  Staff table 
contains information about staff and each are identified by a unique staffId 
string value, SEN contains information about course that can be taken and 
each course is identified by a unique auto-number (generated in Access). 
StaffSEN is used as a link in-between these tables.  It contains the 
staffId, senId, and two other string fields, description and action.

Now, I need to do an INSERT statement to link a staff member to a SEN 
course.  I have the staffId and senId, and so i;m attempting something like

INSERT INTO StaffSEN
(staffId, senId, description, action)
VALUES ("12345a", 3, "some description", "some action");

However, when i do this, i get an exception raised.  Initially I was using 
parameters but i've tried it by constructing the SQL as a string and also by 
hardcoding it.  I get the same result regardless.  If i remove the 
description and action values and references then it all works but i 
desperately need to be able to insert these additional fields.

If i us the original statement in MS Access then it works ok.

Any help or pointers would be much appreciated - i'm meant to have this 
completed by Tuesday.

Many thanks in advance,

Re: Desperate help needed - INSERT issues.

#18

Author: Clayton Arends
Date: Sat, 22 Mar 2008 03:08

16 lines
1501 bytes

"Mike Collins" <its@TheBottomOfThePost> wrote in message 
news:47e46771$1@newsgroups.borland.com...

> INSERT INTO StaffSEN
> (staffId, senId, description, action)
> VALUES ("12345a", 3, "some description", "some action");

"action" might be a Jet reserved word.  Try putting square brackets around 
it:

  INSERT INTO StaffSEN
  (staffId, senId, description, [action])
  ...

HTH,
Clayton

Re: Desperate help needed - INSERT issues.

#19

Author: Minas
Date: Sat, 22 Mar 2008 08:22

24 lines
1659 bytes

"Mike Collins" <its@TheBottomOfThePost> wrote:

> Hi all, first off, happy Easter.

Happy Easter Mike,

> Project SupportMIS.exe raised exception class EOleException with message 
> 'Syntax error in INSERT INTO statement'. Process stopped. Use Step or Run 
> to continue.
> Now, I need to do an INSERT statement to link a staff member to a SEN 
> course.  I have the staffId and senId, and so i;m attempting something 
> like
> INSERT INTO StaffSEN
> (staffId, senId, description, action)
> VALUES ("12345a", 3, "some description", "some action");
>
> However, when i do this, i get an exception raised.

What the exception says ? Do you use Open() method to execute the query ? 
if so , use ExecSQL() method instead, since the query is an INSERT statement

Best Regards

Minas

Re: Desperate help needed - INSERT issues.

#20

Author: Mike Collins
Date: Sat, 22 Mar 2008 13:31

26 lines
1802 bytes

Life saver.  Thank you so much Clayton.

Believe it or not, I did actually check that both "description" and "action" 
were not reserved words  but I only checked in MS Access - I never 
considered that the Jet engine might also reserve them (and it was 3am here 
in the UK).

To be honest, I'm doing this as a favour to someone else - database stuff in 
not really my bread & butter.  I would have been dubious of using these 
names for database fields anyway but it's a legacy system and I have to use 
what I'm given.

Thanks again,

Best regards for Easter,

Mike C


"Clayton Arends" <nospam_claytonarends@hotmail.com> wrote in message 
news:47e47840$1@newsgroups.borland.com...
> "Mike Collins" <its@TheBottomOfThePost> wrote in message 
> news:47e46771$1@newsgroups.borland.com...
>
>> INSERT INTO StaffSEN
>> (staffId, senId, description, action)

Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads