records don't display - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

records don't display

Author

Topic

-Dman100-
Posting Yak Master

210 Posts

Posted - 2004-07-17 : 18:55:52

I'm using a search function, but no records are displaying from the search. Are there problems with the SQL statements?

Here is the code:

<%
Dim Recordset1__MMColParam
Recordset1__MMColParam = "1"
If (Request.Form("txtKeywords") <> "") Then
Recordset1__MMColParam = Request.Form("txtKeywords")
End If
%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_DBConn_STRING
strTest = Request.Form("selMatchType")
Select Case strTest
Case Exact
Recordset1.Source = "SELECT * FROM tblProducts WHERE prod_number = '" + Replace(Recordset1__MMColParam, "'", "''") + "'"
Case Ending
Recordset1.Source = "SELECT * FROM tblProducts WHERE prod_number = '" + Replace(Recordset1__MMColParam, "'", "''") + "'"
Case Contain
Recordset1.Source = "SELECT * FROM tblProducts WHERE prod_number = '" + Replace(Recordset1__MMColParam, "'", "''") + "'"
Case Begin
Case Else Recordset1.Source = "SELECT * FROM tblProducts WHERE prod_number = '" + Replace(Recordset1__MMColParam, "'", "''") + "'"
End Select
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<%
While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF))
%>
<table width="500" border="0" cellspacing="0" cellpadding="0">
<tr>
<td><%=(Recordset1.Fields.Item("id").Value)%></td>
<td><%=(Recordset1.Fields.Item("prod_number").Value)%></td>
</tr>
</table>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
Recordset1.MoveNext()
Wend
%>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>

Thanks for any help.
-Dman100-

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-07-17 : 21:20:29

Does the case "exact" return any records?
Have you tried printing the SQL statement out and running it in Query analyser?
Does the query return any rows?

If you are looking to perform searches that match your criteria "begin ending and contain" have a look at LIKE operator in SQL BOL.

A stored procedure might be a good place to perform this conditional logic and apply the appropriate wildcard character(s).

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-07-18 : 04:06:59

First of all strTest will probably be some sort of string so you will have to enclose your case-options in double-quotes like this:

Case "Exact"

Secondly, all your sql-statements are exactly the same which makes the case obsolete.

And third, making the user search for id's in your db probably isn't a good idea. It might me a supplement, but how many of your users actually know the ID of the record they are looking for? Probably not very many but that's just my assumtion, I might be wrong here.

-Dman100-
Posting Yak Master

210 Posts

Posted - 2004-07-18 : 10:21:50

Hi Lumbago,

Thank you for replying to my post. Sorry for the lack of explanation. I'm still very new to writing SQL and just at an intermediate level using ASP. Here is what I'm trying to do. I'm trying to query one field in a database that has 435 enteries. The field I want to query contains a combination of text and numbers. Here are several examples of prod_number field entries:

cml 10-160s gzi
cml2_8sga
ml8sg
cml 12sgavd

On the search page:
www.dwayneepps.com/test/search.asp

I'm have a text field where a user can type in the exact product number or a portion of of the product number. The user then can choose from the menu how to search for the string in the text field.

So, from the example product number above. A user can type in:
ml8sg and then choose exact from the menu and the search would return the exact match in the database prod_number field.

Or, the user could simply type in:
ml and then choose "begins with" or "ends with" or "contains" and the search would return the matches in the database prod_number field that begin, end or contain the string typed in the text field depending on which menu choice they made. So, if they choose "Begins with" the search would look for entries in the database prod_number field that begins with ml (the value of the string typed into the text field).

So, on the results page. I first tried to capture the value of the text field.

<%
Dim Recordset1__MMColParam
Recordset1__MMColParam = "1"
If (Request.Form("txtKeywords") <> "") Then
Recordset1__MMColParam = Request.Form("txtKeywords")
End If
%>

Then I tried to capture the value of the menu field and build SQL statements that would return the appropriate matches in the prod_nuber field according to the case of the menu of selection:

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_DBConn_STRING
strTest = Request.Form("selMatchType")
Select Case strTest
Case "Exact"
Recordset1.Source = "SELECT * FROM tblProducts WHERE prod_number = '" + Replace(Recordset1__MMColParam, "'", "''") + "'"
Case "Ending"
Recordset1.Source = "SELECT * FROM tblProducts WHERE prod_number = '" + Replace(Recordset1__MMColParam, "'", "''") + "'"
Case "Contain"
Recordset1.Source = "SELECT * FROM tblProducts WHERE prod_number = '" + Replace(Recordset1__MMColParam, "'", "''") + "'"
Case "Begin"
Recordset1.Source = "SELECT * FROM tblProducts WHERE prod_number = '" + Replace(Recordset1__MMColParam, "'", "''") + "'"
End Select
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>

I'm guessing my SQL statements are completely wrong and not correctly identifying the criteria I'm specifying? Does this give a better idea of what I'm trying to do? I hope I made this explanation clearer. Please let me know if I can provide any additional information.

Thank you for your help. I appreciate it.

-Dman100-

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-07-18 : 10:37:51

Assuming strTest actually has a value you should do like this:

Select Case strTest
Case "Exact"
Recordset1.Source = "SELECT * FROM tblProducts WHERE prod_number = '" + Replace(Recordset1__MMColParam, "'", "''") + "'"
Case "Ending"
Recordset1.Source = "SELECT * FROM tblProducts WHERE prod_number LIKE '%" + Replace(Recordset1__MMColParam, "'", "''") + "'"
Case "Contain"
Recordset1.Source = "SELECT * FROM tblProducts WHERE prod_number LIKE '%" + Replace(Recordset1__MMColParam, "'", "''") + "'%"
Case "Begin"
Recordset1.Source = "SELECT * FROM tblProducts WHERE prod_number LIKE '" + Replace(Recordset1__MMColParam, "'", "''") + "%'"
End Select

-Dman100-
Posting Yak Master

210 Posts

Posted - 2004-07-18 : 11:51:17

Thank you! It's working! I've tested it on my web server here at my house, but when I uploaded to my host web server to test, I get the error:

Microsoft JET Database Engine error '80040e0c'

Command text was not set for the command object.

/test/results.asp, line 30

It works fine on my home server though? Any idea why this error would occur on the remote host web server?

Here is the URL for the test page:

www.dwayneepps.com/test/search.asp

I really appreciate the help! Thank you.
-Dman100-

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-07-18 : 11:58:25

This error tells you that Recordset1.Source has not been set. So if you add a Case Else as the last case-statement you should be fine:

Case Else
Response.Write("Uh-oh...something is wrong here...")

You should do some debugging to find out why strTest is empty...probably just a spelling mistake or something.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-07-18 : 12:00:38

Also take a look at your drop-down:

<select name="selMatchType" id="selMatchType">
<option value="AND" selected>Contains</option>
<option value="AND">Begins With</option>
<option value="AND">Ends With</option>
<option value="EXACT">Exact Phrase</option>
</select>

The top three options have the same value (AND)...this doesn't make sense at all

-Dman100-
Posting Yak Master

210 Posts

Posted - 2004-07-18 : 13:10:04

Well, I feel pretty stupid. I didn't upload the new search.asp page with the correct values set for the menu field. Once I uploaded that page it worked fine.

I wanted to thank you again for all your help! I very much appreciate it.

Regards,
-Dman100-

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources