SQL statements on ASP page

[david7777777777]

I'm using SQL 2000 and InterDev 6. I am making a simple "KnowledgeBase" database to hold articles, just like Microsoft's knowledge base, only I hope to make mine easy to use. ;-) I'm now making the ASP pages that will serve as the user interface.

Here is my SQL table:

tbl_Articles
ArticleID smallint(2) PK
ArticleTitle char(50)
ArticleBody text(16)
Category char(30)
Keywords text(16)

Here are my ASP page components:

rsArticles (Recordset)
txtKeywords (user will type his keyword(s)into this textbox)
btnGo (this Go button is pressed to initiate the search)
Grid1 (Grid that will display the results)

I can't figure out, after days of searching and reading now, how to get the search parameter (what the user types into txtKeywords) into the SQL statement that will search the database.

I've tried including the SQL statement in the Recordset and I've tried doing it in script using the setSQLText method. I've never used the setSQLText method before so I might be doing it wrong. But it seems straight forward enough.

Any help, including pointing me to some online resources for this type of stuff, is greatly appreciated. Thanks.

 

[mithrilhall]

<%@ Language=VBScript %>
<%Option Explicit%>
<%Response.Buffer = True%>
<%
'Dim Vars Here
Dim txtKey

strSQL = &quot;SELECT * FROM tbl_Articles WHERE Keywords=&quot;
strSQL = strSQL & (Request.Form(&quot;txtKeywords&quot)

 

[mithrilhall]

<%@ Language=VBScript %>
<%Option Explicit%>
<%Response.Buffer = True%>
<%

strSQL = &quot;SELECT * FROM tbl_Articles WHERE Keywords=&quot;
strSQL = strSQL & (Request.Form(&quot;txtKeywords&quot)

 

[mithrilhall]

Sorry about the double post.

 

[MerlinB]

If you are using DTCs on the page for text boxes and for the recordset, then you could try the following:

In the SQL of a recordset just add a simple where clause:
WHERE (1=0)
this way you can add a grid, with columns etc. most easily - and even test to see if it works (1=0) returns no rows, or you could try (1=1) which returns all rows - depending on how you want the initial page to display. In the following I have called the recordset 'rsKB'...

You will need to save the original SQL clause into a PageObjectDTC - just so that it does not get lost in server round-trips. Here I have created a PageObject variable called KBSQL (knowledge base SQL) with page scope, not visible to the client. In the onenter page event, simply:
thisPage.setKBSQL rsKB.getSqlText()

In the search pushbutton server-side click event, build up a suitable where clause, given the values entered by the users, then:
close the recordset
get the original SQL clause from the PageObject
REPLACE the (1=0) string with the where clause built above
set the new SQL clause in the recordset
open the recordset
in code this looks like:
dim sSQL
dim sWhere
... build the sWhere ...
if rsKB.isOpen() then
rsKB.close
end if
sSQL = thisPage.getKBSQL()
if sWhere <> &quot;&quot; then
sSQL = replace(sSQL, &quot;1=0&quot;, sWhere)
end if
rsKB.setSQLText sSQL
rsKB.open

and (snip-snip) bob is you aunty, as they say.

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)

 

[david7777777777]

That makes sense. Thank you very much Merlin... again. I'm still having problems figuring out how to use a variable in my SQL statement.

For example: my textbox is called txtKeyword. This is the textbox into which the user will type their keyword(s). I've yet to successfully get the value of that textbox into my SQL statement. I'm using CONTAINS in my WHERE clause, which is what I've learned in SQL BOL, but something's still not right. Any clues? I'm trying the SQL forum too. Thanks again Merlin.

 

[MerlinB]

Assume sSQL is
SELECT ArticleID, ArticleTitle
, ArticleBody, Category, Keywords
, R.[RANK]
FROM tbl_Articles
INNER JOIN (CONTAINSTABLE (tbl_Articles, *, '&quot;abc&quot;')) R
ON ArticleID = R.[KEY]

(This version returns the article ranking in the result set - which is slightly better than the simple CONTAINS clause)
Your aim here is to replace &quot;abc&quot; with the correct search term. Each search word should be separated out and placed in double quotes:
You type &quot;The quick brown fox&quot;
The clause could be:
1: &quot;The&quot; OR &quot;quick&quot; OR &quot;brown&quot; OR &quot;fox&quot;
Which selects rows that contain any of those words
- the double quotes are optional - but they make things clearer, so use them!
2: &quot;The*&quot; AND &quot;quick*&quot; AND &quot;brown*&quot; AND &quot;fox*&quot;
Which selects rows with all of those words, or ones starting with those words.
3: &quot;The quick brown fox&quot;
Which selects that exact phrase.
The whole lot is contained in single quotes.

Note: The search engine does not like 'stop words' (the/and/or/when/for/it etc.) So if you only search for these terms, ir returns an error - or (as in the 2nd example above) you use a stop word in a sequence of ANDed terms then it will error unless you add the * wildcard.

You can use the technique that I described earier to adjust the SQL text for a recordset - except that you are replacing &quot;abc&quot; with your actual text:

sub pbSearch_click()

dim sTerms
dim sTerm
dim sContains
dim sJoin

'separate out each word that the user entered
'assumes that each word is separated with a space
sTerms = split(txtKeyword.value, &quot; &quot
sJoin = &quot;&quot;
for each sTerm in sTerms
'or try.. For i = 1 to UBound(sTerms)
sContains = sJoin & &quot;&quot;&quot;&quot; & sTerm & &quot;*&quot;&quot;&quot;
sJoin = &quot; AND &quot;
next
rsRecordset.close
'get the SQL text..
rsRecordset.setSQLtext replace ( _
rsRecordset.getSQLText(), &quot;&quot;&quot;abc&quot;&quot;&quot;, sContains)
'debug test!
response.Write rsRecordset.getSQLText()
rsRecordset.open
end sub

That should get you going!

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)

 

[david7777777777]

That's exactly what I needed to understand. I cannot thank you enough. It's the placement of all of these parentheses and single & double quotes that I'm still learning to use as well. Have a great day Merlin.

 

[Guest_imported]

Here, I am having a problem with SQL server 2000 and ASP. I submit a string to a page and then compare what's in the submit with what's in the field of my table. I am not using a WHERE clause.

SELECT * from TABLE (that is my SQL statement)

************************* ASP CODE *********************
%
do until rs.EOF
for each x in rs.fields
select case x.name
case &quot;Login&quot;:
if x.value = strLogin then
nFlagLogin = 1
end if
case &quot;Pass&quot;:
if x.value = strPass then
nFlagPass = 1
end if
end select
next
rs.movenext
loop
%

The value in the LOGIN field (char 8) is NOT equal to the value in the strLogin variable. I've made them display on my screen to compare them and they are the same, now what am I doing wrong?

 

[MerlinB]

Well, if the column is char(8) {as opposed to varchar} then it will be padded on the right with spaces - which can be a bit difficult to 'see' when comparing on the screen. Try always adding square brackets or some other character to each side of a string value before you print them...
Response.Write &quot;Value=[&quot; & x.value & &quot;]&quot;

So try a Trim() to get rid of these spaces.

Also, I can't quite see how your question was related to the original thread.

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)

 

