Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

the simplest way to write tis sql statement

Posted on 2006-06-10
11
Medium Priority
?
238 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 200 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

609 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