如何重置Delphi Tadocommand参数

I have a Tadocommand on my datamodule which is connected to a MSSQL storedproc. The storedproc is used to update a table. In my code I call the the tadocommand in the beforeupdaterecord method of one of my Tclientdatasets.

我的数据模块上有一个Tadocommand，它连接到MSSQL storedproc。 storedproc用于更新表。在我的代码中，我在我的一个Tclientdatasets的beforeupdaterecord方法中调用了tadocommand。

first I supply values to the tadocommand parameters using the deltads.fieldbyname().newvalue of the Tclientdataset then I call the execute procedure. It works ok for the first update but if i try to do a next update it generates "error changing varchar to datetime".

首先，我使用Tclientdataset的deltads.fieldbyname（）。newvalue为tadocommand参数提供值，然后调用执行过程。它适用于第一次更新，但如果我尝试进行下一次更新，则会生成“将varchar更改为datetime”的错误。

if i dynamically create the tadocommand in the beforeupdaterecord method i.e

如果我在beforeupdaterecord方法中动态创建tadocommand，即

sp1_editcontract:=Tadocommand.Create(nil);
sp1_editcontract.CommandType:=cmdStoredProc;
sp1_editcontract.Connection:=DMDBconn.DBConn;
sp1_editcontract.CommandText:='EditContract';
sp1_editcontract.Parameters.Refresh; 
//assign parameter values
sp1_editcontract.execute;
sp1_editcontract.free;

it works without any errors. I think there is some problem with the parameters values when using the static Tadocommand on the datamodule.

它没有任何错误。我认为在数据模块上使用静态Tadocommand时参数值存在一些问题。

why does multiple update generate an error when using a static created tadocommand and not for the dynamically created tadocommand?

为什么多次更新在使用静态创建的tadocommand而不是动态创建的tadocommand时会产生错误？

2 个解决方案

#1

I'm going to assume you are referring to TDatasetProvider.BeforeUpdateRecord and not TClientDataSet.BeforeUpdateRecord.

我将假设您指的是TDatasetProvider.BeforeUpdateRecord而不是TClientDataSet.BeforeUpdateRecord。

Its kinda hard to say from the information you've provided (you don't indicate the data types or order of the arguments for the stored procedure). The error message is coming from the SQL Server engine. I would make sure the values being assigned to the parameters are always being set in the correct order. Also try to identify which parameter is causing the error. If you can reliably reproduce it in your client code you may try calling the stored procedure in SSMS passing the same values that are causing the error in the client application.

从您提供的信息中很难说（您没有指明存储过程的数据类型或参数的顺序）。该错误消息来自SQL Server引擎。我会确保分配给参数的值始终按正确的顺序设置。还要尝试确定导致错误的参数。如果您可以在客户端代码中可靠地重现它，您可以尝试在SSMS中调用存储过程，传递导致客户端应用程序中的错误的相同值。

Once you identify the parameter you can check that its datatype is consistent between the ADOCommand, DatasetProvider and ClientDataset. If it changes type along the way that may be the cause of the error.

识别参数后，可以检查其数据类型在ADOCommand，DatasetProvider和ClientDataset之间是否一致。如果它改变了可能导致错误的方式的类型。

One last suggestion, make sure you set TDatasetProvider.Applied := True before exiting the BeforeUpdateRecord handler. This prevents the dataset provider from trying to apply the update using dynamic sql after you've already applied the updates. If the data in the client dataset was populated by a TADOQuery it may be attempting to update the tables directly.

最后一个建议，确保在退出BeforeUpdateRecord处理程序之前设置TDatasetProvider.Applied：= True。这可以防止数据集提供程序在您已应用更新后尝试使用动态sql应用更新。如果客户端数据集中的数据由TADOQuery填充，则可能正在尝试直接更新表。

#2

I had a similar problem. In order to clear all the existing parameters of the ADOCommand before adding new ones a used the following code:

我有类似的问题。为了在添加新的ADOCommand之前清除ADOCommand的所有现有参数，请使用以下代码：

while Command.Parameters.Count>0 do
  Command.Parameters.Delete(0);

Parameters.Clear should had worked, but it didn´t, so I decided to remove the parameters one by one. That fixed to me.

参数。清除应该有效，但它没有，所以我决定逐个删除参数。这固定在我身上。

#1