[david7777777777]

Thanks for all your help Merlin. I've got the thing working now but I still have to implement your suggestions for parsing through multiple keywords that might be entered by the user. Right now I'm just testing it with single words.

How do I get the results that are being returned into the grid DTC to be a hyperlink to their associated record in the SQL 2000 database?

 

[MerlinB]

Assuming that the result set contains a key column (or two) then you need to construct a hyperlink in the column expression:

='<A HREF=&quot;displayDetails.asp?id=' + [key] + '&quot;>Details for ' + [title] + '</a>'

If the expression gets remotely complex (with lots of single & double quotes) then you just build a function in VBScript or (preferably) JavaScript and call it from the grid:

=gridBuildHyperlink([key], [title])

function gridBuildHyperlink(i_iKey, i_sTitle)
{
return '<A HREF=&quot;displayDetails.asp?id='
+ i_iKey + '&quot;>Details for ' + i_sTitle + '</a>';
}

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)

 

[david7777777777]

Ok, I made a huge mistake and I really don't have it working at all. I had selected the radio button in the Recordset DTC the made the recordset return all records, as opposed to the SQL radio button in the Recordset DTC. Now that I have more than one record in that table, I can see that every record was being returned. What a clumsy mistake. So, back to the drawing board. Here is my situation and where I am stuck:

