data from query not displaying

-Dman100- · July 17th, 2004, 05:37 PM

I'm not getting any results to display from the following page that queries an Access database. I know the connection is good. I've tested it and used a simple SQL statement to display all the fields, which worked fine. I think the problem might be in the SQL statements? Can anyone see where there are any problems?

Here is the URL to the test search page. The problem is the no records displaying on the results page.

www.dwayneepps.com/test/search.asp

Here is the code to the results.asp page:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%
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").Valu e)%></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-

happygv · July 17th, 2004, 08:43 PM

Hi there,

I don't see any need for this code.
Recordset1__MMColParam = "1"

Exact, Begin, Contain, Ending are all string values which should be checked from within Quotes.

Code:

case "Exact"
SQL...
case "Begin"
SQL...
case "Contain"
SQL...
case Ending
SQL...

What is that you are trying to do there with these codes?
Repeat1__numRows = -1
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
and
While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF))

Just try with this
While NOT Recordset1.EOF
If things are fine then add other conditions that you would like to. But I am not sure what you are trying to do with the other condition in your code posted.
Repeat1__numRows <> 0

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

-Dman100- · July 17th, 2004, 11:37 PM

Hi Vijay,
Thanks for replying to my post.

Here is the link to the test search page, so you can see what I'm trying to do:

www.dwayneepps.com/test/search.asp

I want to be able to enter a keyword search and search the within a database field for the text string based on the choice selected in the menu.

I'm very new to using SQL, so any help or suggestions you have would be appreciated. Thanks again.
-Dman100-

happygv · July 18th, 2004, 01:31 AM

Hi -Dman100-,

What type is the Product number?

From the link you posted, unless I could see something on that page(error/result) that page is no useful for me in suggesting you different options. Also what do I type in Product number on that page, what does product number contain?

I could see you using the same query for all Seachtype CASES. Myself, with no knowledge on what the field type is / what it contains / what you are trying to search, I am not sure how to proceed further.

And finally, did you try out the options I suggested in my previous post here?

Feel free to post your queries.
Cheers!

_________________________
- Vijay G
Strive for Perfection

-Dman100- · July 18th, 2004, 09:13 AM

Hi Vijay,

Sorry for the lack of explanation. I'm still very new to writing SQL and relatively new to 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 and 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 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 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-

Jonax · July 18th, 2004, 09:24 AM

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- · July 18th, 2004, 10:49 AM

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-

-Dman100- · July 18th, 2004, 12:22 PM

I found the problem. I hadn't uploaded the new search.asp file with the correct menu field values. It is working great now.

Oh, if I wanted to add a simple message if no records are found, how could I append the code to display that message?

Something like:

Case Else
Response.Write("No records found.")

I just wanted to thank you again for your help. I very much appreciate it.
Regards,
-Dman100-

happygv · July 19th, 2004, 02:23 AM

Looks like last 2 select types(Ending with and Exact phrase) are not working as expected. I would suggest you to use & as concat operator in those select strings, rather than using + operator.

I won't say it is good to use CASE ELSE for "no records found", as you have only 4 options there and the user cannot select anything other than those 4 and default being CONTAINS.
Case Else
Response.Write("No records found.")

You should check for RECORDSET not having any records after execution of any of those select statements to say "No match found"

Quote:

quote:Microsoft JET Database Engine error '80040e0c'
Command text was not set for the command object.
/test/results.asp, line 30

What is on line 30?

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection