Solved

Query not returning results

Posted on 2013-12-13
11
264 Views
Last Modified: 2013-12-19
I have the query below. I am replacing the values with the values that are actually passed from the form. I have double checked and I SHOULD have results from this query, yet, because of the two last AND I get nothing.

-----------------------------

SELECT  a.UserId ,
        a.UserType ,
        a.FirmId ,
        a.EmployerId ,
        a.LastNm ,
        a.FirstNm ,
        a.DobD ,
        a.SSN ,
        a.Email ,
        a.Nationality ,
        a.NiStatus ,
        a.NIVMaxStatus ,
        a.ExpiresOnD ,
        a.Docstatusstat ,
        a.NIVMaxStatus ,
        a.I94DateD ,
        a.I94 ,
        a.I797Date ,
        a.I797 ,
        a.EadDate ,
        a.Ead ,
        a.ApDate ,
        a.ApNum ,
        a.Iap66Date ,
        a.Iap66 ,
        a.GcDate ,
        a.GC ,
        a.I20Date ,
        a.I20 ,
        a.PpExpireOnD ,
        a.PpNum ,
        a.PobCountry ,
        b.MaidenNm AS Employer ,
        d.MailStr + ' ' + d.MaidenNm AS Attorney ,
        a.PobCountry ,
        a.archivedcont ,
        a.contactstatus ,
        a.Docstatusstat ,
        e.Docstatdesc ,
        a.Dswg ,
        a.VisaExp ,
        a.VisaType ,
        a.VisaStatus
FROM    users a
        LEFT JOIN users AS b ON a.employerid = b.userid
        INNER JOIN attscont AS c ON a.userid = c.userid
        INNER JOIN users AS d ON c.attyid = d.userid
        LEFT JOIN Documentstatus AS e ON e.documentstatus = a.Docstatusstat

WHERE a.usertype = 'contact'   


--- This are the two parts of the statement that make me get nothing in my results, when in reality there are records that match the query ----


