• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • Last Modified:

Query not returning results

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
Aleks
Asked:
Aleks
  • 5
  • 3
  • 3
6 Solutions
 
Tony303Commented:
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
 
AleksAuthor Commented:
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
 
Tony303Commented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Rainer JeschorCommented:
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
 
AleksAuthor Commented:
Thanks .. .Ill try now
0
 
AleksAuthor Commented:
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
 
Rainer JeschorCommented:
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
 
AleksAuthor Commented:
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
 
Tony303Commented:
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
 
Rainer JeschorCommented:
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
 
AleksAuthor Commented:
Thx for the patience
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now