//模糊查询 procedure TForm1.edtcxChange(Sender: TObject); var sqlstr :String; begin if edtcx.SelStart = length(edtcx.Text) then begin try if cbocx.Text = '公告主题' then begin sqlStr := 'select FBZT ,RQSJ,FBNR from ggao where FBZT like ''%'+edtcx.Text+'%'''; end; if cbocx.Text ='发布日期' then begin sqlStr := 'select FBZT ,RQSJ,FBNR from ggao where RQSJ like ''%'+edtcx.Text+'%'''; end; Except end; end; if edtcx.Text=''then begin try DBGrideh1.DataSource.DataSet:=nil; Except end; exit; end; try adoquery1.Close; adoquery1.SQL.Clear; adoquery1.SQL.Add(sqlStr); adoquery1.ExecSQL; adoquery1.Open; DBgrideh1.DataSource := TDataSource.Create(nil); DBGrideh1.DataSource.DataSet := adoquery1; Except end; end;
各TADOQuery及TDataSourse放在同一界面上,引用Datamoudle中TADOConnection 數據庫備份代碼: procedure Tsys_datamodule.DataBaseBack; var FileName:string; ADOQ:TADOQuery; begin ADOQ:=TADOQuery.Create(nil); ADOQ.Connection:=sys_datamodule.con1; if MessageDlg('你確定要備份數據庫嗎?',mtInformation,[mbYes,mbNo],0)=idyes then begin try if SaveDialog1.Execute then begin FileName:=SaveDialog1.FileName; if (Copy(FileName,Length(FileName)-3,4)<>'.BAK') or (Copy(FileName,Length(FileName)-3,4)<>'.bak') then FileName:=FileName+'.BAK'; ADOQ.SQL.Text:='Use Master Backup Database MES to Disk='''+ FileName+''' Use MES'; ADOQ.ExecSQL; MessageDlg('數據庫備份成功!',mtInformation,[mbOK],0); end; except MessageDlg('數據庫備份失敗!',mtInformation,[mbOK],0); Exit; end; end; end;數據庫還原代碼:procedure Tsys_datamodule.RestoreBaseBack; var StrFile:string; ADOQ:TADOQuery; begin ADOQ:=TADOQuery.Create(nil); ADOQ.Connection:=sys_datamodule.con1; if MessageDlg('你確定要還原數據庫嗎?',mtInformation,[mbYes,mbNo],0)=idyes then begin try if OpenDialog1.Execute then begin StrFile:=OpenDialog1.FileName; ADOQ.SQL.Text:='Use Master Restore Database MES From Disk='''+ StrFile+''' Use MES'; ADOQ.ExecSQL; MessageDlg('數據庫還原成功!',mtInformation,[mbOK],0); end; except MessageDlg('數據庫還原失敗!',mtInformation,[mbOK],0); Exit; end; end; end; SQL存儲過程: USE [MES] GO /****** Object: StoredProcedure [dbo].[SP_WKO_GETDATA] Script Date: 10/17/2008 09:01:47 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[SP_WKO_GETDATA] AS begin delete from WKO_SCHEDULE where dateadd(dd,60,ImportDate)<=getdate() declare @WKO_ORDERNO nvarchar(50),@WKO_STATUS nvarchar(50),@WKO_ITEMNO nvarchar(50),@WKO_TYPE nvarchar(10),@WKO_DESC nvarchar(200),@WKO_QTY int,@WKO_CBZX nvarchar(20),@WKO_GX int,@WKO_TIMES float,@WKO_ODATE smalldatetime,@WKO_BZGS float declare Insertcursor cursor for SELECT a.WKO_ORDERNO,a.WKO_STATUS,a.WKO_ITEMNO,a.WKO_TYPE,a.WKO_DESC,a.WKO_QTY,a.WKO_CBZX,a.WKO_GX,a.WKO_TIMES,a.WKO_ODATE FROM (select * from TMP_WKO_ORDER where WKO_TYPE<>'WO' and WKO_TYPE<>'WW' ) a open Insertcursor fetch next from Insertcursor into @WKO_ORDERNO,@WKO_STATUS,@WKO_ITEMNO,@WKO_TYPE,@WKO_DESC,@WKO_QTY,@WKO_CBZX,@WKO_GX,@WKO_TIMES,@WKO_ODATE while(@@fetch_status=0) begin if not exists(select * from WKO_SCHEDULE where WKO_ORDERNO=@WKO_ORDERNO and WKO_ITEMNO=@WKO_ITEMNO and WKO_CBZX=@WKO_CBZX and WKO_GX=@WKO_GX) insert into WKO_SCHEDULE(WKO_ORDERNO,WKO_STATUS,WKO_ITEMNO,WKO_TYPE,WKO_DESC,WKO_QTY,WKO_CBZX,WKO_GX,WKO_TIMES,WKO_ODATE)values( @WKO_ORDERNO,@WKO_STATUS,@WKO_ITEMNO,@WKO_TYPE,@WKO_DESC,@WKO_QTY,@WKO_CBZX,@WKO_GX,@WKO_TIMES,@WKO_ODATE) fetch next from Insertcursor into @WKO_ORDERNO,@WKO_STATUS,@WKO_ITEMNO,@WKO_TYPE,@WKO_DESC,@WKO_QTY,@WKO_CBZX,@WKO_GX,@WKO_TIMES,@WKO_ODATE
IF EXISTS (SELECT * FROM PRO_QUANTITY WHERE PN=@WKO_ITEMNO AND GX=@WKO_GX AND CBZX=@WKO_CBZX) BEGIN select @WKO_BZGS=(SELECT NEWHR/1000 FROM PRO_QUANTITY WHERE PN=@WKO_ITEMNO AND GX=@WKO_GX AND CBZX=@WKO_CBZX) update WKO_SCHEDULE set WKO_TIMES=@WKO_BZGS WHERE WKO_ITEMNO=@WKO_ITEMNO AND WKO_GX=@WKO_GX AND WKO_CBZX=@WKO_CBZX END update WKO_SCHEDULE set WKO_MATIMES=WKO_TIMES / 25 WHERE WKO_ITEMNO=@WKO_ITEMNO AND WKO_GX=@WKO_GX AND WKO_CBZX=@WKO_CBZX end close Insertcursor deallocate Insertcursor end
ADOQuery1.Clear;//?ADOQuery1.Text.Clear;
ADOQuery1.SQL.Text:='your sql';
ADOQuery1.Open;//返回RecordSet,或者 ADOQuery1.Execute;不返回RecordSet不用每个都放一个控件。
有几个RecordSet就放几个。
你连接SQL Server的时候,就能看到选择存储过程名了。
每次用到的时候都
ADOQuery1.Close;
ADOQuery1.SQL.Text:='sql';
ADOQuery1.Open;
就可以了
如果你的数据集在别的时候用的到,必须加一个新的ADOQuery1
procedure TForm1.edtcxChange(Sender: TObject);
var
sqlstr :String;
begin
if edtcx.SelStart = length(edtcx.Text) then
begin
try
if cbocx.Text = '公告主题' then
begin
sqlStr := 'select FBZT ,RQSJ,FBNR from ggao where FBZT like ''%'+edtcx.Text+'%''';
end;
if cbocx.Text ='发布日期' then
begin
sqlStr := 'select FBZT ,RQSJ,FBNR from ggao where RQSJ like ''%'+edtcx.Text+'%''';
end;
Except
end;
end;
if edtcx.Text=''then
begin
try
DBGrideh1.DataSource.DataSet:=nil;
Except
end;
exit;
end;
try
adoquery1.Close;
adoquery1.SQL.Clear;
adoquery1.SQL.Add(sqlStr);
adoquery1.ExecSQL;
adoquery1.Open;
DBgrideh1.DataSource := TDataSource.Create(nil);
DBGrideh1.DataSource.DataSet := adoquery1;
Except
end;
end;
就可以了.
procedure Tsys_datamodule.DataBaseBack;
var
FileName:string;
ADOQ:TADOQuery;
begin
ADOQ:=TADOQuery.Create(nil);
ADOQ.Connection:=sys_datamodule.con1;
if MessageDlg('你確定要備份數據庫嗎?',mtInformation,[mbYes,mbNo],0)=idyes then
begin
try
if SaveDialog1.Execute then
begin
FileName:=SaveDialog1.FileName;
if (Copy(FileName,Length(FileName)-3,4)<>'.BAK') or (Copy(FileName,Length(FileName)-3,4)<>'.bak') then
FileName:=FileName+'.BAK';
ADOQ.SQL.Text:='Use Master Backup Database MES to Disk='''+ FileName+''' Use MES';
ADOQ.ExecSQL;
MessageDlg('數據庫備份成功!',mtInformation,[mbOK],0);
end;
except
MessageDlg('數據庫備份失敗!',mtInformation,[mbOK],0);
Exit;
end;
end;
end;數據庫還原代碼:procedure Tsys_datamodule.RestoreBaseBack;
var
StrFile:string;
ADOQ:TADOQuery;
begin
ADOQ:=TADOQuery.Create(nil);
ADOQ.Connection:=sys_datamodule.con1;
if MessageDlg('你確定要還原數據庫嗎?',mtInformation,[mbYes,mbNo],0)=idyes then
begin
try
if OpenDialog1.Execute then
begin
StrFile:=OpenDialog1.FileName;
ADOQ.SQL.Text:='Use Master Restore Database MES From Disk='''+ StrFile+''' Use MES';
ADOQ.ExecSQL;
MessageDlg('數據庫還原成功!',mtInformation,[mbOK],0);
end;
except
MessageDlg('數據庫還原失敗!',mtInformation,[mbOK],0);
Exit;
end;
end;
end;
SQL存儲過程:
USE [MES]
GO
/****** Object: StoredProcedure [dbo].[SP_WKO_GETDATA] Script Date: 10/17/2008 09:01:47 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_WKO_GETDATA] AS
begin
delete from WKO_SCHEDULE where dateadd(dd,60,ImportDate)<=getdate()
declare @WKO_ORDERNO nvarchar(50),@WKO_STATUS nvarchar(50),@WKO_ITEMNO nvarchar(50),@WKO_TYPE nvarchar(10),@WKO_DESC nvarchar(200),@WKO_QTY int,@WKO_CBZX nvarchar(20),@WKO_GX int,@WKO_TIMES float,@WKO_ODATE smalldatetime,@WKO_BZGS float
declare Insertcursor cursor for SELECT a.WKO_ORDERNO,a.WKO_STATUS,a.WKO_ITEMNO,a.WKO_TYPE,a.WKO_DESC,a.WKO_QTY,a.WKO_CBZX,a.WKO_GX,a.WKO_TIMES,a.WKO_ODATE FROM (select * from TMP_WKO_ORDER where WKO_TYPE<>'WO' and WKO_TYPE<>'WW' ) a
open Insertcursor
fetch next from Insertcursor into @WKO_ORDERNO,@WKO_STATUS,@WKO_ITEMNO,@WKO_TYPE,@WKO_DESC,@WKO_QTY,@WKO_CBZX,@WKO_GX,@WKO_TIMES,@WKO_ODATE
while(@@fetch_status=0)
begin
if not exists(select * from WKO_SCHEDULE where WKO_ORDERNO=@WKO_ORDERNO and WKO_ITEMNO=@WKO_ITEMNO and WKO_CBZX=@WKO_CBZX and WKO_GX=@WKO_GX)
insert into WKO_SCHEDULE(WKO_ORDERNO,WKO_STATUS,WKO_ITEMNO,WKO_TYPE,WKO_DESC,WKO_QTY,WKO_CBZX,WKO_GX,WKO_TIMES,WKO_ODATE)values(
@WKO_ORDERNO,@WKO_STATUS,@WKO_ITEMNO,@WKO_TYPE,@WKO_DESC,@WKO_QTY,@WKO_CBZX,@WKO_GX,@WKO_TIMES,@WKO_ODATE)
fetch next from Insertcursor into @WKO_ORDERNO,@WKO_STATUS,@WKO_ITEMNO,@WKO_TYPE,@WKO_DESC,@WKO_QTY,@WKO_CBZX,@WKO_GX,@WKO_TIMES,@WKO_ODATE
IF EXISTS (SELECT * FROM PRO_QUANTITY WHERE PN=@WKO_ITEMNO AND GX=@WKO_GX AND CBZX=@WKO_CBZX)
BEGIN
select @WKO_BZGS=(SELECT NEWHR/1000 FROM PRO_QUANTITY WHERE PN=@WKO_ITEMNO AND GX=@WKO_GX AND CBZX=@WKO_CBZX) update WKO_SCHEDULE set WKO_TIMES=@WKO_BZGS
WHERE WKO_ITEMNO=@WKO_ITEMNO AND WKO_GX=@WKO_GX AND WKO_CBZX=@WKO_CBZX
END
update WKO_SCHEDULE set WKO_MATIMES=WKO_TIMES / 25 WHERE WKO_ITEMNO=@WKO_ITEMNO AND WKO_GX=@WKO_GX AND WKO_CBZX=@WKO_CBZX
end
close Insertcursor
deallocate Insertcursor
end