How to pull all records from a NUMBER FIELD IN ACCESS - pls help

Forum|Forum|18 years ago
February 7, 2008
9 replies
486 views

hii there

I would like to know how can someone pull a record using a % in ASP
dreamweaver

teh form looks like this when the user searches :

<option value="#">Select Here</option>
<option value="%">Any</option>
<option value="Studio">Studio</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
<option value="6">6</option>
<option value="7">7</option>

I have a field in the MS access DB called bedroom with number as Data Type
!!

so someone selects 2 bedroom it will list all the records with 2 bedrooms

But what i want is if someone selects " ANY" it must show all the records
with all the records against the other criteria, as there is TWO to THREE
and STATEMENTS in the SQL

please can someone tell me how..to do it in DREAMEAVER Cs3 ?

thanks

Server side applications

This topic is closed to new replies. Start a new post to keep the conversation going.

N

Newsgroup_UserAuthor

Inspiring

Techy wrote:
> thanks my friend..
>
> its really weird how the exact query works fine in DW i.e. when i double
> click on Recordset1 and run the SQL test i get teh desired result its only
> when i run it on IE explorer it throws errors abotu string and stuff.
>
> isnt there any way using JS i can achieve this ?
>
> thanks
>
>

The problem is your SQL is expecting a number, but when you select %
thats a string function, so you get a datatype error.

Your SQL should needs to be dynamic so that when it sees the string
"Any" passed to it, it won't do the WHERE part of the SQL statement.

I didn't get time to look at a stored procedure for you this weekend,
but will try something this week.

I don't know JS, sorry!

Steve

M

MikeL7

Inspiring

One way i have dealt with a similar issue is to make a dynamic SQL statement, so based on the form variable a different query is performed. In php it is simple, a little more code in ASP, and if you can hand code your recordsets its not as bad.
So if the name of your select field is 'rooms_needed' and the form is set to post, replace Your table names and column names and you can use your DW connection string variable name. Use you end up with something like this:

N

Newsgroup_UserAuthor

Inspiring

thanks my friend..

its really weird how the exact query works fine in DW i.e. when i double
click on Recordset1 and run the SQL test i get teh desired result its only
when i run it on IE explorer it throws errors abotu string and stuff.

isnt there any way using JS i can achieve this ?

thanks

N

Newsgroup_UserAuthor

Inspiring

Techy wrote:
> hi dooza,
>
> thanks for your reply..i dont mind using SQL but you have to guide me if
> you dont mind.
>
> And ACCESS m using just for testing...actually the client wants ASP and
> SQL but I have never used stored procedures in DW CS3 or earlier with
> ASP so please guide me
>
> I am so keen for this solution
>
> thanks

I will put my thinking cap on and see if I can create the SQL to create
a stored procedure solution for you. Dynamic SQL is not the best way to
do it, as you will be prone to SQL Injection.

Steve

N

Newsgroup_UserAuthor

Inspiring

This is a multi-part message in MIME format.

------=_NextPart_000_002D_01C86A65.515894A0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

hi dooza,

thanks for your reply..i dont mind using SQL but you have to guide me if =
you dont mind.

And ACCESS m using just for testing...actually the client wants ASP and =
SQL but I have never used stored procedures in DW CS3 or earlier with =
ASP so please guide me

I am so keen for this solution

thanks
------=_NextPart_000_002D_01C86A65.515894A0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.6000.16544" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD><FONT face=3DArial><FONT size=3D2>
<BODY>
<DIV>hi dooza,</DIV>
<DIV> </DIV>
<DIV>thanks for your reply..i dont mind using SQL but you have to guide =
me if=20
you dont mind.</DIV>
<DIV> </DIV>
<DIV>And ACCESS m using just for testing...actually the client wants ASP =
and SQL=20
but I have never used stored procedures in DW CS3 or earlier with ASP so =
please=20
guide me</DIV>
<DIV> </DIV>
<DIV>I am so keen for this solution</DIV>
<DIV> </DIV>
<DIV>thanks</DIV></BODY></HTML></FONT></FONT>

------=_NextPart_000_002D_01C86A65.515894A0--

N

Newsgroup_UserAuthor

Inspiring

Techy wrote:
> hii there
>
> I would like to know how can someone pull a record using a % in ASP
> dreamweaver
>
> teh form looks like this when the user searches :
>
> <option value="#">Select Here</option>
> <option value="%">Any</option>
> <option value="Studio">Studio</option>
> <option value="1">1</option>
> <option value="2">2</option>
> <option value="3">3</option>
> <option value="4">4</option>
> <option value="5">5</option>
> <option value="6">6</option>
> <option value="7">7</option>
>
> I have a field in the MS access DB called bedroom with number as Data Type
> !!
>
> so someone selects 2 bedroom it will list all the records with 2 bedrooms
>
> But what i want is if someone selects " ANY" it must show all the records
> with all the records against the other criteria, as there is TWO to THREE
> and STATEMENTS in the SQL
>
> please can someone tell me how..to do it in DREAMEAVER Cs3 ?

I don't think you can, I could be proved wrong, but this requires the
use of dynamic sql. Actually, maybe the use of CASE will work, can
Access use CASE?

CASE is very powerful in SQL as it allows you to have use conditional
logic. I know I could do this in MS SQL with a Stored Procedure, but I
don't know about Access. Maybe someone else can join in with more
insight into Access?

Steve

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.