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

x
?
Solved

insert and update

Posted on 2002-03-03
3
Medium Priority
?
271 Views
Last Modified: 2010-05-18
i have a text file which i want to input the data in it to a database, i means the new data be inserted and if there exists old ones, it be updated without the "key violation " error.

how to do it?

thansk

mzlxkats
0
Comment
Question by:mzlxkats
  • 2
3 Comments
 
LVL 6

Expert Comment

by:Stuart_Johnson
ID: 6838458
If your database is uniquely indexed, then you can do

if newdatakey=dbkey then
  update the database
else
  insert into database

If you're using a Database which has stored procedures, you can do this in one line of code in Delphi :)

Hope this helps,

Stu
0
 

Author Comment

by:mzlxkats
ID: 6840431
hello stu

do you have code more in detail?
0
 
LVL 6

Accepted Solution

by:
Stuart_Johnson earned 200 total points
ID: 6845331
if you have a table that looks like this:

MYKEY         FIELD1   FIELD2   FIELD3
1             DAH      0        TRUE
2             DOO      100      TRUE
3             DEE      -5       FALSE

and you want to add a new record if one doesn't already exist, BUT update a record where it does already exist, then try this:

procedure DoIt(var NewKey: Integer; Field1: String; Field2: Integer; Field3: Boolean);
var
  Exists: Boolean;

begin
  Exists := false;
  with TQuery.Create(nil) do
    try
      DatabaseName := 'WHATEVER';
      SQL.Add('select count(DBKEY) from TABLENAME');
      SQL.Add('where DBKEY=:DBKEY');
      Prepare;
      ParamByName('DBKEY').asInteger := NewKey;
      Open;  
      Exists := RecordCount > 0;
      Close;
      SQL.Clear;  
      if Exists then
        begin
          SQL.Add('update TABLENAME');
          SQL.Add('set FIELD1=:FIELD1,');
          SQL.Add('FIELD2=:FIELD2,');
          SQL.Add('FIELD3=:FIELD3');
          SQL.Add('where DBKEY=:DBKEY');
        end
      else
        begin
          SQL.Add('insert into TABLENAME');
          SQL.Add('DBKEY, FIELD1, FIELD2, FIELD3)');
          SQL.Add('values (:DBKEY, :FIELD1, :FIELD2, :FIELD2');
        end;
      Prepare;
      ParamByName('DBKEY').asInteger := NewKey;
      ParamByName('FIELD1').asString := Field1;
      ParamByName('FIELD2').asInteger := Field2;
      ParamByName('FIELD3').asBoolean := Field3;
      try
        ExecSQL;
      except
        on E: Exception do
          MessageDlg(E.Message, mtError, [mbOK], 0);
      end;
    finally
      Free; {TQuery}
    end;
end;

This is coded from memory, so it may need a bit of correcting to run (but not much!).

The stored procedure issue is something different again.  It depends on the database you're using.  I'm only familiar with MS-SQL Server, so if you're using that, I can definately post the SQL for that and the Delphi code.

Hope that helps,

Stu
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Integration Management Part 2
Suggested Courses
Course of the Month15 days, 16 hours left to enroll

580 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