Error "Multiple-step operation generated errors. Check each status value setting datetime field value" with TADOQuery and TDateTime (Delphi XE2)

Hello all,

I've created an application which should do some automated tasks.

When I run the application normally (as admin on a Window Server 2008 R2 x64 
+ MSSQL Server 2008 R2) everything works just fine.

But whe I run the application a a 'sceduled taks' (with a different Windows 
User account), I get the following error when I modify a datetime field of a 
SQL query and I try to post the record

    Multiple-step operation generated errors. Check each status value 
setting datetime field value


This is what I do (not exact query...):

procedure Edit(aConn: TADOConnection);
var
    qry: TADOQuery;
begin
    qry := TADOQuery.Create(nil);
    try
        qry.SQL.Text := 'select * from admins';
        qry.Open;
        qry.Edit;
        qry.Field['MODIFIED'].Value := Now();
        qry.Post; // <- will raise the given exception
    finally
        qry.Free;
    end;
end;

-- 
Regards,

Ronald Hoek

Application Developer
ComponentAgro B.V.
Oud-Beijerland - The Netherlands

http://www.componentagro.nl

KvK: H24264020
0
Ronald
1/8/2013 3:39:43 PM
embarcadero.delphi.ado 597 articles. 1 followers. Follow

8 Replies
2064 Views

Similar Articles

[PageSpeed] 34

Oh I forgot. We force the FormatSettings in Delphi to 'Dutch' standard when 
loading the application!!!
The locale of the Windows User running the task is not known te me (probebly 
english)


unit SystemLocalesU;

interface

uses
  SysUtils;

const
  sShortDateNames:array[1..12] of string =
    ('jan', 'feb', 'mrt', 'apr', 'mei', 'jun', 'jul', 'aug', 'sep', 'okt', 
'nov', 'dec');
  sLongDateNames:array[1..12] of string =
    ('januari', 'februari', 'maart', 'april', 'mei', 'juni', 'juli', 
'augustus',
     'september', 'oktober', 'november ', 'december');
var
  n:integer;

initialization
  // Het pakket vastzetten op Nederlandse standaard voor datum/tijd/getal 
notatie
  FormatSettings.DateSeparator := '-';
  FormatSettings.TimeSeparator := ':';
  FormatSettings.DecimalSeparator := ',';
  FormatSettings.ThousandSeparator := '.';
  FormatSettings.ShortDateFormat := 'dd-mm-yyyy';
  FormatSettings.LongDateFormat := 'dddd d mmmm yyyy';
  FormatSettings.ShortTimeFormat := 'hh:mm:ss';
  FormatSettings.LongTimeFormat := 'hh:mm:ss';

  for n:=1 to 12 do
  begin
    FormatSettings.ShortMonthNames[n] := sShortDateNames[n];
    FormatSettings.LongMonthNames[n] := sLongDateNames[n];
  end;

end.

-- 
Regards,

Ronald Hoek

Application Developer
ComponentAgro B.V.
Oud-Beijerland - The Netherlands

http://www.componentagro.nl

