如何处理ADO查询与结果与查询没有结果? [英] How to handle ADO Query with results vs. Query with no results?
问题描述
我使用MSSQL和ADO运行各种SQL语句。
代码序列如下所示:
aADOQuery.Active:= False;
aADOQuery.SQL.Text:='MY SQL STATEMENT';
aADOQuery.ExecSQL;
aADOQuery.Active:= True;
如果SQL返回结果为空,则最后一条语句将失败。注意:SQL语句来自用户正在键入SQL的备忘录。
$ b如何检查这种情况以避免运行时错误?$ b
如果您的查询返回一个记录集( SELECT
语句),您应该 / strong>使用 ExecSQL
,但只是 aADOQuery.Open
或 Active:= True
。
对于不返回记录集的查询,例如 INSERT
/ 更新
/ DELETE
,使用 ExecSQL
。在大多数情况下,您将通过查询返回 aADOQuery.RowsAffected
。
您应该使用的其他SQL语句 ExecSQL
是 CREATE
/ ALTER
/ DROP
/ EXEC
etc ...(no RowsAffected
return in this case) p>
如果查询不返回游标到数据(如 INSERT
语句),尝试打开
或将这样的 TDataSet
设置为 Active
将失败。
您可以使用 ADOConnection.Execute
而不是 TADOQuery
执行您的命令文本,然后检查是否有有效的
Recordset
从 ADOConnection
。
在 ADOConnection.OnExecuteComplete
你可以这样做:
procedure TForm1.ADOConnection1ExecuteComplete(Connection:TADOConnection;
RecordsAffected:Integer; const Error:Error;
var EventStatus:TEventStatus; const Command:_Command;
const Recordset:_Recordset);
begin
//检查错误
如果分配(错误)然后
begin
Memo1.Lines.Add('Error:'+ Error.Description);
结束
//检查有效的记录集
如果已分配(Recordset)然后
begin
MyDataSet.Recordset = = Recordset; // MyDataSet是TADODataSet
end;
//检查受影响的行
如果RecordsAffected> = 0然后
Memo1.Lines.Add('Records affected:'+ IntToStr(RecordsAffected))
else
Memo1.Lines.Add('Record count:'+ IntToStr(MyDataSet.RecordCount));
结束
I run various SQL statements using MSSQL and ADO.
The code sequence looks like this:
aADOQuery.Active := False;
aADOQuery.SQL.Text := ' MY SQL STATEMENT ';
aADOQuery.ExecSQL;
aADOQuery.Active := True;
The last statement fails if the SQL return result is empty. How to check for this case to avoid run time errors?
Note: The SQL statement comes from a memo where the user is typing the SQL.
If your query returns a record set (SELECT
statements) you should not use ExecSQL
but simply aADOQuery.Open
or Active := True
.
For queries that do not return a record set e.g. INSERT
/UPDATE
/DELETE
, use ExecSQL
. in most cases you will get back aADOQuery.RowsAffected
by your query.
Other SQL statements that you should use ExecSQL
are CREATE
/ALTER
/DROP
/EXEC
etc... (no RowsAffected
return in this case)
If the query does not return a cursor to data (such as INSERT
statement), trying to Open
or setting such TDataSet
to Active
will fail.
You could use ADOConnection.Execute
instead of TADOQuery
to execute your command-text, and then inspect if there is a valid Recordset
returning from ADOConnection
.
In ADOConnection.OnExecuteComplete
your could do something like this:
procedure TForm1.ADOConnection1ExecuteComplete(Connection: TADOConnection;
RecordsAffected: Integer; const Error: Error;
var EventStatus: TEventStatus; const Command: _Command;
const Recordset: _Recordset);
begin
// check for errors
if Assigned(Error) then
begin
Memo1.Lines.Add('Error: ' + Error.Description);
end;
// check for a valid recordset
if Assigned(Recordset) then
begin
MyDataSet.Recordset := Recordset; // MyDataSet is TADODataSet
end;
// check for affected rows
if RecordsAffected >= 0 then
Memo1.Lines.Add('Records affected: ' + IntToStr(RecordsAffected))
else
Memo1.Lines.Add('Record count: ' + IntToStr(MyDataSet.RecordCount));
end;
这篇关于如何处理ADO查询与结果与查询没有结果?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!