Writing a query ...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bcap - 25 Jul 2009 14:37 GMT

Hi,

I am looking to write a piece of code better.

Lets say I have a table called CustInfo with CustID, FirstName,
LastName, and Subscription Level for simplicity.

How can I create a query that I would be to:

1) Get all records no matter what subscription level they are in
2) Choose only records that have a selected status types
3) Declare that there are no records

Currently, I am using two queries (one that just selects all, another
that needs a parameter) and making a decision based on if there is a
status type. I'd like to learn a more efficent way to do this. Any
advise would be appreciated!

Reply to this Message

Evertjan. - 25 Jul 2009 15:23 GMT

bcap wrote on 25 jul 2009 in microsoft.public.inetserver.asp.general:

> I am looking to write a piece of code better.

Better than what?

Show us your code, only the essence please.

> Lets say I have a table called CustInfo with CustID, FirstName,
> LastName, and Subscription Level for simplicity.

No, let us hear what engine you are using.

> How can I create a query that I would be to:
>
> 1) Get all records no matter what subscription level they are in
> 2) Choose only records that have a selected status types
> 3) Declare that there are no records

Is this a school assignment?
Did you read some of the many SQL tutorials?

> Currently, I am using two queries (one that just selects all, another
> that needs a parameter) and making a decision based on if there is a
> status type.

What is a "status type"?

> I'd like to learn a more efficent way to do this. Any
> advise would be appreciated!

Learning is not letting someone else do the task!

Signature

Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

Reply to this Message

bcap - 25 Jul 2009 15:40 GMT

Thank you for your reply Evertjan!

I am using SQL Server with ASP Classic. This is not a school project
(though I want to take some classes), I am a IT Project Manager trying
to learn code and modify existing projects. Here is what the query
currently looks like, it gets the job done. I want to learn and
understand if this process can be done better.

if request.querystring("StatusDisplay")="" then

strSql = " SELECT CustID, FName, LName, SubLevel FROM CustInfo"

Else

strSql = " SELECT CustID, FName, LName, SubLevel FROM CustInfo WHERE
SubLevel=" & request.querystring("sublevel")

End If

Reply to this Message

Bob Barrows - 25 Jul 2009 16:01 GMT

> Thank you for your reply Evertjan!
>
[quoted text clipped - 14 lines]
>
> End If

Aside from using dynamic sql, there is nothing wrong with this. Other ways
of doing it will decrease the amount of code, but will lead to less
efficient queries. There are some good articles here:
http://www.sommarskog.se/ - read the one about dynamic search conditions
(and then read the one about dynamic sql)

Here is my canned reply about dynamic sql:

Further points to consider:
Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See these links for a better, more secure way to execute your queries by
using
parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36
562fee7804e

Using Command object to parameterize CommandText:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36
562fee7804e
Select statement:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/b3d32
2b882a604bd

Personally, I prefer using stored procedures,
http://groups.google.com/group/microsoft.public.inetserver.asp.general/msg/5d3c9
d4409dc1701?hl=en&

Signature

Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Reply to this Message

Evertjan. - 25 Jul 2009 17:23 GMT

Bob Barrows wrote on 25 jul 2009 in
microsoft.public.inetserver.asp.general:

>> Thank you for your reply Evertjan!

A bit strange, anwering me by name and not supplying one yourself,
"bcap" not sounding like a usable [nick]name.

>> I am using SQL Server with ASP Classic. This is not a school project
>> (though I want to take some classes), I am a IT Project Manager
[quoted text clipped - 18 lines]
> http://www.sommarskog.se/ - read the one about dynamic search
> conditions (and then read the one about dynamic sql)

> Further points to consider:
> Your use of dynamic sql is leaving you vulnerable to hackers using sql
> injection:

I am not that afraid of sql-injection as long as one is constantly on
guard.

If your sublevel is an integer, this:

strSql = "SELECT CustID, FName, LName, SubLevel FROM CustInfo "_
"WHERE SubLevel = " & CInt(request.querystring("sublevel"))

will prevent sql-injection adequately.

When you have a series of levels, you could try:

"WHERE SubLevel > " & CInt(request.querystring("sublevel"))

I agree with Bob that the danger is imminent when you become sloppy.

===================

Always first visualize your SQL result:

<%
strSql = "SELECT ....."
response.write "<p> & strSql & "<p>":response.end
' set mD = conn.Execute(strSql)
%>

and only execute it when you are completely satisfied:

<%
strSql = "SELECT ....."
' response.write "<p> & strSql & "<p>":response.end
set mD = conn.Execute(strSql)
%>

and even then keep a copy of your database
for back-uping to a previous status quo.

Signature

Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

Reply to this Message

Roberto Franceschetti - 25 Jul 2009 16:10 GMT

bcap,

The description of what you'd like to have, and what the actual code is
doing, don't match... :-)

The code below looks fine. If there's a "StatusDisplay" specified, your
query gives all the results that match a specified SubLevel.
If there is no StatusDisplay specified, your query will display all records.

There is actually only one query that is being executed, and the query
syntax is dynamically created depending on the parameters specified. You
could rewrite it like this with just two lines of code

strSql = " SELECT CustID, FName, LName, SubLevel FROM CustInfo"
if request.querystring("StatusDisplay")<>"" then strSql = strSql & "
WHERE SubLevel=" & reque

but I personally prefer your syntax as it makes it easier for who's
reviewing the code to understand what the logic is doing.

The code is thus either selecting all records or only the ones matching
a SubLevel, depending on the presence of the "StatusDisplay" parameter.

For the "3) Declare that there are no records" you will have to test
for the oRS.EOF after executing the query (oRS will be your RecordSet
object).

Signature

Roberto Franceschetti
LogSat Software
Makers of Spam Filter ISP
http://www.logsat.com

> Thank you for your reply Evertjan!
>
[quoted text clipped - 12 lines]
> strSql = " SELECT CustID, FName, LName, SubLevel FROM CustInfo WHERE
> SubLevel=" & reque

Reply to this Message

bcap - 25 Jul 2009 17:40 GMT

Thank you all for your time and thoughts, very much appreciated!

Reply to this Message

Evertjan. - 25 Jul 2009 18:18 GMT

bcap wrote on 25 jul 2009 in microsoft.public.inetserver.asp.general:

> Thank you all for your time and thoughts, very much appreciated!

Please always quote on usenet.

This is not an idle request,
as it is pefectly unclear what you are replying on and to whom.

Not all news servers fill their items ina perfectr and timely fassion, and
some are deleting them more quickly than others.

Signature

Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

Reply to this Message

Dooza - 27 Jul 2009 09:14 GMT

> Thank you for your reply Evertjan!
>
[quoted text clipped - 14 lines]
>
> End If

SELECT CustID, FName, LName, SubLevel
FROM CustInfo
WHERE (@SubLevel IS NULL OR SubLevel = @SubLevel)

@SubLevel is your input variable, if it is NULL, then it lists
everything, if it isn't NULL, it tries matches it.

I don't know how you would put this in Dynamic SQL, as I would use a
Stored Procedure to do this normally.

Dooza

Reply to this Message

Webmaster Forum / ASP / General ASP Topics / July 2009

Writing a query ...