BorlandTalk.com Borland discussion newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  slow ADO refresh, ending in error message Goto page 1, 2  Next          BorlandTalk.com Forum Index -> Delphi Databases (ADO) View previous topic :: View next topic   Author Message Susan Leigh Guest Posted: Wed Feb 04, 2004 8:48 am    Post subject: slow ADO refresh, ending in error message I'm trying to port a Delphi 5 application that used a third-party ADO package, to Delphi 7 using the ADO components that come with it. In particular, I have a form with a DBGrid and DBNavigator, connected to an ADODataSource by way of a Datasource. It works okay except when I click the refresh button on the navigator, there is a very long delay during which the program is blocked, and finally an EOleException with the debugger message: 'Single-row refresh operation returned multiple rows'. What can I do to prevent this? thanks... Back to top Viatcheslav V. Vassiliev Guest Posted: Wed Feb 04, 2004 12:05 pm    Post subject: Re: slow ADO refresh, ending in error message Try to add primary key or at least unique index for every table that is used in SQL query. //------------------------------------------ Regards, Vassiliev V. V. http://www.managed-vcl.com - using .Net objects in Delphi for Win32 + ADO.Net http://www.oledbdirect.com - The fastest way to access MS SQL Server, MS Jet (Access) and Interbase (through OLEDB) "Susan Leigh" <magine (AT) turtleflight (DOT) com> ???????/???????? ? ???????? ?????????: news:4020b1f8 (AT) newsgroups (DOT) borland.com... Quote: I'm trying to port a Delphi 5 application that used a third-party ADO package, to Delphi 7 using the ADO components that come with it. In particular, I have a form with a DBGrid and DBNavigator, connected to an ADODataSource by way of a Datasource. It works okay except when I click the refresh button on the navigator, there is a very long delay during which the program is blocked, and finally an EOleException with the debugger message: 'Single-row refresh operation returned multiple rows'. What can I do to prevent this? thanks... Back to top Susan Leigh Guest Posted: Wed Feb 04, 2004 2:44 pm    Post subject: Re: slow ADO refresh, ending in error message I do have a primary key for each table. Also, I didn't have this problem with the third-party ADO package in Delphi 5, it only began in Delphi 7 using their own ADO component. I feel there must be some setting that isn't obvious, that I'm missing. Viatcheslav V. Vassiliev wrote: Quote: Try to add primary key or at least unique index for every table that is used in SQL query. //------------------------------------------ Regards, Vassiliev V. V. http://www.managed-vcl.com - using .Net objects in Delphi for Win32 + ADO.Net http://www.oledbdirect.com - The fastest way to access MS SQL Server, MS Jet (Access) and Interbase (through OLEDB) "Susan Leigh" <magine (AT) turtleflight (DOT) com> ???????/???????? ? ???????? ?????????: news:4020b1f8 (AT) newsgroups (DOT) borland.com... I'm trying to port a Delphi 5 application that used a third-party ADO package, to Delphi 7 using the ADO components that come with it. In particular, I have a form with a DBGrid and DBNavigator, connected to an ADODataSource by way of a Datasource. It works okay except when I click the refresh button on the navigator, there is a very long delay during which the program is blocked, and finally an EOleException with the debugger message: 'Single-row refresh operation returned multiple rows'. What can I do to prevent this? thanks... Back to top Viatcheslav V. Vassiliev Guest Posted: Wed Feb 04, 2004 2:59 pm    Post subject: Re: slow ADO refresh, ending in error message Could you provide exact error message and error code (EOleException.ErrorCode)? What is your DBMS? //------------------------------------------ Regards, Vassiliev V. V. http://www.managed-vcl.com - using .Net objects in Delphi for Win32 + ADO.Net http://www.oledbdirect.com - The fastest way to access MS SQL Server, MS Jet (Access) and Interbase (through OLEDB) "Susan Leigh" <magine (AT) turtleflight (DOT) com> ???????/???????? ? ???????? ?????????: news:40210547$1 (AT) newsgroups (DOT) borland.com... Quote: I do have a primary key for each table. Also, I didn't have this problem with the third-party ADO package in Delphi 5, it only began in Delphi 7 using their own ADO component. I feel there must be some setting that isn't obvious, that I'm missing. Viatcheslav V. Vassiliev wrote: Try to add primary key or at least unique index for every table that is used in SQL query. //------------------------------------------ Regards, Vassiliev V. V. http://www.managed-vcl.com - using .Net objects in Delphi for Win32 + ADO.Net http://www.oledbdirect.com - The fastest way to access MS SQL Server, MS Jet (Access) and Interbase (through OLEDB) "Susan Leigh" <magine (AT) turtleflight (DOT) com> ???????/???????? ? ???????? ?????????: news:4020b1f8 (AT) newsgroups (DOT) borland.com... I'm trying to port a Delphi 5 application that used a third-party ADO package, to Delphi 7 using the ADO components that come with it. In particular, I have a form with a DBGrid and DBNavigator, connected to an ADODataSource by way of a Datasource. It works okay except when I click the refresh button on the navigator, there is a very long delay during which the program is blocked, and finally an EOleException with the debugger message: 'Single-row refresh operation returned multiple rows'. What can I do to prevent this? thanks... Back to top Susan Leigh Guest Posted: Wed Feb 04, 2004 8:48 pm    Post subject: Re: slow ADO refresh, ending in error message I don't know the error code because the exception is in the debugger, not my program. There's no place to trap the exception in the code since it occurs when the automatic navigation (DBNavigator) component is clicked. After a long (30 seconds or more) pause during which the program is blocked, I get this popup in the IDE: ------------------------------------------------------------------------ Debugger Exception Notification Project MBot.exe raised exception class EOleException with message 'Single-row refresh operation returned multiple rows'. Process stopped. Use Step or Run to continue. ------------------------------------------------------------------------ The database is a local (i.e. on my hard drive) Microsoft Access 2000 database. The connect string I'm using is Driver={Microsoft Access Driver (*.mdb)};Dbq=RPG.mdb;Uid=Admin;Pwd=; Viatcheslav V. Vassiliev wrote: Quote: Could you provide exact error message and error code (EOleException.ErrorCode)? What is your DBMS? //------------------------------------------ Regards, Vassiliev V. V. http://www.managed-vcl.com - using .Net objects in Delphi for Win32 + ADO.Net http://www.oledbdirect.com - The fastest way to access MS SQL Server, MS Jet (Access) and Interbase (through OLEDB) "Susan Leigh" <magine (AT) turtleflight (DOT) com> ???????/???????? ? ???????? ?????????: news:40210547$1 (AT) newsgroups (DOT) borland.com... I do have a primary key for each table. Also, I didn't have this problem with the third-party ADO package in Delphi 5, it only began in Delphi 7 using their own ADO component. I feel there must be some setting that isn't obvious, that I'm missing. Viatcheslav V. Vassiliev wrote: Try to add primary key or at least unique index for every table that is used in SQL query. //------------------------------------------ Regards, Vassiliev V. V. http://www.managed-vcl.com - using .Net objects in Delphi for Win32 + ADO.Net http://www.oledbdirect.com - The fastest way to access MS SQL Server, MS Jet (Access) and Interbase (through OLEDB) "Susan Leigh" <magine (AT) turtleflight (DOT) com> ???????/???????? ? ???????? ?????????: news:4020b1f8 (AT) newsgroups (DOT) borland.com... I'm trying to port a Delphi 5 application that used a third-party ADO package, to Delphi 7 using the ADO components that come with it. In particular, I have a form with a DBGrid and DBNavigator, connected to an ADODataSource by way of a Datasource. It works okay except when I click the refresh button on the navigator, there is a very long delay during which the program is blocked, and finally an EOleException with the debugger message: 'Single-row refresh operation returned multiple rows'. What can I do to prevent this? thanks... Back to top Viatcheslav V. Vassiliev Guest Posted: Thu Feb 05, 2004 7:11 am    Post subject: Re: slow ADO refresh, ending in error message Use Jet OLEDB provider for MS Access databases, not ODBC. Connection string is: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:TempMyDB.mdb; This provider is native for MS Access. //------------------------------------------ Regards, Vassiliev V. V. http://www.managed-vcl.com - using .Net objects in Delphi for Win32 + ADO.Net http://www.oledbdirect.com - The fastest way to access MS SQL Server, MS Jet (Access) and Interbase (through OLEDB) "Susan Leigh" <magine (AT) turtleflight (DOT) com> ???????/???????? ? ???????? ?????????: news:40215ab7 (AT) newsgroups (DOT) borland.com... Quote: I don't know the error code because the exception is in the debugger, not my program. There's no place to trap the exception in the code since it occurs when the automatic navigation (DBNavigator) component is clicked. After a long (30 seconds or more) pause during which the program is blocked, I get this popup in the IDE: ------------------------------------------------------------------------ Debugger Exception Notification Project MBot.exe raised exception class EOleException with message 'Single-row refresh operation returned multiple rows'. Process stopped. Use Step or Run to continue. ------------------------------------------------------------------------ The database is a local (i.e. on my hard drive) Microsoft Access 2000 database. The connect string I'm using is Driver={Microsoft Access Driver (*.mdb)};Dbq=RPG.mdb;Uid=Admin;Pwd=; Viatcheslav V. Vassiliev wrote: Could you provide exact error message and error code (EOleException.ErrorCode)? What is your DBMS? //------------------------------------------ Regards, Vassiliev V. V. http://www.managed-vcl.com - using .Net objects in Delphi for Win32 + ADO.Net http://www.oledbdirect.com - The fastest way to access MS SQL Server, MS Jet (Access) and Interbase (through OLEDB) "Susan Leigh" <magine (AT) turtleflight (DOT) com> ???????/???????? ? ???????? ?????????: news:40210547$1 (AT) newsgroups (DOT) borland.com... I do have a primary key for each table. Also, I didn't have this problem with the third-party ADO package in Delphi 5, it only began in Delphi 7 using their own ADO component. I feel there must be some setting that isn't obvious, that I'm missing. Viatcheslav V. Vassiliev wrote: Try to add primary key or at least unique index for every table that is used in SQL query. //------------------------------------------ Regards, Vassiliev V. V. http://www.managed-vcl.com - using .Net objects in Delphi for Win32 + ADO.Net http://www.oledbdirect.com - The fastest way to access MS SQL Server, MS Jet (Access) and Interbase (through OLEDB) "Susan Leigh" <magine (AT) turtleflight (DOT) com> ???????/???????? ? ???????? ?????????: news:4020b1f8 (AT) newsgroups (DOT) borland.com... I'm trying to port a Delphi 5 application that used a third-party ADO package, to Delphi 7 using the ADO components that come with it. In particular, I have a form with a DBGrid and DBNavigator, connected to an ADODataSource by way of a Datasource. It works okay except when I click the refresh button on the navigator, there is a very long delay during which the program is blocked, and finally an EOleException with the debugger message: 'Single-row refresh operation returned multiple rows'. What can I do to prevent this? thanks... Back to top Susan Leigh Guest Posted: Thu Feb 05, 2004 11:10 am    Post subject: Re: slow ADO refresh, ending in error message Okay, with that connection string I no longer get the error message...but there is still a very long delay during which the program is blocked, which I did not have in Delphi 5 using the third-party ADO component. So something is still wrong, unless Borland's implementation is simply not as fast. Unfortunately there is no version of the third-party ADO for Delphi 7. Viatcheslav V. Vassiliev wrote: Quote: Use Jet OLEDB provider for MS Access databases, not ODBC. Connection string is: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:TempMyDB.mdb; This provider is native for MS Access. //------------------------------------------ Regards, Vassiliev V. V. http://www.managed-vcl.com - using .Net objects in Delphi for Win32 + ADO.Net http://www.oledbdirect.com - The fastest way to access MS SQL Server, MS Jet (Access) and Interbase (through OLEDB) "Susan Leigh" <magine (AT) turtleflight (DOT) com> ???????/???????? ? ???????? ?????????: news:40215ab7 (AT) newsgroups (DOT) borland.com... I don't know the error code because the exception is in the debugger, not my program. There's no place to trap the exception in the code since it occurs when the automatic navigation (DBNavigator) component is clicked. After a long (30 seconds or more) pause during which the program is blocked, I get this popup in the IDE: ------------------------------------------------------------------------ Debugger Exception Notification Project MBot.exe raised exception class EOleException with message 'Single-row refresh operation returned multiple rows'. Process stopped. Use Step or Run to continue. ------------------------------------------------------------------------ The database is a local (i.e. on my hard drive) Microsoft Access 2000 database. The connect string I'm using is Driver={Microsoft Access Driver (*.mdb)};Dbq=RPG.mdb;Uid=Admin;Pwd=; Viatcheslav V. Vassiliev wrote: Could you provide exact error message and error code (EOleException.ErrorCode)? What is your DBMS? //------------------------------------------ Regards, Vassiliev V. V. http://www.managed-vcl.com - using .Net objects in Delphi for Win32 + ADO.Net http://www.oledbdirect.com - The fastest way to access MS SQL Server, MS Jet (Access) and Interbase (through OLEDB) "Susan Leigh" <magine (AT) turtleflight (DOT) com> ???????/???????? ? ???????? ?????????: news:40210547$1 (AT) newsgroups (DOT) borland.com... I do have a primary key for each table. Also, I didn't have this problem with the third-party ADO package in Delphi 5, it only began in Delphi 7 using their own ADO component. I feel there must be some setting that isn't obvious, that I'm missing. Viatcheslav V. Vassiliev wrote: Try to add primary key or at least unique index for every table that is used in SQL query. //------------------------------------------ Regards, Vassiliev V. V. http://www.managed-vcl.com - using .Net objects in Delphi for Win32 + ADO.Net http://www.oledbdirect.com - The fastest way to access MS SQL Server, MS Jet (Access) and Interbase (through OLEDB) "Susan Leigh" <magine (AT) turtleflight (DOT) com> ???????/???????? ? ???????? ?????????: news:4020b1f8 (AT) newsgroups (DOT) borland.com... I'm trying to port a Delphi 5 application that used a third-party ADO package, to Delphi 7 using the ADO components that come with it. In particular, I have a form with a DBGrid and DBNavigator, connected to an ADODataSource by way of a Datasource. It works okay except when I click the refresh button on the navigator, there is a very long delay during which the program is blocked, and finally an EOleException with the debugger message: 'Single-row refresh operation returned multiple rows'. What can I do to prevent this? thanks... Back to top Susan Leigh Guest Posted: Thu Feb 05, 2004 11:21 am    Post subject: Re: slow ADO refresh, ending in error message ....also, when I used the OLE connection string, I get an exception with an "unspecified error" (that's the E.message) when I set ADODataset.Active:=true. (I had that same error with the third-party ADO component in that past when using the OLE connect string, which is why I was using the ODBC connection instead.) Viatcheslav V. Vassiliev wrote: Quote: Use Jet OLEDB provider for MS Access databases, not ODBC. Connection string is: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:TempMyDB.mdb; This provider is native for MS Access. //------------------------------------------ Regards, Vassiliev V. V. http://www.managed-vcl.com - using .Net objects in Delphi for Win32 + ADO.Net http://www.oledbdirect.com - The fastest way to access MS SQL Server, MS Jet (Access) and Interbase (through OLEDB) "Susan Leigh" <magine (AT) turtleflight (DOT) com> ???????/???????? ? ???????? ?????????: news:40215ab7 (AT) newsgroups (DOT) borland.com... I don't know the error code because the exception is in the debugger, not my program. There's no place to trap the exception in the code since it occurs when the automatic navigation (DBNavigator) component is clicked. After a long (30 seconds or more) pause during which the program is blocked, I get this popup in the IDE: ------------------------------------------------------------------------ Debugger Exception Notification Project MBot.exe raised exception class EOleException with message 'Single-row refresh operation returned multiple rows'. Process stopped. Use Step or Run to continue. ------------------------------------------------------------------------ The database is a local (i.e. on my hard drive) Microsoft Access 2000 database. The connect string I'm using is Driver={Microsoft Access Driver (*.mdb)};Dbq=RPG.mdb;Uid=Admin;Pwd=; Viatcheslav V. Vassiliev wrote: Could you provide exact error message and error code (EOleException.ErrorCode)? What is your DBMS? //------------------------------------------ Regards, Vassiliev V. V. http://www.managed-vcl.com - using .Net objects in Delphi for Win32 + ADO.Net http://www.oledbdirect.com - The fastest way to access MS SQL Server, MS Jet (Access) and Interbase (through OLEDB) "Susan Leigh" <magine (AT) turtleflight (DOT) com> ???????/???????? ? ???????? ?????????: news:40210547$1 (AT) newsgroups (DOT) borland.com... I do have a primary key for each table. Also, I didn't have this problem with the third-party ADO package in Delphi 5, it only began in Delphi 7 using their own ADO component. I feel there must be some setting that isn't obvious, that I'm missing. Viatcheslav V. Vassiliev wrote: Try to add primary key or at least unique index for every table that is used in SQL query. //------------------------------------------ Regards, Vassiliev V. V. http://www.managed-vcl.com - using .Net objects in Delphi for Win32 + ADO.Net http://www.oledbdirect.com - The fastest way to access MS SQL Server, MS Jet (Access) and Interbase (through OLEDB) "Susan Leigh" <magine (AT) turtleflight (DOT) com> ???????/???????? ? ???????? ?????????: news:4020b1f8 (AT) newsgroups (DOT) borland.com... I'm trying to port a Delphi 5 application that used a third-party ADO package, to Delphi 7 using the ADO components that come with it. In particular, I have a form with a DBGrid and DBNavigator, connected to an ADODataSource by way of a Datasource. It works okay except when I click the refresh button on the navigator, there is a very long delay during which the program is blocked, and finally an EOleException with the debugger message: 'Single-row refresh operation returned multiple rows'. What can I do to prevent this? thanks... Back to top Viatcheslav V. Vassiliev Guest Posted: Thu Feb 05, 2004 1:43 pm    Post subject: Re: slow ADO refresh, ending in error message Pressing refresh calls Recordset.Resync(adAffectAll, adResyncAllValues) and this fetches all rows into in-memory cache if you are on client-side cursor - if your query returns much records you may have delay. Switching to server-side cursor should make this delay less but you will not be able to use filter/sort. //------------------------------------------ Regards, Vassiliev V. V. http://www.managed-vcl.com - using .Net objects in Delphi for Win32 + ADO.Net http://www.oledbdirect.com - The fastest way to access MS SQL Server, MS Jet (Access) and Interbase (through OLEDB) "Susan Leigh" <magine (AT) turtleflight (DOT) com> ???????/???????? ? ???????? ?????????: news:40222723$1 (AT) newsgroups (DOT) borland.com... Quote: ...also, when I used the OLE connection string, I get an exception with an "unspecified error" (that's the E.message) when I set ADODataset.Active:=true. (I had that same error with the third-party ADO component in that past when using the OLE connect string, which is why I was using the ODBC connection instead.) Viatcheslav V. Vassiliev wrote: Use Jet OLEDB provider for MS Access databases, not ODBC. Connection string is: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:TempMyDB.mdb; This provider is native for MS Access. //------------------------------------------ Regards, Vassiliev V. V. http://www.managed-vcl.com - using .Net objects in Delphi for Win32 + ADO.Net http://www.oledbdirect.com - The fastest way to access MS SQL Server, MS Jet (Access) and Interbase (through OLEDB) "Susan Leigh" <magine (AT) turtleflight (DOT) com> ???????/???????? ? ???????? ?????????: news:40215ab7 (AT) newsgroups (DOT) borland.com... I don't know the error code because the exception is in the debugger, not my program. There's no place to trap the exception in the code since it occurs when the automatic navigation (DBNavigator) component is clicked. After a long (30 seconds or more) pause during which the program is blocked, I get this popup in the IDE: ------------------------------------------------------------------------ Debugger Exception Notification Project MBot.exe raised exception class EOleException with message 'Single-row refresh operation returned multiple rows'. Process stopped. Use Step or Run to continue. ------------------------------------------------------------------------ The database is a local (i.e. on my hard drive) Microsoft Access 2000 database. The connect string I'm using is Driver={Microsoft Access Driver (*.mdb)};Dbq=RPG.mdb;Uid=Admin;Pwd=; Viatcheslav V. Vassiliev wrote: Could you provide exact error message and error code (EOleException.ErrorCode)? What is your DBMS? //------------------------------------------ Regards, Vassiliev V. V. http://www.managed-vcl.com - using .Net objects in Delphi for Win32 + ADO.Net http://www.oledbdirect.com - The fastest way to access MS SQL Server, MS Jet (Access) and Interbase (through OLEDB) "Susan Leigh" <magine (AT) turtleflight (DOT) com> ???????/???????? ? ???????? ?????????: news:40210547$1 (AT) newsgroups (DOT) borland.com... I do have a primary key for each table. Also, I didn't have this problem with the third-party ADO package in Delphi 5, it only began in Delphi 7 using their own ADO component. I feel there must be some setting that isn't obvious, that I'm missing. Viatcheslav V. Vassiliev wrote: Try to add primary key or at least unique index for every table that is used in SQL query. //------------------------------------------ Regards, Vassiliev V. V. http://www.managed-vcl.com - using .Net objects in Delphi for Win32 + ADO.Net http://www.oledbdirect.com - The fastest way to access MS SQL Server, MS Jet (Access) and Interbase (through OLEDB) "Susan Leigh" <magine (AT) turtleflight (DOT) com> ???????/???????? ? ???????? ?????????: news:4020b1f8 (AT) newsgroups (DOT) borland.com... I'm trying to port a Delphi 5 application that used a third-party ADO package, to Delphi 7 using the ADO components that come with it. In particular, I have a form with a DBGrid and DBNavigator, connected to an ADODataSource by way of a Datasource. It works okay except when I click the refresh button on the navigator, there is a very long delay during which the program is blocked, and finally an EOleException with the debugger message: 'Single-row refresh operation returned multiple rows'. What can I do to prevent this? thanks... Back to top Susan Leigh Guest Posted: Thu Feb 05, 2004 3:37 pm    Post subject: Re: slow ADO refresh, ending in error message But why did I not have the same delay with Delphi 5 and the third-party component? I used a client-side cursor in that case also. I've tested both versions with the identical database, so there's no difference in the number of records. Viatcheslav V. Vassiliev wrote: Quote: Pressing refresh calls Recordset.Resync(adAffectAll, adResyncAllValues) and this fetches all rows into in-memory cache if you are on client-side cursor - if your query returns much records you may have delay. Switching to server-side cursor should make this delay less but you will not be able to use filter/sort. //------------------------------------------ Regards, Vassiliev V. V. http://www.managed-vcl.com - using .Net objects in Delphi for Win32 + ADO.Net http://www.oledbdirect.com - The fastest way to access MS SQL Server, MS Jet (Access) and Interbase (through OLEDB) "Susan Leigh" <magine (AT) turtleflight (DOT) com> ???????/???????? ? ???????? ?????????: news:40222723$1 (AT) newsgroups (DOT) borland.com... ...also, when I used the OLE connection string, I get an exception with an "unspecified error" (that's the E.message) when I set ADODataset.Active:=true. (I had that same error with the third-party ADO component in that past when using the OLE connect string, which is why I was using the ODBC connection instead.) Viatcheslav V. Vassiliev wrote: Use Jet OLEDB provider for MS Access databases, not ODBC. Connection string is: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:TempMyDB.mdb; This provider is native for MS Access. //------------------------------------------ Regards, Vassiliev V. V. http://www.managed-vcl.com - using .Net objects in Delphi for Win32 + ADO.Net http://www.oledbdirect.com - The fastest way to access MS SQL Server, MS Jet (Access) and Interbase (through OLEDB) "Susan Leigh" <magine (AT) turtleflight (DOT) com> ???????/???????? ? ???????? ?????????: news:40215ab7 (AT) newsgroups (DOT) borland.com... I don't know the error code because the exception is in the debugger, not my program. There's no place to trap the exception in the code since it occurs when the automatic navigation (DBNavigator) component is clicked. After a long (30 seconds or more) pause during which the program is blocked, I get this popup in the IDE: ------------------------------------------------------------------------ Debugger Exception Notification Project MBot.exe raised exception class EOleException with message 'Single-row refresh operation returned multiple rows'. Process stopped. Use Step or Run to continue. ------------------------------------------------------------------------ The database is a local (i.e. on my hard drive) Microsoft Access 2000 database. The connect string I'm using is Driver={Microsoft Access Driver (*.mdb)};Dbq=RPG.mdb;Uid=Admin;Pwd=; Viatcheslav V. Vassiliev wrote: Could you provide exact error message and error code (EOleException.ErrorCode)? What is your DBMS? //------------------------------------------ Regards, Vassiliev V. V. http://www.managed-vcl.com - using .Net objects in Delphi for Win32 + ADO.Net http://www.oledbdirect.com - The fastest way to access MS SQL Server, MS Jet (Access) and Interbase (through OLEDB) "Susan Leigh" <magine (AT) turtleflight (DOT) com> ???????/???????? ? ???????? ?????????: news:40210547$1 (AT) newsgroups (DOT) borland.com... I do have a primary key for each table. Also, I didn't have this problem with the third-party ADO package in Delphi 5, it only began in Delphi 7 using their own ADO component. I feel there must be some setting that isn't obvious, that I'm missing. Viatcheslav V. Vassiliev wrote: Try to add primary key or at least unique index for every table that is used in SQL query. //------------------------------------------ Regards, Vassiliev V. V. http://www.managed-vcl.com - using .Net objects in Delphi for Win32 + ADO.Net http://www.oledbdirect.com - The fastest way to access MS SQL Server, MS Jet (Access) and Interbase (through OLEDB) "Susan Leigh" <magine (AT) turtleflight (DOT) com> ???????/???????? ? ???????? ?????????: news:4020b1f8 (AT) newsgroups (DOT) borland.com... I'm trying to port a Delphi 5 application that used a third-party ADO package, to Delphi 7 using the ADO components that come with it. In particular, I have a form with a DBGrid and DBNavigator, connected to an ADODataSource by way of a Datasource. It works okay except when I click the refresh button on the navigator, there is a very long delay during which the program is blocked, and finally an EOleException with the debugger message: 'Single-row refresh operation returned multiple rows'. What can I do to prevent this? thanks... Back to top Viatcheslav V. Vassiliev Guest Posted: Thu Feb 05, 2004 3:45 pm    Post subject: Re: slow ADO refresh, ending in error message It is not possible to say without sample project that reproduces this behaviour. Is it possible that database was open by another application (for example, MS Access blocks access to database in table constructor mode)? //------------------------------------------ Regards, Vassiliev V. V. http://www.managed-vcl.com - using .Net objects in Delphi for Win32 + ADO.Net http://www.oledbdirect.com - The fastest way to access MS SQL Server, MS Jet (Access) and Interbase (through OLEDB) "Susan Leigh" <magine (AT) turtleflight (DOT) com> ???????/???????? ? ???????? ?????????: news:40226342$2 (AT) newsgroups (DOT) borland.com... Quote: But why did I not have the same delay with Delphi 5 and the third-party component? I used a client-side cursor in that case also. I've tested both versions with the identical database, so there's no difference in the number of records. Viatcheslav V. Vassiliev wrote: Pressing refresh calls Recordset.Resync(adAffectAll, adResyncAllValues) and this fetches all rows into in-memory cache if you are on client-side cursor - if your query returns much records you may have delay. Switching to server-side cursor should make this delay less but you will not be able to use filter/sort. //------------------------------------------ Regards, Vassiliev V. V. http://www.managed-vcl.com - using .Net objects in Delphi for Win32 + ADO.Net http://www.oledbdirect.com - The fastest way to access MS SQL Server, MS Jet (Access) and Interbase (through OLEDB) "Susan Leigh" <magine (AT) turtleflight (DOT) com> ???????/???????? ? ???????? ?????????: news:40222723$1 (AT) newsgroups (DOT) borland.com... ...also, when I used the OLE connection string, I get an exception with an "unspecified error" (that's the E.message) when I set ADODataset.Active:=true. (I had that same error with the third-party ADO component in that past when using the OLE connect string, which is why I was using the ODBC connection instead.) Viatcheslav V. Vassiliev wrote: Use Jet OLEDB provider for MS Access databases, not ODBC. Connection string is: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:TempMyDB.mdb; This provider is native for MS Access. //------------------------------------------ Regards, Vassiliev V. V. http://www.managed-vcl.com - using .Net objects in Delphi for Win32 + ADO.Net http://www.oledbdirect.com - The fastest way to access MS SQL Server, MS Jet (Access) and Interbase (through OLEDB) "Susan Leigh" <magine (AT) turtleflight (DOT) com> ???????/???????? ? ???????? ?????????: news:40215ab7 (AT) newsgroups (DOT) borland.com... I don't know the error code because the exception is in the debugger, not my program. There's no place to trap the exception in the code since it occurs when the automatic navigation (DBNavigator) component is clicked. After a long (30 seconds or more) pause during which the program is blocked, I get this popup in the IDE: ------------------------------------------------------------------------ Debugger Exception Notification Project MBot.exe raised exception class EOleException with message 'Single-row refresh operation returned multiple rows'. Process stopped. Use Step or Run to continue. ------------------------------------------------------------------------ The database is a local (i.e. on my hard drive) Microsoft Access 2000 database. The connect string I'm using is Driver={Microsoft Access Driver (*.mdb)};Dbq=RPG.mdb;Uid=Admin;Pwd=; Viatcheslav V. Vassiliev wrote: Could you provide exact error message and error code (EOleException.ErrorCode)? What is your DBMS? //------------------------------------------ Regards, Vassiliev V. V. http://www.managed-vcl.com - using .Net objects in Delphi for Win32 + ADO.Net http://www.oledbdirect.com - The fastest way to access MS SQL Server, MS Jet (Access) and Interbase (through OLEDB) "Susan Leigh" <magine (AT) turtleflight (DOT) com> ???????/???????? ? ???????? ?????????: news:40210547$1 (AT) newsgroups (DOT) borland.com... I do have a primary key for each table. Also, I didn't have this problem with the third-party ADO package in Delphi 5, it only began in Delphi 7 using their own ADO component. I feel there must be some setting that isn't obvious, that I'm missing. Viatcheslav V. Vassiliev wrote: Try to add primary key or at least unique index for every table that is used in SQL query. //------------------------------------------ Regards, Vassiliev V. V. http://www.managed-vcl.com - using .Net objects in Delphi for Win32 + ADO.Net http://www.oledbdirect.com - The fastest way to access MS SQL Server, MS Jet (Access) and Interbase (through OLEDB) "Susan Leigh" <magine (AT) turtleflight (DOT) com> ???????/???????? ? ???????? ?????????: news:4020b1f8 (AT) newsgroups (DOT) borland.com... I'm trying to port a Delphi 5 application that used a third-party ADO package, to Delphi 7 using the ADO components that come with it. In particular, I have a form with a DBGrid and DBNavigator, connected to an ADODataSource by way of a Datasource. It works okay except when I click the refresh button on the navigator, there is a very long delay during which the program is blocked, and finally an EOleException with the debugger message: 'Single-row refresh operation returned multiple rows'. What can I do to prevent this? thanks... Back to top Susan Leigh Guest Posted: Fri Feb 06, 2004 8:15 pm    Post subject: Re: slow ADO refresh, ending in error message No, only one application is using the database. Viatcheslav V. Vassiliev wrote: Quote: It is not possible to say without sample project that reproduces this behaviour. Is it possible that database was open by another application (for example, MS Access blocks access to database in table constructor mode)? //------------------------------------------ Regards, Vassiliev V. V. http://www.managed-vcl.com - using .Net objects in Delphi for Win32 + ADO.Net http://www.oledbdirect.com - The fastest way to access MS SQL Server, MS Jet (Access) and Interbase (through OLEDB) "Susan Leigh" <magine (AT) turtleflight (DOT) com> ???????/???????? ? ???????? ?????????: news:40226342$2 (AT) newsgroups (DOT) borland.com... But why did I not have the same delay with Delphi 5 and the third-party component? I used a client-side cursor in that case also. I've tested both versions with the identical database, so there's no difference in the number of records. Viatcheslav V. Vassiliev wrote: Pressing refresh calls Recordset.Resync(adAffectAll, adResyncAllValues) and this fetches all rows into in-memory cache if you are on client-side cursor - if your query returns much records you may have delay. Switching to server-side cursor should make this delay less but you will not be able to use filter/sort. //------------------------------------------ Regards, Vassiliev V. V. http://www.managed-vcl.com - using .Net objects in Delphi for Win32 + ADO.Net http://www.oledbdirect.com - The fastest way to access MS SQL Server, MS Jet (Access) and Interbase (through OLEDB) "Susan Leigh" <magine (AT) turtleflight (DOT) com> ???????/???????? ? ???????? ?????????: news:40222723$1 (AT) newsgroups (DOT) borland.com... ...also, when I used the OLE connection string, I get an exception with an "unspecified error" (that's the E.message) when I set ADODataset.Active:=true. (I had that same error with the third-party ADO component in that past when using the OLE connect string, which is why I was using the ODBC connection instead.) Viatcheslav V. Vassiliev wrote: Use Jet OLEDB provider for MS Access databases, not ODBC. Connection string is: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:TempMyDB.mdb; This provider is native for MS Access. //------------------------------------------ Regards, Vassiliev V. V. http://www.managed-vcl.com - using .Net objects in Delphi for Win32 + ADO.Net http://www.oledbdirect.com - The fastest way to access MS SQL Server, MS Jet (Access) and Interbase (through OLEDB) "Susan Leigh" <magine (AT) turtleflight (DOT) com> ???????/???????? ? ???????? ?????????: news:40215ab7 (AT) newsgroups (DOT) borland.com... I don't know the error code because the exception is in the debugger, not my program. There's no place to trap the exception in the code since it occurs when the automatic navigation (DBNavigator) component is clicked. After a long (30 seconds or more) pause during which the program is blocked, I get this popup in the IDE: ------------------------------------------------------------------------ Debugger Exception Notification Project MBot.exe raised exception class EOleException with message 'Single-row refresh operation returned multiple rows'. Process stopped. Use Step or Run to continue. ------------------------------------------------------------------------ The database is a local (i.e. on my hard drive) Microsoft Access 2000 database. The connect string I'm using is Driver={Microsoft Access Driver (*.mdb)};Dbq=RPG.mdb;Uid=Admin;Pwd=; Viatcheslav V. Vassiliev wrote: Could you provide exact error message and error code (EOleException.ErrorCode)? What is your DBMS? //------------------------------------------ Regards, Vassiliev V. V. http://www.managed-vcl.com - using .Net objects in Delphi for Win32 + ADO.Net http://www.oledbdirect.com - The fastest way to access MS SQL Server, MS Jet (Access) and Interbase (through OLEDB) "Susan Leigh" <magine (AT) turtleflight (DOT) com> ???????/???????? ? ???????? ?????????: news:40210547$1 (AT) newsgroups (DOT) borland.com... I do have a primary key for each table. Also, I didn't have this problem with the third-party ADO package in Delphi 5, it only began in Delphi 7 using their own ADO component. I feel there must be some setting that isn't obvious, that I'm missing. Viatcheslav V. Vassiliev wrote: Try to add primary key or at least unique index for every table that is used in SQL query. //------------------------------------------ Regards, Vassiliev V. V. http://www.managed-vcl.com - using .Net objects in Delphi for Win32 + ADO.Net http://www.oledbdirect.com - The fastest way to access MS SQL Server, MS Jet (Access) and Interbase (through OLEDB) "Susan Leigh" <magine (AT) turtleflight (DOT) com> ???????/???????? ? ???????? ?????????: news:4020b1f8 (AT) newsgroups (DOT) borland.com... I'm trying to port a Delphi 5 application that used a third-party ADO package, to Delphi 7 using the ADO components that come with it. In particular, I have a form with a DBGrid and DBNavigator, connected to an ADODataSource by way of a Datasource. It works okay except when I click the refresh button on the navigator, there is a very long delay during which the program is blocked, and finally an EOleException with the debugger message: 'Single-row refresh operation returned multiple rows'. What can I do to prevent this? thanks... Back to top Susan Leigh Guest Posted: Fri Feb 06, 2004 8:18 pm    Post subject: Re: slow ADO refresh, ending in error message It's the navigation component refresh button, and not my code, that results in the long delay. So I guess I need to change the behavior of the nav component...? Brian Bushay TeamB wrote: Quote: But why did I not have the same delay with Delphi 5 and the third-party component? I used a client-side cursor in that case also. I've tested both versions with the identical database, so there's no difference in the number of records. Your other database may have implemented refresh differently. This is not a good method for performance if you are using a large dataset. Requery will give you better performance if you want to refresh all records. Recordset.Resync(adAffectCurrent, adResyncAllValues) will give the best performance if you want to refresh only the current record. -- Brian Bushay (TeamB) [email]Bbushay (AT) NMPLS (DOT) com[/email] Back to top Susan Leigh Guest Posted: Fri Feb 06, 2004 8:19 pm    Post subject: Re: slow ADO refresh, ending in error message No lookup components. Brian Bushay TeamB wrote: Quote: ...also, when I used the OLE connection string, I get an exception with an "unspecified error" (that's the E.message) when I set ADODataset.Active:=true. Do you have lookupfields defined for this dataset? If you do they are causing this error but it is an error that will be generated only when you run out of the IDE so you can safely ignore it. You can also set LookupCache to true and the error won't be generated. -- Brian Bushay (TeamB) [email]Bbushay (AT) NMPLS (DOT) com[/email] Back to top Susan Leigh Guest Posted: Fri Feb 06, 2004 8:33 pm    Post subject: Re: slow ADO refresh, ending in error message And BTW, if Refresh is so inefficient (not that I doubt you) then why is it the default behavior for the nav component's refresh button? Frankly I'm beginning to feel that the third-party ADO component had a lot better implementation. Maybe I shouldn't even be bothering to port to Delphi 7. Brian Bushay TeamB wrote: Quote: But why did I not have the same delay with Delphi 5 and the third-party component? I used a client-side cursor in that case also. I've tested both versions with the identical database, so there's no difference in the number of records. Your other database may have implemented refresh differently. This is not a good method for performance if you are using a large dataset. Requery will give you better performance if you want to refresh all records. Recordset.Resync(adAffectCurrent, adResyncAllValues) will give the best performance if you want to refresh only the current record. -- Brian Bushay (TeamB) [email]Bbushay (AT) NMPLS (DOT) com[/email] Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First         BorlandTalk.com Forum Index -> Delphi Databases (ADO) All times are GMT Goto page 1, 2  Next Page 1 of 2   Jump to: Select a forum C++ Builder Newsgroups----------------C++ BuilderC++ Builder (ActiveX)C++ Builder (Announcements)C++ Builder (Command Line Tools)C++ Builder DatabasesC++ Builder Databases (ADO)C++ Builder Databases (Desktop)C++ Builder Databases (InterBase Express)C++ Builder Databases (Multi-Tier)C++ Builder Databases (SQL Servers)C++ Builder (Graphics)C++ Builder (IDE)C++ Builder (Internet)C++ Builder (Internet Socket)C++ Builder (Internet Web)C++ Builder (Jobs)C++ Builder (Language)C++ Builder (Language C++)C++ Builder (Compatibility with MFC)C++ Builder (Native API)C++ Builder (Non-Technical)C++ Builder (Students)C++ Builder (Third Party Tools)C++ Builder (Upgrade)C++ Builder (VCL)C++ Builder (VCL Components Usage)C++ Builder (VCL Components Development)C++ Builder (VCL Usage)C++ Builder (WinAPI)C++ Builder Installation Delphi Newsgroups----------------Delphi (General)Delphi ActiveX Controls UsageDelphi ActiveX Controls DevelopmentDelphi As400Delphi DatabasesDelphi Databases (ADO)Delphi Databases (dbExpress)Delphi Databases (Desktop)Delphi Databases (InterBase Express)Delphi Databases (Multi-Tier)Delphi Databases (SQL Servers)Delphi DeploymentDelphi GraphicsDelphi IDEDelphi InternationalizationDelphi InternetDelphi Internet Isapi-WebBrokerDelphi Internet WebSnapDelphi Internet WinsockDelphi JobsDelphi Language BASMDelphi Language ObjectPascalDelphi Model Driven Architecture (General)Delphi Model Driven Architecture (Third Party)Delphi MultimediaDelphi Native APIDelphi Non-TechnicalDelphi ObjectPascalDelphi OLE AutomationDelphi OO designDelphi OpenToolsAPIDelphi Reporting-ChartingDelphi RTLDelphi StudentsDelphi TeamSourceDelphi Thirdparty ToolsDelphi Thirdparty Tools (General)Delphi Thirdparty Tools (IntraWeb)Delphi Thirdparty Tools (RaveReports)Delphi UpgradeDelphi VCLDelphi VCL ComponentsDelphi VCL Components UsingDelphi VCL Components WritingDelphi WebServices SOAPDelphi WinAPIDelphi XMLDelphi Installationcomp.lang.pascal.delphi.components.misccomp.lang.pascal.delphi.components.usagecomp.lang.pascal.delphi.components.writingcomp.lang.pascal.delphi.databasescomp.lang.pascal.delphi.misccomp.sources.delphi JBuilder Newsgroups----------------JBuilderJBuilder AnnouncementsJBuilder Applet IssuesJBuilder As400JBuilder CompilerJBuilder CORBA/RMIJBuilder DatabasesJBuilder DB SwingJBuilder DebuggerJBuilder DeploymentJBuilder DocumentationJBuilder EnterpriseJBuilder HandheldJBuilder IDEJBuilder JavaJBuilder JavaAPIJBuilder Java LanguageJBuilder Java SwingJBuilder Java BeansJBuilder Java Beans UsageJBuilder Java Beans DevelopmentJBuilder JBCLJBuilder JDataStoreJBuilder JobsJBuilder LinuxJBuilder MacintoshJBuilder & MobileSetJBuilder MultiLingual AppsJBuilder Non-TechnicalJBuilder OpenToolsJBuilder Servlets (JSP)JBuilder StudentsJBuilder Team DevelopmentJBuilder Thirdparty ToolsJBuilder UpgradeJBuilder Installation InterBase Newsgroups----------------InterBaseInterBase Bug DiscussionInterBase GeneralInterBase IBConsoleInterBase OpenSourceInterBase ReplicationInterBase SQLInterBase ThirdpartyInterBase Installation Miscellaneous Borland Newsgroups----------------AppCenterAppServerEnteraVisiBroker  You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum Powered by phpBB © 2001, 2006 phpBB Group SEO toolkit © 2004-2006 webmedic.