KvK: H24264020
0
Ronald
1/8/2013 3:42:34 PM
I have received this error a number of times previously, often this has happened when I have tried to enter a string that is too long for the field, I would have expected it to truncate.  Other times it has been from other obscure causes.  I just did a search in my source code to see if I put in a comment about it....but I didn't.  The last time I got this error was more than a year ago, so I can't clearly remember exaxtly when it happened and what I did :-(  I do remember taking a while to work out exact
ly what it was, sorry I can't be of any great help to you....but you are not the first one to have errors like this come out of the blue.


> {quote:title=Ronald Hoek wrote:}{quote}
> Hello all,
> 
> I've created an application which should do some automated tasks.
> 
> When I run the application normally (as admin on a Window Server 2008 R2 x64 
> + MSSQL Server 2008 R2) everything works just fine.
> 
> But whe I run the application a a 'sceduled taks' (with a different Windows 
> User account), I get the following error when I modify a datetime field of a 
> SQL query and I try to post the record
> 
>     Multiple-step operation generated errors. Check each status value 
> setting datetime field value
> 
> 
> This is what I do (not exact query...):
> 
> procedure Edit(aConn: TADOConnection);
> var
>     qry: TADOQuery;
> begin
>     qry := TADOQuery.Create(nil);
>     try
>         qry.SQL.Text := 'select * from admins';
>         qry.Open;
>         qry.Edit;
>         qry.Field['MODIFIED'].Value := Now();
>         qry.Post; // <- will raise the given exception
>     finally
>         qry.Free;
>     end;
> end;
> 
> -- 
> Regards,
> 
> Ronald Hoek
> 
> Application Developer
> ComponentAgro B.V.
> Oud-Beijerland - The Netherlands
> 
> http://www.componentagro.nl
> 
> KvK: H24264020
0
Trevor
1/9/2013 2:27:56 AM
Hello Trevor,

> I can't clearly remember exaxtly when it happened and what I did

To bad, because - as you said - there are a lot of people with this kind of 
error, but no one exactly seems to known what it means.

Though I hope someone can point me to the correct sollution (or tell me what 
the error means)...

-- 
Regards,

Ronald Hoek

Application Developer
ComponentAgro B.V.
Oud-Beijerland - The Netherlands

http://www.componentagro.nl

KvK: H24264020
0
Ronald
1/9/2013 3:13:45 PM
Found the answer for my problem!

I did not have anything to do with the database/ADO query itself, but the 
value that was written to the field.

This error is a result of setting a value of a field, which does not fit for 
some reason:

Ex.
- putting 64-bit int value into a 32-bit integer field
- or in my case: setting an extremely large date into a datetime field (with 
large I mean REALY large - a few 1000 centuries from now)

The problem lies with the Delphi XE2 'variant' type and the MSXML DOM.
When writing 'double' values of 'TDateTime' values to the XML DOM, Delphi 
variants will use the 'FromatSettings' when creatig the node value!!!!

So when a decimal value (Double) is written to the XML using NodeValue and 
one specifies a ',' as a decimal seperator, the actual node value inside the 
XML will become someting like '1,234' instead of '1.234' when writing the 
value 1.234   ....
This would not be a very big problem, when Delphi would handle the conversie 
to same way back, BUT IT DOESN'T!!!

When reading the XML node data back again, Delphi will use a '.' as decimal 
seperator (regardless of the FormatSettings) en wil convert the previosly 
given example ('1,234') to the value 1234.00 !!!

And because I had problems usings TDateTime and the XML DOM, I moved over to 
Double.
But basicly there's the same problem: writing the date 12/31/2012 will 
become '31-12-2012' for the Dutch locale and when reading this data, it will 
'crash' because is will expect an XML formatted DateTime!

-- 
Regards,

Ronald Hoek

Application Developer
ComponentAgro B.V.
Oud-Beijerland - The Netherlands

http://www.componentagro.nl

KvK: H24264020


"Ronald Hoek" <none> schreef in bericht 
news:537459@forums.embarcadero.com...
> Hello all,
>
> I've created an application which should do some automated tasks.
>
> When I run the application normally (as admin on a Window Server 2008 R2 
> x64 + MSSQL Server 2008 R2) everything works just fine.
>
> But whe I run the application a a 'sceduled taks' (with a different 
> Windows User account), I get the following error when I modify a datetime 
> field of a SQL query and I try to post the record
>
>    Multiple-step operation generated errors. Check each status value 
> setting datetime field value
>
>
> This is what I do (not exact query...):
>
> procedure Edit(aConn: TADOConnection);
> var
>    qry: TADOQuery;
> begin
>    qry := TADOQuery.Create(nil);
>    try
>        qry.SQL.Text := 'select * from admins';
>        qry.Open;
>        qry.Edit;
>        qry.Field['MODIFIED'].Value := Now();
>        qry.Post; // <- will raise the given exception
>    finally
>        qry.Free;
>    end;
> end;
>
> -- 
> Regards,
>
> Ronald Hoek
>
> Application Developer
> ComponentAgro B.V.
> Oud-Beijerland - The Netherlands
>
> http://www.componentagro.nl
>
> KvK: H24264020
0
Ronald
1/14/2013 3:13:24 PM
This was the sort of problem I was having, unfortunately many of the error messages given do not truly tell you what the problem is, ie. a "red herring", being Dutch I think you will know about herrings :-)