AND a.PobCountry IN ('Afghanistan, Albania, Algeria, American Samoa, Andorra, Angola, Anguilla, Antarctica, Antigua and Barbuda, Argentina, Armenia, Aruba, Australia, Austria, Azerbaijan, Bahamas, Bahrain, Bangladesh, Barbados, Belarus, Belgium, Belize, Benin, Bermuda, Bhutan, Bolivia, Bosnia and Herzegovina, Botswana, Bouvet Island, Brazil, Brunei, Bulgaria, Burkina Faso, Burundi, Cambodia, Cameroon, Canada, Cape Verde, Cayman Islands, Central African Republic, Chad, Chile, China, Christmas Island, Colombia, Comoros, Congo, Cook Islands, Costa Rica, Croatia (Hrvatska), Cuba, Cyprus, Czech Republic, D. R. P. Corea, D.R.P. Korea, Dem. Rep. of Congo (Zaire), Denmark, Djibouti, Dominica, Dominican Republic, East Timor, Ecuador, Egypt, El Salvador, Equatorial Guinea, Eritrea, Estados Federados de Micronesia, Estonia, Ethiopia, Falkland Islands (Malvinas), Faroe Islands, Fiji, Finland, France, French Guiana, French Polynesia, Gabon, Gambia, Georgia, Germany, Ghana, Gibraltar, Greece, Greenland, Grenada, Guadeloupe, Guam, Guatemala, Guinea, Guinea - Bissao, Guyana, Haiti, Honduras, Hong Kong SAR, PRC, Hungary, Iceland, India, Indonesia, Iran, Iraq, Ireland, Israel, Italy, Ivory Coast, Jamaica, Japan, Jordan, Kazakhstan, Kenya, Kiribati, Kuwait, Kyrgystan, Lao, Latvia, Lebanon, Lesotho, Letonia, Liberia, Libya, Liechtenstein, Lithuania, Luxemburg, Macao, Macedonia, Macronesia, Madagascar, Malawi, Malaysia, Maldives, Mali, Malta, Martinique, Mauritania, Mauritius, Mexico, Moldova, Monaco, Mongolia, Montenegro, Montserrat, Morocco, Mozambique, Myanmar, Namibia, Nauru, Nepal, Netherlands, Netherlands Antilles, New Caledonia, New Zealand, Nicaragua, Niger, Nigeria, Niue, Norfolk Island, Northern Mariana Islands, Norway, Oman, Pakistan, Palau, Panama, Papua New Guinea, Paraguay, Peru, Philippines, Poland, Portugal, Puerto Rico, Qatar, República de las Islas Marshall, Romania, Russia, Rwanda, Saint Kitts and Nevis, Saint Lucia, Saint Vincent and the Grenadines, Samoa, San Marino, Sao Tome and Principe, Saudi Arabia, Scotland, Senegal, Serbia, Serbia and Montenegro, Seychelles, Sierra Leone, Singapore, Slovak Republic, Slovenia, Solomon Islands, Somalia, South Africa, South Korea, Spain, Sri Lanka, St Pierre and Miquelon, Sudan, Suriname, Swaziland, Sweden, Switzerland, Syria, Taiwan, Tajikistan, Tanzania, Thailand, Togo, Tonga, Trinidad and Tobago, Tunisia, Turkey, Turkmeninstan, Turks and Caicos Islands, Tuvalu, Uganda, Ukraine, United Arab Emirates, United Kingdom, United States, Uruguay, Uzbekistan, Vanuatu, Vatican City State (Holy See), Venezuela, Vietnam, Virgin Islands (British), Virgin Islands (US), Western Sahara, Yemen, Zambia, Zimbawe' )  
AND b.userid IN (1717, 23320, 30429, 22455, 22908, 30328, 22673, 14120, 23781, 23321, 30707, 15304, 22428, 22601, 2040, 30034, 14272, 22512, 22513, 22376, 2039, 22682, 22574, 22571, 17185, 30199, 22577, 23777, 23776, 22546, 22478, 22292, 23155, 30259, 23319, 22897, 23317, 22579, 30242, 6138, 2038, 30042, 23362, 17544, 30455, 23570, 22883, 3732, 30743, 22712, 22913, 30434, 22718, 30499, 22589, 22879, 22866, 22888, 22893, 22573, 2104, 6139, 30706, 2103, 23228, 22572, 23846, 30676, 17524, 22470, 22302, 22553, 22578, 22907, 30584, 22604, 22576, 22575, 22511, 1715, 3730, 30684, 14118, 30674, 22454, 3547, 22453, 22469)

Open in new window

0
Comment
Question by:amucinobluedot
  • 5
  • 3
  • 3
11 Comments
 
LVL 12

Accepted Solution

by:
Tony303 earned 251 total points
ID: 39718090
I think you need single quotes around each of the country names. So....

a.PopCountry IN ('Afghanistan','Albania','Algeria'........'Zimbabwe')

Your syntax has to equal all the text between the single quotes.....hence no records match.


I hope this is the answer.

T
0
 

Author Comment

by:amucinobluedot
ID: 39718212
Well .. in my SQL manager I get the results, but with my recordset I don't

--

<%
Dim ContactsFiltered__MMColParam
ContactsFiltered__MMColParam = "0"
If (Session("FirmId") <> "") Then
  ContactsFiltered__MMColParam = Session("FirmId")
End If
%>
<%
Dim ContactsFiltered__MMColParam2
ContactsFiltered__MMColParam2 = "0"
If (Request.Form("AttyParalegals") <> "") Then
  ContactsFiltered__MMColParam2 = Request.Form("AttyParalegals")
End If
%>
<%
Dim ContactsFiltered__MMColParam3
ContactsFiltered__MMColParam3 = "%"
If (Request.Form("statuscont")  <> "") Then
  ContactsFiltered__MMColParam3 = Request.Form("statuscont")
End If
%>
<%
Dim ContactsFiltered__MMColParam4
ContactsFiltered__MMColParam4 = "%"
If (Request.Form("Contype")  <> "") Then
  ContactsFiltered__MMColParam4 = Request.Form("Contype")
