Error message: An unexpected token "<END OF STATEMENT">...

I am getting this error message when i run my getresults loop
"SQL0104N An unexpected token "<END OF STATEMENT>" was found following "".Expected tokens may include "IS".

Code:

---

Sub getresults()
 On Error GoTo errorhandler
  Dim r As Integer
    Dim c As Integer
    Dim str As String
    Dim SQL As String
    Dim rtnVal As String
    Dim WhichWorksheet As String
    Dim NamedRange As String
    Dim fldCount As String
    Dim rs As Recordset
    Dim cn As ADODB.Connection    'Your loop currently picks up the string from the cells and passes it to the Getrecordset function.
                                'It is in that function that you need to create the recordset
    dbNames(0) = "AIS3AM4"
    dbNames(1) = "AIS3AM5"
    dbNames(2) = "AIS3AM7"
    dbNames(3) = "AIS3AM1"
    dbNames(4) = "AIS3AM3"
   
    Call CheckConnection
 
    Set MFrst = Nothing
    If MFcnn.State = 1 Then ' check connection to database
        Dim dbCount As Integer
        For dbCount = 0 To 4
            For c = 31 To 35
                For r = 2 To 39
                    LogStr = LogStr & "------------------------------" & vbCrLf
                    LogStr = LogStr & " Row " & r & " Col " & c & ", dbIndex " & dbCount & vbCrLf
                    LogStr = LogStr & "------------------------------" & vbCrLf
                    If Sheet1.Cells(r, c).Value <> " " Then
                        str = Sheet1.Cells(r, c).Value
                        LogStr = LogStr & str & vbCrLf
                        rtnVal = getrecordset(str, dbCount)
                        LogStr = LogStr & rtnVal & vbCrLf
                        Sheet1.Cells((r + (40 * dbCount)), c - 5).Value = rtnVal
                    Else
                        LogStr = LogStr & " Cell is empty so skipping record " & vbCrLf
                    End If
                Next r
            Next c
        Next dbCount
        If MFcnn.State = adStateOpen Then MFcnn.Close
    Else
        LogStr = LogStr & "------------------------------" & vbCrLf
        LogStr = LogStr & " SKIPPING ALL BECAUSE CONNECTION IS NOT MADE " & vbCrLf
        LogStr = LogStr & "------------------------------" & vbCrLf
        MsgBox "Connection not open. Skipping all queries", vbExclamation
    End If
   

    GoTo ExitSub
errorhandler:
    LogStr = LogStr & "    ERROR OCCURRED:      " & Err.Description & vbCrLf
    If MsgBox("An error occurred while performing action" & vbCrLf & vbCrLf & Err.Description & vbCrLf & vbCrLf & "Do you want to continue?", vbCritical + vbOKCancel, Err.Source) = vbOK Then
        rtnVal = vbNullString
        Resume Next
    End If
    If MFcnn.State = adStateOpen Then MFcnn.Close

---

there's nothing in that code that is database related.... so either the problem is in CheckConnection or in getrecordset....

-tg

the database it is connecting to is db2 an it doesnt seem to accept my sql statements "Use dbname" doesnt seem to be an acceptable format. The connection is working , successfully connected into the database

perhaps you could have mentioned that... and posted the code where your SQL is being built and used... that was what I was trying to tell you... there is nothing in that code above that has anything to do with the database. The closest thing there is is this line:
rtnVal = getrecordset(str, dbCount)
And it doesn't tell me anything at all... what's str? what does getrecordset do? what does it return?

It doesn't surprise me that use dbname doesn't work... db2 is files based database, not a server based system like SQL Server or Oracle. When you connect, you're connecting to a folder where the table files are located. so if you want to switch from one database to another, you have to disconnect from the one, and then re-connect to the other after making changes to the connection string.

-tg

ok sorry for not mentioning all the other information so the get results goes to the excel sheet and run sql and here is more of the code:

Code:

---



Public Function ConnectToDB(username As String, password As String, DatabaseEnv As String) 'recordset'

Range("CredsSaved").Value = "1"
Range("user").Value = username
Range("PWord").Value = password
Range("SaveEnv").Value = DatabaseEnv
Range("CredsSaved,User,PWord,SavedEnv").Font.Color = RGB(255, 255, 255)

If MFcnn.State = 1 Then
    MFcnn.Close
End If
On Error GoTo fErr
With MFcnn
    .Provider = "IBMDADB2.DB2COPY1"
    .Mode = adReadWrite
    .ConnectionString = "Password=" & password & ";Persist Security Info=True;User ID=" & username & ";Data Source=" & DatabaseEnv & ";Mode=ReadWrite;"
    .Open
    If (DatabaseEnv = "") Then
        MsgBox "Database name '" & DatabaseEnv & "' is not valid"
    End If
End With

If MFcnn.State = 1 Then
    ConnectToDB = 0
    Login.Hide
End If
                                    'this logs you in and connects you to the databases'
fContinue:
Exit Function

fErr:
Debug.Print Err.Description
MsgBox (Err.Description)
If Err.Number = -2147217843 Then
    LoginTries = LoginTries + 1
End If

If LoginTries = 2 Then
    ConnectToDB = 2
Else
    ConnectToDB = 1
End If
Exit Function

End Function

Public Sub Disconnect()

Range("Creds, CredsSaved").Clear

Set MRrst = Nothing
Set SYSRST = Nothing                                                'this saves your login details'
If MFcnn.State = 1 Then