Anyway, it took me some time to work out my problem too, I had to make a field wider and the problem went away.


> {quote:title=Ronald Hoek wrote:}{quote}
> Found the answer for my problem!
> 
> I did not have anything to do with the database/ADO query itself, but the 
> value that was written to the field.
> 
> This error is a result of setting a value of a field, which does not fit for 
> some reason:
> 
> Ex.
> - putting 64-bit int value into a 32-bit integer field
> - or in my case: setting an extremely large date into a datetime field (with 
> large I mean REALY large - a few 1000 centuries from now)
> 
> The problem lies with the Delphi XE2 'variant' type and the MSXML DOM.
> When writing 'double' values of 'TDateTime' values to the XML DOM, Delphi 
> variants will use the 'FromatSettings' when creatig the node value!!!!
> 
> So when a decimal value (Double) is written to the XML using NodeValue and 
> one specifies a ',' as a decimal seperator, the actual node value inside the 
> XML will become someting like '1,234' instead of '1.234' when writing the 
> value 1.234   ....
> This would not be a very big problem, when Delphi would handle the conversie 
> to same way back, BUT IT DOESN'T!!!
> 
> When reading the XML node data back again, Delphi will use a '.' as decimal 
> seperator (regardless of the FormatSettings) en wil convert the previosly 
> given example ('1,234') to the value 1234.00 !!!
> 
> And because I had problems usings TDateTime and the XML DOM, I moved over to 
> Double.
> But basicly there's the same problem: writing the date 12/31/2012 will 
> become '31-12-2012' for the Dutch locale and when reading this data, it will 
> 'crash' because is will expect an XML formatted DateTime!
> 
> -- 
> Regards,
> 
> Ronald Hoek
> 
> Application Developer
> ComponentAgro B.V.
> Oud-Beijerland - The Netherlands
> 
> http://www.componentagro.nl
> 
> KvK: H24264020
> 
> 
> "Ronald Hoek" <none> schreef in bericht 
> news:537459@forums.embarcadero.com...
> > Hello all,
> >
> > I've created an application which should do some automated tasks.
> >
> > When I run the application normally (as admin on a Window Server 2008 R2 
> > x64 + MSSQL Server 2008 R2) everything works just fine.
> >
> > But whe I run the application a a 'sceduled taks' (with a different 
> > Windows User account), I get the following error when I modify a datetime 
> > field of a SQL query and I try to post the record
> >
> >    Multiple-step operation generated errors. Check each status value 
> > setting datetime field value
> >
> >
> > This is what I do (not exact query...):
> >
> > procedure Edit(aConn: TADOConnection);
> > var
> >    qry: TADOQuery;
> > begin
> >    qry := TADOQuery.Create(nil);
> >    try
> >        qry.SQL.Text := 'select * from admins';
> >        qry.Open;
> >        qry.Edit;
> >        qry.Field['MODIFIED'].Value := Now();
> >        qry.Post; // <- will raise the given exception
> >    finally
> >        qry.Free;
> >    end;
> > end;
> >
> > -- 
> > Regards,
> >
> > Ronald Hoek
> >
> > Application Developer
> > ComponentAgro B.V.
> > Oud-Beijerland - The Netherlands
> >
> > http://www.componentagro.nl
> >
> > KvK: H24264020
0
Trevor
1/14/2013 9:59:52 PM
> {quote:title=Ronald Hoek wrote:}{quote}
> - or in my case: setting an extremely large date into a datetime field (with 
> large I mean REALY large - a few 1000 centuries from now)
> 
> The problem lies with the Delphi XE2 'variant' type and the MSXML DOM.
> When writing 'double' values of 'TDateTime' values to the XML DOM, Delphi 
> variants will use the 'FromatSettings' when creatig the node value!!!!

Then write the date correctly, instead of using the variant type. If you write it properly formatted as an XML datetime (ccyy-mm-ddThh:nn:ss), you can properly read it back and convert it to a date value. Then you're not subject to the user's locale settings.

See the XML schema for "dateTime" for details.
0
Ken
1/15/2013 9:45:00 PM
I have got this message and hardly found that it is due to data type mismatch.
The error pops up during execution of below statment:

*s:=d.Fields[x].AsString*

