Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 21st, 2007, 04:16 PM
Authorized User
 
Join Date: Jan 2007
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default SELECT * BETWEEN

 Hello,
I am trying to select a range of values from a table called Codes. The values are passed from two input boxes on a form.
I am sooooooooooooooo close but sooooooooooooooooooooooo far away. I know the problems is in the request.form() part of my code.
If I insert numbers in place of the request.form() it returns the correct data. Code is defined as a number in the database (Access 2000)

This is the formula for my logic
SELECT "column_name" FROM "table_name"WHERE "column_name" BETWEEN 'value1' AND 'value2'
************************************************** ***********

qry = "SELECT * FROM LA_Codes WHERE Code BETWEEN 10021 AND 10071 "
This works but I need the user to enter the data in a form to search for.
************************************************** *************

line 21
"SELECT * FROM LA_Codes "WHERE Code BETWEEN request.form("searchCode2") AND request.form("searchCode3")

Generates this error:
Microsoft VBScript compilation error '800a0401'
Expected end of statement
/remmdconnect.asp, line 21
qry = "SELECT * FROM LA_Codes "WHERE Code BETWEEN request.form("searchCode2") AND request.form("searchCode3")
-------------------------------^
************************************************** *****
dim code1=request.form("searchCode2")
dim code2=request.form("searchCode3")
line 21
qry = "SELECT * FROM LA_Codes WHERE Code BETWEEN code1 AND code2 "
line 22
Set oRS = oConn.Execute(qry)

Generates this error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.
/remmdconnect.asp, line 22
************************************************** ******************
line21
qry = "SELECT * FROM LA_Codes WHERE Code BETWEEN 'code1' AND 'code2' "
line 22
Set oRS = oConn.Execute(qry)
Generates this error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
/remmdconnect.asp, line 22
************************************************** *********
"SELECT * FROM LA_Codes WHERE Code BETWEEN & request.form('searchCode2') AND request.form('searchCode3') "
Generates this error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Code BETWEEN & request.form('searchCode2') AND request.form('searchCode3')'.
/remmdconnect.asp, line 21
************************************************** ********

qry = "SELECT * FROM LA_Codes WHERE Code BETWEEN '" & code1 & "' AND '" & code2 & "'"
 Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

/remmdconnect.asp, line 21
Can someone point me in the right direction.
thank you
dink
 
Old January 22nd, 2007, 08:46 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

hi there...

Quote:
quote:
qry = "SELECT * FROM LA_Codes WHERE Code BETWEEN '" & code1 & "' AND '" & code2 & "'"
You were so cloooose with this one!!! just take out the ' from your line...
something like:
Code:
qry = "SELECT * FROM LA_Codes WHERE Code BETWEEN " & code1 & " AND " & code2

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
 
Old January 22nd, 2007, 03:58 PM
Authorized User
 
Join Date: Jan 2007
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks Gonzalo! It is so frustrating to be so close and yet so far away.
dink





Similar Threads
Thread Thread Starter Forum Replies Last Post
Select row in GridView WITHOUT using Select button rao965 ASP.NET 2.0 Professional 1 February 15th, 2008 10:44 AM
Select from another select statement to a repeater simsen ASP.NET 2.0 Professional 0 May 2nd, 2007 04:34 PM
Fill select box and select recordset value markd Classic ASP Databases 1 February 20th, 2006 06:41 PM
select="node1", select="node2"... Baldo XSLT 7 March 12th, 2004 10:38 AM
Select Within A Select Problem vinyl-junkie Classic ASP Databases 6 June 7th, 2003 04:31 PM





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