Results 1 to 7 of 7

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

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Apr 2011
    Posts
    65

    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

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

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

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

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Apr 2011
    Posts
    65

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

    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

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

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

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Apr 2011
    Posts
    65

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

    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

  6. #6
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

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

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Apr 2011
    Posts
    65

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

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width