Solved

the simplest way to write tis sql statement

Posted on 2006-06-10
11
217 Views
Last Modified: 2010-04-23
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
Comment
Question by:kengkit
  • 6
  • 2
11 Comments
 
LVL 9

Expert Comment

by:lojk
ID: 16878407


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

Expert Comment

by:lojk
ID: 16878426
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
 
LVL 11

Accepted Solution

by:
anyoneis earned 50 total points
ID: 16878705
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 9

Expert Comment

by:lojk
ID: 16878764
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
 
LVL 9

Expert Comment

by:lojk
ID: 16878792
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
 
LVL 11

Expert Comment

by:anyoneis
ID: 16878930
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
 
LVL 9

Expert Comment

by:lojk
ID: 16883978
<<General Agreement Comments>>

50 points?   Enough Chatter.... Kengkit, Allocate the points and lets move on.
0
 
LVL 9

Expert Comment

by:lojk
ID: 17080117
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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VB.Net - KeyPress Event 4 38
Error handling in asp.net site 5 34
VB.net Progress Bar - Maximum Value too large 2 15
Error message when using PrintSocket.Send 7 18
This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question