p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

asp_databases thread: all recordsets being pulled


Message #1 by mike@i... on Wed, 06 Dec 2000 01:20:18 -0500
I posted this question the other day then as luck would have it our mail

server went down. so please forgive me if anybody has answered it. I

have a form with 3  fields to search, when the fields are blank it pulls

everything.



Dim strSQL

Dim objSrch

Dim strConnect

Dim blnAddedWhere



strSQL = "SELECT Person.UsrFirstName, Person.UsrLastName,

Person.EmailAddress, " & _

  "BaseNames.Base_Name,BaseNames.Base_YrArrived,

BaseNames.Base_YrDeparted, " & _

  "BaseNames.Base_State, BaseNames.Base_Country " & _

  "FROM Person INNER JOIN BaseNames ON Person.PersonID

BaseNames.MilitaryID "

  blnAddedWhere = False



If Request.Form("SrchFirstName") <> " " Then

 If Not blnAddedWhere Then

  strSQL=strSQL & "WHERE "

  blnAddedWhere = True

 End If

  strSQL = strSQL & " Person.UsrFirstName LIKE '%" &

Request.Form("SrchFirstName") & "%' "

End If



If Request.Form("SrchLastName") <> " " Then

 If Not blnAddedWhere Then

  strSQL = strSQL & "WHERE"

  blnAddedWhere = True

 Else

  strSQL = StrSQL & " AND "

 End If

 strSQL = strSQL &  " Person.UsrLastName LIKE '%" &

Request.Form("SrchLastName") & "%' "

End If



If Request.Form("SrchBaseName") <> " " Then

 If Not blnAddedWhere Then

  strSQL = strSQL & "WHERE"

  blnAddedWhere = True

 Else

  strSQL = StrSQL & " AND "

 End If

 strSQL = strSQL & " BaseNames.Base_Name LIKE '%" &

Request.Form("SrchBaseName") & "%' "

End If



strSQL = strSQL & "ORDER BY Person.UsrLastName, Person.UsrFirstName  "



' Create The Recordset

Set objSrch = Server.CreateObject ("ADODB.Recordset")

objSrch.Open strSQL, strConnect, adOpenForwardOnly, adLockReadOnly,

adCmdText



If objSrch.EOF Then

  Response.Write "No items were found."

Else

 Response.Write "<TABLE WIDTH=472 BORDER=0 CELLSPACING=0 CELLPADDING=0>"



While Not objSrch.EOF

 Response.Write "<TR>" & _

     "<TD CLASS=""dates"" COLSPAN=4>" & "  " &

objSrch("UsrLastName") & ", " & objSrch("UsrFirstName") & "</TD>" & _

     "</TR>" & _

     "<TR>" & _

     "<TD CLASS=""events"">" & "  " & objSrch("Base_Name") &

"</TD>" &  _

     "<TD CLASS=""events"">" & "  " &

objSrch("Base_YrArrived") & "</TD>" & _

     "<TD CLASS=""events"">" & "  " &

objSrch("Base_YrDeparted") & "</TD>" & _

     "<TD CLASS=""events"">" & "  " & objSrch("Base_State") &

", " & objSrch("Base_Country") & "</TD>" & _

     "</TR>" & _

     "<TR>" & _

     "<TD CLASS=""events"" COLSPAN=4>" & "  " & "<A

HREF=""MAILTO:" & objSrch("EmailAddress") & """>" &

objSrch("EmailAddress") & "</A><BR>" & "</TD>" & _

     "</TR>" & _

 objSrch.MoveNext

Wend

 Response.Write "<TR>" & _

     "<TD><A HREF=""search.asp""><IMG SRC=""images/btn_newsearch.gif""

WIDTH=125 HEIGHT=20 BORDER=0></A></TD>" &  _

     "</TR>" & _

     "</TABLE>"

End If



objSrch.close

Set objSrch = Nothing





thanks

Mike Moore



Message #2 by Imar Spaanjaars <Imar@S...> on Wed, 06 Dec 2000 13:18:17 +0100
So???  What exactly is your question / problem?? You are just describing a 

situation and posting code.



I guess it shouldn't pull everything when the fields are empty, right?

This is how it comes:



If Request.Form("SrchFirstName") <> " " Then

         If Not blnAddedWhere Then

                 strSQL=strSQL & "WHERE "

                 blnAddedWhere = True

         End If

         strSQL = strSQL & " Person.UsrFirstName LIKE '%" &

         Request.Form("SrchFirstName") & "%' "

