Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Web Programming > Adobe Web Programming > Dreamweaver (all versions)
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Dreamweaver (all versions) section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #11 (permalink)  
Old June 28th, 2005, 04:12 AM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Although it can be frustrating at times, digging is often the best way to find things out.... ;)

Anyway, I think you can use the IN keyword to filter multiple boxes, e.g.:

bla bla WHERE Box IN (1, 2, 5)

That would get you three records, for box 1, 2 and 5 (if they exist in the database of course)

Does this help?

Imar
Reply With Quote
  #12 (permalink)  
Old June 28th, 2005, 08:44 AM
Authorized User
 
Join Date: May 2005
Location: , WI, .
Posts: 57
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Imar,

This does help - I just have to get the statement to work - as before - if I slect onl;y one value it works - if I select multiples - it returns it in the sql string - but fails to retrieve any records - I think what I need is for it to place an OR after each value - but of course I don't know - I am playing with it now.

Here is the line I am working on: sqlstr= sqlstr + " and Tower IN('" + rssample__vartower + "') "

Any ideas on how to make it work would be appreciated.

Thanks,

Chris

Reply With Quote
  #13 (permalink)  
Old June 28th, 2005, 09:56 AM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

If Tower is a a number in the database, you need to drop the quotes:

Tower IN (1, 2, 3)

not

Tower IN ('1, 2, 3')

Imar
Reply With Quote
  #14 (permalink)  
Old June 28th, 2005, 09:58 AM
Authorized User
 
Join Date: May 2005
Location: , WI, .
Posts: 57
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The tower is setup as a text field - if I try to drop the quotes I get a Data type mismatch in criteria expression.


I worked with this in access - it will work if the string would be:

SQL is SELECT * FROM tblsamples WHERE Tower=('01', '02', '03')

I guess I don't know how to get the individual 's around each value?

Chris
Reply With Quote
  #15 (permalink)  
Old June 28th, 2005, 10:32 AM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Right, in that case, you need to separate each item with a ' like this:

WHERE Tower IN ('1', '2', '3')

This is not as easy to accomplish though.

Two ways to fix it:

1. Change the Tower data type to number in the database. If it's a number, call it a number and not text.

2. In the ASP page, don't use IN (Request.Form) directly, but set up the For Each loop you suggested earlier.

The way I used the IN clause is a quick way to do this. Request.Form("Tower") returns a comma separated list, and the IN clause expects the same. However, Tower being text makes things a little more difficult.

Cheers,

Imar
Reply With Quote
  #16 (permalink)  
Old June 28th, 2005, 01:29 PM
Authorized User
 
Join Date: May 2005
Location: , WI, .
Posts: 57
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Imar - I feel busted! I have been feeling like I am posting too much ASP stuff to the dreamweaver forum - so I posted in the asp form section. I guess I am unsure as to where to post (it is dreamweaver but it is also asp)

Anyway - I am making progress on the code - I had tried earlier today to change the databse field to numeric - total disaster - broke every other page I had - restored back to my old code - should be ok now - I think I will use option 2 above.

Chris

Reply With Quote
  #17 (permalink)  
Old June 28th, 2005, 01:57 PM
Authorized User
 
Join Date: May 2005
Location: , WI, .
Posts: 57
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Ok Imar,

I am confused again - this worked very well in a different page - but now the best I can get is this:

SQL is SELECT * FROM tblsamples WHERE Date_Stored BETWEEN #01/01/2005# AND #06/28/2005# and Tower='01, 02, 03' and Tower='01, 02, 03' and Tower='01, 02, 03'

the code looks like thus:

sqlstr = "SELECT * FROM tblsamples WHERE Date_Stored BETWEEN #" + rssample__varstorefrom + "# AND #" + rssample__varstoreto + "#"

For Each tower In Request.Form("tower")
rssample__vartower = "*"
If (Request.Form("tower") <> "") Then
rssample__vartower = Request.Form("tower")
If rssample__vartower <> "*" then
sqlstr= sqlstr + " and Tower='" + rssample__vartower + "' "
End if
End If
Next