While d is TADODataSet
and s is string
but the problem in SQL statment where I used TO_Date by mistake instead of TO_Char
so the field is DateTime while I am handling as string

> {quote:title=Ronald Hoek wrote:}{quote}
> Hello all,
> 
> I've created an application which should do some automated tasks.
> 
> When I run the application normally (as admin on a Window Server 2008 R2 x64 
> + MSSQL Server 2008 R2) everything works just fine.
> 
> But whe I run the application a a 'sceduled taks' (with a different Windows 
> User account), I get the following error when I modify a datetime field of a 
> SQL query and I try to post the record
> 
>     Multiple-step operation generated errors. Check each status value 
> setting datetime field value
> 
> 
> This is what I do (not exact query...):
> 
> procedure Edit(aConn: TADOConnection);
> var
>     qry: TADOQuery;
> begin
>     qry := TADOQuery.Create(nil);
>     try
>         qry.SQL.Text := 'select * from admins';
>         qry.Open;
>         qry.Edit;
>         qry.Field['MODIFIED'].Value := Now();
>         qry.Post; // <- will raise the given exception
>     finally
>         qry.Free;
>     end;
> end;
> 
> -- 
> Regards,
> 
> Ronald Hoek
> 
> Application Developer
> ComponentAgro B.V.
> Oud-Beijerland - The Netherlands
> 
> http://www.componentagro.nl
> 
> KvK: H24264020
0
Ahmed
9/25/2013 12:12:58 PM
> *s:=d.Fields[x].AsString*

Not sure, but probebly AsString is implemented as 'Result := 
VarToStr(Value);'

-- 
Regards,

Ronald Hoek

Application Developer
ComponentAgro B.V.
Oud-Beijerland - The Netherlands

http://www.componentagro.nl

KvK: H24264020
0
Ronald
12/11/2013 11:04:31 AM
Reply:

Similar Artilces:

"Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done"
I have made a script in c#. The script ad some data to a database. But if i ad something i will get the following error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done" I don`t get it. If I fill in all the forms, there is no problem. What can I do? Please post your code and what line the error occurs. It sounds like the SQL statement is incorrect. What type of database are you using? It sounds like Access.If an answer has been provided to you question please mark it as an answer. Check out http:...

ADO "Multiple-step operation generated errors" error
I am migrating from Delphi 2007 to Delphi 2009 (June2009). I have them both concurrently installed on my machine during the migration. With the following code: With FFeedDataset Do Begin Active:=False; CommandText:='Select * From '+FeedsTableName; Active:=True; If Locate('ID',InFeed.ID,[]) Then Edit Else Insert; FieldByName('Title').AsString:=InFeed.Title; FieldByNam...

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
 Please help,i have made several query on this forum regarding this error but i have'nt found  any solution,this is my code: <asp:DropDownList ID="PlanGroup0" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource3" DataTextField="PlanGroups" DataValueField="PlanGroup" Width="150px"> </asp:DropDownList> <asp:SqlDataSource ID="SqlDataSource3" runat="server" ...

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
hi ,this is navin. since i am a beginner to asp.net 2.0.. i am facing few problems.. i am using visual studio 2005 and MS access as back end tool .. and using login inbuilt wizards in visual studio 2005.. its storing the data to an mdf  database - \App_Data\ASPNETDB.MDF.. i need to retrieve the data from the table aspnet_Users...i have attached the code with this mail for your reference.. when i execute this code , it showing me an error Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.  can u help me out to solve ...

need an urgent help:Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
hello everyone, i need an urgent help........ thanx in advance... i m getting this  error Server Error in '/AJAXEnabledWebSite1' Application. Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Runtime.InteropServices.COMException: Multiple-step OLE DB operation generated errors. Check eac...

System.Data.OleDb.OleDbException: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
Hi All, I am getting an exception with description as System.Data.OleDb.OleDbException: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. I am trying to connect to access database and I had written connection string in web.config file. the code for that as <appSettings>  <add key="UserSpecs" value="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=localhost; Initial Catalog=BaseData; User Id=; Password=;"/> </appSettings> I am getting exception on connection.open() . Any idea...

