SQL search results not working??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL search results not working???

Author

Topic

malhyp
Starting Member

21 Posts

Posted - 2005-12-10 : 06:18:23

Can anyone suggest whey I dont get any results with this search?

SQL reads:

SELECT SupplierName, Location, ShortDescription
FROM Query1
WHERE 'TimberSpecies' LIKE '%MMColParam%' AND 'CategoryTable' LIKE '%MMColParam2%' AND 'Location' LIKE '%MMColParam3%'

MMColParam 1 Request.Form("keywordSearch")
MMColParam2 2 Request.Form("location")
MMColParam3 3 Request.Form("category")

Mally.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-12-10 : 06:33:19

you need to write the stored procedure.. where u have to pass this 3 paramters..
somthing like this ..

Create proc searchdesc
(
@pCol1 varchar(200),
@pCol2 varchar(200),
@pCol3 varchar(200)
)
As
Begin
SELECT SupplierName, Location, ShortDescription
FROM Query1
WHERE TimberSpecies LIKE '%' +@pCol1 + '%' AND CategoryTable LIKE '%' +@pCol2 + '%'AND Location LIKE '%' +@pCol3 + '%'
End
??
is this u r looking for ..

or post the sample data..???

Sucess Comes to those who Believe in Beauty of their Dream..

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-12-10 : 15:18:59

looks like dreamweaver you are using
your asp variable should be

MMColParam1 = Request.Form("keywordSearch")

or as dreamweaver puts its


if MMColParam1 = "1"
if MMColParam1 <> "" then 
MMColParam1 = request.form("KeywordSearch")

It also seems to me that you are using a recordset ???

lets see some sample code

malhyp
Starting Member

21 Posts

Posted - 2005-12-10 : 18:39:30

I hope this is the data required. Let me know if I need to post more.


<%
Dim rsSearchResult__MMColParam1
rsSearchResult__MMColParam1 = "%"
If (Request.Form("keywordSearch") <> "") Then
rsSearchResult__MMColParam1 = Request.Form("keywordSearch")
End If
%>
<%
Dim rsSearchResult__MMColParam2
rsSearchResult__MMColParam2 = "%"
If (Request.Form("CategoryTable") <> "") Then
rsSearchResult__MMColParam2 = Request.Form("CategoryTable")
End If
%>
<%
Dim rsSearchResult__MMColParam3
rsSearchResult__MMColParam3 = "%"
If (Request.Form("Location") <> "") Then
rsSearchResult__MMColParam3 = Request.Form("Location")
End If
%>
<%
Dim rsSearchResult
Dim rsSearchResult_numRows
Set rsSearchResult = Server.CreateObject("ADODB.Recordset")
rsSearchResult.ActiveConnection = MM_connTimber_STRING
rsSearchResult.Source = "SELECT SupplierName, Location, ShortDescription FROM Query1 WHERE TimberSpecies LIKE '%" + Replace(rsSearchResult__MMColParam1, "'", "''") + "%' AND 'CategoryTable' LIKE '%" + Replace(rsSearchResult__MMColParam2, "'", "''") + "%' AND 'Location' LIKE '%" + Replace(rsSearchResult__MMColParam3, "'", "''") + "%'"
rsSearchResult.CursorType = 0
rsSearchResult.CursorLocation = 2
rsSearchResult.LockType = 1
rsSearchResult.Open()
rsSearchResult_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = 4
Repeat1__index = 0
rsSearchResult_numRows = rsSearchResult_numRows + Repeat1__numRows
%>
<%
' *** Recordset Stats, Move To Record, and Go To Record: declare stats variables

' set the record count
rsSearchResult_total = rsSearchResult.RecordCount

' set the number of rows displayed on this page
If (rsSearchResult_numRows < 0) Then
rsSearchResult_numRows = rsSearchResult_total
Elseif (rsSearchResult_numRows = 0) Then
rsSearchResult_numRows = 1
End If

' set the first and last displayed record
rsSearchResult_first = 1
rsSearchResult_last = rsSearchResult_first + rsSearchResult_numRows - 1

' if we have the correct record count, check the other stats
If (rsSearchResult_total <> -1) Then
If (rsSearchResult_first > rsSearchResult_total) Then rsSearchResult_first = rsSearchResult_total
If (rsSearchResult_last > rsSearchResult_total) Then rsSearchResult_last = rsSearchResult_total
If (rsSearchResult_numRows > rsSearchResult_total) Then rsSearchResult_numRows = rsSearchResult_total
End If
%>

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-12 : 01:29:30

Did you get any error message?
Did you get unexpected result?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources