Please help with SQL string using ADO

[deadseasquirrels]

I am getting a little bit closer to getting my macro to connect to my database. I can get it to connect using ADO and a short SQL string, but when I try to enter in a long string like below:

Private Sub SQLTest()

    Dim dbsNDF As New ADODB.connection
    Dim connString As String
    Dim dbRS As Object
    Dim sqlString As String
    
    connString = "Provider=MSDAORA.1;Password=[pwd];User ID=[id];Data Source=DKARCH1.WORLD;Persist Security Info=True"
    dbsNDF.connectionString = connString
    dbsNDF.Open

    sqlString = "SELECT  SO.PURCHASE.STATUS, SO.STUDENT.ENTRY_DATE, SO.STAFF.USER_NAME, " & _
        "SO.PURCHASE.CODE, SO.STUDENT.FIRST_NAME,SO.STUDENT.LAST_NAME " & _
        "FROM SO.STUDENT, SO.PURCHASE"

    MsgBox (sqlString)
    Set dbRS = dbsNDF.Execute(sqlString)
    
    Cells(106, 1).CopyFromRecordset dbRS
    
    If dbRS.EOF And dbRS.BOF Then
        MsgBox "End of File"
        GoTo CleanUpAndClose
    Else

        'If dbRS.Supports(adMovePrevious) Then
        '    MsgBox ("wilson, in adMoveFirst")
        'End If
        dbRS.MoveFirst
        Do While Not dbRS.EOF
            If dbRS.RecordCount = 0 Then
                MsgBox ("empty")
            End If
            MsgBox (dbRS.Fields(1).Value)
            dbRS.MoveNext
        Loop
    End If
    
CleanUpAndClose:
    dbRS.Close
    dbsNDF.Close
    Set dbRS = Nothing
    Set dbsNDF = Nothing
End Sub

I get an error. So I know everything else works, I just can't figure out why my string doesn't work. My actual string is actual much longer than this one but I cut it down hoping it would help me figure out what was wrong, but I still can't figure it out from this part. If anybody can help, I would greatly appreciate it. Thank you.

 

[kieran]

The SQL statement has no join between SO.STUDENT and SO.PURCHASE. YOu will therefore get a cartesian join. THis may not be supported by the database.
Try adding a join in a WHERE clause and see if it makes a difference.

eg

SELECT SO.PURCHASE.STATUS, SO.STUDENT.ENTRY_DATE, SO.STAFF.USER_NAME, " & _
"SO.PURCHASE.CODE, SO.STUDENT.FIRST_NAME,SO.STUDENT.LAST_NAME " & _
"FROM SO.STUDENT, SO.PURCHASE"

 

[deadseasquirrels]

I'll try to do that, but i had a WHERE statement in there originally and I took it out for simplification reasons. I'll put it back in but I don't think that is the problem because I used this string

SELECT v_intg_status_all.DATABASE, v_intg_status_all.DK_BOX, v_intg_status_all.INT_THRU, v_intg_status_all.BOB_INT_THRU" & Chr(13) & "" & Chr(10) & "FROM so.v_intg_status_all

and it worked just fine. I'll try it though and see how it goes.

 

[deadseasquirrels]

Well I added the WHERE function as shown below:

Private Sub SQLTest()

    Dim dbsNDF As New ADODB.connection
    Dim connString As String
    Dim dbRS As Object
    Dim sqlString As String
    
    connString = "Provider=MSDAORA.1;Password=[pwd];User ID=[uid];Data Source=DKARCH1.WORLD;Persist Security Info=True"
    dbsNDF.connectionString = connString
    dbsNDF.Open

    sqlString = "SELECT  SO.PURCHASE.STATUS, SO.STUDENT.ENTRY_DATE, SO.STAFF.USER_NAME, " & _
        "SO.PURCHASE.CODE, SO.STUDENT.FIRST_NAME,SO.STUDENT.LAST_NAME " & _
        "FROM SO.STUDENT, SO.PURCHASE " & _
        "WHERE   SO.STUDENT.ENTRY_USER_ID = SO.STAFF.ID " & _
        "AND     SO.PURCHASE.PRODUCT_ID = SO.PRODUCT.ID" & _
        "AND     SO.PRODUCT_GROUP.ID = SO.PRODUCT.PROD_GROUP_ID" & _
        "AND     SO.STUDENT.SCHOOL_ID = SO.SCHOOL.ID"

    MsgBox (sqlString)
    Set dbRS = dbsNDF.Execute(sqlString)
    
    Cells(106, 1).CopyFromRecordset dbRS
    
    If dbRS.EOF And dbRS.BOF Then
        MsgBox "End of File"
        GoTo CleanUpAndClose
    Else

        'If dbRS.Supports(adMovePrevious) Then
        '    MsgBox ("wilson, in adMoveFirst")
        'End If
        dbRS.MoveFirst
        Do While Not dbRS.EOF
            If dbRS.RecordCount = 0 Then
                MsgBox ("empty")
            End If
            MsgBox (dbRS.Fields(1).Value)
            dbRS.MoveNext
        Loop
    End If
    
CleanUpAndClose:
    dbRS.Close
    dbsNDF.Close
    Set dbRS = Nothing
    Set dbsNDF = Nothing
End Sub

but still it doesn't work. The error says "The command is not properly ended. What now, I feel like I'm really close but I can't finish it off.

The strange thing is, I use this same exact script in another program (Golden) and I hit the same database and it works fine.