Single Quote Error While writing RegisterClientScriptBlock("key", "Value")
Hi Friends, I am facing one problem due to single quote. I am writing underwritten script to close currently opened form and to get those values in another form. ############################################################# strScript as string strscript = " <script> window.opener.document.Form1.txtAddressLocationh2.value='Factory'; window.close(); </script> " Me.RegisterClientScriptBlock("key", strScript) ############################################################# Now if i have value of "Facto'ry" instead o...

Error: Could not open in Design view. Quote values differently inside a '<%.."value"...%>'block.
I have this code for my datagrid ----------------------------------------------- <asp:TemplateColumn HeaderText="Title" Visible="true" runat="server" SortExpression="title" > <ItemTemplate> <%# DataBinder.Eval(Container.DataItem, "title") %> </ItemTemplate> </asp:TemplateColumn> ------------------------------------------------------------------------- it just work fine and compile no error... but when I want to swtich from HTML view to Design View,...

Error:"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
I still don't know what is happening with my application. I used before in another page the formview control and I didn't have the out of range Date error. It was normally. But now, I'm doing everything manually and I don't know why, but I'm with this problem. I need to safe this problem today... Here go my code! The error occurs here: (It's the Sub that Insert data inside of the database(SQLServer)Protected Sub Gravar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim SQL As String = "Update RECEBER SET  EMISSAO = '" & CType(tbDataEmissao.Text, Date) & "', VENCIME...

Model error for empty field
I have a simple form that is being bound the model using the DefaultModelBinder. One of the fields in the model is an integer field. Whenever the model binding occurs I get a model error for the integer field saying "A value is required" if the form field is empty. I'm guessing this is because the empty string value passed cannot be converted to an integer. The problem is this is hijacking my other custom model errors as I manually check for required fields in my business rules. Outside of writing a custom model binder to manually bind each field is there a way to get rid of th...

"set-src-attr-value" fails with no errors
Trying to craft a policy in an existing Null driver that will fix up attributes on a rename operation. The attributes in question are used by our email system and, with a few exceptions, are equal to "[CN]"@"uga.edu" Everything works fine with one exception - a particular attribute does not get set, and there is no error message in the trace to indicate where I should look. The driver is running with security equal to admin (in a dev environment) so I do not think it's a rights issue. Here is a trace. The attribute that fails to set correctly is "mi...

Delphi 2010 IDE throws error on "find" and "find in files"
I will greatly appreciate any advice how to cure the suddenly appeared misbehaviour: I just loaded the main project file and then tried to find code snippets fore re-use with "find" and "find in files" the Eurekalog extract is: 2.2 Address : 20EB30B6 2.3 Module Name : vclide140.bpl - (Integrated Development Environment Specific Components) 2.4 Module Version: 14.0.3593.25826 2.5 Type : EOutOfResources 2.6 Message : Unable to insert a line. 2.7 ID : 61FB 2.8 Count : 1 2.9 Status ...

Problem on setting to DropDownList Value="id" and Text="Name" by reading values from SQL...
Hi!Well, my problem is that i don't know how to set different values on a DropDownList. I have an DropDownList and an SqlDataSource. When the aspx page loads, i want the DropDownList to fill data from a table on my SQL Server. Especially, i want each row of the DropDownList to take the Value property from the "ID" cell of my SQL Table and to take the Text property from the "Name" cell of my same SQL Table.Now, with my solution, the DropDownList takes the value for its "Value" and "Text" properties only from my  "Name" cell of my same...

"SQL Error: Error mapping failed" with Delphi 6 Application and DBExpress
Im using Delphi 6, Firebird 2.x, dbExpress The problem is only with remote database connection and not with local connection. The following connection gives and error Database := MyServer:C:\DEMO.FDB But when locally connected i.e database in the same computer Database := C:\FBdatabases\dbAccounts.FDB Following solution from Firebird forun worked ----------------------------------------- Actually, the issue is inside Borland's DBX driver for InterBase. It's not very much compatible with Firebird. First of all, it uses some internal (metadata) queries in the...

Web resources about - Error "Multiple-step operation generated errors. Check each status value setting datetime field value" with TADOQuery and TDateTime (Delphi XE2) - embarcadero.delphi.ado

Resources last updated: 11/26/2015 5:28:22 AM