Database Query not working

[tsbarnes]

I am writing a asp page that query's my db for matching records. I Have an input box named Partnumber and twi variables that are predefined that I pass to to the db.

If the PartNumber Field has a value thne I run one Sql statemnet and if it has another I run a diff. sql statement.

When I put a value in the Partnumber field the query works fine but when I leave it empty I get no results written to the page. I have pasted the code below. Let me know if you see something that I have left off. You can test the page to see the results by going to http://www.object5.com/test/dbsearch.asp

Thanks,

tsbarnes

************** Code ********************


<%
strProducttype = "Bearings"
strBrand = "Timken"

If strPartNumber = "" Then

strTest = "Partnumber is Empty"
strBrand = "Timken"

Response.Write strTEst & "<br>" & strBrand & "<br>"
'Build our query based on the input.

strSQL = "SELECT * From Products " _
& "WHERE ProductType = '" & strProductType & "' " _
& "and BrandAbbrev = '" & strBrand & "' "
'& "ORDER BY PartNumber Asc"

Else

' Build our query based on the input.
strSQL = "SELECT * FROM Products " _
& "WHERE PartNumber LIKE '%" & strPartNumber & "%' " _
& "and ProductType = '" & strProductType & "' " _
& "ORDER BY PartNumber, cost, BrandAbbrev Asc"


Set rstSearch = objConn.Execute(strSQL)


%>
<table border="1">
<tr>
<th>PartNumber</th>
<th>Brand</th>
</tr>
<%
Do While Not rstSearch.EOF
%>
<tr>
<td><%= rstSearch.Fields("PartNumber").Value %></td><td> <%= rstSearch.Fields("ProductType").Value %></td>
<td><%= rstSearch.Fields("Cost").Value %></td>
</tr>
<%

rstSearch.MoveNext
Loop
%>
</table>
<%
' Close our recordset and connection and dispose of the objects
rstSearch.Close
Set rstSearch = Nothing


End If


%>

[tsbarnes]

I figured it out thanks!

[whammy]

So what was the problem? Just out of curiosity.

[tsbarnes]

Whammy,

Yeah the problem was with this bit of code:

************************************************
If strPartNumber = "" Then

strTest = "Partnumber is Empty"
strBrand = "Timken"

Response.Write strTEst & "<br>" & strBrand & "<br>"
'Build our query based on the input.

strSQL = "SELECT * From Products " _
& "WHERE ProductType = '" & strProductType & "' " _
& "and BrandAbbrev = '" & strBrand & "' "
'& "ORDER BY PartNumber Asc"

Else

' Build our query based on the input.
strSQL = "SELECT * FROM Products " _
& "WHERE PartNumber LIKE '%" & strPartNumber & "%' " _
& "and ProductType = '" & strProductType & "' " _
& "ORDER BY PartNumber, cost, BrandAbbrev Asc"

**************************************************

What I wanted to do was run one Sql statement if they leave the PartNumber empty and run another if they gave it a value. So instead of doing a If...Then to include both SQL statements I did a if...then for earch statement and it worked. Don't understand why it wouldn't work the other way...but oh well.

Here is the new code:

*************************************************
if strPartNumber <> "" Then

' Build our query based on the input.
strSQL = "SELECT * FROM Products " _
& "WHERE PartNumber LIKE '%" & strPartNumber & "%' " _
& "and ProductType = '" & strProductType & "' " _
& "ORDER BY PartNumber, cost, BrandAbbrev Asc"

end if

If strPartNumber = "" Then


'Build our query based on the input.
strSQL = "SELECT * From Products where ProductType = '" & strProductType & "' and BrandAbbrev= '" & strBrand & "'"

end if

**************************************************

Thanks,

tsbarnes