I am making a simple KnowledgeBase with search capabilities.
Here's what the table looks like in the SQL 2000 database:

tbl_Articles
ArticleID, smallint, 2
ArticleTitle, varchar, 50
ArticleBody, text, 16
Category, varchar, 50
Keywords, text, 16

I have a textbox DTC (txtKeywords) on my ASP page. My intention is for the user to enter a keyword or two or three and have those values used as the search criteria using the SQL CONTAINS statement. I have a grid (Grid1) to show the results.

I have a recordset DTC (rsArticles). My problem is that I cannot figure out the proper syntax and code location to get the user-entered values from txtKeywords into a valid SQL statement to work with the rsArticles recordset. I've tried placing the SQL code within the Recordset and using the &quot;?&quot; parameter trick to pull the value of the txtKeywords textbox, but when I tried that it told me that it cannot determine the value of the parameter when it's used in a function. Here's roughly what the SQL text looked like:

SELECT ArticleTitle
FROM tbl_Articles
WHERE CONTAINS(Keywords, ?)

So if I can't use a variable in a function directly in SQL code in the Recordset DTC, and I have to explicitly provide SQL code for this transaction to work correctly (so the Recordset doesn't return ALL records), how the heck do I get the value of txtKeywords to work correctly with the rsArticles recordset DTC? Thanks for any help.

P.S. Merlin,
I know you've listed a lot of relevent code examples in this thread so far. Most of it makes sense but I have yet to figure out exactly how to replace &quot;abc&quot; with the value of the textbox txtKeywords. Thanks.

 

[david7777777777]

I got it working now and I've tested it to make sure it's really working and not just returning all records. Here's some of my code:
*******************************************************

Sub btnGo_onclick()
dim strKeywords
dim sqlText
strKeywords = txtKeywords.value
sqlText = &quot;SELECT ArticleTitle FROM tbl_Articles &quot;
sqlText = sqlText & &quot;WHERE CONTAINS(Keywords,'&quot; & strKeywords & &quot;');&quot;
rsArticles.setSQLText(sqlText)
rsArticles.open
btnGo.hide
txtKeywords.hide
lblInfo1.hide
lblInfo2.show
End Sub

Sub thisPage_onenter()
thisPage.setKBSQL rsArticles.getsqlText()
End Sub

***************************************************

Thanks for all the code examples though, it definitely helpd me.

 

[MerlinB]

Well done!
I tend to put a valid select into the recordset DTC, using a place holder for the 'parameter':

SELECT x FROM y WHERE 1=1
or (in your case)
SELECT x FROM y WHERE CONTAINS(Z, &quot;parameter&quot

Then I store the SQL in a pageObjectDTC during the first page load:
thisPage.setSQLClause rsArticles.getSQLText()

And whenever a search is performed, just replace the place holder text with the actual search clause:

rsArticles.close
dim sSearch
..build the search clause..
dim sSQL
sSQL = thisPage.getSQLClause()
sSQL = replace(sSQL, &quot;parameter&quot;, sSearch)
rsArticles.setSQLText sSQL
(you could do this in one line)
rsArticles.open

Which is pretty much what you have - but a complex SQL clause in VB strings is quite horrid.
This has the benefit that the SQL in the recordset is valid (you can test it at design time) and also the grid columns and other DTC's will list the recordset columns. Less typing - that has to be good!

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)

 

[david7777777777]

Merlin,
Now to get multiple keywords to work. I'm working from your example code above where you wrote this:

************************************************************
Assume sSQL is
SELECT ArticleID, ArticleTitle
, ArticleBody, Category, Keywords
, R.[RANK]
FROM tbl_Articles
INNER JOIN (CONTAINSTABLE (tbl_Articles, *, '&quot;abc&quot;')) R
ON ArticleID = R.[KEY]

************************************************************

Did you mean that this SQL code should be inside the Recordset or outside with the rest of my code? I've modified this code to work with my named objects. I placed the following code inside the Recordset:

