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.

 

[david7777777777]

That worked. Thank you very much. I'm now getting NO errors.

I'm now working on trying to figure out why only one keyword is being sent with the SQL when I input 2 keywords separated by only a comma. Here's the code for the pushbutton:

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

Here's the SQL inside the Recordset:

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

I enter "429,ActiveX" without the quotes as the keywords in the txtSearch textbox. This is the resulting SQL from response.write:

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

SELECT ArticleID, ArticleTitle, ArticleBody, Category, Keywords , R.[RANK] FROM tbl_Articles A INNER JOIN CONTAINSTABLE (tbl_Articles, *, ' AND"ActiveX*"') R ON ArticleID = R.[KEY]

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

It's not picking up the first keyword, which is "429." I'm beginning my debugging now. Throw me a clue if you'd like. I'm enjoying this troubleshooting today for some reason.

 

[MerlinB]

Try...

for each sTerm in sTerms
sContains = sContains & sJoin & """" & sTerm & "*"""

http://www.websitedesign.co.uk

http://www.corporateinternet.com

http://www.zentext.com

(Content Management)