Hi guys!

I was wondering if any of you guys would be able to help solve what is probably a simple problem....

I am using dreamweaver / asp/ access to create a dynamic web application. I have created a record set which queries the database and displays the results. The recordset searches for a product id which is inserted by the user, the product details are then displayed. I somehow can only get the first record in the database to display in the dynamic table. This record is not even searched for it is just there already when i preview the page in the browser then when I enter another ID nothing happens.

here is my code :-

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/dbtest.asp" -->
<%
Dim rs_search__MMColParam
rs_search__MMColParam = "1"
If (Request.Form("ProductID") <> "") Then 
  rs_search__MMColParam = Request.Form("ProductID")
End If
%>
<%
Dim rs_search
Dim rs_search_numRows

Set rs_search = Server.CreateObject("ADODB.Recordset")
rs_search.ActiveConnection = MM_dbtest_STRING
rs_search.Source = "SELECT * FROM product WHERE ProductID = " + Replace(rs_search__MMColParam, "'", "''") + " ORDER BY ProductID ASC"
rs_search.CursorType = 0
rs_search.CursorLocation = 2
rs_search.LockType = 1
rs_search.Open()

rs_search_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = 10
Repeat1__index = 0
rs_search_numRows = rs_search_numRows + Repeat1__numRows
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>search</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>Insert product ID you wish to search for:-
<form method="POST" name="formsearch" id="formsearch">
  <table width="528" height="136" border="1">
    <tr>
      <td width="153">productID</td>
      <td width="359"><input name="txtProductID" type="text" id="txtProductID" size="50"></td>
      <td><input name="btnsearch" type="submit" id="btnsearch" value="search"></td>
    </tr>
  </table>

    <input type="hidden" name="MM_search" value="formsearch">
</form>
<p>&nbsp;</p>

<table border="1">
  <tr>
    <td>ProductID</td>
    <td>Product</td>
    <td>Price</td>
  </tr>
  <% While ((Repeat1__numRows <> 0) AND (NOT rs_search.EOF)) %>
  <tr>
    <td><%=(rs_search.Fields.Item("ProductID").Value)%></td>
    <td><%=(rs_search.Fields.Item("Product").Value)%></td>
    <td><%= FormatCurrency((rs_search.Fields.Item("Price").Value), 2, -2, -2, -2) %></td>
  </tr>
  <% 
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  rs_search.MoveNext()
Wend
%>
</table>
</body>
</html>
<%
rs_search.Close()
Set rs_search = Nothing
%>

Any ideas would be greatly appreciated! Thanks!!! :)

GLT

It seems to be that my default value in the recordset is set to '1' , I changed this to '0' and it said 'no data', this is because there is not product with the Id of 0. All i want to do is search for an ID and then it is displayed to the user not just what is in the default value. I have tried not putting anything in the default value and Dreamweaver wont let me.

Any ideas???

Try to close the variable with a single quote 'rs_search__MMColParam = "1" and test. It is set to the static value "1" for some reason. It is possible that value overrides the input.

Test this:

Dim rs_search__MMColParam
'rs_search__MMColParam = "1"
If (Request.Form("ProductID") <> "") Then 
rs_search__MMColParam = Request.Form("ProductID")
End If

.... or try to contain the variable within the (If then).

Dim rs_search__MMColParam
If (Request.Form("ProductID") <> "") Then 
rs_search__MMColParam = Request.Form("ProductID")
ElseIf (Request.Form("ProductID") = "") Then
rs_search__MMColParam = "1"
End If

Another option that maybe solve the thing is to change the name to txtProductID

Dim rs_search__MMColParam
rs_search__MMColParam = "1"
If (Request.Form("txtProductID") <> "") Then 
rs_search__MMColParam = Request.Form("txtProductID")
End If

I still cant get it to work.

I think its because I have a default value of 1 but i have to put something into the default value so what should i do??

all i want to do is create a simple search and results page, the user will enter an id and the details are displayed.

any ideas would be greatly apprieciated!
Thanks!
GLT

this is the code i have at the minute:-

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/dbtest.asp" -->
<%
Dim rs_search__MMColParam
If (Request.Form("ProductID") <> "") Then 
  rs_search__MMColParam = Request.Form("ProductID")
ElseIf (Request.Form("ProductID") = "") Then
rs_search__MMColParam = "1"
End If
%>
<%
Dim rs_search
Dim rs_search_numRows

Set rs_search = Server.CreateObject("ADODB.Recordset")
rs_search.ActiveConnection = MM_dbtest_STRING
rs_search.Source = "SELECT * FROM product WHERE ProductID = " + Replace(rs_search__MMColParam, "'", "''") + " ORDER BY ProductID ASC"
rs_search.CursorType = 0
rs_search.CursorLocation = 2
rs_search.LockType = 1
rs_search.Open()

rs_search_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = 10
Repeat1__index = 0
rs_search_numRows = rs_search_numRows + Repeat1__numRows
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>search</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>Insert product ID you wish to search for:-
<form method="POST" name="formsearch" id="formsearch">
  <table width="528" height="136" border="1">
    <tr>
      <td width="153">productID</td>
      <td width="359"><input name="txtProductID" type="text" id="txtProductID" size="50"></td>
      <td><input name="btnsearch" type="submit" id="btnsearch" value="search"></td>
    </tr>
  </table>

    <input type="hidden" name="MM_search" value="formsearch">
</form>
<p>&nbsp;</p>
<p>results:-</p>
<table border="1">
  <tr>
    <td width="87">ProductID</td>
    <td width="87">Product</td>
    <td width="87">Price</td>
  </tr>
  <% While ((Repeat1__numRows <> 0) AND (NOT rs_search.EOF)) %>
  <tr>
    <td><%=(rs_search.Fields.Item("ProductID").Value)%></td>
    <td><%=(rs_search.Fields.Item("Product").Value)%></td>
    <td><%=(rs_search.Fields.Item("Price").Value)%></td>
  </tr>
  <% 
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  rs_search.MoveNext()
Wend
%>
</table>
</body>
</html>
<%
rs_search.Close()
Set rs_search = Nothing
%>

I solved it myself! It was my fault!

I had to change the code to point to the text box in the search form not the field in the database table! I didnt realise it was doing this as they are both called the same except the text box is called txtProductID not ProductID.

Here is the new code for the section i changed:-

Dim rs_search__MMColParam
rs_search__MMColParam = "1"
If (Request.Form("txtProductID") <> "") Then 
  rs_search__MMColParam = Request.Form("txtProductID")
End If

Thanks for you help!!
GLT :)

This question has already been answered. Start a new discussion instead.