数据库问题

Question

检查有没有数据，有则修改，无则添加
        with ADOQuery1 do
        begin   
          Close;
          SQL.Clear;
          SQLString :='select * from session where type=1 and Owner='+quotedstr(templogin.Accounts)+' and Name='+quotedstr(TempLogin.Name);
          SQL.Add(SQLString);
          Open;
        end;
        if ADOQuery1.RecordCount<>0 then
        begin
          with  ADOQuery1 do
          begin
            Close;
            SQL.Clear;
            SQLString:='UPDate Session SET LocalIP='+quotedstr(TempLogin.RemoteIP)+', LocalPort='+(IntToStr(TempLogin.RemotePort))+
                                          ', EngineServerIP='+quotedstr(TempLogin.ServerIP)+', EngineServerPort='+IntToStr(TempLogin.ServerPort)+', Online='+inttostr(1)+
                                          ' where type=1 and owner='+quotedstr(TempLogin.Accounts)+' and name='+quotedstr(TempLogin.Name);
            SQL.Add(SQLString);
            ExecSQL;
          end;
        end
        else
        begin
          with ADOQuery1 do
          begin
            Close;
            SQL.Clear;
            SQLString :='Insert into Session (type, Owner, Name, LocalIP, LocalPort, EngineServerIP, EngineServerPort, Online) VALUES '+'('+
                                             IntToStr(1)+','+quotedstr(TempLogin.Accounts)+','+quotedstr(TempLogin.Name)+','+quotedstr(TempLogin.RemoteIP)+','+
                                             IntToStr(TempLogin.RemotePort)+','+quotedstr(TempLogin.ServerIP)+','+inttostr(TempLogin.RemotePort)+','+inttostr(1)+')';
            SQL.Add(SQLString);
            ExecSQL;
          end;
        end;
修改
      with ADOQuery1 do
      begin
        Close;
        SQLstring :='update session set Online='+inttostr(0)+'where Owner='+quotedstr(Accounts)+'and Name='+quotedstr(RName);
        SQL.Add(SQLstring);
        ShowMsg(SQLstring);
        ExecSQL;
      end;
为什么修改数据的数据的时候，会自动添加一行呢？就等于又执行了一次：UPDate Session SET
哪里错误啊？还有，我刚学数据库，这样写代码有错误吗？

Answer 1

抱歉，我不懂sql方面的东西。但是，若换我写以上代码将是这样：
var recount:integer;
begin
        with  ADOQuery1 do
        begin
          Close;
          SQL.Text:='select * from session where type=1 and Owner='+quotedstr(templogin.Accounts)+' and Name='+quotedstr(TempLogin.Name);
          Open;
          recount:=RecordCount;
          Close;
          if recount>0 then
            SQL.Text:='UPDate Session SET LocalIP='+quotedstr(TempLogin.RemoteIP)+', LocalPort='+(IntToStr(TempLogin.RemotePort))+
                                          ', EngineServerIP='+quotedstr(TempLogin.ServerIP)+', EngineServerPort='+IntToStr(TempLogin.ServerPort)+', Online='+inttostr(1)+
                                          ' where type=1 and owner='+quotedstr(TempLogin.Accounts)+' and name='+quotedstr(TempLogin.Name)
          else
            SQL.Text:='Insert into Session (type, Owner, Name, LocalIP, LocalPort, EngineServerIP, EngineServerPort, Online) VALUES '+'('+
                                             IntToStr(1)+','+quotedstr(TempLogin.Accounts)+','+quotedstr(TempLogin.Name)+','+quotedstr(TempLogin.RemoteIP)+','+
                                             IntToStr(TempLogin.RemotePort)+','+quotedstr(TempLogin.ServerIP)+','+inttostr(TempLogin.RemotePort)+','+inttostr(1)+')';
          ExecSQL;
        end;这样会简洁、易读。

Answer 2

若要核实sql语句也不难：
 ShowMsg(ADOQuery1.SQL.Text);

Answer 3

可能是主键值问题，因为Updata在无主键时，就是Insert。

Answer 4

程序把几个sql分开执行，容易在并发（多个用户同时操作）时导致前面的判断与事实不符（其它人正好在插入了）