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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 records don't display
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

-Dman100-
Posting Yak Master

210 Posts

Posted - 07/17/2004 :  18:55:52  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1632 Posts

Posted - 07/17/2004 :  21:20:29  Show Profile  Reply with Quote
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).
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 07/18/2004 :  04:06:59  Show Profile  Reply with Quote
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.
Go to Top of Page

-Dman100-
Posting Yak Master

210 Posts

Posted - 07/18/2004 :  10:21:50  Show Profile  Reply with Quote
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-
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 07/18/2004 :  10:37:51  Show Profile  Reply with Quote
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
Go to Top of Page

-Dman100-
Posting Yak Master

210 Posts

Posted - 07/18/2004 :  11:51:17  Show Profile  Reply with Quote
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-
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 07/18/2004 :  11:58:25  Show Profile  Reply with Quote
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.
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 07/18/2004 :  12:00:38  Show Profile  Reply with Quote
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
Go to Top of Page

-Dman100-
Posting Yak Master

210 Posts

Posted - 07/18/2004 :  13:10:04  Show Profile  Reply with Quote
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-
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000