?
Solved

ADO - MS ACCESS QUERY

Posted on 2005-03-10
11
Medium Priority
?
350 Views
Last Modified: 2010-04-05
experts,

i'm stuck with possibly an easy query.  here is a sample of my code:

   ids:=2;
   sentawait:=2;
   ADO.Close;
   ADO.SQL.Clear;
   ADO.SQL.Add(' INSERT INTO Reminders(status)');
   ADO.SQL.Add(' values(:P_STS)');
   //ADO.SQL.Add(' WHERE (reminderid = :ids)');
       ADO.Parameters.ParamByName('P_STS').Value := sentawait;
   ADO.ExecSQL;

this snippet works fine without the WHERE clause but when i try and use it i get 'paramter object improperly defined'.  any ideas???

cheers
0
Comment
Question by:Oli999
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 15

Expert Comment

by:mikelittlewood
ID: 13504950
You have not defined the second parameter :ids
0
 
LVL 15

Expert Comment

by:mikelittlewood
ID: 13504959
ADO.Parameters.ParamByName('P_STS').Value := ids
0
 
LVL 15

Assisted Solution

by:mikelittlewood
mikelittlewood earned 248 total points
ID: 13504964
oops

ADO.Parameters.ParamByName('ids).Value := ids
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Expert Comment

by:mikelittlewood
ID: 13504968
did you mean not to have a second parameter and put

   ids:=2;
   sentawait:=2;
   ADO.Close;
   ADO.SQL.Clear;
   ADO.SQL.Add(' INSERT INTO Reminders(status)');
   ADO.SQL.Add(' values(:P_STS)');
   //ADO.SQL.Add(' WHERE (reminderid = '+IntToStr(ids)+')');
      ADO.Parameters.ParamByName('P_STS').Value := sentawait;
   ADO.ExecSQL;
0
 
LVL 2

Author Comment

by:Oli999
ID: 13505549
  ids:=2;
   sentawait:=2;
   ADO.Close;
   ADO.SQL.Clear;
   ADO.SQL.Add(' INSERT INTO Reminders(status)');
   ADO.SQL.Add(' values(:P_STS)');
   ADO.SQL.Add(' WHERE (reminderid = '+inttostr(ids)+')');
       ADO.Parameters.ParamByName('P_STS').Value := sentawait;
   ADO.ExecSQL;


says i'm missing a semicolon at end of sql statement???
0
 

Accepted Solution

by:
bbop1322 earned 252 total points
ID: 13506043
Are you using this with Analysis Services against a SQL Server box? That's the only place i have ever seen a where clause used with an INSERT statement. If not an UPDATE statement would probably be the way to go here. Something like below;

ado.sql.Clear;
ado.sql.Add('UPDATE Reminders ');
ado.sql.Add('Set Status = :P_STS ');
ado.sql.Add('WHERE ReminderID = ' + IntToStr(ids));
ado.Parameters.ParamByName('P_STS').Value := sentawait;
ado.ExecSQL;

It might not be what you want but if so i hope it helps.
0
 
LVL 15

Expert Comment

by:mikelittlewood
ID: 13506069
I usually write it this way

with ADO do
  try
    if active then
      close;
    SQL.Text := 'INSERT INTO Reminders (status) ' +
                'VALUES (:P_STS) ' +
                'WHERE reminderid = ' + IntToStr(ids);
    Parameters.ParamByName('P_STS').Value := sentawait;
    ExecSQL
  finally
    Close
  end;
0
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 13506957
INSERT INTO ... WHERE ....

I am not quite sure you can use such statement. Basically the format is :
INSERT INTO table_name (Col1, Col2, Col3, ...) VALUES (Value1, Value2, Value3, ...)

As bbop1322 guessed, maybe you need UPDATE statement:

  UPDATE Reminders
  SET status = :P_STS
  WHERE reminderid = :ids
0
 
LVL 13

Expert Comment

by:BlackTigerX
ID: 13507469
very strange query you are trying to execute... what is the purpose of the query?
0
 
LVL 15

Expert Comment

by:mikelittlewood
ID: 13507873
Yeah it does sound like you are trying to do an update not an inert.
Maybe he meant to insert 2 values not just the one

INSERT INTO Reminders (Status, ReminderID)
VALUES (:P_STS, :ids)

Otherwise you need the update query supplied
0
 
LVL 11

Expert Comment

by:calinutz
ID: 13514821
Why do you need to use the params?

You can allways do this:

   ids:=2;
   sentawait:=2;
   ADO.Active:=false;
   ADO.SQL.Clear;
   ADO.SQL.Add(' UPDATE Reminders set status='+IntToStr(sentawait)+' where reminderid ='+IntToStr(ids));
   ADO.ExecSQL;

And I'm pretty sure you need an update, not an insert.
But you can do the same thing with the insert statement too.
Cheers
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

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…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses
Course of the Month12 days, 17 hours left to enroll

777 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