Solved

the simplest way to write tis sql statement

Posted on 2006-06-10
11
208 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

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…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

776 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