End If



You check whether Request.Form("SrchFirstName")  is equal to a space, which 

it won't be if the field is blank.



Try this instead:



If Trim(Request.Form("SrchFirstName")) <> "" Then ' remove whitespace with 

Trim() and compare to 0-length string





HtH



Imar





At 01:20 AM 12/6/2000 -0500, you wrote:

>I posted this question the other day then as luck would have it our mail

>server went down. so please forgive me if anybody has answered it. I

>have a form with 3  fields to search, when the fields are blank it pulls

>everything.

>

>Dim strSQL

>Dim objSrch

>Dim strConnect

>Dim blnAddedWhere

>

>strSQL = "SELECT Person.UsrFirstName, Person.UsrLastName,

>Person.EmailAddress, " & _

>   "BaseNames.Base_Name,BaseNames.Base_YrArrived,

>BaseNames.Base_YrDeparted, " & _

>   "BaseNames.Base_State, BaseNames.Base_Country " & _

>   "FROM Person INNER JOIN BaseNames ON Person.PersonID

>BaseNames.MilitaryID "

>   blnAddedWhere = False

>

>If Request.Form("SrchFirstName") <> " " Then

>  If Not blnAddedWhere Then

>   strSQL=strSQL & "WHERE "

>   blnAddedWhere = True

>  End If

>   strSQL = strSQL & " Person.UsrFirstName LIKE '%" &

>Request.Form("SrchFirstName") & "%' "

>End If

>

>If Request.Form("SrchLastName") <> " " Then

>  If Not blnAddedWhere Then

>   strSQL = strSQL & "WHERE"

>   blnAddedWhere = True

>  Else

>   strSQL = StrSQL & " AND "

>  End If

>  strSQL = strSQL &  " Person.UsrLastName LIKE '%" &

>Request.Form("SrchLastName") & "%' "

>End If

>

>If Request.Form("SrchBaseName") <> " " Then

>  If Not blnAddedWhere Then

>   strSQL = strSQL & "WHERE"

>   blnAddedWhere = True

>  Else

>   strSQL = StrSQL & " AND "

>  End If

>  strSQL = strSQL & " BaseNames.Base_Name LIKE '%" &

>Request.Form("SrchBaseName") & "%' "

>End If

>

>strSQL = strSQL & "ORDER BY Person.UsrLastName, Person.UsrFirstName  "

>

>' Create The Recordset

>Set objSrch = Server.CreateObject ("ADODB.Recordset")

>objSrch.Open strSQL, strConnect, adOpenForwardOnly, adLockReadOnly,

>adCmdText

>

>If objSrch.EOF Then

>   Response.Write "No items were found."

>Else

>  Response.Write "<TABLE WIDTH=472 BORDER=0 CELLSPACING=0 CELLPADDING=0>"

>

>While Not objSrch.EOF

>  Response.Write "<TR>" & _

>      "<TD CLASS=""dates"" COLSPAN=4>" & "  " &

>objSrch("UsrLastName") & ", " & objSrch("UsrFirstName") & "</TD>" & _

>      "</TR>" & _

>      "<TR>" & _

>      "<TD CLASS=""events"">" & "  " & objSrch("Base_Name") &

>"</TD>" &  _

>      "<TD CLASS=""events"">" & "  " &

>objSrch("Base_YrArrived") & "</TD>" & _

>      "<TD CLASS=""events"">" & "  " &

>objSrch("Base_YrDeparted") & "</TD>" & _

>      "<TD CLASS=""events"">" & "  " & objSrch("Base_State") &

>", " & objSrch("Base_Country") & "</TD>" & _

>      "</TR>" & _

>      "<TR>" & _

>      "<TD CLASS=""events"" COLSPAN=4>" & "  " & "<A

>HREF=""MAILTO:" & objSrch("EmailAddress") & """>" &

>objSrch("EmailAddress") & "</A><BR>" & "</TD>" & _

>      "</TR>" & _

>  objSrch.MoveNext

>Wend

>  Response.Write "<TR>" & _

>      "<TD><A HREF=""search.asp""><IMG SRC=""images/btn_newsearch.gif""

>WIDTH=125 HEIGHT=20 BORDER=0></A></TD>" &  _

>      "</TR>" & _

>      "</TABLE>"

>End If

>

>objSrch.close

>Set objSrch = Nothing

>

>

>thanks

>Mike Moore

>

>




  Return to Index