Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 352
  • Last Modified:

ADO - MS ACCESS QUERY

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
Oli999
Asked:
Oli999
2 Solutions
 
mikelittlewoodCommented:
You have not defined the second parameter :ids
0
 
mikelittlewoodCommented:
ADO.Parameters.ParamByName('P_STS').Value := ids
0
 
mikelittlewoodCommented:
oops

ADO.Parameters.ParamByName('ids).Value := ids
0
Independent Software Vendors: 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!

 
mikelittlewoodCommented:
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
 
Oli999Author Commented:
  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
 
bbop1322Commented:
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
 
mikelittlewoodCommented:
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
 
Ivanov_GCommented:
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
 
BlackTigerXCommented:
very strange query you are trying to execute... what is the purpose of the query?
0
 
mikelittlewoodCommented:
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
 
calinutzCommented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now