?
Solved

Please explain SELECT FROM WHERE IN()

Posted on 2005-04-11
11
Medium Priority
?
306 Views
Last Modified: 2009-07-29
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
Comment
Question by:cjinsocal581
  • 6
  • 5
11 Comments
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13755345
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
 
LVL 6

Author Comment

by:cjinsocal581
ID: 13755360
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
 
LVL 6

Author Comment

by:cjinsocal581
ID: 13755521
What would be the equiv to AND instead of OR?
0
Independent Software Vendors: 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!

 
LVL 28

Expert Comment

by:rafrancisco
ID: 13755553
Your code above should be able to display the information for the 3 IDs you've selected.
0
 
LVL 6

Author Comment

by:cjinsocal581
ID: 13755575
For some reason, it does not.

Am I missing something?
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13755593
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
 
LVL 6

Author Comment

by:cjinsocal581
ID: 13755649
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
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13755683
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
 
LVL 6

Author Comment

by:cjinsocal581
ID: 13755721
I got this error: Data type mismatch in criteria expression when I removed the ' from the sql statement.
0
 
LVL 28

Accepted Solution

by:
rafrancisco earned 2000 total points
ID: 13756062
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
 
LVL 6

Author Comment

by:cjinsocal581
ID: 13756093
That did it.

It was varchar.

Thanks.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

850 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