VBA conversion of MySQL erroneously returns nothing

[Calligra]

I've spent the past 3 days working with MySQL and vba within the Excel spreadsheet attempting to utilize a sql query within my code to see if the value within the column "thetext" is contained within a cell within the worksheet. Any assistance would be greatly appreciated!

If I put the following code into mySQLCC, the command works just as desired and the paragraph starting with "There may" is returned:
[green]set @MyMessage := "Running Opera on Suse Linux 10.1 menus do not appear. The sub window
for
the menu is drawn. No select actions take place, i.e. clicking on where a menu
entry should be does nothing. For instance you cannot logout via the last menu
entry. Sometimes if you click fast enough you can get a menu item to go.

There may be more bugs than this. Bryan initially wrote his stuff with only
firefox and IE as targets. Running Opera on Suse Linux menus do not appear. The sub
window for
the menu is drawn. No select actions take place, i.e. clicking on where a menu
entry should be does nothing. For instance you cannot logout via the last menu
entry. Sometimes if you click fast enough you can get a menu item to go.";

SELECT thetext FROM longdescs WHERE thetext in (SELECT thetext FROM longdescs WHERE (507 = 
     bug_id AND 20070704 <= bug_when AND thetext NOT LIKE "%" + @MyMessage + "%"))

In the attempt to utilize the same statement within my vba code, I cannot get a return of any values:

myString = .Cells(irow, 17).Formula

vSQL = "SELECT thetext FROM longdescs WHERE thetext in (SELECT thetext FROM longdescs WHERE ( thetext LIKE ""%" & Left(myString, Len(myString) - 2) & "%"" AND " & myBug & " = bug_id AND " & myDay & " <= bug_when))"

NOR

vSQL = "SELECT thetext FROM longdescs WHERE thetext in (SELECT thetext FROM longdescs WHERE ( thetext LIKE ""*" & Left(myString, Len(myString) - 2) & "*"" AND " & myBug & " = bug_id AND " & myDay & " <= bug_when))"

NOR

