Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Creating selection criteria for table search.

Status
Not open for further replies.
Work23

Work23

Technical User
Mar 8, 2005
94
US
Hello. Below I have some SQL code that is simple to select some data that I need. I have specified a specific value. I need to create some ASP code that would allow a user to enter any 'Sold-to' number and would then receieve the display count. I'm confused on how to set up the ASP to be able to search the table based on any 'Sold-to' number someone chooses. Please let me know of any ideas. Thanks so much.


SELECT DISTINCT
SOLDTO,
PAGENAME, COUNT(PAGENAME) AS 'PAGE-COUNT'
FROM DBO.STATS
WHERE SOLDTO=17890
GROUP BY SOLDTO, PAGENAME
ORDER BY (SOLDTO) ASC, ('PAGE-COUNT') DESC
 
Sort by date Sort by votes
Hi,
Provide a text box for input.
Name it something meaningful, like varSoldTo.

Your Sql statement would then be something like:

SqlCmd = "SELECT DISTINCT_
SOLDTO, _
PAGENAME, COUNT(PAGENAME) AS 'PAGE-COUNT' _
FROM DBO.STATS _
WHERE SOLDTO=" & varSoldTo _
& "GROUP BY SOLDTO, PAGENAME _
ORDER BY (" & " varSoldTo & " ASC, ('PAGE-COUNT') DESC"

Call this after you have accepted the user input..




[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
Upvote 0 Downvote
For simplicity it might be easiest to start with 2 separate pages. The first one contains a plain HTML and the second is your ASP.

page1.htm
Code: 
<html>
  <head>
    <title>Test Page</title>
  </head>
  <body>
    <form method="post" action="page2.asp">
      Sold To: 
      <input type="text" name="SoldToCriteria">
      <br>
      <input type="submit">
    </form>
  </body>
</html>

page2.asp
Code: 
<%
Dim MySearchValue
[highlight]MySearchValue = Request.Form("SoldToCriteria")[/highlight]

'Verify input
If Not IsNumeric(MySearchValue) Then
  Response.Write "Error: Bad Input!"
  Response.End
End If


'Build SQL command string
Dim sSQL
sSQL = "SELECT DISTINCT SOLDTO, PAGENAME, " _
     & "COUNT(PAGENAME) AS 'PAGE-COUNT' " _
     & "FROM DBO.STATS " _
     & "WHERE SOLDTO= " & [highlight]MySearchValue[/highlight] & " " _
     & "GROUP BY SOLDTO, PAGENAME " _
     & "ORDER BY (SOLDTO) ASC, ('PAGE-COUNT') DESC "


'Open ADO connection to database
Dim cn
Set cn = Server.CreateObject("ADODB.Connection")
cn.ConnectionStriong = "[red]blahblahblah[/red]"
cn.Open
If (cn.State <> 1) Then
  Response.Write "Error: ADO Connection failed to open!"
  Set cn = Nothing
  Response.End
End If


'Fetch ADO recordset using SQL command string
Dim rs
Set rs = cn.Execute(sSQL)
If (rs.State <> 1) Then
  Response.Write "Error opening ADO recordset."
  Response.End
End If


'Write results rows here
Dim fld
Do While Not rs.Eof
  For Each fld in rs.Fields
    Response.Write fld.Value & "&nbsp;&nbsp;&nbsp;"
  Next

  Response.Write vbCrLf & "<br>" & vbCrLf
  rs.MoveNext
Loop
%>

Visit to find out the proper ADO ConnectionString for your database.

This is only example code. I don't have access to your database so I obviously havn't even run it. There are probably errors but the purpose is just to demonstrate how you might do this.
 
Upvote 0 Downvote
Hello. Thank you for this valuable post. I'm just confused about why you would use two files, an html and an asp. I lack extensive web programming knowledge, so I apologize. Please let me know when you have the opportunity. Thank you so much. Take care.
 
Upvote 0 Downvote
Hi,
Sheco 's code is better ( especially the 'Order by' clause which I miscoded)- You use 2 pages to make the processing order explicit..Using a Subroutine call in asp can be tricky, so a 'plain' html page for user interaction and a separate asp page to execute actions based on that user input is a 'standard' way of handling this sort of app.



[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
Upvote 0 Downvote
I feel like having them on one page can be more confusing when you are just starting out with ASP.

You can indeed do this with a single page but you must keep in mind that the same page will run twice and produce different output. In my opinion it is best to start out with two separate pages and only combine them after you get the code working properly.
 
Upvote 0 Downvote
I tried using this code. I can't actually get the ASP page to work? There are no errors shown. I am kind of confused on how to make this function. Would you have any ideas? Thanks so much.
 
Upvote 0 Downvote
Do you have any other asp pages on the server?
 
Upvote 0 Downvote
Yes. We do. And they are working fine.
 
Upvote 0 Downvote
There might not actually be a bug. The code will produce no output if no records were found.

Edit the page to insert the following before the code that writes the rows:
Code: 
'Check for empty recordset
If rs.Eof Then
  Response.Write "No records found"
  Response.End
End If

There are settings in both the browser and on the server that can prevent errors from being displayed. If you are using Internet Explorer got to the Tools menu and choose Internet Options. Then flip to the "Advanced" tab and scroll down and verify that "Show friendly HTTP error messages" has no checkmark.

If you still see no error then check to make sure the server is not also being friendly. Use the IIS Admin tool to view the properties of the folder that is the root for your web application. Click the configure button on the first property sheet and that will bring up another form. I don't recall off hand the exact text but there will be a checkbox that allows you to turn friendly messages on and off. I think it is on the same tab that lets you specify debugging options.

If that doesnt help then try making a copy of one of your working ASP pages and then introducing a bug into the copy... just as a way to test that you can receive the errors.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
178
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
First execute an input command and only then proceed with displaying the next record of the table
Replies
0
Views
141
leifoet
leifoet
how2trainyourdragon
  • Locked
  • Question
Request.Server variables blank for classic ASP
Replies
12
Views
489
GriffMG
GriffMG
leifoet
  • Locked
  • Question
Multi-selection dropdown list does not work
Replies
0
Views
84
leifoet
leifoet
gesrod
  • Locked
  • Question
Multiple search criteria
Replies
2
Views
99
gesrod
gesrod

Part and Inventory Search

Sponsor

Back
Top