[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

the simplest way to write tis sql statement

the current solution is juz not good enough..

i'm seeking better solution for handle the following situation..  anyone can give me suggestion... how to make it better...

Dim name as string = Request.QueryString("name")
Dim age as string = Request.QueryString("age")
Dim tel1 as string = Request.QueryString("tel1")


      Str = "SELECT name FROM customer WHERE "

      If NOT name = "" then
         Str = Str + "postcode = '"+ postcode +"'"
      End If          
     
      If NOT age = "" then
         Str = Str + "AND age = '"+ age +"'"          
      End If
     
      If NOT tel1 = "" then
         Str = Str + "AND tel1 = '"+ tel1 +"'"          
      End If
0
kengkit
Asked:
kengkit
  • 6
  • 2
1 Solution
 
lojk.Net and Infrastructure ConsultantCommented:


Dim name as string = Request.QueryString("name")
Dim age as string = Request.QueryString("age")
Dim tel1 as string = Request.QueryString("tel1")

dim tWhere as new collection
dim twherestring as string=""

tWhere.add (iif(trim(postcode)="","","Postcode='" & postcode & "'"))
tWhere.add(iif(trim(age)="","","age='" & age & "'"))
tWhere.add(iif(trim(tel1)="","","tel1='" & tel1 & "'"))

for t as integer =0 to twhere.count-1
if trim(twhere(t))<>"" then
twherestring+=twhere (t) + " "
if t<twhere.count-1 and trim(twhere(t+1))<>"" then
twherestring+=" and "
end if
end if
next

twherestring = iif(trim(twherestring)="","", " where ")  + twherestring

dim Str s string  = "SELECT name FROM customer " & twherestring


that what you had in mind? pretty flexible and means you can add as many params as you like (or none at all)

hth

0
 
lojk.Net and Infrastructure ConsultantCommented:
sorry should say

for t as integer =0 to twhere.count-1
if trim(twhere(t))<>"" then
twherestring+=twhere (t) + " "
if t<twhere.count-1 and trim(twherestring)<> "" and trim(twhere(t+1))<>"" then
twherestring+=" and "
end if
end if
next

0
 
anyoneisCommented:
Some people frown on building queries on the fly. Possibly it is becuase they are purists. Or perhaps it is becuase they have been burned in the past by SQL Injection attacks and they take security seriously. In any case, my advice is DON'T DO THAT!

You should, at a minimum, use parameters to pass your arguments to the data provider. Ideally, you would use a stored procedure, e.g.;

[Code]
CREATE PROCEDURE procGetNames
  @postcode VARCHAR(10) = NULL,
  @age INTEGER = NULL,
  @tel1 VARCHAR(12) = NULL
AS

SELECT name FROM customer
WHERE
      (@postcode IS NULL OR @postcode = ISNULL(postcode, ''))
AND       (@age IS NULL OR @age = ISNULL(age, 0))
AND       (@tel1 IS NULL OR @tel1 = ISNULL(tel1, ''))
GO

[/Code]

However, if you don't want to do that, just use the select statement by itself and use parameters to pass your three arguments.

David

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
lojk.Net and Infrastructure ConsultantCommented:
Fair point by david I suppose, however SP can be a bit 'round the houses' and also (quite rightly) ties you into a slighlty more planned approach. his solution is more purist/correct, mines quick, dirty and easier to manage (in the short term) and possibly the best or only way if you wanted your code to work for sql AND access..
the orignal question only specified the language, not the underlying db format...


0
 
lojk.Net and Infrastructure ConsultantCommented:
oh yeah you could use davids code and pass the parameters even into access however I still like my example more, its worked for me in hundreds of apps over many years but the choice is yours

(insert standard 'theres always more than one way to do things line' here ;-))
0
 
anyoneisCommented:
lojk: >>(insert standard 'theres always more than one way to do things...<<

"Nice thing about standards is that everybody has one!" :-)

If he goes the "quick, dirty and easier to manage " route, shouldn't he first run the arguments through some sort of filter, to ensure that the resulting sql isn't dangerous and is functional? For instance, if you were passing a name field, and "O'Dell" was entered, wouldn't you need to do something special before building the select statement?

(not a web programmer) David






0
 
lojk.Net and Infrastructure ConsultantCommented:
<<General Agreement Comments>>

50 points?   Enough Chatter.... Kengkit, Allocate the points and lets move on.
0
 
lojk.Net and Infrastructure ConsultantCommented:
Hello TheLearnedOne

Im quite happy to yield the points to anyoneis, his answer is generally 'more correct' and its not worth splitting this few points...
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now