delphi操作SQL+ACCESS数据库

unit Unit1;

interface

uses

Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,

Dialogs, StdCtrls, xmldom, XMLIntf, msxmldom, XMLDoc, ComCtrls, DB,

DBTables, Mask, DBCtrls, Grids, DBGrids, ADODB, ExtCtrls, inifiles ;

type

TForm1 = class(TForm)

Button1: TButton;

XMLDocument1: TXMLDocument;

ListView1: TListView;

Button2: TButton;

number: TEdit;

Label1: TLabel;

Memo1: TMemo;

Edit2: TEdit;

Label2: TLabel;

Label3: TLabel;

ComboBox1: TComboBox;

Edit4: TEdit;

Label4: TLabel;

Button3: TButton;

Label5: TLabel;

Edit1: TEdit;

Label6: TLabel;

Edit6: TEdit;

ListBox1: TListBox;

ListBox2: TListBox;

Label7: TLabel;

ListView2: TListView;

Label8: TLabel;

Edit3: TEdit;

Button4: TButton;

Label9: TLabel;

Edit8: TEdit;

Edit9: TEdit;

Edit10: TEdit;

OpenDialog1: TOpenDialog;

Label10: TLabel;

Label11: TLabel;

Timer1: TTimer;

Button5: TButton;

Button6: TButton;

ADOConnection2: TADOConnection;

ADOQuery1: TADOQuery;

ADOConnection1: TADOConnection;

ADOQuery2: TADOQuery;

Button7: TButton;

procedure Button1Click(Sender: TObject);

procedure AddNewAttribute(ANode :IXMLNode; AValue :WideString);

procedure ComboBox1Change(Sender: TObject);

procedure Button3Click(Sender: TObject);

ALastName, AName,

procedure Button4Click(Sender: TObject);

procedure Button2Click(Sender: TObject);

procedure Timer1Timer(Sender: TObject);

procedure numberKeyPress(Sender: TObject; var Key: Char);

procedure Button5Click(Sender: TObject);

procedure Button7Click(Sender: TObject);

procedure Button6Click(Sender: TObject);

private

{ Private declarations }

public

{ Public declarations }

end;

var

Form1: TForm1;

myinifile:TInifile;

implementation

uses unit2;

{$R *.dfm}

//==================================================================//

function GetStringCN(Source:string):string;

var

i:integer;

begin

delete(Source,1,11); {固定的格式:销售出库---}

i:=Length(Source);

while i>0 do

begin

if ByteType(Source,i) <> mbTrailByte then

begin

delete(Source,i,1);

dec(i);

end else

dec(i,2);

end;

Result:=Source;

end;

//======================================================//

//==================================================================//

function GetStringremark(Source:string):string;

begin

delete(Source,1,11); {固定的格式:销售出库---}

Result:=Source;

end;

//======================================================//

//==================================================================//

function Getsale(Source:string):string;

begin

delete(Source,1,18); {固定的格式:SalesWareHouseOut_}

//delete(Source,26,30); {固定的格式:SalesWareHouseOut_}

delete(Source,8,12); {固定的格式:SalesWareHouseOut_}

Result:=Source;

end;

//======================================================//

//function TestDll(i:integer):integer;stdcall; external 'TXTClass.dll';

function GetString(tStr,sStr,eStr :string;CheckCase :Boolean = True):string;

var

sPos,ePos :integer;

begin

Result := '';

if CheckCase then

sPos := Pos(sStr,tStr)

else

sPos := Pos(LowerCase(sStr),LowerCase(tStr));

if sPos > 0 then

tStr := Copy(tStr,sPos + Length(sStr),Length(tStr) - sPos - Length(sStr) + 1);

if CheckCase then

ePos := Pos(eStr,tStr)

else

ePos := Pos(LowerCase(eStr),LowerCase(tStr));

if ePos > 0 then

Result := Copy(tStr,1,ePos - 1)

else

Result := tStr;

end;

procedure TForm1.AddNewAttribute(ANode :IXMLNode; ALastName, AName, AValue :WideString);

var

index :Integer;

begin

index := ANode.AttributeNodes.IndexOf(ALastName);

if index > -1 then

ANode.AttributeNodes.Insert(index, ntAttribute))