MFcnn.Close

End If
Set MFcnn = Nothing
End Sub
Function getrecordset(str) As String
    'create recordset object
    Dim rs As New ADODB.Recordset
   
    If (MFcnn Is Nothing) Then
        MsgBox "Connection not open"
        LogStr = LogStr & "connection is nothing" & vbCrLf
    End If
    If (MFcnn.State = adStateClosed) Then
        MsgBox "Connection not open"
        LogStr = LogStr & "connection not opened" & vbCrLf
    End If
   
      MFcnn.Execute "Use " & dbNames(dbIndex)
     
    rs.ActiveConnection = MFcnn
   
    If rs.State = adStateOpen Then rs.Close
    'using the str variable open the recordset
    rs.Source = str
    Debug.Print str
    rs.Open
    'if recordset NOT NULL
    If Not (rs.BOF And rs.EOF) Then        'stores the sql query results
        getrecordset = rs(0)
        'getrecordset = Recordset
    Else
        'getrecordset = "No Result"
        getrecordset = vbNullString
    End If
                                'close and destroy recordset object
    If rs.State = adStateOpen Then rs.Close
    Set rs = Nothing
   
    If (MFcnn.State = adStateClosed) Then
    MsgBox "already logged in"
    LogStr = LogStr & "connection is open" & vbCrLf
    End If
End Function


Sub getresults()
 On Error GoTo errorhandler
  Dim r As Integer
    Dim c As Integer
    Dim str As String
    Dim SQL As String
    Dim rtnVal As String
    Dim WhichWorksheet As String
    Dim NamedRange As String
    Dim fldCount As String
    Dim rs As Recordset
    Dim cn As ADODB.Connection    'Your loop currently picks up the string from the cells and passes it to the Getrecordset function.
                                'It is in that function that you need to create the recordset
   
    Call CheckConnection
 
    Set MFrst = Nothing
    If MFcnn.State = 1 Then ' check connection to database
        For c = 33 To 35
            For r = 2 To 39
                LogStr = LogStr & "------------------------------" & vbCrLf
                LogStr = LogStr & " Row " & r & " Col " & c & vbCrLf
                LogStr = LogStr & "------------------------------" & vbCrLf
                If Sheet1.Cells(r, c).Value <> "" Then
                    str = Sheet1.Cells(r, c).Value
                    LogStr = LogStr & str & vbCrLf
                    rtnVal = getrecordset(str)
                    LogStr = LogStr & rtnVal & vbCrLf
                    Sheet1.Cells(r, c - 5).Value = rtnVal
                Else
                    LogStr = LogStr & " Cell is empty so skipping record" & vbCrLf
                End If
            Next r
        Next c
        If MFcnn.State = adStateOpen Then MFcnn.Close
    Else
        MsgBox "Connection not open. Skipping all queries", vbExclamation
    End If
   

    GoTo ExitSub
errorhandler:
    LogStr = LogStr & "    ERROR OCCURRED :      " & Err.Description & vbCrLf
    If MsgBox("An error occurred while performing action" & vbCrLf & vbCrLf & Err.Description & vbCrLf & vbCrLf & "Do you want to continue?", vbCritical + vbOKCancel, Err.Source) = vbOK Then
        rtnVal = vbNullString
        Resume Next
    End If
    If MFcnn.State = adStateOpen Then MFcnn.Close
   
ExitSub:

   
    Open ThisWorkbook.Path & IIf(Right(ThisWorkbook.Path, 1) = "\", "", "\") & "log" For Append As #99
    Print #99, "***********************************************************"
    Print #99, "****  LOG STATED AT " & Now & "  ********"
    Print #99, "***********************************************************"
    Print #99, "Conn Str : " & MFcnn.ConnectionString & vbCrLf
    Print #99, "***********************************************************"
    Print #99, LogStr
    Close #99
    MsgBox "Log File saved at '" & ThisWorkbook.Path & IIf(Right(ThisWorkbook.Path, 1) = "\", "", "\") & "log" & "'"
End Sub




Static Sub CheckConnection()
    MsgBox "Establishing connection"
    If MFcnn.State = 0 Then
        If Range("User").Value = "" Or Range("PWord").Value = "" Or Range("SavedEnv").Value = "" Then
            Login.Show
        Else
            Call ConnectToDB(Range("User").Value, Range("PWord").Value, Range("SavedEnv").Value)
        End If
    Else

        On Error GoTo fErr
        Set MFrst = Nothing
        MFrst.Open sqlString, MFcnn, adOpenStatic, adLockReadOnly
   
    End If
    If MFcnn.State = adStateOpen Then
        MsgBox "Connection Established successfully!"
    Else
        MsgBox "Connection not open. Please try again to connect"
    End If
   
   
    Exit Sub
   
     
fErr:

Debug.Print Err.Description    'checks users login and stores the details'

If Range("CredsSaved").Value = "1" Then
    Call ConnectToDB(Range("User").Value, Range("PWord").Value, Range("SaveEnv").Value)
Else
    Login.Show
End If


End Sub

---

Have you tried displaying your SQL before running it? Make sure it's correct. Sounds like it's building an invalid SQL statement... What I suspect is that it's reading a field or something from the Excel file that has a space in it, which is throwing off the full SQL.

-tg

Actually i think that could be the problem i have been double checking the sql and i had to add ' and "" but it doesnt seem to be updating these e.g. when i login to log they sql queries dont have "" ' thats why they are not being successful :s