UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> Adodb Subquery Error    
 
   
Sailor
post Feb 3 2017, 12:14 AM
Post#1



Posts: 95
Joined: 14-December 16



I am using ADODB and VB6 , the following query is works fine with open method
CODE
"SELECT sub.want-Count(table1.job) AS totals,Count(table1.job) as totx,sub.JobName AS jobs " & _
            "FROM table1 " & _
            "RIGHT JOIN sub ON sub.JobName=table1.job " & _
            "GROUP BY sub.JobName , sub.want; "


but when trying to use a sub query as follows the error occurs :
CODE
"SELECT sub.want-Count(countx.job) AS totals,Count(countx.job) as totx,totx/sub.want as ratios, sub.JobName AS jobs " & _
                                "FROM (SELECT DISTINCT names,job,employ_type,in_rest FROM table1 WHERE employ_type='full' and in_rest=0) AS countx " & _
                                "RIGHT JOIN sub ON sub.JobName=countx.job " & _
                                "GROUP BY sub.JobName , sub.want; "


the error message is :

run-time error '2147467259 (80004005)':
method 'open' of object '_Recordset' failed
what's the wrong frown.gif
Go to the top of the page
 
 
Start new topic
Replies
Doug Steele
post Feb 3 2017, 08:49 AM
Post#2


UtterAccess VIP
Posts: 22,234
Joined: 8-January 07
From: St. Catharines, ON (Canada)


totx/sub.want as ratios looks invalid.

If you're trying to use the calculated totx field, try repeating the calculation in the SQL.
Go to the top of the page
 
Sailor
post Feb 3 2017, 12:27 PM
Post#3



Posts: 95
Joined: 14-December 16



thanks Doug for the reply
I am afraid it's not the problem
even with the follows I get the same error :
CODE
"SELECT sub.want-Count(table1.job) AS totals,Count(table1.job) as totx,sub.JobName AS jobs " & _
                                "FROM (SELECT DISTINCT names,job FROM table1) AS countx " & _
                                "RIGHT JOIN sub ON sub.JobName=table1.job " & _
                                "GROUP BY sub.JobName , sub.want; "


I have forgot to explain my tables , they are as bellow :
table1 ,contains the fields (names = text , job = text)

sub , contains the fields (JobName = text , want = long number)
Go to the top of the page
 
Doug Steele
post Feb 3 2017, 01:26 PM
Post#4


UtterAccess VIP
Posts: 22,234
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Sorry: didn't look closely enough at your SQL. blush.gif

Because you've aliased the SELECT inside your query as countx, you cannot refer to table1. The rest of the query knows nothing about Table1. Try:

CODE
"SELECT sub.want-Count(countx.job) AS totals,Count(countx.job) as totx,sub.JobName AS jobs " & _
                                "FROM (SELECT DISTINCT names,job FROM table1) AS countx " & _
                                "RIGHT JOIN sub ON sub.JobName=countx.job " & _
                                "GROUP BY sub.JobName , sub.want; "
Go to the top of the page
 
Sailor
post Feb 3 2017, 01:57 PM
Post#5



Posts: 95
Joined: 14-December 16



CODE
"SELECT sub.want-Count(countx.job) AS totals,Count(countx.job) as totx,sub.JobName AS jobs " & _
                                "FROM (SELECT DISTINCT names,job FROM table1) AS countx " & _
                                "RIGHT JOIN sub ON sub.JobName=countx.job " & _
                                "GROUP BY sub.JobName , sub.want; "


I have tried that before it's ok in ms access , but in ADODB , it gives the error that I've described in my 1'st post
Go to the top of the page
 
Doug Steele
post Feb 3 2017, 03:53 PM
Post#6


UtterAccess VIP
Posts: 22,234
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Are you sure it's the query that's causing the error? Might there be a problem with your code opening the recordset?
Go to the top of the page
 
Sailor
post Feb 4 2017, 02:36 AM
Post#7



Posts: 95
Joined: 14-December 16



CODE
"SELECT sub.want-Count(table1.job) AS totals,Count(table1.job) as totx,sub.JobName AS jobs " & _
            "FROM table1 " & _
            "RIGHT JOIN sub ON sub.JobName=table1.job " & _
            "GROUP BY sub.JobName , sub.want; "


then why this one is works fine ?
as soon as removing the alias (countx) and the sub select
and it will works fine , but I need that subquery
OK , I will put in the bellow all the project hope you would notice something wrong
I am using a DataGrid to view the result

this is the entire project with error of course :

CODE
Private Sub Form_Load()
    Dim db As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim str As String
    Set db = New ADODB.Connection
    db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
        App.Path & "\" & "yy.mdb;Mode=Read|Write"
        
    str = "SELECT sub.want-Count(countx.job) AS totals,Count(countx.job) as totx,sub.JobName AS jobs " & _
                                "FROM (SELECT DISTINCT names,job FROM table1) AS countx " & _
                                "RIGHT JOIN sub ON sub.JobName=countx.job " & _
                                "GROUP BY sub.JobName , sub.want; "
            
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.Open str, db, adOpenStatic, adLockReadOnly
    Set rs.ActiveConnection = Nothing
    
    Set DataGrid1.DataSource = rs
    
    Set rs = Nothing
    db.Close
    Set db = Nothing
End Sub
Go to the top of the page
 

Posts in this topic



Custom Search


RSSSearch   Top   Lo-Fi    22nd January 2020 - 10:23 PM