怎样防止数据库中插入重复数据
如下代码是将excel中的数据插入到数据库中,编号是唯一的,为了防止重复插入,各位大侠有什么好的方法,谢谢.
ADOquery2.First;
for i := 1 to ADOquery2.RecordCount do //
begin
ADOquery1.Append;
ADOquery1.FieldValues['编号']:=ADOquery2.FieldValues['编号'];
ADOquery1.FieldValues['名称']:=ADOquery2.FieldValues['名称'];
ADOquery1.FieldValues['价格']:=ADOquery2.FieldValues['价格'];
ADOquery1.Post;
ADOquery2.Next;
[解决办法]
插入数据之前，先查询编号，看是否存在，如果存在不准插入；
如果不存在则允许插入；
以下是我有bde做过的例子：（其中物料编码是唯一）
unit Unit4;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, Grids, DBGrids, StdCtrls, DB, ADODB, Buttons, DBTables;
type
TForm4 = class(TForm)
Label1: TLabel;
Label2: TLabel;
Label3: TLabel;
Label4: TLabel;
Label5: TLabel;
Label6: TLabel;
Label7: TLabel;
Label8: TLabel;
Edit1: TEdit;
Edit2: TEdit;
Edit3: TEdit;
Edit4: TEdit;
Edit5: TEdit;
Edit6: TEdit;
Edit7: TEdit;
Edit8: TEdit;
DBGrid1: TDBGrid;
DataSource1: TDataSource;
Label9: TLabel;
BitBtn1: TBitBtn;
BitBtn2: TBitBtn;
BitBtn3: TBitBtn;
GroupBox1: TGroupBox;
Label10: TLabel;
Label11: TLabel;
Edit9: TEdit;
Edit10: TEdit;
BitBtn4: TBitBtn;
Table1: TTable;
Query1: TQuery;
Database1: TDatabase;
procedure FormCreate(Sender: TObject);
procedure BitBtn1Click(Sender: TObject);
procedure BitBtn3Click(Sender: TObject);
procedure BitBtn4Click(Sender: TObject);
// procedure RadioButton1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form4: TForm4;
implementation
{$R *.dfm}
procedure TForm4.FormCreate(Sender: TObject);
begin
Edit1.Clear;
Edit2.Clear;
Edit3.Clear;
Edit4.Clear;
Edit5.Clear;
Edit6.Clear;
Edit7.Clear;
Edit8.Clear;
Edit8.Enabled :=False;
Edit9.Clear;
Edit10.Clear;
Table1.Active :=True;
end;
procedure TForm4.BitBtn1Click(Sender: TObject);
begin
if Trim(Edit1.Text) ='' Then
begin
MessageDlg('物料编码不能为空',mtInformation,[mbYes],0);
Edit1.SetFocus;
Exit;
end;
if Trim(Edit2.Text) ='' Then
begin
MessageDlg('工艺路线不能为空',mtInformation,[mbYes],0);
Edit2.SetFocus;
Exit;
end;
if Trim(Edit3.Text) ='' Then
begin
MessageDlg('工艺路线不能为空',mtInformation,[mbYes],0);
Edit3.SetFocus;
Exit;
end;
if Trim(Edit4.Text) ='' Then
begin
MessageDlg('工序路线不能为空',mtInformation,[mbYes],0);
Edit4.SetFocus;
Exit;
end;
if Trim(Edit5.Text) ='' Then
begin
MessageDlg('工序名称不能为空',mtInformation,[mbYes],0);
Edit5.SetFocus;
Exit;
end;
if Trim(Edit6.Text) ='' Then
begin
MessageDlg('加工中心编码不能为空',mtInformation,[mbYes],0);
Edit6.SetFocus;
Exit;
end;
if Trim(Edit7.Text) ='' Then
begin
MessageDlg('使用标志不能为空',mtInformation,[mbYes],0);
Edit7.SetFocus;
Exit;
end;
with Query1 do
begin
Close;
SQL.Clear;
SQL.Add('select id_materiel from materiel;');
Open;
First;
While Not Eof do
begin
if Query1.FieldByName('id_materiel').AsString
= Trim(Edit1.Text) Then
begin
break;
end
else
begin
Next;
end;
Application.MessageBox('外码不存在','错误提示',0);
Edit1.SetFocus;
Exit;
end;
end;
with Query1 do
begin
Close;
SQL.Clear;
SQL.Add('select id_path from process_path;');
Open;
First;
While Not Eof do
begin
if Query1.FieldByName('id_path').AsString
<> Trim(Edit2.Text) Then
begin
Next;
end
else
begin
Application.MessageBox('主码不能相同','错误提示',0);
Edit2.SetFocus;
Exit;
end;
end;
end;
DataSource1.DataSet :=Table1;
Table1.Active :=True;
Table1.Append;
Table1.FieldByName('id_materiel').Value :=Edit1.Text;
Table1.FieldByName('id_path').Value :=Edit2.Text;
Table1.FieldByName('name_path').Value :=Edit3.Text;
Table1.FieldByName('id_procedure').Value :=Edit4.Text;
Table1.FieldByName('name_procedure').Value :=Edit5.Text;
Table1.FieldByName('id_work_center').Value :=Edit6.Text;
Table1.FieldByName('path_flag').Value :=StrToInt(Edit7.Text);
Table1.Post;
end;
procedure TForm4.BitBtn3Click(Sender: TObject);
begin
Self.Close;
end;
procedure TForm4.BitBtn4Click(Sender: TObject);
begin
if (Trim(Edit9.Text) ='')
AND (Trim(Edit10.Text) ='') Then
begin
Application.MessageBox('请选择查询方式','提示信息',0);
Edit9.SetFocus;
Exit;
end;
if (Trim(Edit9.Text) <>'')
AND (Trim(Edit10.Text) <>'') Then
begin
Application.MessageBox('请选择查询方式','提示信息',0);
Edit9.SetFocus;
Edit9.Clear;
Edit10.Clear;
Exit;
end;
if (Trim(Edit9.Text) <>'')
And (Trim(Edit10.Text) ='') Then
begin
DataSource1.DataSet :=Query1;
With Query1 do
begin
Close;
SQL.Clear;
SQL.Add('select * from process_path ');
SQL.Add('where id_path = '''+Trim(Edit9.Text)+'''');
Open;
if Query1.RecordCount =0 Then
begin
Application.MessageBox('没有记录存在','提示信息',0);
Edit9.SetFocus;
Edit9.Clear;
Exit;
end;
end;
Edit9.Clear;
end;
if (Trim(Edit9.Text) ='')
And (Trim(Edit10.Text) <>'') Then
begin
DataSource1.DataSet :=Query1;
With Query1 do
begin
Close;
SQL.Clear;
SQL.Add('select * from process_path ');
SQL.Add('where name_path = '''+Trim(Edit10.Text)+'''');
Open;
if Query1.RecordCount =0 Then
begin
Application.MessageBox('没有存在相关的记录','提示信息',0);
Edit10.SetFocus;
Edit10.Clear;
Exit;
end;
end;
Edit10.Clear;
end;
end;
end.
[解决办法]
ADOquery2.First;
for i := 1 to ADOquery2.RecordCount do //
begin
if not ADOquery1.Locate('编号',TRIM(ADOquery2.FieldValues['编号']),[]) then
begin
ADOquery1.Append;
ADOquery1.FieldValues['编号']:=ADOquery2.FieldValues['编号'];
ADOquery1.FieldValues['名称']:=ADOquery2.FieldValues['名称'];
ADOquery1.FieldValues['价格']:=ADOquery2.FieldValues['价格'];
ADOquery1.Post;
end;
ADOquery2.Next;
end;