I will keep at it - but if you see any glaring errors could you let me know.

Chris

Reply With Quote
  #18 (permalink)  
Old June 28th, 2005, 02:04 PM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

No need to feel busted; I was just wondering if this was a different issue.

You're right in that it's probably closer to ASP than to Dreamweaver. However, the Dreamweaver ASP syntax might look a little odd for none DW users, so in the end, both forums might work for you.

Changing a database structure later in the design phase always gives you problems. That's why it's so important to come up with a good design in an early stage. When you changed the type, you broke all pages that assumed the column was text, using single quotes instead of nothing.... Oh well.

You could deploy a little trick in the display of the list items. It's a bit dirty, hard to understand if someone else must change your code later, and it might break other stuff. However, if it's only one page we're talking about here, you might be able to get away with this:

<option value="'1'">1</option>
<option value="'2'">2</option>
<option value="'3'">3</option>

When you get this from Request.Form, it will hold something like:

'1', '2', '3'

which would work in an IN clause:

WHERE Tower IN ('1', '2', '3')

Not pretty though, so it's probably better to use a For loop....

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Reply With Quote
  #19 (permalink)  
Old June 28th, 2005, 02:11 PM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

I think you're missing some basics about SQL. Consider this:

Tower ='01, 02, 03'

What does this ask the database? It asks for records that have a Tower column of exactly 01, 02, 03, not for a Tower that has 01 OR 02 OR 03.

In SQL there are a few ways to match records:

=
The = operator does exactly what you expect. It compares a value against a column and returns the record when there is an exact match

LIKE
Compares parts of columns: e.g. LIKE 'some%' would match something, someone etc

IN
IN is a quick way to set up a list filter. E.g. Tower IN (1, 2, 3) returns records that have a Tower of either 1, 2 or 3. Not all three of them, not a mix of them, but only one of those values.

There are more (<>, <, > BETWEEN etc etc) but this should get you in the right direction.

If you have the time and the budget, get your self a copy of Sql Server 2000 Programming. It's targeting Sql Server but you'll find invaluable information about T-Sql in general: http://www.wrox.com/WileyCDA/WroxTit...764543792.html

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Reply With Quote
  #20 (permalink)  
Old June 28th, 2005, 03:36 PM
Authorized User
 
Join Date: May 2005
Location: , WI, .
Posts: 57
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the reference - I definitely need a book on using SQL - though I am trying to decide where to go - I am currently using access and asp - was trying to decide if I should make the leap to ASP.NET or PHP (I will probably end up using MySQL (because I don't know anyone using MS SQL)) Tough decision - but I figure I can go either way seeing as ASP.NET is very different from ASP 3.0.

I have also solved one of my problems - though it has created a few more for me!!

Here is the solution;

sqlstr = "SELECT * FROM tblsamples WHERE Date_Stored BETWEEN #" + rssample__varstorefrom + "# AND #" + rssample__varstoreto + "#"

rssample__vartower = "*"
If (Request.Form("tower") <> "") Then
rssample__vartower = Request.Form("tower")
If rssample__vartower <> "*" then
dim temp
temp = replace(rssample__vartower, ", ", "','")
sqlstr= sqlstr + " and Tower in ('" + temp + "') "
End if
End If

Now I have broken my ability to have a multiple page repeat region. It is nearly impossible to fix in dreamweaver now - it doesn't see rssample - so the code I had in there from earlier is what I have to tweak. Wish me luck - that code is really ugly!

Chris

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
query result into xls97 worksheet len Access VBA 1 September 26th, 2005 08:51 PM
Show result from query akibaMaila VB.NET 2002/2003 Basics 2 January 18th, 2005 12:43 PM
Unexpected result from the query tsimsha Classic ASP Databases 1 August 23rd, 2004 03:50 AM
Query Result mateenmohd SQL Server 2000 0 November 1st, 2003 03:53 AM
Archiving query result Ned SQL Server 2000 5 October 8th, 2003 03:34 PM



All times are GMT -4. The time now is 05:22 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.