vSQL = "SET @MyMessage := """ & Left(myString, Len(myString) - 2) & """;" & Chr(10) & Chr(13)

NOR

vSQL = vSQL & "SELECT thetext FROM longdescs WHERE thetext in (SELECT thetext FROM longdescs WHERE (thetext LIKE ""%"" + @MyMessage + ""%"" AND " & myBug & " = bug_id AND " & myDay & " <= bug_when))"

The last statement just return a -2147217900 SQL error.

[hensa22]

could pls use tag code, so we can help you

[RajWolf]

I to did not under stand your code properly, but i think you are missing

single quote every where



SELECT thetext FROM longdescs WHERE thetext in (SELECT thetext FROM longdescs WHERE (507 =
bug_id AND 20070704 <= bug_when AND thetext NOT LIKE " %" + @MyMessage + "%"))

instead use

SELECT thetext FROM longdescs WHERE thetext in 
(SELECT thetext FROM longdescs WHERE (507 = 
bug_id AND 20070704 <= bug_when AND thetext NOT LIKE '%" + @MyMessage + "%' ))"

HOPE I HAVE UNDERSTOOD YOU CORRECT

[PeejAvery]

I to did not under stand your code properly, but i think you are missing single quote every where

Basically, Calligra, you have tried to echo double quotes the VB way by doing "". In SQL, you use single-quotes around field values.

[Calligra]

OK the single quotes didn't work either and I still have no clue as why this query returns no values from the DB but I do thank everyone that tried to assist. I have found a work around to my problem. Basically I had to split the cell up into fragments and then do a direct one to one comparison with each record. Not very elegant but seems to be working great. The code is as follows:

    Dim conn As ADODB.Connection
    Dim rs2 As ADODB.Recordset
    Dim vSQL As String

    Dim myDay As Double
    Dim myDate As Date
    Dim myBug As Integer
    Dim myString As String
    Dim myFieldValue As String
    
    Dim i, i2, iRow, strLen, itemp As Long
    Dim x As Integer
    
    Dim myText() As String
    Dim myChar As String
    Dim vBool As Boolean

    Set conn = New ADODB.Connection
    conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
            & "SERVER=server;" _
            & "DATABASE=dbname;" _
            & "UID=uid;" _
            & "PWD=password;"

    conn.CursorLocation = adUseClient
    conn.Open

    myDate = Now
    myDate = DatePart("yyyy", myDate) & "/" & DatePart("m", myDate) - 2 & "/" & DatePart("d", myDate) - 15
    myDay = Format(myDate, "yyyymmddhhmmss")
    
    myBug = 49
    
    vSQL = "SELECT LD.thetext, LD.bug_when, P.realname FROM longdescs LD, profiles P WHERE " & myBug & " = LD.bug_id"
    Set rs2 = New ADODB.Recordset
    
    On Error Resume Next
    rs2.Open vSQL, conn, , , adCmdText
    
    With Sheet17
        If .Cells(ActiveCell.Row, 1).Value = myBug And ActiveSheet.CodeName = "Sheet17" Then
            iRow = ActiveCell.Row
        Else
            Sheet17.Activate
            Cells.Find(What:=myBug, After:=Range("A1"), LookIn:= _
                xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:= _
                xlNext, MatchCase:=False, SearchFormat:=False).Activate
            If Err = 91 Then
                Err = 0
                Cells.Find(What:=myBug, After:=Range("A1"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
                If Err = 91 Then
'                    GoTo Label1
                Else
                    iRow = ActiveCell.Row
                End If
            Else
                iRow = ActiveCell.Row
            End If
        End If
        
        For i = 1 To 1000
            myString = myString & .Cells(iRow, 17).Value
            If .Cells(iRow + i, 2).Value <> "" Then
                Exit For
            End If
        Next i
        
        'Clean out the extraneous chr(10)'s
        If myString Like "*" & Chr(10) & Chr(10) & "*" Then
            'Count the number of Chr(10)'s in the string
            strLen = Len(myString)
            ReDim myText(0)
            x = 0
            For i = 1 To strLen
                If i = 1 Then
                    myChar = Right(Left(myString, i), strLen - i)
                Else
                    myChar = Right(Right(Left(myString, i), strLen - i), i - (i - 1))
                End If
                
                If (myChar = Chr(10) And (Right(Right(Left(myString, i + 1), strLen - (i + 1)), (i + 1) - ((i + 1) - 1)) = Chr(10) And (Right(Right(Left(myString, i + 2), strLen - (i + 2)), (i + 2) - ((i + 2) - 1)) = "-")) Or i = strLen - 1) Then
                    If (i <> strLen - 1) And (i + 2 <> strLen) Then
                        i = i + 2
                    End If
                    If x = 0 Then
                        myText(x) = Left(myString, i - 3)
                    ElseIf (i <> strLen - 1) And (i + 2 <> strLen) Then
                        myText(x) = Right(Left(myString, i - 3), (i - 2) - (i2 + 1))
                        
                    Else
                        itemp = Len(Right(Left(myString, i - (strLen - i)), ((i - (strLen - i)) - i2)))
                        myText(x) = Right(Left(myString, i - (strLen - i)), ((i - (strLen - i)) - i2))
                    End If
                    
                    If (i + 2 <> strLen) And (i + 1 <> strLen) Then
                        x = x + 1
                        ReDim Preserve myText(x)
                        'Progress i until all of the comment field has been passed
                        For i2 = i To strLen
                            If Right(Right(Left(myString, i2), strLen - i2), i2 - (i2 - 1)) = Chr(10) And Right(Right(Left(myString, (i2 - 2)), strLen - (i2 - 2)), (i2 - 2) - ((i2 - 2) - 1)) = "-" Then
                                i = i2
                                Exit For
                            End If
                        Next i2
                    Else
                        i = strLen
                    End If
                End If
            Next i
        End If
        
        
        Do Until rs2.EOF
            vBool = False
            If skipBool = False Then
                For x = 0 To UBound(myText)
                    If rs2!thetext = Left(myText(x), Len(myText(x)) - 1) Or rs2!thetext = myText(x) Then
                    
                        vBool = True
                        rs2.MoveNext
                    Else
                        vBool = False
                    End If
                Next x
                
                If (x - 1 = UBound(myText)) And vBool = True Then skipBool = True
                
            End If
            
           
            If vBool = False Then   'No match was found so write new file
                Err = 0
                If .Cells(iRow, 17).Value = "" Then
                    .Cells(iRow, 17).Formula = rs2.Fields("thetext") & Chr(10) & Chr(10)
                Else
                    .Cells(iRow, 17).Formula = .Cells(iRow, 17).Formula & "------- Additional Comment #" & rs2.AbsolutePosition & " From " & rs2!realname & " " & Format(rs2!bug_when, "yyyy-mm-dd hh:mm") & " [reply] -------" & Chr(10) & rs2.Fields("thetext") & Chr(10) & Chr(10)
                    If Err = 1004 Then  'The cell is full and record must be written to new line
                        Err = 0
                        Rows(iRow + 1 & ":" & iRow + 1).Insert Shift:=xlDown
                        iRow = iRow + 1
                        .Cells(iRow, 17).Formula = .Cells(iRow, 17).Formula & "------- Additional Comment #" & rs2.AbsolutePosition & " From " & rs2!realname & " " & Format(rs2!bug_when, "yyyy-mm-dd hh:mm") & " [reply] -------" & Chr(10) & rs2.Fields("thetext") & Chr(10) & Chr(10)
                    End If
                End If
            End If
            rs2.MoveNext
        Loop
        On Error Resume Next
    End With
    rs2.Close
    conn.Close
    
    Set rs2 = Nothing
    Set conn = Nothing
End Sub

[dglienna]

is your field a date or string? try this

vSQL = "SELECT LD.thetext, LD.bug_when, P.realname FROM longdescs LD, profiles P WHERE '" & myBug & "' = LD.bug_id"

[Calligra]

Bug_When is a datetime stamp
Bug_ID is a mediumint(9)

and

thetext is a mediumtext field.

Bug_When is being passed the value of 20070704000000
Bug_ID is being passed 507 &
thetext is being passed a rather long string (of variable lengths) containing special characters.

I suspect that the issue is that the entire string wasn't making it through the vba code for when I parse the string out by paragraphs (as in the above code), the code will now do the comparison properly (both the equal and Like comparisons now will function as expected within the vba code); however when attempting to pull the desired fields from the DB via a comparison (even with the parsed strings), the resulting field is nothing.

This suspicion is further affirmed with the fact that passing the bug_when and bug_id, the correct information is returned & the only time the query fails is with the inclusion of the thetext portion of the query so the single vs. double quotes around the bug_id & bug_when portion of the query is kind of moot; however, I did try it both ways (single and double) and it made no difference whatsoever.