【转大富翁笔记】
1.首先对部门表加一个拼音码字段,然后建立函数
Create function fun_getPY
(
@str nvarchar(4000)
)
returns nvarchar(4000)
as
begin
declare @word nchar(1),@PY nvarchar(4000)
set @PY=''
while len(@str)>0
begin
set @word=left(@str,1)
--如果非汉字字符，返回原字符
set @PY = @PY+ (case when unicode(@word) between 19968 and 19968+20901
then (
select top 1 PY
from
(
select 'A' as PY, N'驁' as word
union all select 'B',N'簿'
union all select 'C',N'錯'
union all select 'D',N'鵽'
union all select 'E',N'樲'
union all select 'F',N'鰒'
union all select 'G',N'腂'
union all select 'H',N'夻'
union all select 'J',N'攈'
union all select 'K',N'穒'
union all select 'L',N'鱳'
union all select 'M',N'旀'
union all select 'N',N'桛'
union all select 'O',N'漚'
union all select 'P',N'曝'
union all select 'Q',N'囕'
union all select 'R',N'鶸'
union all select 'S',N'蜶'
union all select 'T',N'籜'
union all select 'W',N'鶩'
union all select 'X',N'鑂'
union all select 'Y',N'韻'
union all select 'Z',N'咗'
) T
where word>=@word collate Chinese_PRC_CS_AS_KS_WS
order by PY ASC
)
else @word
end)
set @str=right(@str,len(@str)-1)
end
return @PY
end
2.然后对部门表加个触发器
CREATE TRIGGER [NamePY] ON [dbo].[Department]
FOR INSERT, UPDATE
AS
declare @name varchar(100),@npy varchar(100),@cid char(10)
if(update(name))
begin
select @name=name,@cid=id from inserted
set @npy=dbo.fun_getPY(@name)
update Department set namepy=@npy where id=@cid
end
3.数据库部分完成, 接下来是用combobox实现自动下拉
用keydown事件, KEYPRESS肯定是不行的, 还要打开autodropdown
procedure TFrm_Employee.RzComboBox3KeyDown(Sender: TObject; var Key: Word;
Shift: TShiftState);
var
flag : boolean ;
KeyChar : Char ;
begin
flag := false ;
KeyChar := chr(key);
if ((key = VK_BACK) and (length(keyValue) > 0 )) then
begin
keyValue := copy(keyValue , 1 , length(keyValue) - 1 ) ; //keyValue 全局变量
flag := true ;
end
else if (KeyChar in ['A'..'Z', 'a'..'z']) then
begin
keyValue := keyValue + KeyChar ;
flag := true ;
end
else if (KeyChar = #13) then
flag := true ;
if flag then
begin
deptQry.Filtered := false ;
if flag and ((length(keyValue) > 0 )) then
begin
deptQry.Filter := 'namepy like ' + quotedstr(keyValue + '%') ;
deptQry.Filtered := true;
end;
deptQry.First ;
RzCombobox3.Clear;
while not deptQry.Eof do
begin
RzCombobox3.Items.Add('['+ deptQry.FieldByName('ID').AsString+']'+deptQry.FieldByName('Name').AsString); //部门筛选
deptQry.Next;
end;
RzCombobox3.Items.Insert(0,'全部部门');
end;
//RzCombobox3.Text:=keyValue; //防止倒序插入
RzCombobox3.SelStart:=length(keyValue);
end;
存在问题:
1.字符从头开始(倒序)插入, 如果用selStart则以前的字符串消失
如果用RzCombobox.text:=keyValue, 则还是会在之后再在头里插入
2.用回车不能选择