-
Jun 13th, 2011, 06:22 AM
#1
Thread Starter
Lively Member
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
-
Jun 13th, 2011, 06:54 AM
#2
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
-
Jun 13th, 2011, 07:02 AM
#3
Thread Starter
Lively Member
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
-
Jun 13th, 2011, 07:30 AM
#4
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
-
Jun 13th, 2011, 09:04 AM
#5
Thread Starter
Lively Member
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
-
Jun 13th, 2011, 09:30 AM
#6
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
-
Jun 13th, 2011, 09:35 AM
#7
Thread Starter
Lively Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|