PDA

Click to See Complete Forum and Search --> : 2 text box form search database

crater
08-16-2004, 03:07 PM
O'kay this is my first asp page. I have a form where the user needs to enter the load # and the Order # into text boxes. I want to query a database to only return the fields that match BOTH of these inputs after the submit button executes query. How do I search by both boxes. And I'm now getting no results. (At one point I did get results and now with trying to fix it I've managed to make things worse.) Lost cause!

<%
Dim strURL
Dim DbConn
Dim rstSearch

Dim strSQL
Dim strSearch
Dim strSearch1
strURL = Request.ServerVariables("URL")

strSearch = Request.QueryString("search")

%>
<p>Search By Load and Order</p>
<p>Enter a Load Number</p>
<form action="<%= strURL %>" method="GET">
<input name="search" value="<%= strSearch %>" size="20" />
<span style="position: absolute; left: 228; top: 129; width: 116; height: 22">
<input type="submit" /></span><p>Enter a Order Number</p>
</p><input name="Search1" size="20" value="<%=strSearch1%>"></p>
</form>
<p>&nbsp;</p>
<%
If strSearch <> "" Then


Set DbConn = server.CreateObject("adodb.connection")
DSName = ("USCMHB00")
DbConn.open "DSN=database","sa","database"


strSQL = "SELECT SHIPMENT, SHPORDN, SHPRESC, SHPLOTN, SHPLOCN, SHPQYSH " _
& "FROM Upload " _
& "WHERE SHIPMENT = '%" & Replace(strSearch, "'", "''") & "%' " _
& "AND SHPORDN = '%" & Replace(strSearch1, "'", "''") & "%' " _
& "ORDER BY SHPRESC;"

Set rstSearch = DbConn.Execute(strSQL)

%>
<table border="1" width="532">
<tr>
<th width="92">Load</th>
<th width="115">Order</th>
<th width="28">Resource#</th>
<th width="137">Lot#</th>
<th width="1">Location</th>
<th width="1">Quantity</th>
</tr>
<%
Do While Not rstSearch.EOF
%>
<tr>
<td width="92"><%= rstSearch.Fields("SHIPMENT").Value %>&nbsp;</td>
<td width="115"><%= rstSearch.Fields("SHPORDN").Value %>&nbsp;</td>
<td width="103"><%= rstSearch.Fields("SHPRESC").Value %>&nbsp;</td>
<td width="293"><%= rstSearch.Fields("SHPLOTN").Value %>&nbsp;</td>
<td width="79"><%= rstSearch.Fields("SHPLOCN").Value %>&nbsp;</td>
<td width="88"><%= rstSearch.Fields("SHPQYSH").Value %>&nbsp;</td>
</tr>
<%

rstSearch.MoveNext
Loop
%>
</table>
<%

rstSearch.Close
Set rstSearch = Nothing
DbConn.Close
Set DbConn = Nothing
End If

%>
lmf232s
08-16-2004, 06:24 PM
strSQL = "SELECT SHIPMENT, " & _
" SHPORDN, " & _
" SHPRESC, " & _
" SHPLOTN, " & _
" SHPLOCN, " & _
" SHPQYSH " & _
"FROM Upload " & _
"WHERE SHIPMENT = '" & Replace(strSearch, "'", "''")& "%' " & _
"AND SHPORDN = '" & Replace(strSearch1, "'", "''") & "%' " & _
"ORDER BY SHPRESC ASC"

Try this one. Not sure, i may have messed up syntax. Are you looking for an exact match or anything that matchs. If you want an exact match take out the % on the where statement.
crater
08-17-2004, 08:25 AM
I tried it and it didn't work. I did take out the % from the where statement. My select statement has not changed. Any other suggestions?
Does the rest of the code look to be correct? I still don' get any results. I think you were correct about your syntax. It will need to be exact match of both load and order number. Thanks for yours and anyone elses help.
lmf232s
08-17-2004, 10:00 AM
do a response.write strSQL right after the SQL statement and before you execute it.

See if your SQL statement is correct as far as, you are doing a replace in the middle of the statment. Make sure that it is doing what you want. Its the SQL statement thats going to effect the records that you return. Just see what the SQL statement looks like befor execution.

Maybe the replace is not working as it is supposed to .
crater
08-17-2004, 10:30 AM
Thanks Imf232s! :)
I am very new to all of this. Did you want me to change the "replace" with response.write? Just wanted to make sure I'm doing as you suggested so I don't waste any more of your time.

& "WHERE SHIPMENT = '" & Response.write(strSearch, "'", "''") & "%' " _
& "AND SHPORDN = '" & Response.write(strSearch1, "'", "''") & "%' " _


