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

Steve Moore · 05-03-2000 08:16 PM

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>

Dave Kraft · 05-04-2000 09:37 AM

\"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

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

Thread Tools

Display

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

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

Bookmarks

Bookmarks

Posting Permissions