Solved

the simplest way to write tis sql statement

Posted on 2006-06-10
11
200 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

863 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now