crater
lmf232s
08-17-2004, 10:56 AM
No right after your sql statment.

SQL = "Select * from ?where ........."

Response.write SQL

This will allow you to see what your sql statement will look like before it executes.

It may be the replace that is not doing what you thought it should be doing and this will allow you to see the whole sql statement
the way it will be executed.
crater
08-17-2004, 11:32 AM
Alright, This is the response.write:

SELECT SHIPMENT, SHPORDN, SHPRESC, SHPLOTN, SHPLOCN, SHPQYSH FROM Upload WHERE SHIPMENT = '10524%' AND SHPORDN = '%' ORDER BY SHPRESC;

It is not seeing the SHPORDN that I'm entering for the query. Should there be another If statement before the strSQL? like

If strSearch <> "" Then
If strSearch1 <> "" Then

Another interesting thing is happening. When I click the button the order number that I entered dissapears while it's executing. If I click the back button on the tool bar because I did not get the desired results I can see the order number I entered.

Thank You! I will continue to look for the answer and wait on your response. What do you think?

crater
lmf232s
08-17-2004, 11:36 AM
See, Now you know that you have problems that exist besides the SQL statment.

What is strSearch1. It does not look like you are setting that variable.

I will past your code into a page and see what it looks like and see if i can find anything else wrong.
lmf232s
08-17-2004, 11:47 AM
Try this code
Also always put
<%option explicit%>
as your very first line on a page. It will catch
So many errors for you, and save you alot of time.
It makes you declare all your variables but that is good
practice.
You did not set strSearch1 = to anything that is why the field
was blank.


<%option explicit%>

<%
Dim strURL
strURL = Request.ServerVariables("URL")

Dim strSearch
strSearch = Request.QueryString("Search")

Dim strSearch1
strSearch1 = Request.QueryString("Search1")
%>
<p>Search By Load and Order</p>
<p>Enter a Load Number</p>

<form action="<%=strURL%>" method="GET">
<input name="Search" value="<%= strSearch %>" size="20" />
<span style="position: absolute; left: 228; top: 129; width: 116; height: 22">
<input type="submit" ></span><p>Enter a Order Number</p>
</p><input name="Search1" size="20" value="<%=strSearch1%>"></p>
</form>
<%
If strSearch <> "" Then

Dim DbConn
Set DbConn = server.CreateObject("adodb.connection")
DSName = ("USCMHB00")
DbConn.open "DSN=database","sa","database"

Dim strSQL
strSQL = "SELECT SHIPMENT, SHPORDN, SHPRESC, SHPLOTN, SHPLOCN, SHPQYSH " & _
"FROM Upload " & _
"WHERE SHIPMENT = '" & Replace(strSearch, "'", "''") & "%' " & _
"AND SHPORDN = '" & Replace(strSearch1, "'", "''") & "%' " & _
"ORDER BY SHPRESC"

Dim rstSearch
Set rstSearch = DbConn.Execute(strSQL)%>

<table border="1" width="532">
<tr>
<th width="92">Load</th>
<th width="115">Order</th>
<th width="28">Resource#</th>
<th width="137">Lot#</th>
<th width="1">Location</th>
<th width="1">Quantity</th>
</tr>

<%Do While Not rstSearch.EOF%>

<tr>
<td width="92"><%= rstSearch.Fields("SHIPMENT").Value %> </td>
<td width="115"><%= rstSearch.Fields("SHPORDN").Value %> </td>
<td width="103"><%= rstSearch.Fields("SHPRESC").Value %> </td>
<td width="293"><%= rstSearch.Fields("SHPLOTN").Value %> </td>
<td width="79"><%= rstSearch.Fields("SHPLOCN").Value %> </td>
<td width="88"><%= rstSearch.Fields("SHPQYSH").Value %> </td>
</tr>

<%rstSearch.MoveNext
Loop%>

</table>

<% rstSearch.Close
Set rstSearch = Nothing
DbConn.Close
Set DbConn = Nothing
End If%>
crater
08-17-2004, 12:10 PM
Getting closer but still no results. I made the changes as you suggested.

response.write strSQL =

SELECT SHIPMENT, SHPORDN, SHPRESC, SHPLOTN, SHPLOCN, SHPQYSH FROM PrismUpload WHERE SHIPMENT = '10524%' AND SHPORDN = 'M110123%' ORDER BY SHPRESC;

Here is the code now

<%option explicit%>
<%
Dim strURL
strURL = Request.ServerVariables("URL")

Dim strSearch
strSearch = Request.QueryString("Search")

Dim strSearch1
strSearch1 = request.QueryString("Search1")
%>
<body background="../../images/Backgrounds/CC04.png">

