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

Please explain SELECT FROM WHERE IN()

Here is what I have.

I have an ASP form that calls info from a SQL db. This then is posted to a web page in the form of a list with images, check boxes and IDs.

On that page, I select, oh let's say 3 of the 4 checkboxes. I then click the submit button and I want those 3 of 4 items displayed onto the next web page.

Here is the results before I need to post it to the 3rd page.

SELECT * FROM PhotoDB WHERE PhotoID IN('234,235,236')

I need to be able to loop through each of the three ID's and pull their info from the DB.
Here is my idea for the 3rd page:
_________________________________________
<%While Not RS.EOF%>
<tr>
<td width="200"><%=(RS("PDesc"))%></td>
<td width="200">
<img border="0" src="<%=(RS("Filename"))%>"></td>
<td width="200"><input type="checkbox" name="order" value="ON"></td>
<td width="200"><%=(RS("PhotoID"))%></td>
<td width="200">NUM</td>
</tr>
<%RS.MoveNext
wEnd%>

I want to try here since it is mostly a SQL question, then I will try the ASP forums.
0
cjinsocal581
Asked:
cjinsocal581
  • 6
  • 5
1 Solution
 
rafranciscoCommented:
SELECT * FROM PhotoDB WHERE PhotoID IN('234,235,236')

is similar to

SELECT * FROM PhotoDB WHERE PhotoID = '234' OR PhotoID = '235' OR PhotoID = '236'
0
 
cjinsocal581Author Commented:
Any idea on how to display the information based on the selected ID's using the following code?

<%While Not RS.EOF%>
<tr>
<td width="200"><%=(RS("PDesc"))%></td>
<td width="200">
<img border="0" src="<%=(RS("Filename"))%>"></td>
<td width="200"><input type="checkbox" name="order" value="ON"></td>
<td width="200"><%=(RS("PhotoID"))%></td>
<td width="200">NUM</td>
</tr>
<%RS.MoveNext
wEnd%>
0
 
cjinsocal581Author Commented:
What would be the equiv to AND instead of OR?
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
rafranciscoCommented:
Your code above should be able to display the information for the 3 IDs you've selected.
0
 
cjinsocal581Author Commented:
For some reason, it does not.

Am I missing something?
0
 
rafranciscoCommented:
I think I got it.

Change your statement from this:

SELECT * FROM PhotoDB WHERE PhotoID IN('234,235,236')

to

SELECT * FROM PhotoDB WHERE PhotoID IN(234,235,236)

Remove the single-quotes at the start and end of the IN clause.
0
 
cjinsocal581Author Commented:
Here is what I did:

Dim strSQL
dim str, sql

str = Replace(Request.Form("pics"), " ", "")
strSQL = "SELECT * FROM PhotoDB WHERE PhotoID IN('" & str & "')" 'This is the table it gets the data from and saves it to.
RS.open strSQL, objConn
response.write("SELECT * FROM PhotoDB WHERE PhotoID IN('" & str & "')")

It posted this: SELECT * FROM PhotoDB WHERE PhotoID IN('234,235,236')
when I selected multiple checkboxes.

When I select only one, it posted this: SELECT * FROM PhotoDB WHERE PhotoID IN('234') and displayed the image.

Any ideas?
0
 
rafranciscoCommented:
Try this one:

strSQL = "SELECT * FROM PhotoDB WHERE PhotoID IN(" & str & ")" 'This is the table it gets the data from and saves it to.

I simply removed the single-quotes in the IN statement.

When you select just one, it will work because it is comparing against '234' string.  But with more than one, it is comparing the PhotoID to the string '234,235' together and not separately.

So by removing the single-quotes, it will now work.
0
 
cjinsocal581Author Commented:
I got this error: Data type mismatch in criteria expression when I removed the ' from the sql statement.
0
 
rafranciscoCommented:
What's the data type of your PhotoID?  Is it numeric or varchar?

If it is varchar, you can try this:

str = Replace(Request.Form("pics"), " ", "")
str = Replace(str, ",", "','")
strSQL = "SELECT * FROM PhotoDB WHERE PhotoID IN('" & str & "')" 'This is the table it gets the data from and saves it to.

I put back the single-quotes in the strSQL and added another step.  The extra step that I put there simply replaces commas with single-quote/comma/single-quote.  So if the value of str is 234,235,236, it will now become 234','235','236.  Then the opening and closing singe-quotes will come from the strSQL.
0
 
cjinsocal581Author Commented:
That did it.

It was varchar.

Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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