Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312

    Unanswered: Unexpected Query Results

    I have a function that compares a filename to a file pattern. If the filename matches a pattern the Pattern ID is returned. I am building an SQL string and opening an ADO recordset. The problem is, the SQL string returns zero records in the function, but if I use the Immediate window, copy the string and past it into a query it returns what I expect.

    Has anyone experienced this? I am using Option Explicit so I don't think it is a variable name. This is an example of the SQL:

    SELECT * FROM tblFileDefinition WHERE "Orders_2006_03_06.txt" LIKE [FilePattern]

    Any thoughts?

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I switched it to a DAO recordset and it works how I expected it would. I am trying to get away from DAO so I would rather keep it ADO. But if I can't find an answer I will leave it as a DAO recordset.

  3. #3
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by DCKunkle
    This is an example of the SQL:

    SELECT * FROM tblFileDefinition WHERE "Orders_2006_03_06.txt" LIKE [FilePattern]

    Any thoughts?
    Please copy and paste the entire line of code that includes the SQL statement. Sometimes the way you access the query determines its behavior, depending on the type of query.

    Sam

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    After you replied I remembered that the table is linked and I was using CurrentProject.Connection to get a reference to the connection. SO I thought I would create a connection to that database and try it that way. I get the same results. Here is my code for setting up the recordset:

    Code:
        Dim rstFileDefinitions As New ADODB.Recordset
        'Dim rstFileDefinitions As DAO.Recordset
        Dim cnnFileDefinitions As New Connection
        Dim strSQL As String
        
        PushCallStack "GetDefaultFileDefinition"
        
        On Error GoTo ErrorHandler
        
        cnnFileDefinitions.ConnectionString = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & COMPADatabasePath("COMPA Tables")
        cnnFileDefinitions.Open
        
        strSQL = "SELECT * FROM tblFileDefinition WHERE "
        strSQL = strSQL & "(""" & strFilename & """ LIKE [FilePattern])"
        
        rstFileDefinitions.Open strSQL, cnnFileDefinitions, adOpenStatic, adLockOptimistic
        'Set rstFileDefinitions = CurrentDb().OpenRecordset(strSQL, dbOpenDynaset)

  5. #5
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by DCKunkle
    After you replied I remembered that the table is linked and I was using CurrentProject.Connection to get a reference to the connection. SO I thought I would create a connection to that database and try it that way. I get the same results. Here is my code for setting up the recordset:

    Code:
        Dim rstFileDefinitions As New ADODB.Recordset
        'Dim rstFileDefinitions As DAO.Recordset
        Dim cnnFileDefinitions As New Connection
        Dim strSQL As String
        
        PushCallStack "GetDefaultFileDefinition"
        
        On Error GoTo ErrorHandler
        
        cnnFileDefinitions.ConnectionString = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & COMPADatabasePath("COMPA Tables")
        cnnFileDefinitions.Open
        
        strSQL = "SELECT * FROM tblFileDefinition WHERE "
        strSQL = strSQL & "(""" & strFilename & """ LIKE [FilePattern])"
        
        rstFileDefinitions.Open strSQL, cnnFileDefinitions, adOpenStatic, adLockOptimistic
        'Set rstFileDefinitions = CurrentDb().OpenRecordset(strSQL, dbOpenDynaset)
    Hi, DC,

    Try putting a stop in your program on the rstFileDefinitions.Open line of code, and analyze the contents of strSQL in the Immediate window. I think you'll find a problem there, although I'm not sure I can pinpoint what it is exactly. I just think you'll find it doesn't contain what you want it to.

    What you want (in English) is: strSQL = SELECT * FROM tblFileDefinition WHERE strFilename LIKE [FilePattern]. Unless strFilename is a field in tblFileDefinition, I don't see how this can work without a JOIN of some kind. Also, I hope FilePattern contains a "*" (star) at the end, or the LIKE won't work properly.

    The fact that this works in the Immediate window confounds me even more. It seems to me to have a syntax error in it!

    Scratching my head,

    Sam

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    FilePattern has data similar to "Orders_*.txt" so the LIKE should match properly. In fact, it does when the recordset is DAO. When I change it to an ADO recordset is when I have the problems.

  7. #7
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I understand, DC, what really confuses me is my first question: how does strFilename work without a JOIN? Also, did you get a chance to analyze strSQL?

    Sam

  8. #8
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    There is nothing wrong with the SQL in the strSQL variable. If I take the value, as it is executing (?strSQL in the Immediate window) and paste it into a new query, I get the results I expect.

    If I use a DAO recordset, and open the SQL in strSQL the function works like I expect it would.

    If I try to use ADO, using the same strSQL, I get an empty recordset.

  9. #9
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I give up. Anybody?

    Sam

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi DC

    Do you have Quoted Identiers set to yes? ANSI SQL (and by extension T-SQL as an option) states that double quotes are used to identify columns not values. The [ and ] of Access and SQL Server is actually proprietry so... what happens if you change to:
    Code:
        strSQL = "SELECT * FROM tblFileDefinition WHERE "
        strSQL = strSQL & "'" & strFilename & "'" LIKE [FilePattern]"
    ???

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh yeah - just noticed another thing:
    Your BE is SQL Server yes? Remember the wild cards are:

    Code:
       T-SQL    JET
       %        *
       _        ?
                #   (no real T-SQL equivelent - can be got round with [0-9]
    As such the underscores in txt string will match against any character.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Beg your pardon - forgot that you have an unconventional (reversed) WHERE clause. The underscores in the text string will match as literal characters but any %, _, [N] in the column [FilePattern] will be treated as wildcards.

    Deliberate yes?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    My backend is an Access database, not SQL Server. Do you have any suggestions for this situation?

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh - ok. Ignore the first post then. However, ADO still uses the SQL Server (or ANSI SQL) wildcards whereas DAO does not. Does [FilePattern] contain *s and ?s and #s ?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Yes. I have *s in all the fields. That is all I will be using.

Posting Permissions

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