********************************************************
SELECT ArticleID, ArticleTitle, ArticleBody, Category, Keywords
, R.[RANK]
FROM tbl_Articles
INNER JOIN (CONTAINSTABLE (tbl_Articles, *, '&quot;abc&quot;')) R
ON ArticleID = R.[KEY]
*********************************************************

...and the following code is for my button's onclick event:

**********************************************************
Sub btnGo_onclick()
dim sTerms
dim sTerm
dim sContains
dim sJoin
sTerms = split(txtKeywords.value, &quot;,&quot
sJoin = &quot;&quot;
for each sTerm in sTerms
sContains = sJoin & &quot;&quot;&quot;&quot; & sTerm & &quot;*&quot;&quot;&quot;
sJoin = &quot; AND&quot;
next
rsArticles.close
rsArticles.setSQLText replace(_
rsArticles.getSQLText(), &quot;&quot;&quot;abc&quot;&quot;&quot;, sContains)
Response.Write rsArticles.getSQLText()
rsArticles.open
btnGo.hide
txtKeywords.hide
lblInfo1.hide
End Sub
***********************************************************

When I entered one or two keywords separated by a comma, I got this error:

**********************************************************
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Tables or functions 'tbl_Articles' and 'tbl_Articles' have the same exposed names. Use correlation names to distinguish them.

/KnowledgeBase/_ScriptLibrary/Recordset.ASP, line 636
**********************************************************

This error is new to me. What is it talking about?

 

[MerlinB]

Always try the SQL that you think you need in Query Analyser.
Basically, print the final SQL to the web page and (if you can't see the problem) paste it into Query Analyser then execute it. It sometimes gives you additional error messages.
In the case above, it seems to dislike the 'tbl_Articles' being in the FROM and in the CONTAINSTABLE. Try giving the tbl_Articles a 'correlation name' (an alias, like the R for the containstable):

SELECT ArticleID, ArticleTitle, ArticleBody, Category, Keywords
, R.[RANK]
FROM tbl_Articles A
INNER JOIN (CONTAINSTABLE (tbl_Articles, *, '&quot;abc&quot;')) R
ON ArticleID = R.[KEY]

You should not need to prefix the columns, as they are uniquely named.

http://www.websitedesign.co.uk

http://www.corporateinternet.com

http://www.zentext.com

(Content Management)

 

[david7777777777]

Ok, I added the correlation name like you suggested and it fixed the error. Now it gives me this error:

************************************************************
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ')'.

/KnowledgeBase/_ScriptLibrary/Recordset.ASP, line 636
***********************************************************

I can't figure out which ')' it's talking about. Here's the SQL from response.write

***********************************************************

SELECT ArticleID, ArticleTitle, ArticleBody, Category, Keywords , R.[RANK] FROM tbl_Articles A INNER JOIN (CONTAINSTABLE (tbl_Articles, *, '&quot;429*&quot;')) R ON ArticleID = R.[KEY]
***********************************************************

I'm working on it now but feel free to throw me any clues in the mean time, like the brilliant idea to use Query Analyser to assist with my debugging. Thanks.

 

[david7777777777]

I ran this SQL, which is from response.write, in the SQL Query Analyzer:

**********************************************************
SELECT ArticleID, ArticleTitle, ArticleBody, Category, Keywords , R.[RANK] FROM tbl_Articles A INNER JOIN (CONTAINSTABLE (tbl_Articles, *, '&quot;429*&quot;')) R ON ArticleID = R.[KEY]

***********************************************************
and got this error:

***********************************************************
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
***********************************************************

That's the latest. I'm working on it.

 

[MerlinB]

Remove the brackets around the containstable:

SELECT ArticleID
, ArticleTitle
, ArticleBody
, Category
, Keywords
, R.[RANK]
FROM tbl_Articles A
INNER JOIN CONTAINSTABLE (tbl_Articles, *, '&quot;429*&quot;') R
ON ArticleID = R.[KEY]

(ps. split the sql over several lines in Analyzer, and it may give a clue to the line with the error.)

ttfn

http://www.websitedesign.co.uk

http://www.corporateinternet.com

http://www.zentext.com

(Content Management)