Discussion on:

Using SQL statements with ASP

It's nice to have someone put together a simple guide. I write SQL into ASP daily, but sometimes even I forget something as simple as how to write an "order by" part of a statement. It's nice to have a reference guide that's straightforward and easy to find what you need when you don't want to search through a book.

Wow. So that's everything I need to know (for now) in six concise pages. Thanks.

Please fix this code on your web page:

BETWEEN:
…WHERE ID BETWEEN 1 AND 10

Or you can use familiar mathematical equations:
…WHERE ID >= 1 AND ID >= 10

The last line should be
WHERE ID >= 1 AND ID = 10

Loved your article on SQL and ASP, however, I am working with JSP and SQL. I understand most of what I need to do to get the results with my SQL statement. It's the presentation that I am having a problem with. I want a header from one field and have several records for the detail that belongs to the header information from the same file. How do I seperate the results to get the header only once with the details listed out below? Anyone up to the challenge? Thanks.

In my experience you really need to use SQL to unleash the potential of ASP based appliactions.

The article was well written and covered the most common SQL techniques. I would like to see a follow-on article - "Putting it all together" - where a simple working app & database are used. In addition, an example of passing form variables(parameters) to a stored procedure would be nice, and use of parameterized statements in ASP code.

Thanks again for the article.

Although it may seem a bit verbose, you should ALWAYS use a command object when working with SQL. Here's why:

Let's say we have a page that displays information for a product whose ID is passed on the querystring or through a form.


The Queryfor this page is: SQL = "SELCT * from tbProducts WHERE ProductID=" & request("ProductID")

This is a huge security risk... Why you ask? Well what if I pass the following value for the product in the querystring?

productid=123;DELETE FROM tbProducts;

If the user account you're accessing your database with has permissions your products table is history. Even worse, many admins run their queries throught the sa account... then you could substitute master for tbProducts and the whole thingcomes crashing down.

Use an ADODB.Command object with parameters becauase it enforces data types and prevents the above vulnerability:

set cmd = server.createobject("adodb.command")
cmd.activeConnection = CONN_STR
cmd.commandType = adCmdText
cmd.commandText = "SELECT * FROM tbProducts WHERE ProductID=?"
cmd.parameters.append cmd.createParameter("ProductID", adInteger, adParamInput, ,request("ProductID"))
set rs = cmd.execute

Hope this helps bring more security to your asp apps.

The article suggested to me that one should use MS-Access/JET for building and testing the SQL statements.

The problem here is that JET SQL breaks from the ANSI standard in some problematic ways. The worst of which is the text delimiter. WhatJET SQL uses to keep a string of text together, ANSI SQL uses to refer to a table or field name.

For example:
ProductName = "Tree Trimmer"

In JET SQL, this would compare the contents of the field "ProductName" with the text "Tree Trimmer".In ANSI SQL, this would compare the contents of the field "ProductName" with a (probably non-existant) field "Tree Trimmer".

I've had several Access users send me JET queries, and ask me to put them in as T-SQL queries. I generally re-write the query from scratch, since the general editing would take longer.

For example if you have selected two unique IDs from a table and you want to perform a calculation on them by taking "x" from "y" you may have found the two prices in a query like this:-

"SELECT goodsid,goodsprice FROM pricelis WHERE goodsid="x" OR "y""

How can you then deduct "x" from "y"

Hey,
it is a gr8 article. really cool. makes u understand the basics of SQL.
hats down;)

Very nice article, with clear and understandable explanations and examples.
Great !
Congratulations.

All of the variables listed in your examples covered text fields. What if the variable is a created object that refers to a numberic field in the data base? How do you write the select statement to include this type of variable?

EI:
"Select thisname from tblnames where namesid = __" what goes in the blank. I have tried closing quotes right after the the equal sign and concat "&" with the variable following. but got an error that the statement ended too soon. I tried several different ways to place the variable inside the quotes but it still errored with too few paramenters. What do I do?

Wrap your numeric variable in a number-to-string conversion function (such as Str() or CStr()), like this:

"Select thisname from tblnames where namesid = " & Str(namesid_numvar)

This article did a great job of introducing (or reminding) some important concepts when writing SQL code. Unfortunately the topic of stored procedures did not receive the attention that it should have.

Anyone dealing with SQL server from an ASP environment should understand that stored procedures are not only a good idea - they are essential for several reasons:
1) Reusability - When data access and modification procedures are stored in a central place, it allows the same code to be easily re-used both in other parts of the same application and in other applications altogether.
2) Performance - Whenever a query is submitted to SQL Server, an execution plan must be generated before SQL Server can carry out the requested operation. When a stored procedure is compiled, this execution plan is saved, allowing subsequent calls to the procedure to skip the generation of the execution plan.
3) Security - Using stored procedures allows for better security. Permissions can be granted to stored procedures rather than tables, allowing the application to ensure that updates and retrievals are done in a controlled manner. Stored procedures also help address the risk posed by SQL injection attacks. A SQL injection attack is where the attacker is able to make use of design flaws in an application to execute SQL code on the system. This is generally accomplished by putting special characters followed by SQL commands into text fields. For a full explanation of the dangers posed by SQL injection, do a web search for "sql injection".