Shamil Salakhetdinov
shamil at smsconsulting.spb.ru
Fri Oct 29 13:08:37 CDT 2010
Thank you, John, Yes, I'm glad too but that still means some reprogramming because I used IN clause in several places in one custom application... Well, first of all I will try to use linked tables instead of IN clause. If that works (I doubt it will) then it will be the most easy solution. Although then I will have to find a way how to reattach linked tables from .NET C# code without using DAO - using OleDB preferably. Is it possible at all? Anybody here knows how to do such reattachment? If linked tables will not work then I will have to reprogram even more... Thank you. -- Shamil -----Original Message----- From: dba-vb-bounces at databaseadvisors.com [mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: 29 ??????? 2010 ?. 21:56 To: Discussion concerning Visual Basic and related programming issues. Subject: Re: [dba-VB] Unspecified Error Glad you figured it out! John W. Colby www.ColbyConsulting.com On 10/29/2010 1:43 PM, Shamil Salakhetdinov wrote: > Hi All, > > Yes, the issue was with IN clause - here is the test solution: > > http://northwind.codeplex.com/releases/view/26600#DownloadId=162049 > > It has tow tests of the same join SQL with and without using IN clause. > Below is the sample output of the test case using IN clause - it fails. > When IN clause isn't used then it works OK. > > F:\Temp6\OleDbProviderTest\Bin>OleDbProviderTest.exe 1 > + ******** OleDbConnection Test ********* + > 1st arg = 1 - test case > 2nd arg = 100 max cycles qty > Test case #1: use IN '' clause > Test case #2: DO NOT use IN '' clause > + ************* TEST SQL **************** + > SELECT > Order.OrderID, Order.OrderDate, > Customer.CompanyName, Employee.FirstName, > Employee.LastName, Product.ProductName, > OrderDetail.Quantity, Category.CategoryName FROM Customer > INNER JOIN ((select * from Employee IN '' > [;Database=F:\Temp6\OleDbProvider > Test\Bin\Northwind.NET.mdb]) as Employee > INNER JOIN (((select * from Category IN '' > [;Database=F:\Temp6\OleDbProvide > rTest\Bin\Northwind.NET.mdb]) as Category > INNER JOIN (select * from Product IN '' > [;Database=F:\Temp6\OleDbProviderTes > t\Bin\Northwind.NET.mdb]) as Product ON Category.CategoryID = > Product.CategoryID > ) > INNER JOIN ((select * from [Order] IN '' > [;Database=F:\Temp6\OleDbProviderTe > st\Bin\Northwind.NET.mdb]) as [Order] > INNER JOIN (select * from OrderDetail IN '' > [;Database=F:\Temp6\OleDbProvid > erTest\Bin\Northwind.NET.mdb]) as OrderDetail > ON Order.OrderID = OrderDetail.OrderID) > ON Product.ProductID = OrderDetail.ProductID) > ON Employee.EmployeeID = Order.EmployeeID) > ON Customer.CustomerId = Order.CustomerId ORDER BY > Order.OrderDate DESC > - ************* TEST SQL **************** - 29/10/2010 21:36:31: 1. > ThreadCount = 9 29/10/2010 21:36:32: 2. ThreadCount = 18 29/10/2010 > 21:36:32: 3. ThreadCount = 21 29/10/2010 21:36:33: 4. ThreadCount = 26 > 29/10/2010 21:36:33: 5. ThreadCount = 31 29/10/2010 21:36:33: 6. > ThreadCount = 33 29/10/2010 21:36:34: 7. ThreadCount = 36 29/10/2010 > 21:36:34: 8. ThreadCount = 38 29/10/2010 21:36:34: 9. ThreadCount = 37 > 29/10/2010 21:36:35: 10. ThreadCount = 33 29/10/2010 21:36:35: 11. > ThreadCount = 36 29/10/2010 21:36:36: 12. ThreadCount = 40 29/10/2010 > 21:36:36: 13. ThreadCount = 44 29/10/2010 21:36:36: 14. ThreadCount = > 46 29/10/2010 21:36:37: 15. ThreadCount = 45 29/10/2010 21:36:37: 16. > ThreadCount = 52 29/10/2010 21:36:37: 17. ThreadCount = 57 29/10/2010 > 21:36:38: 18. ThreadCount = 61 29/10/2010 21:36:38: 19. ThreadCount = > 60 29/10/2010 21:36:38: 20. ThreadCount = 65 > TestConnection.ERROR:Unspecified error > TestConnection.ERROR:Unspecified error > TestConnection.ERROR:Unspecified error TestConnection.ERROR:Cannot > open any more databases. > TestConnection.ERROR:Cannot open any more databases. > 29/10/2010 21:36:39: 21. ThreadCount = 61 TestConnection.ERROR:Cannot > open any more databases. > 29/10/2010 21:36:39: 22. ThreadCount = 63 > TestConnection.ERROR:Unspecified error 29/10/2010 21:36:39: 23. > ThreadCount = 63 TestConnection.ERROR:Unspecified error > TestConnection.ERROR:Unspecified error > TestConnection.ERROR:Unspecified error TestConnection.ERROR:Cannot > open any more databases. > 29/10/2010 21:36:40: 24. ThreadCount = 62 TestConnection.ERROR:Cannot > open any more databases. > TestConnection.ERROR:Unspecified error TestConnection.ERROR:Cannot > open any more databases. > 29/10/2010 21:36:40: 25. ThreadCount = 62 TestConnection.ERROR:Cannot > open any more databases. > TestConnection.ERROR:Cannot open any more databases. > TestConnection.ERROR:Unspecified error 29/10/2010 21:36:40: 26. > ThreadCount = 63 TestConnection.ERROR:Unspecified error > TestConnection.ERROR:Cannot open any more databases. > TestConnection.ERROR:Cannot open any more databases. > 29/10/2010 21:36:41: 27. ThreadCount = 61 29/10/2010 21:36:41: 28. > ThreadCount = 64 TestConnection.ERROR:Unspecified error > TestConnection.ERROR:Unspecified error > TestConnection.ERROR:Unspecified error TestConnection.ERROR:Cannot > open any more databases. > TestConnection.ERROR:Cannot open any more databases. > 29/10/2010 21:36:41: 29. ThreadCount = 62 > TestConnection.ERROR:Unspecified error 29/10/2010 21:36:42: 30. > ThreadCount = 63 TestConnection.ERROR:Unspecified error > TestConnection.ERROR:Unspecified error TestConnection.ERROR:Cannot > open any more databases. > 29/10/2010 21:36:42: 31. ThreadCount = 62 TestConnection.ERROR:Cannot > open any more databases. > 29/10/2010 21:36:42: 32. ThreadCount = 55 TestConnection.ERROR:Cannot > open any more databases. > 29/10/2010 21:36:43: 33. ThreadCount = 56 StartTime = 29/10/2010 > 21:36:31, EndTime = 29/10/2010 21:36:43 OK, TotalCount = 297, > ElapsedTime = 0.0032 hours, ErrCount = 31 Press any key to quit... > TestConnection.ERROR:Cannot open any more databases. > > > Thank you. > > -- > Shamil > > > <<< snip>>>