BorlandTalk.com Borland discussion newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  Multiple Steps OLE DB generated errors.          BorlandTalk.com Forum Index -> Delphi Databases (ADO) View previous topic :: View next topic   Author Message fiaola Guest Posted: Thu May 17, 2007 4:43 am    Post subject: Multiple Steps OLE DB generated errors. I'm getting this error when on this QUERY. Data is an AS/400 Database and D7 using ADO OLE Connection. ERROR: Multiple steps OLE DB generated errors. Here is the query. It was working fine before, but now i'm getting this error. SQL.Add('Select a.U0GLN, a.U0BDT, a.U0CRC, a.U0AM0, b.UGGLN, b.UGDES '); SQL.Add('From "MYLIB"."UBCRJT" a, '); SQL.Add('"MYLIB"."UBGLMST" b'); SQL.Add('WHERE a.U0GLN = b.UGGLN'); SQL.Add('AND a.U0BDT >= :pDateFrom '); {AND :pDateTo ');} SQL.Add('AND a.U0BDT <= :pDateTo '); Parameters.ParamByName('pDateFrom').Value := FormatDatetime('YYYYMMDD', dtFrom.Date); Parameters.ParamByName('pDateTo').Value := FormatDateTime('YYYYMMDD', dtTo.Date); Open; Any suggestion would be appreciated. Thanks Back to top Brian Bushay TeamB Guest Posted: Thu May 17, 2007 6:15 am    Post subject: Re: Multiple Steps OLE DB generated errors. Quote: I'm getting this error when on this QUERY. Data is an AS/400 Database and D7 using ADO OLE Connection. ERROR: Multiple steps OLE DB generated errors. Here is the query. It was working fine before, but now i'm getting this error. SQL.Add('Select a.U0GLN, a.U0BDT, a.U0CRC, a.U0AM0, b.UGGLN, b.UGDES '); SQL.Add('From "MYLIB"."UBCRJT" a, '); SQL.Add('"MYLIB"."UBGLMST" b'); SQL.Add('WHERE a.U0GLN = b.UGGLN'); SQL.Add('AND a.U0BDT >= :pDateFrom '); {AND :pDateTo ');} SQL.Add('AND a.U0BDT <= :pDateTo '); Parameters.ParamByName('pDateFrom').Value := FormatDatetime('YYYYMMDD', dtFrom.Date); Parameters.ParamByName('pDateTo').Value := FormatDateTime('YYYYMMDD', dtTo.Date); Open; If you are using persistent Tfields for this query try deleting them and recreating the tfields. -- Brian Bushay (TeamB) Bbushay (AT) NMPLS (DOT) com Back to top Dmitry Arefiev Guest Posted: Thu May 17, 2007 8:11 am    Post subject: Re: Multiple Steps OLE DB generated errors. Hello Quote: Parameters.ParamByName('pDateFrom').Value := FormatDatetime('YYYYMMDD', dtFrom.Date); Parameters.ParamByName('pDateTo').Value := FormatDateTime('YYYYMMDD', dtTo.Date); Why you are converting datetime value into string ? Why not just use datetime value: Parameters.ParamByName('pDateFrom').Value := dtFrom.Date; Parameters.ParamByName('pDateTo').Value := dtTo.Date; Regards, Dmitry -- Dmitry Arefiev - www.da-soft.com AnyDAC - Oracle, MySQL, MSSQL, MSAccess, IBM DB2, Sybase ASA, DbExpress, ODBC freeware data access engine ThinDAC - multitier data access engine Back to top fiaola Guest Posted: Thu May 17, 2007 8:11 am    Post subject: Re: Multiple Steps OLE DB generated errors. I'm not. I'm just changing the format so it will work with the query. "Dmitry Arefiev" <darefiev@da-soft.com> wrote in message news:464bdc71 (AT) newsgroups (DOT) borland.com... Quote: Hello Parameters.ParamByName('pDateFrom').Value := FormatDatetime('YYYYMMDD', dtFrom.Date); Parameters.ParamByName('pDateTo').Value := FormatDateTime('YYYYMMDD', dtTo.Date); Why you are converting datetime value into string ? Why not just use datetime value: Parameters.ParamByName('pDateFrom').Value := dtFrom.Date; Parameters.ParamByName('pDateTo').Value := dtTo.Date; Regards, Dmitry -- Dmitry Arefiev - www.da-soft.com AnyDAC - Oracle, MySQL, MSSQL, MSAccess, IBM DB2, Sybase ASA, DbExpress, ODBC freeware data access engine ThinDAC - multitier data access engine Back to top Ralf Jansen Guest Posted: Thu May 17, 2007 3:51 pm    Post subject: Re: Multiple Steps OLE DB generated errors. fiaola schrieb: Quote: I'm getting this error when on this QUERY. Data is an AS/400 Database and D7 using ADO OLE Connection. ERROR: Multiple steps OLE DB generated errors. Check the ADOConnection.Errors collection after the error you might gets some more details. And recheck what Dimitry asks. A Datetime type has no format so formatting a datetime means getting a string. That is what FormatDatetime does. -- Ralf Jansen deepinvent Software GmbH - Viersen, Germany - http://www.deepinvent.com Archiving E-mails with MailStore: http://www.mailstore.com Back to top Ralf Jansen Guest Posted: Thu May 17, 2007 3:57 pm    Post subject: Re: Multiple Steps OLE DB generated errors. Looking more closely at your sql i believe it misses some spaces. fiaola schrieb: Quote: SQL.Add('Select a.U0GLN, a.U0BDT, a.U0CRC, a.U0AM0, b.UGGLN, b.UGDES '); SQL.Add('From "MYLIB"."UBCRJT" a, '); SQL.Add('"MYLIB"."UBGLMST" b'); ^ here SQL.Add('WHERE a.U0GLN = b.UGGLN'); ^ and here SQL.Add('AND a.U0BDT >= :pDateFrom '); {AND :pDateTo ');} SQL.Add('AND a.U0BDT <= :pDateTo '); Parameters.ParamByName('pDateFrom').Value := FormatDatetime('YYYYMMDD', dtFrom.Date); Parameters.ParamByName('pDateTo').Value := FormatDateTime('YYYYMMDD', dtTo.Date); Open; -- Ralf Jansen deepinvent Software GmbH - Viersen, Germany - http://www.deepinvent.com Archiving E-mails with MailStore: http://www.mailstore.com Back to top yannis Guest Posted: Thu May 17, 2007 4:13 pm    Post subject: Re: Multiple Steps OLE DB generated errors. fiaola formulated the question : Quote: I'm getting this error when on this QUERY. Data is an AS/400 Database and D7 using ADO OLE Connection. ERROR: Multiple steps OLE DB generated errors. Here is the query. It was working fine before, but now i'm getting this error. SQL.Add('Select a.U0GLN, a.U0BDT, a.U0CRC, a.U0AM0, b.UGGLN, b.UGDES '); SQL.Add('From "MYLIB"."UBCRJT" a, '); SQL.Add('"MYLIB"."UBGLMST" b'); SQL.Add('WHERE a.U0GLN = b.UGGLN'); SQL.Add('AND a.U0BDT >= :pDateFrom '); {AND :pDateTo ');} SQL.Add('AND a.U0BDT <= :pDateTo '); Parameters.ParamByName('pDateFrom').Value := FormatDatetime('YYYYMMDD', dtFrom.Date); Parameters.ParamByName('pDateTo').Value := FormatDateTime('YYYYMMDD', dtTo.Date); Open; Any suggestion would be appreciated. Thanks First you do not need to convert the date parameters to string just assign them as dates and the rest are taken care by the library. Second if you do convert them make sure that you have the correct format for example in MS SQL server the correct format would be 'YYYY-MMM-DD' with out the dushes it raises an error. Third when you do convert them to a string then the values must enclosed in single or double quotes so use the command QuotedStr(FormatDateTime('YYYYMMDD', dtTo.Date)); for single quotes or add '"'+ FormatDateTime.......+'"' for double quotes. After all of the above are taken care then try to collect the error messages from the errors collection of the ADOConnection. Regards Yannis. Back to top Del Murray Guest Posted: Thu May 17, 2007 4:43 pm    Post subject: Re: Multiple Steps OLE DB generated errors. Are you doing SQL.CLEAR before you start "adding" the new sql statements ....??? Back to top fiaola Guest Posted: Fri May 18, 2007 5:46 am    Post subject: Re: Multiple Steps OLE DB generated errors. Yes i am clearing the SQL after closing and then changing the text. "Del Murray" <Del.Murray (AT) CreditHawk (DOT) Net> wrote in message news:464c4055 (AT) newsgroups (DOT) borland.com... Quote: Are you doing SQL.CLEAR before you start "adding" the new sql statements ...??? Back to top fiaola Guest Posted: Fri May 18, 2007 5:57 am    Post subject: Re: Multiple Steps OLE DB generated errors. If i do this: Close; SQL.Clear; SQL.Add('SELECT U0GLN,U0BDT,U0CRC,U0AM0,UGGLN,UGDES'); SQL.Add('FROM "TEPALIB"."UBCRJT",'); SQL.Add('"TEPALIB"."UBGLMST"'); SQL.Add('Where U0BDT Between 20070501 AND 20070517 '); //with this it work fine. SQL.Add('AND U0GLN < 173600'); //set limit on GL's to extract SQL.Add('AND U0GLN = UGGLN'); //Link tables SQL.Add('ORDER BY U0GLN'); //set sort order Open; If i hardcode the dates as specified above, it works fine. If i hard code the dates in the Parameter statement, it gives an error. The field type for the DATE FIELD is BCD. Parameters.ParamByName('pDateFrom').Value := 20070501; //this gives an error Parameters.ParamByName('pDateTo').Value := 20070517; "yannis" <none (AT) noware (DOT) non> wrote in message news:mn.8b557d7506323f40.0 (AT) noware (DOT) non... Quote: fiaola formulated the question : I'm getting this error when on this QUERY. Data is an AS/400 Database and D7 using ADO OLE Connection. ERROR: Multiple steps OLE DB generated errors. Here is the query. It was working fine before, but now i'm getting this error. SQL.Add('Select a.U0GLN, a.U0BDT, a.U0CRC, a.U0AM0, b.UGGLN, b.UGDES '); SQL.Add('From "MYLIB"."UBCRJT" a, '); SQL.Add('"MYLIB"."UBGLMST" b'); SQL.Add('WHERE a.U0GLN = b.UGGLN'); SQL.Add('AND a.U0BDT >= :pDateFrom '); {AND :pDateTo ');} SQL.Add('AND a.U0BDT <= :pDateTo '); Parameters.ParamByName('pDateFrom').Value := FormatDatetime('YYYYMMDD', dtFrom.Date); Parameters.ParamByName('pDateTo').Value := FormatDateTime('YYYYMMDD', dtTo.Date); Open; Any suggestion would be appreciated. Thanks First you do not need to convert the date parameters to string just assign them as dates and the rest are taken care by the library. Second if you do convert them make sure that you have the correct format for example in MS SQL server the correct format would be 'YYYY-MMM-DD' with out the dushes it raises an error. Third when you do convert them to a string then the values must enclosed in single or double quotes so use the command QuotedStr(FormatDateTime('YYYYMMDD', dtTo.Date)); for single quotes or add '"'+ FormatDateTime.......+'"' for double quotes. After all of the above are taken care then try to collect the error messages from the errors collection of the ADOConnection. Regards Yannis. Back to top Dmitry Arefiev Guest Posted: Fri May 18, 2007 8:11 am    Post subject: Re: Multiple Steps OLE DB generated errors. Hello Replace that: Quote: Parameters.ParamByName('pDateFrom').Value := 20070501; //this gives an error with this: Parameters.ParamByName('pDateFrom').Value := StrToDate('2007/05/01'); PS: That is right idea to use parameters <g> Regards, Dmitry -- Dmitry Arefiev - www.da-soft.com AnyDAC - Oracle, MySQL, MSSQL, MSAccess, IBM DB2, Sybase ASA, DbExpress, ODBC freeware data access engine ThinDAC - multitier data access engine Back to top fiaola Guest Posted: Fri May 18, 2007 1:28 pm    Post subject: Re: Multiple Steps OLE DB generated errors. Thanks for the tip. My query still crashes at the Parameter.ParamByName call. "Dmitry Arefiev" <darefiev@da-soft.com> wrote in message news:464d3a7e (AT) newsgroups (DOT) borland.com... Quote: Hello Replace that: Parameters.ParamByName('pDateFrom').Value := 20070501; //this gives an error with this: Parameters.ParamByName('pDateFrom').Value := StrToDate('2007/05/01'); PS: That is right idea to use parameters <g Regards, Dmitry -- Dmitry Arefiev - www.da-soft.com AnyDAC - Oracle, MySQL, MSSQL, MSAccess, IBM DB2, Sybase ASA, DbExpress, ODBC freeware data access engine ThinDAC - multitier data access engine 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 Page 1 of 1   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.