XMLDocument1.CreateNode(AName,

else

ANode.AttributeNodes.Add(XMLDocument1.CreateNode(AName, ntAttribute));

ANode.SetAttribute(AName, AValue);

end;

procedure SaveUTF8File(fName, fText :string);

var

tmpFile, s :string;

f :TFileStream;

const

FileHead = #$EF#$BB#$BF;

begin

tmpFile := ExtractFilePath(fName) + ChangeFileExt(ExtractFileName(fName), '') + '_tmp' + ExtractFileExt(fName);

s := AnsiToUtf8(fText);

f := TFileStream.Create(tmpFile, fmCreate);

try

f.Write(FileHead[1], Length(FileHead));

f.Write(s[1], Length(s));

except

end;

FreeAndNil(f);

DeleteFile(fName);

RenameFile(tmpFile, fName);

end;

procedure TForm1.Button1Click(Sender: TObject);

var

Root, pNode, cNode :IXMLNode;

begin

//Memo1.Lines.Clear;

Memo1.Lines.LoadFromFile(''+edit4.Text+'');

ListView2.Items.Clear;

XMLDocument1.LoadFromFile(edit4.Text);

Root := XMLDocument1.DocumentElement;

pNode := Root.ChildNodes.First;

if (pNode <> nil) and (pNode.NodeName = 'Events') then

begin

pNode := pNode.ChildNodes.First;

if (pNode <> nil) and (pNode.NodeName = 'Event') then

begin

pNode := pNode.ChildNodes.First;

if (pNode <> nil) and (pNode.NodeName = 'DataField') then

begin

cNode := pNode.ChildNodes.First;

while cNode <> nil do

begin

if cNode.NodeName = 'Data' then

begin

AddNewAttribute(cNode, 'Actor', 'ToCorpID', Edit6.Text);

with ListView2.Items.Add do

begin

Caption := (cNode.Attributes['Code']);

SubItems.Add(cNode.Attributes['CorpOrderID']);

end;

end;

cNode := cNode.NextSibling;

end;

end;

end;

pNode := pNode.NextSibling;

end;

SaveUTF8File(Edit4.Text, XMLDocument1.XML.Text);

end;

procedure TForm1.ComboBox1Change(Sender: TObject);

var

dir:string;

sql:string; //定义字符串

sql1:string; //定义字符串

begin

getdir(0,dir); //获取路径

if ComboBox1.Text='销售出库' then begin

Edit3.Text:='SalesWareHouseOut_' end else if

(ComboBox1.Text='退货入库') or (ComboBox1.Text='销后退回') Edit3.Text:='ReturnWareHouseIn_' end else if

then begin

ComboBox1.Text='采购入库'then begin

Edit3.Text:='PurchaseWareHouseIn_' end else if

ComboBox1.Text='采购退回'then begin

Edit3.Text:='PurchaseWareHouseOut_' end ;

ADOConnection1.ConnectionString :='FILE NAME='+dir+'\\godos.udl';

ADOConnection1.Provider :=dir+'\\godos.udl';

ADOConnection1.Connected := true;

ADOConnection1.LoginPrompt := false;

//////////////////////////////////////////////////////////////

sql:='select BillDate, Number, CustName,BillName from VSel_OutInEditForm where'

+' IsNull(Verify,0)=1 and IsNull(Review,0)=1 and isnull(IsDelete,0)=0 '

+'and Number'+' =''' + trim(number.Text) + '''';

ADOQuery2.Close;

ADOQuery2.SQL.Clear;

ADOQuery2.SQL.Add(sql);

ADOQuery2.Open;

ADOQuery2.First;

if ADOQuery2.RecordCount > 0 then

begin

// edit10.Text:=GetStringremark(query1.fieldbyname('Remark').AsString); //过滤后的内容

// Edit1.Text:=GetStringCN(query1.fieldbyname('Remark').AsString);

// Edit10.Text:=Query1.fieldbyname('CustName').AsString;

Edit10.Text:=ADOQuery2.fieldbyname('CustName').AsString;

Edit1.Text:=ADOQuery2.fieldbyname('CustName').AsString;

Edit8.Text:=ADOQuery2.fieldbyname('Number').AsString;

Edit9.Text:=ADOQuery2.fieldbyname('BillName').AsString;

end;

ADOQuery2.Next;

//ADOQuery2.Close;

ADOConnection1.Connected := false;

sql1:='SELECT top 1 khid from ''%'+Edit1.text+'%''' ;

ADOQuery1.Close;

ADOQuery1.SQL.Clear;

ADOQuery1.SQL.Add(sql1) ;

ADOQuery1.Open;

ADOQuery1.First;

//while not ADOQuery2.Eof do

if ADOQuery1.RecordCount > 0 then

begin

Customers where like

khmc

//TempItem.SubItems.Add(ADOQuery1.Fields[i].AsString);

edit6.Text:=ADOQuery1.fieldbyname('khid').AsString;

end;

Button1.Enabled:=true;

ADOQuery1.Next;

// Edit6.Text:=Query3.fieldbyname('yaojianID').AsString;

// ADOQuery1.Close;

//edit4.Text:=Edit2.Text+Edit3.Text+number.Text+'.xml';

edit4.Text:=Edit2.Text+Edit3.Text+Edit8.Text+'.xml';

Button1.Enabled:=true;

end;

procedure TForm1.Button3Click(Sender: TObject);

var

filename:string;

begin

filename:=ExtractFilePath(paramstr(0))+'godos.ini';

myinifile:=TInifile.Create(filename);

OpenDialog1.Execute;

//edit2.Text:=OpenDialog1.GetNamePath;

edit2.Text:=ExtractFilePath(OpenDialog1.FileName);

///写入设置参数

myinifile.writestring('程序参数','路径',ExtractFilePath(OpenDialog1.FileName));

myinifile.Destroy;

end;

procedure TForm1.Button4Click(Sender: TObject);

var

tls: TStringList;

FindFileData: TWIN32FindData;

// FindFileData:string*11;

FindHanle: THandle;

FilePath, FileExt: String;

Filename:string;

begin

Filename:=ExtractFilePath(paramstr(0))+'godos.ini';

myinifile:=TInifile.Create(filename);

edit2.Text:= myinifile.readstring('程序参数','路径','');

ListView1.Clear;

ListView2.Clear;

ListBox1.Items.Clear;

ListBox2.Items.Clear;

tls := TStringList.Create;

try

FillChar(FindFileData, SizeOf(TWIN32FindData), 0); //文件大小

FilePath := edit2.Text; //目录

FileExt := '.xml'; //扩展名

// FindHanle := FindFirstFile(PAnsiChar(FilePath + '*' + FileExt), FindFileData);

FindHanle := FindFirstFile(PAnsiChar(FilePath +'SalesWareHouseOut_'+ '*' + FileExt), FindFileData);

if FindHanle <> INVALID_HANDLE_VALUE then begin

repeat

if (FindFileData.dwFileAttributes and FILE_ATTRIBUTE_DIRECTORY) <> FILE_ATTRIBUTE_DIRECTORY then begin

//过滤掉属性为“目录”，即“文件”

// tls.Add(FindFileData.cFileName);

// tls.Add(GetSale( FindFileData.cFileName)+chr(13) );

ListView1.Items.Add.Caption:=GetSale( FindFileData.cFileName)+chr(13); //显示到listview中

end;

until Not FindNextFile(FindHanle, FindFileData) ;

windows.FindClose(FindHanle);

end;

//ListView1.Items.Add.Caption:=(tls.Text+chr(13));

// ListView1.Items.Add.Caption:=tls.Text ;

form1.Label10.Caption:='待处理：'+inttostr(ListView1.Items.Count) +'条数据！ ';

Button2.Enabled:=true;

finally

tls.Free;

end;

end;

procedure TForm1.Button2Click(Sender: TObject);

begin

if ListView1.Items.Count=0 then

begin

Button2.Enabled:=false;

Button1.Enabled:=false;

timer1.Enabled:=false;

Button7.Visible:=false;

form1.Label10.Caption:='待处理：'+inttostr(ListView1.Items.Count) +'条数据！ ';

Button1.Click;

end

else

begin

listview1.SetFocus; // 选择第一条

//listview1.Items[i].Selected := True;// 选择第一条

listview1.Items[Listview1.Items.Count -1].Selected := True; //选择最后一条

// listview1.Items[Listview1.Items.Count -(Listview1.Items.Count

-1)].Selected := True;

