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!

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

proper code syntax for dynamically switching sql recordset query

Status
Not open for further replies.

aonefun

Technical User
May 21, 2007
79
US
Each of the following recordsets work as intended when used individually, however, I would like to switch between the two based on if Request.QueryString("PostalCode")has or does not have a value. I'm not sure if the proper code would include the IsNull or IsEmpy but when tried either one (see code that follows), the app reads the second recordset as if there is a value for Request.QueryString("PostalCode") even when there really is no value for it.

If IsNull(Request.QueryString("PostalCode")) Then
Recordset1.Source = "SELECT SoldToCMAccountNo, ID, DocStatus, CustomText10, CustomText07, CustomText08, ShipToCompany, ShipToContact, ShipToAddress1, ShipToAddress2, ShipToCity, ShipToState, ShipToPostalCode, ShipToCountry, ShipToPhone, ShipToPhoneExt, DocID, ManufacturerPartNumber, prodfamid_fam, ZIP_CODE, LATITUDE, LONGITUDE, STATE FROM Query1 ORDER BY DocID DESC"
Else
Recordset1.Source = "SELECT SoldToCMAccountNo, ID, DocStatus, CustomText10, CustomText07, CustomText08, ShipToCompany, ShipToContact, ShipToAddress1, ShipToAddress2, ShipToCity, ShipToState, ShipToPostalCode, ShipToCountry, ShipToPhone, ShipToPhoneExt, DocID, ManufacturerPartNumber, prodfamid_fam, ZIP_CODE, LATITUDE, LONGITUDE, STATE FROM Query1 WHERE LATITUDE <= " + Replace(Recordset1__HighLatitude, "'", "''") + " AND latitude >= " + Replace(Recordset1__LowLatitude, "'", "''") + " AND longitude >= " + Replace(Recordset1__LowLongitude, "'", "''") + " AND longitude <= " + Replace(Recordset1__HighLongitude, "'", "''") + " ORDER BY DocID DESC"
End If
 
The Request.QueryString collection contains name/value pairs.

To pull a particular value from the collection, you pass in the the name and it returns the value.

If the name is not found in the collection, an empty string is returned.

Lets look at an example... Suppose your QueryString looks like this:[tt]
?ThingOne=Foo&ThingTwo=Bar&ThingThree=&Hoppy=Frood
[/tt]
Request.QueryString("ThingOne") would return "Foo"
Request.QueryString("ThingTwo") would return "Bar"
Request.QueryString("ThingThree") would return ""
Request.QueryString("Hoppy") would return "Frood"
... and Request.QueryString("Hippopotamus") would return ""

Notice: It is not possible using this technique to distinguish between an empty value like "ThingThree" and a non-existant value like "Hippopotamus". If you need to do this then you can use the InStr() function to search the raw QueryString as so:[tt]
If Instr(Request.QueryString, "Hippopotamus") < 1 Then
Response.Write "No Hippopotamus in QueryString"
End If
[/tt]

So here is a test for an empty string:[tt]
strTest = ""
If strTest = "" Then
Response.Write "The string is empty"
Else
Response.Write "The string was not empty"
End If[/tt]


ps: Empty is not the same as Null, and an empty string is different from both Empty and Null.
Here is a trick: If you have a variable which may be one of these values (or non-values), concatinating it will an empty string will force an implicit datatype conversion to string.
For example, suppose you have a variable named UserID and it may contain Null or Empty or it might contain a valid value... do this:[tt]
If UserID [red]& ""[/red] = "" Then
Response.Write "UserID has nothing"
Else
Response.Write "UserID has something"
End If
[/tt]
This is a lot easier than frist testing for Null, then testing for Empty, and finally testing for an empty string.
 
I did this test:

If Request.QueryString("PostalCode") ="" Then
Response.Write "The string is empty"
Else
Response.Write "The string was not empty"
End If

Surprisingly enough when I thought the string was empty it was not! Thanks for this valuable tip. Now what do I do?

More app details-This is a three page app:

Page one contains the form which includes Request.Form("PostalCode")

Page two is required to process a script and then redirects to page three - and passes the value of Request.Form("PostalCode") as a querystring to page three.

Page three is the page containing the recordsets that rely on the querystring containing a postal code value or not. Now I learn that the string was not empty even though the Request.Form("PostalCode") was left blank on page one.

What would you suggest I do to determine if the postal code form element is filled out or not on page while on page three?
 
would a possible solution be setting the initial value of Request.Querystring("PostalCode") to a default value, such as "-1" and having this value remain unchanged unless there is a fresh value provided by the querystring value passed on from page two? If yes, how would I code this?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top