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

Report · Feb 06, 2008

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

Report · Feb 07, 2008

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

Report · Feb 08, 2008

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--

Report · Feb 08, 2008

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

Report · Feb 09, 2008

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

Report · Feb 11, 2008

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

Report · Feb 11, 2008

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:

Report · Feb 12, 2008

MikeL7 wrote:
> 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:
>
>
> <%
> Dim queryText, requestStr
> requestStr = If request.form("rooms_needed")
> If requestStr = "Any" Then
> queryText = "SELECT * FROM YourTable"
> End If
>
> If requestStr = "Studio" Then
> queryText = "SELECT * FROM YourTable WHERE roomType = 'Studio'"
> End If
>
> If requestStr = "Any" Then
> queryText = "SELECT * FROM YourTable"
> End If
>
> If isnumeric(requestStr ) Then
> queryText = "SELECT * FROM YourTable WHERE roomType = requestStr"
> End If
>
> Dim objComm, rs_rooms
> Set objComm = Server.CreateObject("ADODB.Command")
> objComm.ActiveConnection = YOUR_CONNECTION_STRING
> obj.Comm.CommandText = queryText
> objComm.Prepared = true
> SET rs_rooms = objComm.Execute
>
> %>
>
> use this to display result
> <% response.write (rs_ro0ms.Fields.Item("roomType").Value) %>
>

Thats exactly what I was talking about, thank you for giving an ASP
example, my only concern is SQL injection and Dreamweaver not
recognizing the recordset.

We could use the same dynamic sql logic in a stored procedure that would
give the same results, but also work in Dreamweaver...the only thing is
that too might fall foul of sql injection due to its dynamic nature, so
validation would be needed before submitting the data to the stored
procedure.

Steve

Report · Feb 12, 2008

thanks guys i really appreciate your help, but now that I have done this I
would like to know more abotu SQL injection

can anyone of you explain me this Injection thing and look at my code below
and see if injection is possible to this code belwo

i got it done this is what i am using :

<%
Dim Recordset2
Dim Recordset2_cmd
Dim Recordset2_numRows

Set Recordset2_cmd = Server.CreateObject ("ADODB.Command")
Recordset2_cmd.ActiveConnection = MM_conCH_STRING

StrSQL = "SELECT * FROM Chome WHERE CH_PRICE between ? and ? "

if Request.Form("prop_bed") <> "" then
StrSQL = StrSQL & " AND CH_bedroom = ? "
end if

if Request.Form("prop_type") <> "" then
StrSQL = StrSQL & " AND CH_type = ? "
end if
Recordset2_cmd.CommandText = StrSQL

Recordset2_cmd.Prepared = true
Recordset2_cmd.Parameters.Append Recordset2_cmd.CreateParameter("param1", 5,
1, -1, Recordset2__SQLprop_min) ' adDouble
Recordset2_cmd.Parameters.Append Recordset2_cmd.CreateParameter("param2", 5,
1, -1, Recordset2__SQLprop_max) ' adDouble

if Request.Form("prop_bed") <> "" then
Recordset2_cmd.Parameters.Append Recordset2_cmd.CreateParameter("param3",
5, 1, -1, Recordset2__SQLprop_bed) ' adDouble
end if
if Request.Form("prop_type") <> "" then
Recordset2_cmd.Parameters.Append Recordset2_cmd.CreateParameter("param4",
200, 1, 255, Recordset2__SQLprop_type) ' adVarChar
end if

Set Recordset2 = Recordset2_cmd.Execute
Recordset2_numRows = 0
%>

i have removed any from the drop down and put "" this instead and works all
great !

Report · Feb 12, 2008

Techy wrote:
> thanks guys i really appreciate your help, but now that I have done this I
> would like to know more abotu SQL injection
>
> can anyone of you explain me this Injection thing and look at my code below
> and see if injection is possible to this code belwo

I can't say 100% but your using a paramatised command, so you might be
safe.

Here is a good resource: http://en.wikipedia.org/wiki/Sql_injection

One peace of advice I can give you is this, if your in control of your
SQL server, make sure that the account that runs the sql service is not
the default one, create a standard windows account dedicated to it.
Also, create an sql user specifically for your front end pages, and only
assign it privileges to the tables/views/stored procedures that are used
on the front end. Have a separate sql user for the backend that has
delete/update/create etc privileges. I learnt this the hard way several
years ago, the pages were using an sql account that had full admin
rights, and I got hacked via sql injection, and they had full access to
my databases AND file system. They even created windows accounts and
logged in via terminal services. The only thing I could do was take the
server off line, reinstall from scratch and recode ALL my pages that
used the database.

Steve