Timer1.Enabled:=true;

Button7.Visible:=true;

Button7.Height:=76;

Button7.Left:=592;

Button7.Top:=37;

Button7.Width:=75;

Button2.Visible:=false;

end ;

end;

procedure TForm1.Timer1Timer(Sender: TObject);

var

i:integer;

// i,IstrIndex:Integer;

// SstrList,SextractList,SsubstrList:TStringList;

//c :char;

begin

For i:=0 to ListView1.Items.Count-1 Do

If ListView1.Items[i].Selected then //i=ListView1.Selected.index

begin

number.Text:=listview1.Items[i].Caption;

ListView1.Items.Delete(i); //删除当前选中行

form1.Label10.Caption:='待处理：'+inttostr(ListView1.Items.Count) +'条数据！ ';

Button1.Click;

Button2.Click;

if edit6.Text='' then begin

ListBox1.Items.Add(number.Text+' end ;

if POS('公司',edit10.Text)>0 then ListBox2.Items.Add(number.Text+' end

end;

end;

'+edit9.Text+' begin

'+edit9.Text+' '+edit10.Text)

'+edit10.Text)

procedure TForm1.numberKeyPress(Sender: TObject; var Key: Char);

begin

if Not (key in ['0'..'9']) then

key := #0;

end;

procedure TForm1.Button5Click(Sender: TObject);

begin

form2.Show;

end;

procedure TForm1.Button7Click(Sender: TObject);

begin

Timer1.Enabled:=false;

Button2.Visible:=true;

Button7.Visible:=false;

end;

procedure TForm1.Button6Click(Sender: TObject);

var

dir:string;

begin

getdir(0,dir); //获取路径

if ListBox1.Items.Count<>0 then

begin

ListBox1.Items.SaveToFile(dir+'未录入数据.txt');

end;

if ListBox2.Items.Count<>0 then

begin

ListBox2.Items.SaveToFile(dir+'未录入数据公司.txt');

end;

end;

end.