SQL querry dont accept some zip codes, but do accept others..(ASP) 0.o

[Sovik]

Basiclly the aplication has to find some places in a radius of 30 miles according to the zip code provided.

The problemm that I found is a bit wierd for me, because it's accept some zip codes but does not accept others giving the error in the sql string and saying Line 1: unexpected token ")' .

Examples 90### is ok, the 208## gives the error, and so on, some zip codes work great, some of them dont

I can not post the SQL string right now. But if you can point me to the possible problem I will glad to hear/see them.

BTW, this is an ASP aplication, and the code was not done by me, so I am a bit lost in the SQL querry wich is quite long

 

[mrdenny]

We'll need the SQL string to give you any good info.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Sovik]

Here is the related code to the problemm:

set ziplocator = Server.CreateObject("ZipLocator.Locator")

	on error resume next

	'// Get a Recordset of distances from the user's ZIP Code ordered from nearest to farthest

	SET rsresults  = ziplocator.RadiusScan(zipcode,30)
		
	if err.number <> 0 then
		
	else
		on error goto 0
		'// display the results
		if rsresults.EOF then
			cmn_strMessage =  "<BR> Zip Locator : No Zip Codes were found within the 30 Mile Radius!"
			exit sub
		else

		zipRetVal= "'10000'"
		do until rsresults.EOF

			zipRetVal =  zipRetVal & ",'" & rsresults(0) & "'"
			'response.write zipretval & "<br>"
			rsresults.MoveNext
		loop
		end if
	end if

The SQL string wich is pritty big :

strSQL = "SELECT TOP 10 CASE WHEN (RTRIM(LTRIM(strOrganization)) = 'N/A') "
	strSQL = strSQL & "	THEN ' ' "
	strSQL = strSQL & "	 ELSE RTRIM(LTRIM(strOrganization)) "
	strSQL = strSQL & "	 END Organization "
	strSQL = strSQL & "	 , CASE WHEN (RTRIM(LTRIM(strFirstName)) = 'N/A') "
	strSQL = strSQL & "	THEN ' ' "
	strSQL = strSQL & "	WHEN (RTRIM(LTRIM(strLastName)) = 'N/A') "
	strSQL = strSQL & "	THEN ' ' "
	strSQL = strSQL & "	ELSE RTRIM(LTRIM(strFirstName)) "
	strSQL = strSQL & "	+ ' ' + RTRIM(LTRIM(strLastName)) "
	strSQL = strSQL & "	END Name,TBLAddress.strAddress,TBLCITY.strCity,tlkpProvinceState.strProvince,tblPostalZip.strPostalZip"
	strSQL = strSQL & " FROM tblProvider "
	strSQL = strSQL & " LEFT OUTER JOIN tblPostalZip ON tblProvider.fkPostalZipID = tblPostalZip.pkPostalZipID "
	strSQL = strSQL & " LEFT OUTER JOIN	tblAddress ON tblProvider.fkAddressID = TBLAddress.pkAddressID "
	strSQL = strSQL & " LEFT OUTER JOIN	tblCity ON tblProvider.fkCityID = tblCity.pkCityID "
	strSQL = strSQL & " LEFT OUTER JOIN	tlkpProvinceState ON tblProvider.fkProvinceStateID = tlkpProvinceState.pkProvinceStateID "
	strSQL = strSQL & " LEFT OUTER JOIN	tblOrganization ON tblProvider.fkOrganizationID = tblOrganization.pkOrganizationID	"
	strSQL = strSQL & " LEFT OUTER JOIN	tblProviderType ON tblProvider.fkProviderTypeID = tblProviderType.pkProviderTypeID "
	strSQL = strSQL & " LEFT OUTER JOIN	tblFirstName ON tblProvider.fkFirstNameID = tblFirstName.pkFirstNameID "
	strSQL = strSQL & " LEFT OUTER JOIN	tblLastName ON tblProvider.fkLastNameID = tblLastName.pkLastNameID "
	strSQL = strSQL  & " WHERE tblPostalZip.strPostalZip IN ("&zipRetVal&") AND tblProviderType.pkProviderTypeID = " & request.form("provType") &"" '& " AND blnPreferred = 1 "
	'response.write "<BR> " & strSQL
	objProvCommand.CommandType= adCmdText
	objProvCommand.CommandText = strSQL
	SET rsProvDBResults = objProvCommand.execute

Any help is welcome.!!

 

[SkipVought]

You state that 280## produces an error. An error in the SQL that you posted when it executes?

Are there 280## ZIPs in tblProvider and/or tblPostalZip?

What are the corresponding fkPostalZipID values?

How do these compare with a ZIP that works?



Skip,

_[red][/red]

 

[Sovik]

Something strange happens today, without touching any code, the zip code that was not working, worked!! It was 20815.
But still some zip codes don't work, like 30815 for example.

If the zip code does not exists in the table the programm will report the error: "No zip code found"


The error I get is next"
"Microsoft OLE DB Provider for SQL Server (0x80040E31)
Timeout expired
/e-services/ziplookup/index.asp, line 178"

Line 178: SET rsProvDBResults = objProvCommand.execute

What are the corresponding fkPostalZipID values?

How do these compare with a ZIP that works?

I can not say you this right now, because I dont have acces to the Db itself I have to ask 4 it.

In my opinion the problemm resides in the ZipRetVal variable..

 

[SQLBill]

Timeout expired

It looks like the query is taking to long to get a response back. Is the Zip Code field indexed or does the query have to examine EVERY Zip Code?

-SQLBill

Posting advice: FAQ481-4875

 

[Sovik]

As I said before I dont have the acces to the DB , so I can not chek it's structure.

But what I know that the variable ZipRetVal is pritty big. Containing about 700 values. Wich is used here:
.
.
.
strSQL = strSQL & " WHERE tblPostalZip.strPostalZip IN ("&zipRetVal&") AND tblProviderType.pkProviderTypeID = " & request.form("provType") & " AND blnPreferred = 1 "

So may be you are right.

 

[Sovik]

I am wrong in what I just said in the previous post, because for the zip code like 30815 the variable ZipRetVal only contains 384 records.

So again, I have no clues why this sh*t happenes

 

[SkipVought]

In the zipRetVal string, are there ONLY complete 5-digit ZIPs, each enclosed in TICs?

Skip,

_[red][/red]

 

[Sovik]

Yep, for the 30815 its looks like this:

'99999','30815','30812','30906','30910','30913','30805','30905','30916','30816',....

 

[mrdenny]

If you are getting a timeout, then either the server is to slow or you are trying to return to much data.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com