End If
%>
<%
Dim ContactsFiltered__MMColParam5
ContactsFiltered__MMColParam5 = "%"
If (Request.Form("FirstNm") <> "") Then
  ContactsFiltered__MMColParam5 = Request.Form("FirstNm")
End If
%>
<%
Dim ContactsFiltered__MMColParam6
ContactsFiltered__MMColParam6 = "%"
If (Request.Form("LastNm") <> "") Then
  ContactsFiltered__MMColParam6 = Request.Form("LastNm")
End If
%>
<%
Dim ContactsFiltered__MMColParam7
ContactsFiltered__MMColParam7 = "%"
If (Request.Form("SSN") <> "") Then
  ContactsFiltered__MMColParam7 = Request.Form("SSN")
End If
%>
<%
Dim ContactsFiltered__MMColParam8
ContactsFiltered__MMColParam8 = "%"
If (Request.Form("email") <> "") Then
  ContactsFiltered__MMColParam8 = Request.Form("email")
End If
%>
<%
Dim ContactsFiltered__MMColParam9
ContactsFiltered__MMColParam9 = "%"
If (Request.Form("Nationality") <> "") Then
  ContactsFiltered__MMColParam9 = Request.Form("Nationality")
End If
%>
<%
Dim ContactsFiltered__MMColParam10
ContactsFiltered__MMColParam10 = "%"
If (Request.Form("Currstatus") <> "") Then
  ContactsFiltered__MMColParam10 = Request.Form("Currstatus")
End If
%>
<%
Dim ContactsFiltered__MMColParam500
ContactsFiltered__MMColParam500 = "a.FirstNm"
If (Request.Form("Orderbygeninfo")  <> "") Then
  ContactsFiltered__MMColParam500 = Request.Form("Orderbygeninfo")
End If
%>
<%
Dim ContactsFiltered__MMColParam11
ContactsFiltered__MMColParam11 = "a"
If (Request.Form("countryDDM")  <> "") Then
  ContactsFiltered__MMColParam11 = Request.Form("countryDDM")
End If
%>
<%
Dim ContactsFiltered__MMColParam12
ContactsFiltered__MMColParam12 = "0"
If (Request.Form("Employerlist") <> "") Then
  ContactsFiltered__MMColParam12 = Request.Form("Employerlist")
End If
%>
<%
Dim ContactsFiltered
Dim ContactsFiltered_numRows

Set ContactsFiltered = Server.CreateObject("ADODB.Recordset")
ContactsFiltered.ActiveConnection = MM_eimmigration_STRING
ContactsFiltered.Source = "SELECT a.UserId ,          a.UserType ,          a.FirmId ,          a.EmployerId ,          a.LastNm ,          a.FirstNm ,          a.DobD ,          a.SSN ,          a.Email ,          a.Nationality ,          a.NiStatus ,          a.NIVMaxStatus ,          a.ExpiresOnD ,          a.Docstatusstat ,          a.NIVMaxStatus ,          a.I94DateD ,          a.I94 ,          a.I797Date ,          a.I797 ,          a.EadDate ,          a.Ead ,          a.ApDate ,          a.ApNum ,          a.Iap66Date ,          a.Iap66 ,          a.GcDate ,          a.GC ,          a.I20Date ,          a.I20 ,          a.PpExpireOnD ,          a.PpNum ,          a.PobCountry ,          b.MaidenNm AS Employer ,          d.MailStr + ' ' + d.MaidenNm AS Attorney ,          a.PobCountry ,          a.archivedcont ,          a.contactstatus ,          a.Docstatusstat ,          e.Docstatdesc ,          a.Dswg ,          a.VisaExp ,          a.VisaType ,          a.VisaStatus  FROM users a          LEFT JOIN users AS b ON a.employerid = b.userid          INNER JOIN attscont AS c ON a.userid = c.userid          INNER JOIN users AS d ON c.attyid = d.userid          LEFT JOIN Documentstatus AS e ON e.documentstatus = a.Docstatusstat  WHERE a.usertype = 'contact'            AND a.firmid = " + Replace(ContactsFiltered__MMColParam, "'", "''") + "            AND d.userid = " + Replace(ContactsFiltered__MMColParam2, "'", "''") + "            AND a.Archivedcont LIKE '" + Replace(ContactsFiltered__MMColParam3, "'", "''") + "'            AND a.Contactstatus LIKE '" + Replace(ContactsFiltered__MMColParam4, "'", "''") + "'            AND ISNULL(a.FirstNm, '') LIKE '" + Replace(ContactsFiltered__MMColParam5, "'", "''") + "%'            AND ISNULL(a.LastNm, '') LIKE '" + Replace(ContactsFiltered__MMColParam6, "'", "''") + "%'            AND ISNULL(a.SSN, '') LIKE '" + Replace(ContactsFiltered__MMColParam7, "'", "''") + "%'            AND ISNULL(a.email, '') LIKE '" + Replace(ContactsFiltered__MMColParam8, "'", "''") + "%'            AND ISNULL(a.Nationality, '') LIKE '" + Replace(ContactsFiltered__MMColParam9, "'", "''") + "%'            AND ISNULL(a.Nistatus, '') LIKE '" + Replace(ContactsFiltered__MMColParam10, "'", "''") + "%'  AND a.PobCountry IN ('" + Replace(ContactsFiltered__MMColParam11, "'", "''") + "' )  AND b.userid IN (" + Replace(ContactsFiltered__MMColParam12, "'", "''") + ")  ORDER BY " + Replace(ContactsFiltered__MMColParam500, "'", "''") + ""
ContactsFiltered.CursorType = 0
ContactsFiltered.CursorLocation = 2
ContactsFiltered.LockType = 1
ContactsFiltered.Open()