<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p align="center">&nbsp;</p>
<p align="center">&nbsp;</p>
<p align="center">Search By Load and Order</p>
<p align="center">Enter the Load Number</p>
<form action="<%= strURL %>" method="GET">
<p align="center">
<!--webbot bot="Validation" b-value-required="TRUE" i-minimum-length="5" i-maximum-length="5" --><input name="search" value="<%= strSearch %>" size="16" maxlength="5" />
<span style="position: absolute; left: 400; top: 396; width: 116; height: 22">
<input type="submit" /></span></p>
<p align="center">Enter the Order Number</p>
</p>
<p align="center">
<!--webbot bot="Validation" b-value-required="TRUE" i-minimum-length="7" i-maximum-length="7" --><input type="text" name="Search1" size="17" value="<%=strSearch1%>" maxlength="7"></p>
<p align="center">
&nbsp;</p>
</p>
</form>
<p>&nbsp;</p>
<%

If strSearch <> "" Then

Dim DbConn
Set DbConn = server.CreateObject("adodb.connection")
Dim DSName
DSName = ("USCMHB00")
DbConn.open "DSN=cocacola","sa","cocacola"

Dim strSQL
strSQL = "SELECT SHIPMENT, SHPORDN, SHPRESC, SHPLOTN, SHPLOCN, SHPQYSH " _
& "FROM Upload " _
& "WHERE SHIPMENT = '" & Replace(strSearch, "'", "''") & "%' " _
& "AND SHPORDN = '" & Replace(strSearch1, "'", "''") & "%' " _
& "ORDER BY SHPRESC;"

response.write strSQL

Dim rstSearch
Set rstSearch = DbConn.Execute(strSQL)

%>
<div align="center">
<center>
<table border="1" width="532" style="border-collapse: collapse" bordercolor="#111111" cellpadding="0" cellspacing="0">
<tr>
<th width="115">Load</th>
<th width="148">Order</th>
<th width="1">Resource#</th>
<th width="216">Lot#</th>
<th width="1">Location</th>
<th width="1">Quantity</th>
</tr>
<%
Do While Not rstSearch.EOF
%>
<tr>
<td width="115"><%= rstSearch.Fields("SHIPMENT").Value %>&nbsp;</td>
<td width="115"><%= rstSearch.Fields("SHPORDN").Value %>&nbsp;</td>
<td width="136"><%= rstSearch.Fields("SHPRESC").Value %><p align="center">&nbsp;</td>
<td width="181"><%= rstSearch.Fields("SHPLOTN").Value %>&nbsp;</td>
<td width="158"><%= rstSearch.Fields("SHPLOCN").Value %>&nbsp;</td>
<td width="88"><%= rstSearch.Fields("SHPQYSH").Value %>&nbsp;</td>
</tr>
<%

rstSearch.MoveNext
Loop
%>
</table>
</center>
</div>

<%

rstSearch.Close
Set rstSearch = Nothing
DbConn.Close
Set DbConn = Nothing
End If

%>
lmf232s
08-17-2004, 01:20 PM
strSQL = "SELECT SHIPMENT, SHPORDN, SHPRESC, SHPLOTN, SHPLOCN, SHPQYSH " _
& "FROM Upload " _
& "WHERE SHIPMENT = '" & Replace(strSearch, "'", "''") & "%' " _
& "AND SHPORDN = '" & Replace(strSearch1, "'", "''") & "%' " _
& "ORDER BY SHPRESC;"


get rid of the ; after SHPRESC at the end of the statement.

strSQL = "SELECT SHIPMENT, SHPORDN, SHPRESC, SHPLOTN, SHPLOCN, SHPQYSH " _
& "FROM Upload " _
& "WHERE SHIPMENT = '" & Replace(strSearch, "'", "''") & "%' " _
& "AND SHPORDN = '" & Replace(strSearch1, "'", "''") & "%' " _
& "ORDER BY SHPRESC"

Not sure if that will fix it but im sure that is not helping
crater
08-17-2004, 01:48 PM
lmf232s,

I really appreciate all the help. I removed the semi colon and changed = to LIKE and that did the trick. I know that for someone new to this it's difficult for the "experts" to understand what they are asking let alone what they are trying to accomplish. Thanks again.

I'm sure I'll use this forum in the futture.

crater
lmf232s
08-17-2004, 01:53 PM
glad you got it,
I didnt even think about the Like.
You would not have gotten what you were looking for without the Like in
the SQL statement.

I would defintaly come back to these forms. People here are pretty good about answering quesitons. I have only been using this form for a couple of months, i have another form that I did use on a regular basis but this form has much more activity between its users.

Good Luck.