Problems with building some dynamic SQL to talk to a MS Access DB


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Problems with building some dynamic SQL to talk to a MS Access DB

  1. #1
    Steve Moore Guest

    Problems with building some dynamic SQL to talk to a MS Access DB



    I have been having this problem with attempting to build some dynamic SQL
    to
    talk to a MS Access DB.
    The SQL I generate correctly brings back the records I require when using
    a
    query inside of MS Access itself
    but when I use the same SQL using a connect object no records are returned.

    I have been able to successfully bring back records with the connect using
    a
    "simple select * from tablename;"
    but as soon as I include a like '*Keyword*' I get no results.

    Any ideas?

    Steve

    Test script to generate a sql string dynamically for 2 key words (strSQL).
    I have included the sql string I used that did work (strSQL2).

    <% Option Explicit %>

    <HTML>
    <BODY>

    <h1>The Univation News Page</h1>
    <%
    Dim Connect, rstUnivation, strSQL, strSQL2
    Dim strKeyWords, strPlus, strKeyWord1, strKeyWord2, strKeyWord3
    Dim intKWdL, intWdPos
    Set Connect = Server.CreateObject("ADODB.Connection")
    Connect.Open "Avcc"


    strPlus="+"
    strKeyWords=cstr(Request.Form("KeyWord"))

    'if 0 is returned then a comma was not found
    intWdPos=Instr(strKeyWords, strPlus)
    intKWdL=Len(strKeyWords)
    strKeyWord1=Trim(Left(strKeyWords,intWdPos-1))
    strKeyWord2=Trim(Right(strKeyWords,intKWdl -intWdPos))

    strSQL="SELECT Title, URL, University, EntryDate, Contact, Name, Email FROM
    tblUnivation WHERE Title like '*"
    strSQL=strSQL & strKeyWord1 &"*'"
    strSQL=strSQL & " Or Title like '*" & strKeyWord2 & "*'"
    strSQL=strSQL & " ORDER BY Title;"

    strSQL2="SELECT Title, URL, University, EntryDate, Contact, Name, Email
    FROM
    tblUnivation; "
    Set rstUnivation = Connect.Execute(strSQL2)
    'Set rstUnivation = Connect.Execute(strSQL)
    %>
    <% do until rstUnivation.EOF %>
    <h2>Title: <a href="<% =rstUnivation("URL")
    %>"><%=rstUnivation("Title")%></a></h2>
    <b>University:</b> <%=rstUnivation("University")%><br>
    <b>EntryDate: </b><%=FormatDateTime(rstUnivation("EntryDate"), 1)%><br>
    <b>Contact: </b><%=rstUnivation("Contact")%><br>
    <b>Name: </b><%=rstUnivation("Name")%></a><br>
    <b>Email: </b><A
    href="mailto:<%=rstUnivation("Email")%>"><%=rstUnivation("Email")%></a><br>
    <br>
    <br>
    <br>
    <%=strSQL%>
    <% rstUnivation.MoveNext %>
    <% loop %>
    strSQL= <%=strSQL%><br>
    </BODY>
    </HTML>

    I WAS HOPING TO USE THE SQL WITH THE BETTER KEY WORD SEARCHING CODE BELOW.
    <% Option Explicit %>

    <HTML>
    <BODY>

    <h1>The Univation News Page</h1>
    <%
    Dim Connect, rstUnivation, strSQL
    Dim strKeyWords, strPlus, strKeyWord
    Dim intKWdL, intWdPos, i
    Dim aryKeyWords(10)
    Set Connect = Server.CreateObject("ADODB.Connection")
    Connect.Open "Avcc"

    strPlus="+"
    strKeyWords=cstr(Request.Form("KeyWord"))
    strSQL="SELECT Title, URL, University, EntryDate, Contact, Name, Email FROM
    tblUnivation WHERE "

    'if 0 is returned then a comma was not found
    'NOTE: syntax instr(startpos,str1,str2) did not work

    intWdPos=1
    i=1 'loop counter var
    Do Until intWdPos = 0

    if i <> 1 then
    strSQL=strSQL & "OR "
    end if

    intWdPos=Instr(strKeyWords, strPlus) 'find the position of the first +
    If intWdPos = 0 Then
    aryKeyWords(i)=Trim(strKeyWords)
    Else
    aryKeyWords(i)=Trim(left(strKeyWords,intWdPos-1))
    strKeyWords=trim(right(strKeyWords, len(strKeyWords) - intWdPos))
    End if

    strKeyWord=aryKeyWords(i)
    strSQL=strSQL & " Title like '%" & strKeyWord & "%' "
    i=i+1 'increment the array counter

    Loop
    strSQL=strSQL & "ORDER BY Title;"
    %>
    sql is <%=strSQL%>
    </BODY>
    </HTML>



  2. #2
    Dave Kraft Guest

    Re: Problems with building some dynamic SQL to talk to a MS Access DB

    \"Steve Moore" <steve.moore@centrelink.gov.au> wrote in message
    news:3910c144$1@news.devx.com...
    > I have been able to successfully bring back records with the connect using
    > a
    > "simple select * from tablename;"
    > but as soon as I include a like '*Keyword*' I get no results.
    >

    <..snip..>
    > Set Connect = Server.CreateObject("ADODB.Connection")
    > Connect.Open "Avcc"
    >

    I'm assuming that Avcc is an ODBC DSN? If you're going to use the like
    operator, you need to use % as your wildcard character.

    Dave Kraft
    TSR Solutions, Inc.
    My views aren't necessarily my emplyoer'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
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center