ContactsFiltered_numRows = 0
%>

--

Note that the last two parameters are the ones being compared with "IN"

AND a.PobCountry IN ('" + Replace(ContactsFiltered__MMColParam11, "'", "''") + "' )  
AND b.userid IN (" + Replace(ContactsFiltered__MMColParam12, "'", "''") + ")  

Do I need to change anything so each record is separated with '  ?
0
 
LVL 12

Assisted Solution

by:Tony303
Tony303 earned 251 total points
ID: 39718379
Hi,

In your record set code, can you hardcode a valid country and a valid userid just to test that there will be records returned.

If it works for 1 country then try with 2 valid countries.
Remember the in clause needs to be contained between brackets. Values contained in the IN clause need to be separated by a comma.
Remember text items need to have a single quote at the start and end on the text string....

EG
IN ('Austria','Australia')

Traditionally, values that are int don't need single quotes around them. So your userid's are fine.
The interesting thing is, your first posting has the userid's listed correctly, your second posting has these userid's via the Param12 variable being surrounded by quotes. I'm not totally convinced that the quotes are needed.

T
0
 
LVL 44

Assisted Solution

by:Rainer Jeschor
Rainer Jeschor earned 249 total points
ID: 39718424
Hi,
could you try to replace this line
ContactsFiltered__MMColParam11 = Request.Form("countryDDM") 

Open in new window

with
Dim tmpCountryArray
tmpCountryArray =  Request.Form("countryDDM").Split(",")
ContactsFiltered__MMColParam11 = Join(tmpCountryArray, "','")

Open in new window

and then in your SQL statement creation instead of
a.PobCountry IN ('" + Replace(ContactsFiltered__MMColParam11, "'", "''") + "' )

Open in new window

use
a.PobCountry IN ('" + ContactsFiltered__MMColParam11 + "' )

Open in new window


Explanation of my code changes:
From the form we get a comma separated value as string. We then SPLIT the string based on comma to get an array of individual items. Then we use the JOIN function to build again a string, this time with single quote + comma + single quote

Sample:
Austria,Germany,Mexico-> Austria','Germany','Mexico

This we can then put into the SQL query string - we just have to set the initial and the ending single quotes.

HTH
Rainer
0
 

Author Comment

by:amucinobluedot
ID: 39718846
Thanks .. .Ill try now
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:amucinobluedot
ID: 39718857
I get this error:

Microsoft VBScript runtime error '800a01b6'

Object doesn't support this property or method: 'Request.Form(...).Split'

/bluedot/Intranet/reports/ContactReports/07results.asp, line 466

On this piece of code:

Dim tmpCountryArray
tmpCountryArray =  Request.Form("countryDDM").Split(",")
ContactsFiltered__MMColParam11 = Join(tmpCountryArray, "','")
0
 
LVL 44

Assisted Solution

by:Rainer Jeschor
Rainer Jeschor earned 249 total points
ID: 39718943
Sorry, my mistake (too much .NET and Javascript).
This is the correct version:
Dim tmpCountryArray
tmpCountryArray =  Split(Request.Form("countryDDM"), ",")
ContactsFiltered__MMColParam11 = Join(tmpCountryArray, "','")

Open in new window

0
 

Author Comment

by:amucinobluedot
ID: 39719061
I did the above with no errors for the above, but I get no results at all.  Is there a way to see what the value is on the results page and see how the countries are being displayed ?


<%
Dim ContactsFiltered__MMColParam
ContactsFiltered__MMColParam = "0"
If (Session("FirmId") <> "") Then
  ContactsFiltered__MMColParam = Session("FirmId")
End If
%>
<%
Dim ContactsFiltered__MMColParam2
ContactsFiltered__MMColParam2 = "0"
If (Request.Form("AttyParalegals") <> "") Then
  ContactsFiltered__MMColParam2 = Request.Form("AttyParalegals")
End If
%>
<%
Dim ContactsFiltered__MMColParam3
ContactsFiltered__MMColParam3 = "%"
If (Request.Form("statuscont")  <> "") Then
  ContactsFiltered__MMColParam3 = Request.Form("statuscont")
End If
%>
<%
Dim ContactsFiltered__MMColParam4
ContactsFiltered__MMColParam4 = "%"
If (Request.Form("Contype")  <> "") Then
  ContactsFiltered__MMColParam4 = Request.Form("Contype")
End If
%>
<%
Dim ContactsFiltered__MMColParam5
ContactsFiltered__MMColParam5 = "%"
If (Request.Form("FirstNm") <> "") Then
  ContactsFiltered__MMColParam5 = Request.Form("FirstNm")
End If
%>
<%
Dim ContactsFiltered__MMColParam6
ContactsFiltered__MMColParam6 = "%"
If (Request.Form("LastNm") <> "") Then
  ContactsFiltered__MMColParam6 = Request.Form("LastNm")
End If
%>
<%
Dim ContactsFiltered__MMColParam7
ContactsFiltered__MMColParam7 = "%"
If (Request.Form("SSN") <> "") Then
  ContactsFiltered__MMColParam7 = Request.Form("SSN")
End If
%>
<%
Dim ContactsFiltered__MMColParam8
ContactsFiltered__MMColParam8 = "%"
If (Request.Form("email") <> "") Then
  ContactsFiltered__MMColParam8 = Request.Form("email")
End If
%>
<%
Dim ContactsFiltered__MMColParam9
ContactsFiltered__MMColParam9 = "%"
If (Request.Form("Nationality") <> "") Then
  ContactsFiltered__MMColParam9 = Request.Form("Nationality")
End If
%>
<%
Dim ContactsFiltered__MMColParam10
ContactsFiltered__MMColParam10 = "%"
If (Request.Form("Currstatus") <> "") Then
  ContactsFiltered__MMColParam10 = Request.Form("Currstatus")
End If
%>
<%
Dim ContactsFiltered__MMColParam500
ContactsFiltered__MMColParam500 = "a.FirstNm"
If (Request.Form("Orderbygeninfo")  <> "") Then
  ContactsFiltered__MMColParam500 = Request.Form("Orderbygeninfo")
End If
%>
<%
Dim tmpCountryArray
tmpCountryArray =  Split(Request.Form("countryDDM"), ",")
ContactsFiltered__MMColParam11 = Join(tmpCountryArray, "','")
%>

<%
Dim ContactsFiltered
Dim ContactsFiltered_numRows

Set ContactsFiltered = Server.CreateObject("ADODB.Recordset")
ContactsFiltered.ActiveConnection = MM_eimmigration_STRING
ContactsFiltered.Source = "SELECT a.UserId ,          a.UserType ,          a.FirmId ,          a.EmployerId ,          a.LastNm ,          a.FirstNm ,          a.DobD ,          a.SSN ,          a.Email ,          a.Nationality ,          a.NiStatus ,          a.NIVMaxStatus ,          a.ExpiresOnD ,          a.Docstatusstat ,          a.NIVMaxStatus ,          a.I94DateD ,          a.I94 ,          a.I797Date ,          a.I797 ,          a.EadDate ,          a.Ead ,          a.ApDate ,          a.ApNum ,          a.Iap66Date ,          a.Iap66 ,          a.GcDate ,          a.GC ,          a.I20Date ,          a.I20 ,          a.PpExpireOnD ,          a.PpNum ,          a.PobCountry ,          b.MaidenNm AS Employer ,          d.MailStr + ' ' + d.MaidenNm AS Attorney ,          a.PobCountry ,          a.archivedcont ,          a.contactstatus ,          a.Docstatusstat ,          e.Docstatdesc ,          a.Dswg ,          a.VisaExp ,          a.VisaType ,          a.VisaStatus  FROM users a          LEFT JOIN users AS b ON a.employerid = b.userid          INNER JOIN attscont AS c ON a.userid = c.userid          INNER JOIN users AS d ON c.attyid = d.userid          LEFT JOIN Documentstatus AS e ON e.documentstatus = a.Docstatusstat  WHERE a.usertype = 'contact'            AND a.firmid = " + Replace(ContactsFiltered__MMColParam, "'", "''") + "            AND d.userid = " + Replace(ContactsFiltered__MMColParam2, "'", "''") + "            AND a.Archivedcont LIKE '" + Replace(ContactsFiltered__MMColParam3, "'", "''") + "'            AND a.Contactstatus LIKE '" + Replace(ContactsFiltered__MMColParam4, "'", "''") + "'            AND ISNULL(a.FirstNm, '') LIKE '" + Replace(ContactsFiltered__MMColParam5, "'", "''") + "%'            AND ISNULL(a.LastNm, '') LIKE '" + Replace(ContactsFiltered__MMColParam6, "'", "''") + "%'            AND ISNULL(a.SSN, '') LIKE '" + Replace(ContactsFiltered__MMColParam7, "'", "''") + "%'            AND ISNULL(a.email, '') LIKE '" + Replace(ContactsFiltered__MMColParam8, "'", "''") + "%'            AND ISNULL(a.Nationality, '') LIKE '" + Replace(ContactsFiltered__MMColParam9, "'", "''") + "%'            AND ISNULL(a.Nistatus, '') LIKE '" + Replace(ContactsFiltered__MMColParam10, "'", "''") + "%'  AND a.PobCountry IN ('" + ContactsFiltered__MMColParam11 + "' )  ORDER BY " + Replace(ContactsFiltered__MMColParam500, "'", "''") + ""
ContactsFiltered.CursorType = 0
ContactsFiltered.CursorLocation = 2
ContactsFiltered.LockType = 1
ContactsFiltered.Open()

ContactsFiltered_numRows = 0
%>
0
 
LVL 12

Assisted Solution

by:Tony303
Tony303 earned 251 total points
ID: 39719095
How about what I suggested previously...


"can you hardcode a valid country and a valid userid just to test that there will be records returned.

If it works for 1 country then try with 2 valid countries."
0
 
LVL 44

Assisted Solution

by:Rainer Jeschor
Rainer Jeschor earned 249 total points
ID: 39719235
Additional to Tony303`s comment, can you just do a response write on the generated SELECT query?
Like
Response.Write(ContactsFiltered.Source)

Open in new window

right before you execute the query?

What happens if you copy this statement to a SQL Server Management Studio query and execute it there?
0
 

Author Closing Comment

by:amucinobluedot
ID: 39729796
Thx for the patience
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now